最近被一個客戶問到了這樣一個有趣的問題執(zhí)行計劃在生成過程中使用了那些統(tǒng)計信息呢？針對這個問題，客戶也做了一些研究，并給了我下面兩篇博文作為切入點。 1. How to Find the Statistics Used to Compile an Execution Plan ( http://sqlblog.com/blogs/p
最近被一個客戶問到了這樣一個有趣的問題——執(zhí)行計劃在生成過程中使用了那些統(tǒng)計信息呢？針對這個問題，客戶也做了一些研究，并給了我下面兩篇博文作為切入點。
1.????? How to Find the Statistics Used to Compile an Execution Plan (
2.????? Statistics used in a cached query plan (
在查看了這兩篇博文后，我把其中的奧秘摘出來跟大家分享一下。
第一篇博文介紹了兩個Trace Flag， 9204和9292。這兩個Trace Flag是這樣解釋的：
9204 – 打印出被加載的統(tǒng)計信息
9292 – 打印出從元數(shù)據(jù)中得到的統(tǒng)計信息的頭信息
當然，要看到這些信息，我們還需要打開Trace Flag 3604才能讓這些顯示出來。以下面的腳本為例，
<ol class="dp-xml"><li class="alt"><span><span>USE Northwind </span></span></li><li><span> </span></li><li class="alt"><span>GO </span></li><li><span> </span></li><li class="alt"><span>DBCC FREEPROCCACHE() </span></li><li><span> </span></li><li class="alt"><span>GO </span></li><li><span> </span></li><li class="alt"><span>DBCC TRACEON(3604, 9292, 9204) </span></li><li><span> </span></li><li class="alt"><span>GO </span></li><li><span> </span></li><li class="alt"><span> </span></li><li><span> </span></li><li class="alt"><span>SELECT Employees.FirstName, COUNT(Orders.OrderID) </span></li><li><span> </span></li><li class="alt"><span>FROM Orders </span></li><li><span> </span></li><li class="alt"><span>INNER JOIN Employees </span></li><li><span> </span></li><li class="alt"><span> ON </span><span class="Attribute">Orders.EmployeeID</span><span> = </span><span class="attribute-value">Employees</span><span>.EmployeeID </span></li><li><span> </span></li><li class="alt"><span>WHERE </span><span class="attribute">Employees.FirstName</span><span> = </span><span class="attribute-value">'Steven'</span><span> </span></li><li><span> </span></li><li class="alt"><span>GROUP BY Employees.FirstName </span></li><li><span> </span></li><li class="alt"><span>GO </span></li></ol>
其返回結果為：
Stats header loaded: DbName: Northwind, ObjName: Orders, IndexId: 4, ColumnName: EmployeeID, EmptyTable: FALSE
?
Stats loaded: DbName: Northwind, ObjName: Orders, IndexId: 4, ColumnName: EmployeeID, EmptyTable: FALSE
?
Stats header loaded: DbName: Northwind, ObjName: Orders, IndexId: 5, ColumnName: EmployeeID, EmptyTable: FALSE
?
Stats header loaded: DbName: Northwind, ObjName: Employees, IndexId: 1, ColumnName: EmployeeID, EmptyTable: FALSE
?
Stats loaded: DbName: Northwind, ObjName: Employees, IndexId: 1, ColumnName: EmployeeID, EmptyTable: FALSE
?
Stats header loaded: DbName: Northwind, ObjName: Employees, IndexId: 4, ColumnName: FirstName, EmptyTable: FALSE
?
Stats loaded: DbName: Northwind, ObjName: Employees, IndexId: 4, ColumnName: FirstName, EmptyTable: FALSE
?
FirstName?
———- ———–
Steven???? 42
從這個SELECT語句中，我們不難看出查詢結果應該是員工Steven的First Name和這個員工有的所有的訂單Order的數(shù)量。表Employees和Orders是用過EmployeeID來聯(lián)系起來的。所以，在Employees.EmployeeID和Orders.EmployeeID上建立的索引上面的統(tǒng)計信息會被用來產(chǎn)生執(zhí)行計劃。當然除了他們，還應該有在字段Employees.FirstName上的統(tǒng)計信息，因為這個字段上沒有索引存在，所以系統(tǒng)會在這里自動生成一個統(tǒng)計信息。
需要注意的是，Trace Flag 9204和9292 只會在這個SELECT語句第一次被編譯的時候打印出這些統(tǒng)計信息的記錄。如果執(zhí)行計劃已經(jīng)存在于緩存中，那么這些統(tǒng)計信息的記錄則不會被打印出來。只有在用DBCC FREEPROCCACHE(plan_handle)清理了這條語句的緩存計劃后，再次執(zhí)行的時候才會再次被打印出來。
第二篇博文介紹了另一個Trace Flag，8666。這個Trace Flag是這樣解釋的：
8666 – 將Showplan的詳細信息打印出來
這個Trace Flag會將一些關于統(tǒng)計信息的內部信息顯示出來。例如，
<ol class="dp-xml"><li class="alt"><span><span>USE Northwind </span></span></li><li><span> </span></li><li class="alt"><span>GO </span></li><li><span> </span></li><li class="alt"><span>DBCC FREEPROCCACHE() </span></li><li><span> </span></li><li class="alt"><span>GO </span></li><li><span> </span></li><li class="alt"><span>DBCC TRACEON(8666) </span></li><li><span> </span></li><li class="alt"><span>GO </span></li><li><span> </span></li><li class="alt"><span> </span></li><li><span> </span></li><li class="alt"><span>SELECT Employees.FirstName, COUNT(Orders.OrderID) </span></li><li><span> </span></li><li class="alt"><span>FROM Orders </span></li><li><span> </span></li><li class="alt"><span>INNER JOIN Employees </span></li><li><span> </span></li><li class="alt"><span> ON </span><span class="attribute">Orders.EmployeeID</span><span> = </span><span class="attribute-value">Employees</span><span>.EmployeeID </span></li><li><span> </span></li><li class="alt"><span>WHERE </span><span class="attribute">Employees.FirstName</span><span> = </span><span class="attribute-value">'Steven'</span><span> </span></li><li><span> </span></li><li class="alt"><span>GROUP BY Employees.FirstName </span></li><li><span> </span></li><li class="alt"><span>GO </span></li></ol>
?
