SQL server 數據庫統計資訊在執行後降級

我在查詢我的 SQL Server DB 時遇到了超時，特別是一個表。我嘗試更新統計資訊，並修復了幾天的性能問題。

我開始深入探勘，發現有一個計劃的同步任務，它從 Web 服務中檢索數據並更新數據庫。此任務為此目的使用實體框架。幾位同事試圖解決這個問題，但到目前為止沒有運氣。我想退後一步。

1. 把EF扔出畫面
2. 這不應該是一段 C#/EF 程式碼來執行此操作，但對於 ETL 工具來說是一項完美的工作。增量更新應該（根據我對數據庫統計的理解來完成這項工作）。到目前為止，每次計劃任務執行時，都會更新所有內容。

有人可以在這種情況下為我提供更多資訊或見解以獲取線索，以便我可以在引入新工具和解決方案之前嘗試修復程式碼。

從頭開始：

SQL Server 在優化階段使用統計對象為結果集步驟生成基數估計。基數估計用於生成查詢計劃，這是 SQL Server 物理訪問和檢索數據以滿足請求的邏輯計劃。一般來說，更準確的統計數據會帶來更好的計劃，因為 SQL Server 根據這些基數估計選擇要使用的邏輯和物理運算符。良好的估計使 SQL Server 有更好的機會為它期望處理的數據量選擇最佳操作。

SQL Server Statistics 對象由標題、密度向量和直方圖組成。估計基數時，在不同情況下使用密度向量和直方圖。

修改（通過INSERT、UPDATE和DELETE）必然會更改數據配置文件（INSERT並DELETE通過修改表本身的行UPDATE數，以及通過修改滿足條件的行數），因此這些“寫入”中的任何一個最終都會影響優化和查詢執行的計劃階段。對於少量的寫入，這通常不是問題，因為這里和那裡的幾行不一定會改變 SQL Server 使用的運算符。一個值得注意的例外是，如果寫入的數據是“添加”到現有數據集的插入（例如IDENTITY列，或插入第二天的數據）。這稱為升序鍵，可能會出現問題，因為值存在於直方圖的邊界之外，並且（直到 SQL Server 2014）SQL Server 不知道將滿足使用新鍵作為請求的請求的行數過濾值。有時，您會聽到將許多修改視為“陳舊”的統計數據。

因為您沒有共享計劃同步任務的性質，所以我無法告訴您為什麼更新統計資訊對您有幫助。它可能是升序鍵，在這種情況下，更新將在直方圖中包含新的鍵值。或者可能是您已經壓縮了足夠的數據來影響現有值的結果集的基數，但是您的記憶體計劃不適合這個新的數據量。它還取決於您遇到這種性能下降的查詢。他們是查詢新數據、舊數據還是所有數據？此外，我看到 Entity Framework 生成了一些糟糕的 SQL，這增加了優化和規劃階段的複雜性，因此當糟糕的 SQL 與“陳舊”的統計資訊結合使用時，你會得到一個糟糕的計劃，並且會隨著時間的推移而降級（如果你’ 再幸運）。因此，廢棄 EF 的呼籲並不是一個壞建議，但讓我們繼續關注統計部分。

根據您的 SQL Server 版本，更新統計資訊可能會導致計劃記憶體失效。AUTO_UPDATE_STATISTICS如果您正在執行 2005->2008R2，則只有在數據庫上啟用了數據庫選項時，相關計劃才會發生失效。此外，對於 >= 2012 的版本，如果對 stats objects 進行了任何修改，您將在 stats 更新後獲得一個無效的計劃。這意味著如果您沒有修改任何數據，盲目地執行統計更新（尤其是在 2012 年及更新版本）不會為您提供更好的計劃。

那麼如何檢查是否修改了任何數據？這是我使用的一個查詢（您特別感興趣的是該ModificationCounter列）：

select 
   SchemaName = sch.name,
   TableName = t.name,
   StatName = s.name,
   IndexName = i.name,
   StatLeadingColumn = index_col(quotename(sch.name)+'.'+quotename(t.name),s.stats_id,1),
   StatColumns = sc.StatColumns,
   IsFiltered = s.has_filter,
   StatFilter = s.filter_definition,
   IsAutoCreated = s.auto_created,

   StatLastUpdated = sp.last_updated,
   ModificationCounter = sp.modification_counter,
   StatRows = sp.rows,
   StatRowsSampled = sp.rows_sampled,
   StatUnfilteredRows = sp.unfiltered_rows,
   StatSteps = sp.steps
from sys.stats s 
join sys.tables t 
   on s.object_id = t.object_id
join sys.schemas sch 
   on t.schema_id = sch.schema_id
cross apply sys.dm_db_stats_properties(s.object_id,s.stats_id) sp
cross apply (
               select
               stuff(
                       (
                           select ', '+c.name
                           from sys.stats_columns scol
                           join sys.columns c
                               on scol.object_id = c.object_id
                               and scol.column_id = c.column_id
                           where scol.object_id = s.object_id
                               and scol.stats_id = s.stats_id
                           for xml path(''), type
                       ).value('.','nvarchar(max)')
                       ,1,2,''
                   ) StatColumns
           ) sc
left join sys.indexes i 
   on s.object_id = i.object_id
   and s.name = i.name

為了執行上述內容，您需要成為db_owner或db_ddladmin固定數據庫角色的成員。伺服器角色顯然也可以執行它sysadmin，但我通常不提倡隨機添加人員到該伺服器角色。

要檢查過濾器值是否在直方圖中，您可以執行

DBCC SHOW_STATISTICS('dbo.TableName','StatisticsName') WITH HISTOGRAM;

這將輸出直方圖步長邊界值 ( RANGE_HI_KEY)。如果過濾器大於此列的最後一個值，則它超出了表的邊界，並且您面臨基數估計非常不正確的風險。

該行為還有一個警告AUTO_UPDATE_STATISTICS：它根據表中的行數進行更新。

• 如果表有 0 行，則添加任何行時統計資訊將“過時”（無效）。
• 如果表有 <= 500 行（上次收集統計資訊時），當統計資訊的前導列上的數據更改超過 500時，統計資訊將失效。
• 如果表的行數 > 500 行（上次收集統計資訊時），則當對 (500 + 表基數的 20%) 的前導列進行修改時，統計資訊將失效。

當滿足這些門檻值時，統計資訊無效，相關查詢計劃也無效。下次執行查詢時，SQL Server 將看到失效並嘗試載入統計資訊以製定新計劃。如果統計資訊也無效，SQL Server 將在進行下一步優化和規劃之前以預設速率對數據進行採樣。您可以通過啟用數據庫選項來解決此問題AUTO_UPDATE_STATISTICS_ASYNC，但隨後您會遇到另一個執行的無效查詢計劃。

還建議將跟踪標誌 2371作為潛在修復。這會將上述門檻值從階梯線性門檻值更改為對數門檻值，當修改計數器命中時觸發更新SQRT(1000*Table Cardinality)（埋在本白皮書中）。

如果您啟用了 AUTO_UPDATE_STATISTICS，那麼是的，您的統計資訊應該在 20% 的數據被修改後更新。但是，如果您知道是這個過程使您的統計數據失控，您也可以在該過程執行後自行更新它們。

https://msdn.microsoft.com/en-us/library/ms187348.aspx

程序完成後，對受影響的表呼叫 UPDATE STATISTICS。

引用自：https://dba.stackexchange.com/questions/124247