統計值與執行計畫的關係 - SQL Server

Posted by: bart30508 | in SQL Server | 8 months, 3 weeks ago |

上一篇找到一大串勒勒長的執行計畫，這次要來筆記一下統計值是怎麼一回事以及如何優化。

什麼是統計值

SQL Server的QO(Query Optimizer)透過cost-based model來選擇一個最合適計畫(估算成本最低)來執行查詢

參考什麼做選擇?

每個統計資料物件都是針對一或多個資料表資料行的清單所建立，其中包含「長條圖」以顯示第一個資料行中的值分佈狀態。

多個資料行的統計資料物件也會儲存這些資料行之間值相互關聯的相關統計資料。 這些相互關聯統計資料 (或稱「密度」) 衍生自資料行值之相異資料列的數目。

長條圖

「長條圖」可測量資料集中每一個相異值的發生頻率。

密度

密度是給定資料行或組合資料行中的重複項目數量資訊，其計算方式為 1/(相異值數目)。 查詢最佳化工具會使用密度來增強查詢的基數預估，這些查詢會從相同的資料表或索引檢視表傳回多個資料行。 當密度降低時，值的選擇性會增加。 例如，在表示車種的資料表中，許多車種的製造商都是相同的，但每輛車都有一個唯一的汽車識別號碼。 由於 VIN 的密度比製造商低，因此 VIN 的索引會比製造商的索引更具選擇性。

「頻率」是統計資料物件第一個索引鍵資料行中每一個相異值的發生次數資訊，其計算方式為資料列計數乘以密度。 如果資料行具有唯一值，則其最大頻率為 1。

密度向量針對統計資料物件中資料行的每個前置詞各包含一個密度。 例如，如果統計資料物件具有 **CustomerId**、**ItemId** 和 **Price** 等索引鍵資料行，就會根據下列每一個資料行前置詞來計算密度。

(CustomerId)與 CustomerId 的值相符的資料列(CustomerId, ItemId)與 CustomerId 和 ItemId 的值相符的資料列(CustomerId, ItemId, Price)與 CustomerId、ItemId 和 Price 的值相符的資料列

這也是為什麼PK要選擇選擇性高(也就是密度低)的欄位!

注意每個執行計畫是使用CPU來做估算，使用過的執行計畫一般會Cache起來已便下次使用

QO會依照基數估計(Cardinality estimation)來產生執行計畫，基數估計扮演一個很重要的角色

SQL Server統計值是對於每個Index或欄位資料分布做紀錄，任何型態都支援統計值資料.

過期的統計值資料導致QO誤判產生不良執行計畫

在我們建立Index時，統計值會自動創建。此外當欄位在查詢裡被使用(作為WHERE條件的一部分,group by子句,join條件)統計值會被自動建立

何時建立統計值?

每個索引都會有自己個統計資訊，在UI查看統計資訊如下圖.

如果查詢條件欄位沒有統計值，Query Optimizer會在編譯前將統計值建立或有門檻條件性的更新。

如果查詢條件欄位沒有統計值，Query Optimizer會在編譯前將統計值建立或有門檻條件性的更新。

如下圖我們使用C3沒有建立索引欄位來查詢，SQL-Server就會幫我們自動產生\_WA\_Sys\_00000003\_6EF57B66

這個統計資訊來讓QO產生執行計畫時有個依據.

查詢資料表統計值 & 了解統計值欄位含意

想要查詢資料表索引的統計值可以輸入DBCC SHOW\_STATISTICS，第一個參數是查詢資料表，第二個參數是查詢的索引或統計值.

DBCC SHOW\_STATISTICS('dbo.posts','PK\_Posts')

使用上語法查詢會出現三個結果集

第一個結果集

顯示出此統計值的基本資訊其中有幾個重要的欄位

  • 最後更新時間
  • 密度

以上是微軟上的解釋,但我實際計算後發現,顯示的數值是:

Density = distinct values / all values , 與微軟統計值篇的"頻率"相符

  • 統計值Key的欄位大小

第二個結果集

密度分布,使用常數查詢,直接使用子方圖進行資料筆數估計

第三個結果集

  • RANGE_HI_KEY`：每個區域資料的分佈。
  • RANGE_ROWS``：上圖列出(120 + 1) ~(126)區間的Row是57.175筆資料
  • EQ_ROWS``：代表這個區間值。
  • DISTINCT_RANGE_ROWS``：代表這個區間裏面有幾個特殊/單一(Unique)值。
  • AVG_RANGE_ROWS``：代表這個區間每個特殊值平均有幾筆

觸發統計值更新

假如有設定自動更新統計值，異動資料筆數超過 (500 + 20%)資料，會觸發統計值更新

如果是大資料表容易造成統計值不準確，因為要達到自動更新門檻有點困難

找尋是否有同一個欄位名稱的統計值重複，如果有建立刪除語法

--查詢Table所有統計值

CREATE PROCEDURE usp\_showStatistics   
 [@TableName](http://twitter.com/TableName "Twitter profile for @TableName") varchar(50)  
AS   
BEGIN  
SELECT  
 s.name AS 'Statistics'  
 ,so.name AS TableName  
 ,COL\_NAME(scol.object\_id, scol.column\_id) AS 'Column'  
 ,s.auto\_created  
 ,s.user\_created  
 ,sp.last\_updated  
 ,sp.rows AS RowsInTableWhenUpdated  
 ,sp.rows\_sampled  
 ,sp.modification\_counter  
FROM sys.stats s (NOLOCK)  
JOIN sys.objects so  
 ON s.object\_id = so.object\_id  
JOIN sys.stats\_columns AS scol (NOLOCK)  
 ON s.stats\_id = scol.stats\_id  
  AND s.object\_id = scol.object\_id  
JOIN sys.tables AS tab (NOLOCK)  
 ON tab.object\_id = s.object\_id  
CROSS APPLY [sys].[dm\_db\_stats\_properties](so.object\_id, s.stats\_id) [sp]  
WHERE   
   so.name = [@TableName](http://twitter.com/TableName "Twitter profile for @TableName")  
--s.name like '\_WA%'  
--and stats\_column\_id = 1  
ORDER BY so.name, s.name  
GO



EXEC usp\_showStatistics 'RealMadridClub'  
GO



--產生刪除語法  
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;

註 : modification_counter

This column contains the total number of modifications for the leading statistics column since the last time statistics were updated.

更新統計值優化

SQL2017之前版本建議啟用TF2371

DBCC TRACEON (2371,-1)--啟用TF2371

DBCC TRACESTATUS (2371)--查詢TF2371狀態

DBCC TRACEOFF (2371, -1)-- 關閉TF2371

啟動後大資料就不會只使用(500 + 20%)條件來更新統計值，會依照資料表筆數來判斷(如下圖)

假如使用執行計畫(估計值)很不準確可以查看，當前的統計值是否是正確

如果要更新統計值可以使用下面語法.

**UPDATE** **STATISTICS** dbo.T1; --更新統計值 DBCC SHOW\_STATISTICS ('dbo.T1', idx1) --顯示統計值

啟動非同步更新統計值(先執行再更新統計值。)

當您執行變更資料分佈的作業時 (例如截斷資料表，或大量更新大部分的資料列)，請考慮使用同步統計資料。

如果您沒有在完成此作業之後更新統計資料，使用同步統計資料將可在針對變更的資料執行查詢之前，確保統計資料處於最新狀態。

在下列狀況中，請考慮使用非同步統計資料來達到更可預測的查詢回應時間：

  • 您的應用程式經常會執行相同的查詢、相似的查詢或相似的快取查詢計劃。 相較於使用同步統計資料更新，使用非同步統計資料更新可能會讓您更容易預測查詢的回應時間，因為查詢最佳化工具不需要等候最新的統計資料，就可以執行傳入的查詢。 這樣會避免延遲某些查詢，但無法避免延遲其他查詢。
  • 您的應用程式遇到等候更新統計資料之一或多個查詢所造成的用戶端要求逾時。 在某些情況下，等候同步統計資料可能會造成具有彙總逾時的應用程式失敗。

查詢資料庫統計值有關的屬性(預設)

SELECT is\_auto\_create\_stats\_on  
               ,is\_auto\_create\_stats\_incremental\_on  
                           ,is\_auto\_update\_stats\_on  
                           ,is\_auto\_update\_stats\_async\_on  
FROM sys.databases WHERE name = 'StatisticsDb';

啟動非同步更新統計值

USE [master] GO ALTER DATABASE [StatisticsDb] SET AUTO\_UPDATE\_STATISTICS\_ASYNC ON WITH NO\_WAIT GO

### 改善變數和函式的基數估計值

若要改善變數和函數的基數估計值，請遵循下列指導方針：

  • 如果查詢述詞使用區域變數，請考慮將查詢重新撰寫成使用參數而非區域變數。 當查詢最佳化工具建立查詢執行計畫時，並無法得知區域變數的值。 當查詢使用參數時，查詢最佳化工具就會使用傳遞給預存程序之第一個實際參數值的基數估計值。
  • 請考慮使用標準資料表或暫存資料表來保存多重陳述式資料表值函式 (mstvf) 的結果。 查詢最佳化工具不會針對多重陳述式資料表值函式建立統計資料。 透過這種方法，查詢最佳化工具就可以建立資料表資料行的統計資料，然後使用它們來建立較佳的查詢計劃。
  • 請考慮使用標準資料表或暫存資料表當做資料表變數的取代項目。 查詢最佳化工具不會針對資料表變數建立統計資料。 透過這種方法，查詢最佳化工具就可以建立資料表資料行的統計資料，然後使用它們來建立較佳的查詢計劃。 當您在判斷要使用暫存資料表或資料表變數時，存在權衡取捨。在預存程序中使用的資料表變數會讓預存程序重新編譯的次數比暫存資料表更少。 根據應用程式而定，使用暫存資料表來取代資料表變數可能不會改善效能。
  • 如果預存程序包含使用傳入參數的查詢，請避免在查詢中使用之前，變更預存程序中的參數值。 查詢的基數估計值是以傳入參數而非更新的值為基礎。 若要避免變更參數值，您可以將查詢重新撰寫成使用兩個預存程序。

  • 例如，下列預存程序 Sales.GetRecentSales 會在 @date 為 NULL 時變更 @date 參數的值。

    USE AdventureWorks2012;
    GO
    IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetRecentSales;
    GO
    CREATE PROCEDURE Sales.GetRecentSales (@date datetime)
    AS BEGIN
    IF @date IS NULL
    SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
    SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
    WHERE h.SalesOrderID = d.SalesOrderID
    AND h.OrderDate > @date
    END
    GO

如果預存程序 **Sales.GetRecentSales** 的第一次呼叫傳遞 NULL 給 **@date** 參數，查詢最佳化工具就會使用 **@date = NULL** 的基數估計值來編譯此預存程序，即使沒有使用 **@date = NULL** 來呼叫查詢述詞也一樣。

這個基數估計值可能會與實際查詢結果中的資料列數目具有大幅差異。

因此，查詢最佳化工具可能會選擇到次佳的查詢計劃。 為了協助避免這種情況發生，您可以將此預存程序重新撰寫成兩個程序，如下所示：

USE AdventureWorks2012;    
GO    
IF OBJECT\_ID ( 'Sales.GetNullRecentSales', 'P') IS NOT NULL    
    DROP PROCEDURE Sales.GetNullRecentSales;    
GO    
CREATE PROCEDURE Sales.GetNullRecentSales ([@date](http://twitter.com/date "Twitter profile for @date") datetime)    
AS BEGIN    
    IF [@date](http://twitter.com/date "Twitter profile for @date") is NULL    
        SET [@date](http://twitter.com/date "Twitter profile for @date") = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))    
    EXEC Sales.GetNonNullRecentSales [@date](http://twitter.com/date "Twitter profile for @date");    
END    
GO    
IF OBJECT\_ID ( 'Sales.GetNonNullRecentSales', 'P') IS NOT NULL    
    DROP PROCEDURE Sales.GetNonNullRecentSales;    
GO    
CREATE PROCEDURE Sales.GetNonNullRecentSales ([@date](http://twitter.com/date "Twitter profile for @date") datetime)    
AS BEGIN    
    SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d    
    WHERE h.SalesOrderID = d.SalesOrderID    
    AND h.OrderDate > [@date](http://twitter.com/date "Twitter profile for @date")    
END    
GO

使用計劃指南來改善基數估計值

對於某些應用程式而言，查詢設計指導方針可能不適用，因為您無法變更查詢或者使用 RECOMPILE 查詢提示可能會導致重新編譯次數太多。 此時，您可以使用計畫指南來指定其他提示 (例如 USE PLAN)，以便控制查詢的行為，同時向應用程式廠商調查應用程式變更。 如需有關計畫指南的詳細資訊，請參閱 計畫指南。

參考來源:

石頭的coding之路
今天要來談談影響SQL-Server執行計畫的關鍵人物.... SQL Server的QO(Query Optimizer)透過 cost-based model 來選擇一個最合適計畫(估算成本最低)來執行查詢…dotblogs.com.twhttps://docs.microsoft.com/zh-tw/sql/relational-databases/statistics/statistics?view=sql-server-ver15

史丹利好熱
最近在駐點的客戶端遇到一個很神奇的問題，剛匯入大筆資料的隔天(…dotblogs.com.tw史丹利好熱
在SQL2016以前，當自動更新統計值選項啟用後，20% +…dotblogs.com.twDBCC TRACESTATUS (Transact-SQL) - SQL Server
Applies to: SQL Server (all supported versions) Azure SQL Managed Instance Displays the status of trace flags…docs.microsoft.comDBCC TRACEOFF (Transact-SQL) - SQL Server
Applies to: SQL Server (all supported versions) Azure SQL Managed Instance Disables the specified trace flags…docs.microsoft.comDBCC TRACEON (Transact-SQL) - SQL Server
Applies to: SQL Server (all supported versions) Azure SQL Managed Instance Enables the specified trace flags…docs.microsoft.comhttps://blog.sqlauthority.com/2020/06/01/sql-server-statistics-modification-counter-sys-dm_db_stats_properties/

Currently unrated
 or 

Subscribe

* indicates required

Recent Posts

Archive

2022
2021

Categories

Apache 1

Data Science 2

Dbfit 1

Design Pattern 1

Devops 3

DigitalOcean 1

Django 1

English 3

Excel 5

Flask 3

Git 1

HackMD 1

Heroku 1

Html/Css 1

Linux 4

Machine Learning 2

Manufacture 1

Mezzanine 18

Oracle 1

Postgresql 7

PowerBI 4

Powershell 3

Python 21

SEO 2

SQL Server 51

SQLite 1

Windows 1

database 8

work-experience 1

其他 1

自我成長 1

資料工程 1

Tags

SEO(1) Github(2) Title Tag(2) ML(1) 李宏毅(1) SQL Server(18) Tempdb(1) SSMS(1) Windows(1) 自我成長(2) Excel(1) python Flask(1) python(5) Flask(2)

Authors

bart30508 (145)

Feeds

RSS / Atom

統計值與執行計畫的關係 - SQL Server

© COPYRIGHT 2011-2022. Max的文藝復興. ALL RIGHT RESERVED.