执行计划读书笔记

1. 以下行为会导致执行计划重新编译：

• 更改查询所引用的表的结构或模式
• 更改查询所使用的索引
• 删除查询所使用的索引
• 更新查询所使用的统计信息
• 调用函数，sp_recompile
• 将查询所引用的表中的键置于大量插入或删除中
• 对于触发器的表，插入或删除表的显式增长
• 混合的DDL和DML在一个单一的查询，通常称为递延编译
• 在查询的执行范围内更改设置选项
• 更改查询所使用的临时表的结构或模式
• 查询所使用的动态视图的更改
• 在查询中的光标选项的更改
• 一个远程数据集的变化，就像在一个分布式分区视图
• 当使用客户端游标，如果浏览选项被改变

2. 要完全清除缓存，运行此：

DBCCfreeproccache

 

SELECT[cp].[refcounts]

,[cp].[usecounts]

,[cp].[objtype]

,[st].[dbid]

,[st].[objectid]

,[st].[text]

,[qp].[query_plan]

FROMsys.dm_exec_cached_plans cp

CROSS APPLYsys.dm_exec_sql_text(cp.plan_handle) st

CROSS APPLYsys.dm_exec_query_plan(cp.plan_handle)

qp ;

 

我们可以看到这个查询的SQL调用和XML计划生成通过SQL执行。你可以直接使用XML或打开它作为一个图形化的执行计划。

 

3.        文本的计划

这是一个有点难读，但更多的信息是立即可用。有三个文本计划格式：

• showplan_all：一套完整的数据显示估计的查询执行计划
• showplan_text：提供一组有限的工具，像osql.exe使用数据。它只显示了估计的执行计划
• statistics profile：类似于showplan_all除了它所代表的数据的实际执行计划

 

4.        XML计划

XML计划提供了一个计划中最完整的数据集，所有的数据都显示在结构化的XML格式中。有两个品种的XML计划：

• showplan_xml：计划由优化器执行之前。
• statistics_xml：的实际执行计划的XML格式。

 

5.        示例代码

在下面的文字中，我将提供T-SQL代码，你鼓励自己跑。所有的源代码可以免费下载从简单的谈话出版网站（HTTP：/ / www.simple-talk .com /）。

例子是SQL 2005数据库AdventureWorks样本写的。你可以从这里得到一份http://www.codeplex.com/msftdbprodsamples

 

GRANT SHOWPLAN TO [username]

 

6.        得到估计的计划

我们可以找出什么是优化的估计是最不昂贵的计划在以下方式之一：

• 单击工具栏上的“显示估计执行计划”图标。
• 右键单击“查询”窗口，并从菜单中选择相同的选项。
• 点击菜单栏中的查询选项，然后选择相同的选项。
• 点击CTRL-L在键盘。

 

7.        获得实际计划

再次，有几种方法来生成我们的第一个图形实际执行计划：

• 点击工具栏上的图标称为“包括实际执行计划”。
• 在查询窗口中单击右键，并选择“包括实际执行计划”菜单项。
• 在查询菜单选择中选择相同的选项。
• control-m

 

你也会注意到，有一个箭头指向两个图标之间。您可以在这些箭头上悬停鼠标指针，它将显示它所表示的行数。

 

8.        工具提示

• 缓存的计划大小，有多少内存的计划所产生的此查询将在存储过程缓存中占用。这是一个调查缓存性能问题时有用的数字，因为你将能够看到哪些计划正在采取更多记忆。
• 估计运营商的成本，我们已经看到了这一百分比成本如图1所示。
• 预计子树成本–告诉我们积累的优化器分配到这一步和所有以前的步骤的成本，但记住从右到左阅读。这个数字是没有意义的现实世界，但是一个数学评价所用的查询优化，以确定问题的运营商的成本；它表示优化程序认为此的时间量运营商将采取。
• 估计的行数计算的基础上的可用的用于表或索引的优化程序的统计数据问题。

 

9.        算子的性质

更多信息可比在提示了。右键单击图形执行计划内的任何图标，并选择“属性”菜单项，以获得有关该操作的详细信息列表。

 

10.    获取估计的文本计划

要激活估计文本执行计划的文本版本，只需要在查询开始时发出以下命令：

SET SHOWPLAN_ALLON;

SET SHOWPLAN_ALLOFF;

 

11.    获取实际的文本计划

为了激活和失活的实际执行计划的文本，使用：

SET STATISTICSPROFILE ON

SET STATISTICSPROFILE OFF

 

12.    获取实际的和估计的XML计划

为了激活和停用的估计执行计划的XML版本，使用：

SET SHOWPLAN_XMLON

SET SHOWPLAN_XMLOFF

 

13.    解释XML计划

该链接是指向位于这里的XML文件的指针：

\Microsoft SQLServer\90\Tools\Binn\schemas\sqlserver\2003\03\showplan\showplanxml.xsd

你可以改变输出格式的查询|将结果保存到菜单选项。

 

14. 事件探查器的详细报道是这本书的范围，但更多的信息可以在网上找到

(http://msdn2.microsoft.com/en-us/library/ms173757.aspx).

15. 事件探查器中的执行计划事件
切换到“事件选择”选项卡，然后单击“显示所有事件”复选框。计划XML事件位于性能部分，所以点击加（+）号展开，选择。在计划XML点击事件。
虽然你可以捕获计划XML事件本身在分析器，它通常是更有用的如果你抓住它，连同其他一些基本的事件，如：

• RPC: Completed
• SQL:BatchStarting
• SQL:BatchCompleted

https://msdn.microsoft.com/zh-cn/library/ms175913.aspx

图形执行计划图标: 有三种通用图标：蓝色（用于迭代器）、橙色（用于游标）和绿色（用于 Transact-SQL 语言构造）。

GRANT SHOWPLAN TO [username]

Getting the Estimated Plan
We can find out what the optimizer estimates to be the least costly plan in one of following ways:

• Click on the "Display Estimated Execution Plan" icon on the tool bar.
• Right-click the query window and select the same option from the menu.
• Click on the Query option in the menu bar and select the same choice.
• Simply hit CTRL-L on the keyboard.

Getting the Actual Plan
Again, there are several ways to generate our first graphical Actual Execution Plan:

• Click on the icon on the tool bar called "Include Actual Execution Plan."
• Right-click within the query window and choose the "Include Actual Execution Plan" menu item.
• Choose the same option in the Query menu choice.
• Type Control-M.

You'll also note that there is an arrow pointing between the two icons. You can hover with the mouse pointer over these arrows and it will show the number of rows that it represents.

ToolTips

• Cached plan size – how much memory the plan generated by this query will take up in stored procedure cache. This is a useful number when investigating cache performance issues because you'll be able to see which plans are taking up more memory.
• Estimated Operator Cost – we've already seen this as the percentage cost in Figure 1.
• Estimated Subtree Cost – tells us the accumulated optimizer cost assigned to this step and all previous steps, but remember to read from right to left. This number is meaningless in the real world, but is a mathematical evaluation used by the query optimizer to determine the cost of the operator in question; it represents the amount of time that the optimizer thinks this operator will take.
• Estimated number of rows – calculated based on the statistics available to the optimizer for the table or index in question.

Operator Properties
More information is available than that presented in the ToolTips. Right-click any icon within a graphical execution plan and select the "Properties" menu item to get a detailed list of information about that operation.

Getting the Estimated Text Plan
To activate the text version of the Estimated text execution plan, simply issue the following command at the start of the query:
SET SHOWPLAN_ALL ON;
SET SHOWPLAN_ALL OFF;

Getting the Actual Text Plan
In order to activate and deactivate the text version of the Actual execution plan, use:
SET STATISTICS PROFILE ON
And:
SET STATISTICS PROFILE OFF

Getting the Actual and Estimated XML Plans
In order to activate and deactivate the XML version of the Estimated execution plan, use:
SET SHOWPLAN_XML ON
…
SET SHOWPLAN_XML OFF

For the XML version of the Actual plan, use:
SET STATISTICS XML ON
…
SET STATISTICS XML OFF

Interpreting XML Plans
The link is a pointer to an XML file located here:
\Microsoft SQL Server\90\Tools\Binn\schemas\sqlserver\2003\03\showplan\showplanxml.xsd
You can change the output format from the Query | Results To menu option.
-39/251

A clustered index stores not just the key structure, like a regular index, but also sorts and stores the data, which is the main reason why there can be only one clustered index per table.

Clustered Index Seek
we have a Clustered Index Seek operation carried out against the Person.Contact table, specifically on the PK_Contact_ContactId, which is happens to be both the primary key and the clustered index for this table.

Note on the ToolTips window for the Clustered Index Seek that the Ordered property is now true, indicating that the data was ordered by the optimizer.

A Key LookUp is always accompanied by the Nested Loop join operation that combines the results of the two operations.
61--251

If you look at the ToolTip above for the Index Seek, we see "Bmk1000" is in the Output list. This"Bmk1000" is telling us that this Index Seek is actually part of a query plan that has a bookmark lookup.

--Hashing

Hashing is a programmatic technique where data is converted into a symbolic form that makes it easier to be searched for quickly. For example, a row of data in a table can be programmatically converted into a unique value that represents the contents of the row. In many ways it is like taking a row of data and encrypting it. Like encryption, a hashed value can be converted back to the original data. (a Hash Match join occurs when SQL Server joins two tables by hashing the rows from the smaller of the two tables to be joined, and then inserting them into a hash table, then processing the larger table one row at a time against the smaller hashed table, looking for matches where rows need to be joined.) On the other hand, if both tables are very large, a Hash Match join can

be very inefficient as compared to other types of joins.

Hash Match joins also work well for tables that are not sorted on join columns, and they can be efficient in cases where there are no useable indexes.

For example, seeing a

Hash Match join in an execution plan sometimes indicates:

• a missing or incorrect index - adding appropriate indexes to the joined tables

• a missing WHERE clause - reducing the amount of data returned by adding a more restrictive WHERE clause

• a WHERE clause with a calculation or conversion that makes it non-sargeable (a commonly used term meaning that the search argument, "sarg" can't be used). This means it won't use an existing index.

a larger discrepancy indicates that your statistics are out of date and need to be updated. A large difference can lead to differences in the Estimated and Actual plans. (the estimated number of rows returned and the actual number of rows)

--Nested Loops Join

As long as the inner data set is small and the outer data set, small or not, is indexed, this becomes an extremely efficient join mechanism. Unless you have very large data sets, this is the type of join that you most want to see in an execution plan.

--Merge Join

A Merge Join is an efficient way to join two tables, when the join columns are presorted but if the join columns are not presorted, the query optimizer has the option of

a) sorting the join columns first, then performing a Merge Join, or

b) performing a less efficient Hash Join. The query optimizer considers all the options and generally chooses the execution plan that uses the least resources.

76-251

Sort

when sorting takes more than 50% of a query's total execution time:
1. we are missing a WHERE clause.
2. Is the sort really necessary? If not, remove it to reduce overhead.
3. you will save sorting overhead if you create the appropriate clustered index.

If you see that your server is performing a lot of sorts, and many Sort Warnings are generated, then you may need to add more RAM to your server, or to speed up tempdb access.

Hash Match (Aggregate)
a Hatch Match with an aggregate causes SQL Server to create a temporary hash table in memory in order to count the number of rows that match the GROUP BY column
if you want to speed the performance of a query with aggregations, the only way to do so in code is to add a WHERE clause to the query to limit the number of rows that need to be selected and aggregated.

Rebinds and Rewinds Explained
if any of the following six operators occur, the rebind and rewind counts are populated:
• Nonclustered Index Spool
• Remote Query
• Row Count Spool
• Sort
• Table Spool
• Table-Valued Function

If the physical operator is ON the inner side of a loop join used by an operator, then the sum of the rebinds and rewinds will equal the number of rows process on the outer side of a join used by the operator. Generally speaking, it is ideal if the rebind and rewind counts are as low as possible, as higher counts indicate more disk I/O.

Insert, Update and Delete Execution Plans
we had a read during the INSERT to check for referential integrity on the foreign key by a Clustered Index Seek
An Assert verifies that a particular condition exists. This one checks that the value of Expr1014 equals zero. Or, in other words, that 

the data that was attempted to be inserted into the Person.Address.StateProvinceId field matched a piece of data in the Person.StateProvince table; this was the referential check.

Creating an indexed view is, to say the least, a costly operation. Fortunately, this is a one-time operation that can be scheduled to occur when your server is less busy.

134/251

The execution plan is able to use a single operator to find and return all the data we need because the index is now covering, meaning it includes all the necessary columns.

Let's see the I/O output of the query. This can be done by navigating from the main menu, Query Query Options, selecting the advanced tab and activating the "Set Statistics IO" check box.


LOOP|MERGE|HASH JOIN
-OPTION ( LOOP JOIN )
-OPTION ( MERGE JOIN )
-OPTION ( HASH JOIN )
-OPTION ( FAST 10 )
-OPTION (FORCE ORDER)
155/251

DECLARE @City NVARCHAR(30)
SET @City = 'London'
SELECT *
FROM [Person].[Address]
WHERE [City] = @City
SET @City = 'London'
SELECT *
FROM [Person].[Address]
WHERE [City] = @City
OPTION ( OPTIMIZE FOR ( @City = 'Newark' ) )

ROBUST PLAN
This hint is used when you need to work with very wide rows. For example:
1. A row that contains one or more variable length columns set to very large size or even the MAX size allowed in 2005
2. A row that contains one or more large objects (LOB) such as BINARY, XML or TEXT data types.

 

USE PLAN
This hint simply substitutes any plan the optimizer may have created with the XML plan supplied with the hint. This is covered in great detail in Chapter 8.


Join Hints
A join hint provides a means to force SQL Server to use one of the three join methods that we've encountered previously, in a given part of a query. To recap, these join methods are:
• Nested Loop join: compares each row from one table ("outertable") to each row in another table ("inner table") and returns rows that satisfy the join predicate. Cost is proportional to the product of the rows in the two tables. Very efficient for smaller data sets.
• Merge join: compares two sorted inputs, one row at a time. Cost is proportional to the sum of the total number of rows. Requires an equi-join condition. Efficient for larger data sets
• Hash Match join: reads rows from one input, hashes the rows, based on the equi-join condition, into an in-memory hash table. Does the same for the second input and then returns matching rows. Most useful for very large data sets (especially data warehouses)

169/251