Shulou(Shulou.com)06/01 Report--

Estimated vs. Actual query plans

We can have SQL server output whether the plan (for any display plan option-graphics, text, or XML) contains the actual running query.

A query plan generated without executing a query is called an "estimate execution plan" because SQL server may choose to recompile the query (recompilation may occur for various reasons) and may generate a different query plan at execution time.

Estimating the execution plan is helpful for a variety of purposes, such as viewing the query plan for a long-running query without waiting for it to complete; viewing the query plan for insert, update, or delete statements without changing the database state or acquiring any locks; or exploring the impact of various optimization tips on the query plan without actually running the query.

The estimated execution plan includes cardinality, row size, cost estimates, and a new feature in version 2012-estimated execution mode, which will be described later in this chapter.

Tip: the estimated cost reported by the optimizer is intended as a guide to compare the expected relative costs of different operators in the relative costs of a single query plan or two different plans. These unit estimates do not imply any absolute interpretation, such as milliseconds or seconds.

The query plan generated after the query is executed is called the actual execution plan. The actual implementation plan contains the same information as the estimated implementation plan plus the actual line count and the actual number of executions for each operator. Comparing the estimated value with the actual number of rows can help us identify cardinality estimation errors, which can lead to other planning problems.

Tip: the actual execution plan contains the same cost estimate as the estimated execution plan. Although SQL server actually executes the query plan when it generates the actual execution plan, these cost estimates are still the same as those generated by the optimizer and do not reflect the actual execution cost.

When you run an ad hoc query from the SQL server management studio or the SQLCMD command line utility, you can use several transact-sql commands to collect the display plan option output. With these commands, you can collect text and XML plans, as well as estimates and actual plans.

Table 10-1 lists all available set commands that enable the display schedule option.

We can also use extension events and XML plans to use dynamic management views (DMVs) to collect query plan information. These options are particularly useful when analyzing applications that do not have access to source code.

Chapter 12 discusses the DMVs that contains plan information.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.