[SQL Server][Statistics]統計值(五)重複統計值(Overlapping Statistics)

2018-01-14

最近在駐點的客戶端遇到一個很神奇的問題，剛匯入大筆資料的隔天(50%以上的異動量)，同事有一支程式跑了很久都沒執行完，想辦法和AP人員清理舊資料、加索引，更新統計值後，程式瞬間秒殺，但過沒多久，同事另一支類似功能的程式又塞住，這次即使更新統計值、清除plan cache 、Recompile SQL都沒效。

同事的SQL在執行時，請DBA大人幫忙觀察，沒有等待資源(wait resource)、沒有阻塞(block)，也沒有死結(deadlock)，另外一方面也同步開case請微軟技術前線支援，隔天提供相關資訊後，很快收到技術支援工程師的回覆，和DBA大人商量後馬上服用官方解藥，最後，果然藥到病除，身體健康，快來筆記。

統計值系列之五:　重覆統計值

統計資訊(Statistics)

統計值是描述欄位值與索引欄位值的分佈統計，也是SQL Query Optimizer進行基數預估(cardinality estimate)的分析來源，透過基數預估，進而決策出優秀的執行計畫來擷取或更新資料。

SQL資料庫預設是自動建立(Auto Create)與自動更新(Auto update)欄位統計資訊，除了欄位統計資料(_WA_sys)；當索引建立時，系統也會建立鍵值欄位的統計值。

更新統計值(Update Statistics)

在SQL2016以前，當資料更新量超過資料表總筆數的20% + 500筆時，系統會在下一次查詢時自動更新。

除了自動更新，當資料庫重建索引、大量資料匯入或是資料庫搬家後，DBA也會排定工作進行統計值更新來確保統計值的新鮮度。

清除重複統計值(Overlapping Statistics)

這次碰到同一個欄位名稱的統計值重複，一個是系統自動建立的欄位統計(_WA_Sys)資訊，另一個是索引中的欄位統計值。

執行觀察統計值的預存程序

EXEC usp_showStatistics 'RealMadridClub'

就像以下的統計值資訊，NAME的統計重複了!

後來微軟技術支援工程師有提供語法查詢overlapping的統計值，另外從國外網友kendalvandyke blog也找到類似的語法來刪除:

WITH    autostats(object_id, stats_id, name, column_id)
            AS (
                SELECT  sys.stats.object_id ,
                        sys.stats.stats_id ,
                        sys.stats.name ,
                        sys.stats_columns.column_id
                FROM    sys.stats
                        INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id
                                                        AND sys.stats.stats_id = sys.stats_columns.stats_id
                WHERE   sys.stats.auto_created = 1
                        AND sys.stats_columns.stats_column_id = 1
                )
    SELECT  OBJECT_NAME(sys.stats.object_id) AS [Table] ,
            sys.columns.name AS [Column] ,
            sys.stats.name AS [Overlapped] ,
            autostats.name AS [Overlapping] ,
            'DROP STATISTICS [' + OBJECT_SCHEMA_NAME(sys.stats.object_id) + '].[' + OBJECT_NAME(sys.stats.object_id) + '].[' + autostats.name + ']'
    FROM    sys.stats
            INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id
                                            AND sys.stats.stats_id = sys.stats_columns.stats_id
            INNER JOIN autostats ON sys.stats_columns.object_id = autostats.object_id
                                    AND sys.stats_columns.column_id = autostats.column_id
            INNER JOIN sys.columns ON sys.stats.object_id = sys.columns.object_id
                                        AND sys.stats_columns.column_id = sys.columns.column_id
    WHERE   sys.stats.auto_created = 0
            AND sys.stats_columns.stats_column_id = 1
            AND sys.stats_columns.stats_id != autostats.stats_id
            AND OBJECTPROPERTY(sys.stats.object_id, 'IsMsShipped') = 0;

刪除重複但由系統自動建立的統計值(_WA_sys)

刪除重複的統計值

DROP STATISTICS [dbo].[RealMadridClub].[_WA_Sys_00000002_3E52440B]

刪除後，交易又恢復正常。

小結:

暫時參不透這次問題是否因重複統計值影響到執行計畫編譯? 還是執行時更新統計值太久? 自動建立的欄位統計值過時導致Optimizer產生壞的執行計畫?
定時清理重複且由SQL自動建立的統計資訊(_WA_sys*)。
重複的統計資訊可能會影響執行計畫編譯。
更新欄位統計資訊時，Optimizer將會採取資料表掃瞄(table scan)的方式，也可能會影響交易時間。

花蓮六十石山

參考

Tuning Tip: Identifying Overlapping Statistics

What are the SQL Server _WA_Sys... statistics?

sql query optimizer

回首頁

史丹利好熱

巧遇吉他手