在SQL2016以前,當自動更新統計值選項啟用後,20% + 500筆是一般資料表觸發重新統計的門檻(RT),資料異動累積量達標後,第一個使用統計值的交易會先更新統計值才完成編譯後再實際執行,在某些資料表比較胖的時候,也許先更新統計值再查詢資料的順序可能就會影響OLTP個案交易的執行效能,此時可以試試非同步更新統計值。
統計值系列之四: 非同步更新統計值
查詢資料庫統計值有關的屬性(預設)
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
非同步更新的選項啟用!
透過SSMS GUI
查看統計值現況
EXEC usp_showStatistics 'RealMadridClub'
最近的統計值更新是在21:31:02
大量異動資料並查詢,達到觸發更新統計值的條件
來刪除達門檻的21,372筆(104358 * 20% + 500 )
SET STATISTICS TIME ON
DELETE TOP(21372) FROM RealMadridClub
WHERE NAME = 'Benzema'
執行SQL查詢(使用統計值)並觀察查詢時間。(避免第一次編譯時就被發現要更新統計值,使用sp_executesql執行)
PRINT convert(varchar, getdate(), 113)
DECLARE @SQLString nvarchar(500);
set @SQLString = N'
SELECT * FROM RealMadridClub
WHERE NAME = ''Benzema'''
EXECUTE sp_executesql @SQLString
執行時沒有編譯等待時間,21:35:02.193就開始執行了,執行只花了0.1秒。
接下來是統計值更新時間,執行預存程序usp_showStatistics觀察: 21:35:03秒才完成統計值更新
先執行再更新統計值。
雲林斗六棒球場
參考
ALTER DATABASE SET 選項 (Transact-SQL)
https://docs.microsoft.com/zh-tw/sql/t-sql/statements/alter-database-transact-sql-set-options