最近被一個客戶問到了這樣一個有趣的問題執行計劃在生成過程中使用了那些統計信息呢？針對這個問題，客戶也做了一些研究，并給了我下面兩篇博文作為切入點。 1. How to Find the Statistics Used to Compile an Execution Plan ( http://sqlblog.com/blogs/p
最近被一個客戶問到了這樣一個有趣的問題——執行計劃在生成過程中使用了那些統計信息呢？針對這個問題，客戶也做了一些研究，并給了我下面兩篇博文作為切入點。
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 – 打印出被加載的統計信息
9292 – 打印出從元數據中得到的統計信息的頭信息
當然，要看到這些信息，我們還需要打開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的數量。表Employees和Orders是用過EmployeeID來聯系起來的。所以，在Employees.EmployeeID和Orders.EmployeeID上建立的索引上面的統計信息會被用來產生執行計劃。當然除了他們，還應該有在字段Employees.FirstName上的統計信息，因為這個字段上沒有索引存在，所以系統會在這里自動生成一個統計信息。
需要注意的是，Trace Flag 9204和9292 只會在這個SELECT語句第一次被編譯的時候打印出這些統計信息的記錄。如果執行計劃已經存在于緩存中，那么這些統計信息的記錄則不會被打印出來。只有在用DBCC FREEPROCCACHE(plan_handle)清理了這條語句的緩存計劃后，再次執行的時候才會再次被打印出來。
第二篇博文介紹了另一個Trace Flag，8666。這個Trace Flag是這樣解釋的：
8666 – 將Showplan的詳細信息打印出來
這個Trace Flag會將一些關于統計信息的內部信息顯示出來。例如，
<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>
?
