自增長(zhǎng)鍵列統(tǒng)計(jì)信息的處理方法

這篇文章通過文字代碼的形式講解了如何處理用自增長(zhǎng)鍵列的統(tǒng)計(jì)信息。我們都知道，在SQL Server里每個(gè)統(tǒng)計(jì)信息對(duì)象都有關(guān)聯(lián)的直方圖。直方圖用多個(gè)步長(zhǎng)描述指定列數(shù)據(jù)分布情況。在一個(gè)直方圖里，SQL Server最大支持200的步長(zhǎng)，但當(dāng)你查詢的數(shù)據(jù)范圍在直方圖最后步長(zhǎng)后，這是個(gè)問題。我們來看下面的代碼，重現(xiàn)這個(gè)情形：

 -- Create a simple orders table
 CREATE TABLE Orders
 (
  OrderDate DATE NOT NULL,
  Col2 INT NOT NULL,
  Col3 INT NOT NULL
 )
 GO
 
 -- Create a Non-Unique Clustered Index on the table
 CREATE CLUSTERED INDEX idx_CI ON Orders(OrderDate)
 GO
 
 -- Insert 31465 rows from the AdventureWorks2008r2 database
 INSERT INTO Orders (OrderDate, Col2, Col3) SELECT OrderDate, CustomerID, TerritoryID FROM AdventureWorks2008R2.Sales.SalesOrderHeader
 GO
 
 -- Rebuild the Clustered Index, so that we get fresh statistics.
 -- The last value in the Histogram is 2008-07-31.
 ALTER INDEX idx_CI ON Orders REBUILD
 GO
 
 -- Insert 200 additional rows *after* the last step in the Histogram
 INSERT INTO Orders (OrderDate, Col2, Col3)
 VALUES ('20100101', 1, 1)
 GO 200

在索引重建后，我們?cè)倏聪轮狈綀D，我們發(fā)現(xiàn)最后步進(jìn)的值是2008-07-31。

你已經(jīng)看到，在最后步進(jìn)到表里后，我們插入了200條額外記錄。這樣的話，直方圖并沒有真實(shí)反饋實(shí)際的數(shù)據(jù)分布情況，但SQL Server還是要進(jìn)行基數(shù)計(jì)算。我們現(xiàn)在來看看在不同版本里SQL Server是如何處理這個(gè)問題的。

在SQL Server 2014之前，基數(shù)計(jì)算對(duì)此問題的處理非常簡(jiǎn)單：SQL Server估計(jì)行數(shù)為1，你可以從下面的圖片里看到。

點(diǎn)擊工具欄的顯示包含實(shí)際的執(zhí)行計(jì)劃，并執(zhí)行如下查詢：

 自SQL Server 2005 SP1起，查詢優(yōu)化器可以標(biāo)記1列為自增長(zhǎng)（Ascending）來克服剛才介紹的限制。如果你用自增長(zhǎng)列值更新了統(tǒng)計(jì)信息對(duì)象3次，那列就會(huì)被標(biāo)記為自增長(zhǎng)列。為了看有沒有列標(biāo)記為自增長(zhǎng)，你可以使用跟蹤標(biāo)記2388。當(dāng)你啟用這個(gè)跟蹤標(biāo)記，DBCC SHOW_STATISTICS的輸出就改變了，有額外列返回。

現(xiàn)在下面的代碼更新統(tǒng)計(jì)信息3次，每次用自增長(zhǎng)鍵列值在我們聚集索引末尾插入行。

 -- => 1st update the Statistics on the table with a FULLSCAN
  UPDATE STATISTICS Orders WITH FULLSCAN
  GO
  
  -- Insert 200 additional rows *after* the last step in the Histogram
  INSERT INTO Orders (OrderDate, Col2, Col3)
 VALUES ('20100201', 1, 1)
  GO 200
  
 -- => 2nd update the Statistics on the table with a FULLSCAN
 UPDATE STATISTICS Orders WITH FULLSCAN
 GO
 
 -- Insert 200 additional rows *after* the last step in the Histogram
 INSERT INTO Orders (OrderDate, Col2, Col3)
 VALUES ('20100301', 1, 1)
 GO 200
 
 -- => 3rd update the Statistics on the table with a FULLSCAN
 UPDATE STATISTICS Orders WITH FULLSCAN
 GO

然后，當(dāng)我們執(zhí)行DBCC SHOW_STATISTICS命令，你會(huì)看到SQL Server已講那列標(biāo)記為Ascending。

現(xiàn)在當(dāng)你再次執(zhí)行查詢不是直方圖范圍的數(shù)據(jù)時(shí)，沒有任何改變。為了使用標(biāo)記為自增長(zhǎng)鍵列，你要啟用另外一個(gè)跟蹤標(biāo)記-2389。如果你啟用這個(gè)跟蹤標(biāo)記，查詢優(yōu)化器就是密度向量（Density Vector）來進(jìn)行基數(shù)計(jì)算。

-- Now we query the newly inserted range which is currently not present in the Histogram.
-- With Trace Flag 2389, the Query Optimizer uses the Density Vector to make the Cardinality Estimation.
SELECT * FROM Orders
WHERE OrderDate = '20100401'
OPTION (RECOMPILE, QUERYTRACEON 2389)
GO

來看下現(xiàn)在的表密度：

現(xiàn)在的表密度是0.0008873115，因此查詢優(yōu)化器的估計(jì)行數(shù)是28.4516：0.0008873115*（32265-200）。

這雖然不是最好的結(jié)果，但比估計(jì)行數(shù)1好很多！

（這里有問題，我本地是SQL Server 2008r2，測(cè)試估計(jì)行數(shù)還是1，不知原因，望知道的朋友解釋下，多謝！)

SQL Server 2014
在SQL Server 2014引入的一個(gè)新功能是新基數(shù)計(jì)算。新基數(shù)計(jì)算對(duì)于自增長(zhǎng)鍵問題的處理非常簡(jiǎn)單：默認(rèn)不使用任何跟蹤標(biāo)記，來使用統(tǒng)計(jì)信息對(duì)象的密度向量來進(jìn)行基數(shù)計(jì)算。下面查詢啟用2312跟蹤標(biāo)記的基數(shù)計(jì)算來運(yùn)行同個(gè)查詢。

1 -- With the new Cardinality Estimator SQL Server estimates 28.4516 rows at the Clustered Index Seek operator.
2 SELECT * FROM Orders
3 WHERE OrderDate = '20100401'
4 OPTION (RECOMPILE, QUERYTRACEON 2312)
5 GO

我們來看這里的基數(shù)計(jì)算，你會(huì)看到查詢優(yōu)化器再次估計(jì)行數(shù)是28.4516，但這一次沒表上自增長(zhǎng)。這是SQL Server 2014的自帶功能。

（SQL Server 2014測(cè)試失敗，估計(jì)行數(shù)也是1……）

在這篇文章，我向你展示了SQL Server的查詢優(yōu)化器如何處理自增長(zhǎng)鍵問題。在SQL Server 2014之前，你需要啟用2389跟蹤標(biāo)記來獲得更好的基數(shù)計(jì)算——這樣的話那列會(huì)標(biāo)記為自增長(zhǎng)（ascending）。SQL Server 2014，查詢優(yōu)化器默認(rèn)就使用密度向量來進(jìn)行基數(shù)計(jì)算，這樣就方便很多。我希望你對(duì)此有所收獲，在SQL Server里如何處理自增長(zhǎng)鍵列問題你會(huì)有更好的想法。

希望對(duì)大家有所啟迪，謝謝。