[SQL Server][Statistics]統計值(四)非同步更新統計值(is_auto_update_stats_async_on)

在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