上一篇找到一大串勒勒長的執行計畫,這次要來筆記一下統計值是怎麼一回事以及如何優化。
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 的值相符的資料列
注意每個執行計畫是使用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 , 與微軟統計值篇的"頻率"相符
第二個結果集
密度分布,使用常數查詢,直接使用子方圖進行資料筆數估計
第三個結果集
假如有設定自動更新統計值,異動資料筆數超過 (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
### 改善變數和函式的基數估計值
若要改善變數和函數的基數估計值,請遵循下列指導方針:
例如,下列預存程序 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/
Grafana + Telegraf + InfluxDB 監控 SQL Server - SQL Server
Task Scheduler設定排程錯誤時發送Mail - Powershell
JOIN ON 常數的用途是什麼?跟放在WHERE一樣嗎?/ FULL JOIN要注意的事項- SQL Server
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)
bart30508 (147)