欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

一文詳解SQL Server如何跟蹤自動統(tǒng)計信息更新

 更新時間：2025年03月20日 14:36:33   作者：瀟湘隱者  
SQL Server數(shù)據(jù)庫中,我們都清楚統(tǒng)計信息對于優(yōu)化器來說非常重要,所以本文就來和大家簡單聊一聊SQL Server如何跟蹤自動統(tǒng)計信息更新吧

SQL Server數(shù)據(jù)庫中，我們都清楚統(tǒng)計信息對于優(yōu)化器來說非常重要。一般情況下，我們會開啟"自動更新統(tǒng)計信息"(Auto Update Statistics)這個選項，以便數(shù)據(jù)庫能自動更新過期/過時的統(tǒng)計信息，因為過期/過時的統(tǒng)計信息可能會導(dǎo)致數(shù)據(jù)庫生成一個糟糕的執(zhí)行計劃，SQL性能將會大打折扣，舉一個例子，我們大腦做一些決策的時候，嚴(yán)重依賴所獲取做決策信息的真實性與準(zhǔn)確性，如果你所獲得的信息是錯誤的，那么十有八九你會做出一個嚴(yán)重錯誤的決定。例如，如果當(dāng)下環(huán)境中，你獲取的信息：”買房穩(wěn)賺不賠;買房會抗通脹......“是過時/錯誤的信息，那么你就會為當(dāng)下的決策付出慘痛代價。

"自動更新統(tǒng)計信息"固然是不錯的一個功能，但是很多人對它內(nèi)部的原理知之甚少。對于"自動更新統(tǒng)計信息"是否開啟也是有一些爭論的。如果你監(jiān)控發(fā)現(xiàn)一個SQL的執(zhí)行計劃經(jīng)常出現(xiàn)變化，除了參數(shù)嗅探外等因素外，那么你要考慮一下可能是因為SQL語句中所涉及的表的統(tǒng)計信息自動更新導(dǎo)致。個人曾遇到一個案例，SQL語句的執(zhí)行計劃在凌晨2點變了，而且是性能變差，具體原因是在這個時間段，有一個作業(yè)會歸檔清理數(shù)據(jù)，導(dǎo)致觸發(fā)自動統(tǒng)計信息更新，而它使用的是自動采樣比例，而由于采樣比例過低，導(dǎo)致優(yōu)化器生成了一個較差的執(zhí)行計劃。如果你不用擴(kuò)展事件去跟蹤、分析的話，那么真的很難搞清楚為什么出現(xiàn)這種玄幻的現(xiàn)象。

下面是一個SQL執(zhí)行計劃經(jīng)常出現(xiàn)變化的例子的截圖，來自SolarWinds的DPA。

下面介紹一下，如何使用擴(kuò)展事件跟蹤統(tǒng)計信息自動更新?？梢栽谧鲆恍┥钊敕治鰰r用到。

創(chuàng)建擴(kuò)展事件stat_auto_update_event

CREATE EVENT SESSION [stat_auto_update_event] ON SERVER 
ADD EVENT sqlserver.auto_stats(
    ACTION(sqlserver.sql_text,sqlserver.username,sqlserver.database_name))
ADD TARGET package0.event_file(SET filename=N'E:\extevntlog\stat_auto_update_event',max_rollover_files=(60)),
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

啟動會話,擴(kuò)展事件就能捕獲數(shù)據(jù)庫中"自動更新統(tǒng)計信息"的一些事件了。

ALTER EVENT SESSION [stat_auto_update_event] ON SERVER
STATE = START;

此時，你就可以用下面SQL查看/分析"自動更新統(tǒng)計信息"的一些詳細(xì)信息了。

IF OBJECT_ID('tempdb..#stat_auto_update_event') IS NOT NULL
   DROP TABLE #stat_auto_update_event;

CREATE TABLE #stat_auto_update_event
(
         [ID] INT IDENTITY(1, 1)
                  NOT NULL ,
         [stat_update_dtl] XML ,
         CONSTRAINT [pk_stat_auto_update_event] PRIMARY KEY CLUSTERED ( [ID] )
);

INSERT  #stat_auto_update_event
        ( [stat_update_dtl] )
SELECT  CONVERT(XML, [event_data]) AS [stat_update_dtl]
FROM    [sys].[fn_xe_file_target_read_file]('E:\extevntlog\stat_update_event*.xel', NULL, NULL, NULL)

CREATE PRIMARY XML INDEX [xml_idx_stat_dtl] ON #stat_auto_update_event([stat_update_dtl]);

CREATE XML INDEX [xml_idx_stat_dtl_path] ON [#stat_auto_update_event]([stat_update_dtl])
USING XML INDEX [xml_idx_stat_dtl] FOR VALUE;



WITH cte_stat AS (
SELECT
[sw].[stat_update_dtl].[value]('(/event/data[@name="database_id"]/value)[1]', 'INT') AS [database_id],               
[sw].[stat_update_dtl].[value]('(/event/@timestamp)[1]', 'DATETIME2(7)') AS [event_time],
[sw].[stat_update_dtl].[value]('(/event/@name)[1]', 'VARCHAR(MAX)') AS [event_name],
[sw].[stat_update_dtl].[value]('(/event/data[@name="index_id"]/value)[1]', 'BIGINT') AS [index_id],
[sw].[stat_update_dtl].[value]('(/event/data[@name="object_id"]/value)[1]', 'BIGINT') AS [object_id],
[sw].[stat_update_dtl].[value]('(/event/data[@name="job_type"]/text)[1]', 'VARCHAR(MAX)') AS [job_type],
[sw].[stat_update_dtl].[value]('(/event/data[@name="sample_percentage"]/value)[1]','INT') AS [sample_pct],
[sw].[stat_update_dtl].[value]('(/event/data[@name="status"]/text)[1]', 'VARCHAR(MAX)') AS [status],
[sw].[stat_update_dtl].[value]('(/event/data[@name="duration"]/value)[1]', 'BIGINT') / 1000000. AS [duration],
[sw].[stat_update_dtl].[value]('(/event/data[@name="statistics_list"]/value)[1]', 'VARCHAR(MAX)') AS [statistics_list]
FROM [#stat_auto_update_event] AS [sw]  
)
SELECT  
        DB_NAME([cte_stat].[database_id]) AS [database_name] ,
        DATEADD(HOUR, DATEDIFF(HOUR, GETUTCDATE(), GETDATE()), [cte_stat].[event_time]) AS [event_time] ,
        [cte_stat].[event_name] ,
        OBJECT_NAME([cte_stat].[object_id],[cte_stat].[database_id]) AS object_name,
        [cte_stat].[index_id] ,
        [cte_stat].[job_type] ,
        [cte_stat].[status] ,
        [cte_stat].[sample_pct],
        [cte_stat].[duration] ,
        [cte_stat].[statistics_list]
FROM cte_stat
ORDER BY [cte_stat].[event_time];

上面擴(kuò)展事件是跟蹤整個數(shù)據(jù)庫實例下的所有"自動更新統(tǒng)計信息"事件，會存在一定的開銷，如果我只想跟蹤某個對象，那么可以在創(chuàng)建擴(kuò)展事件時進(jìn)行過濾處理，如下所示，我只跟蹤表test的"自動更新統(tǒng)計信息"，那么就可以通過下面腳本添加擴(kuò)展事件

CREATE EVENT SESSION [test_auto_update_event] ON SERVER 
ADD EVENT sqlserver.auto_stats(
    SET collect_database_name=(0)
    ACTION
    (
         sqlserver.client_app_name      
        ,sqlserver.sql_text             
        ,sqlserver.tsql_stack           
        ,sqlserver.username
        ,sqlserver.database_name
    )
    WHERE 
        [object_id] =45243216/* order of conditions matters - pick the most selective first */
        AND [database_id] =5
        AND [package0].[not_equal_uint64]([status], 'Loading stats without updating')
    
    )
ADD TARGET package0.event_file(SET filename=N'E:\extevntlog\test_auto_update_event',max_rollover_files=(60)),
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

注意：要根據(jù)實際情況調(diào)整相關(guān)值，例如[database_id]、[object_id]的值。

手動構(gòu)造一些條件，觸發(fā)表test自動更新統(tǒng)計信息，此時，你可以使用ssms工具查看擴(kuò)展事件捕獲的一些數(shù)據(jù)了，如下截圖所示：

當(dāng)然，你也可以使用下面SQL語句進(jìn)行查詢

IF OBJECT_ID('tempdb..#stat_auto_update_event') IS NOT NULL
   DROP TABLE #stat_auto_update_event;

CREATE TABLE #stat_auto_update_event
(
         [ID] INT IDENTITY(1, 1)
                  NOT NULL ,
         [stat_update_dtl] XML ,
         CONSTRAINT [pk_stat_auto_update_event] PRIMARY KEY CLUSTERED ( [ID] )
);

INSERT  #stat_auto_update_event
        ( [stat_update_dtl] )
SELECT  CONVERT(XML, [event_data]) AS [stat_update_dtl]
FROM    [sys].[fn_xe_file_target_read_file]('E:\extevntlog\test_auto_update_event*.xel', NULL, NULL, NULL)

CREATE PRIMARY XML INDEX [xml_idx_stat_dtl] ON #stat_auto_update_event([stat_update_dtl]);

CREATE XML INDEX [xml_idx_stat_dtl_path] ON [#stat_auto_update_event]([stat_update_dtl])
USING XML INDEX [xml_idx_stat_dtl] FOR VALUE;

WITH cte_stat AS (
SELECT
[sw].[stat_update_dtl].[value]('(/event/data[@name="database_id"]/value)[1]', 'INT') AS [database_id],               
[sw].[stat_update_dtl].[value]('(/event/@timestamp)[1]', 'DATETIME2(7)') AS [event_time],
[sw].[stat_update_dtl].[value]('(/event/@name)[1]', 'VARCHAR(MAX)') AS [event_name],
[sw].[stat_update_dtl].[value]('(/event/data[@name="index_id"]/value)[1]', 'BIGINT') AS [index_id],
[sw].[stat_update_dtl].[value]('(/event/data[@name="object_id"]/value)[1]', 'BIGINT') AS [object_id],
[sw].[stat_update_dtl].[value]('(/event/data[@name="job_type"]/text)[1]', 'VARCHAR(MAX)') AS [job_type],
[sw].[stat_update_dtl].[value]('(/event/data[@name="sample_percentage"]/value)[1]','INT') AS [sample_pct],
[sw].[stat_update_dtl].[value]('(/event/data[@name="status"]/text)[1]', 'VARCHAR(MAX)') AS [status],
[sw].[stat_update_dtl].[value]('(/event/data[@name="duration"]/value)[1]', 'BIGINT') / 1000000. AS [duration],
[sw].[stat_update_dtl].[value]('(/event/data[@name="statistics_list"]/value)[1]', 'VARCHAR(MAX)') AS [statistics_list],
[sw].[stat_update_dtl].[value]('(/event/action[@name="sql_text"]/value)[1]','VARCHAR(MAX)') AS [sql_text],
[sw].[stat_update_dtl].[value]('(/event/action[@name="client_app_name"]/value)[1]','VARCHAR(MAX)') AS [client_app_name]
FROM [#stat_auto_update_event] AS [sw]  
)
SELECT  
        DB_NAME([cte_stat].[database_id]) AS [database_name] ,
        DATEADD(HOUR, DATEDIFF(HOUR, GETUTCDATE(), GETDATE()), [cte_stat].[event_time]) AS [event_time] ,
        [cte_stat].[event_name] ,
        OBJECT_NAME([cte_stat].[object_id],[cte_stat].[database_id]) AS object_name,
        [cte_stat].[index_id] ,
        [cte_stat].[job_type] ,
        [cte_stat].[status] ,
        [cte_stat].[sample_pct],
        [cte_stat].[duration] ,
        [cte_stat].[statistics_list],
        [cte_stat].[sql_text],
  [cte_stat].[client_app_name]
FROM cte_stat
ORDER BY [cte_stat].[event_time];

關(guān)于擴(kuò)展信息捕獲的aut_stat數(shù)據(jù)，status狀態(tài)一般有下面一些值(狀態(tài))，其中Loading stats without updating通常指的是加載統(tǒng)計信息而不進(jìn)行更新操作

  • Loading stats without updating
  • Other
  • Loading and updating stats

那么使用擴(kuò)展事件追蹤統(tǒng)計自動統(tǒng)計信息更新，有哪一些用途呢？ 下面是我簡單的一些總結(jié)，不僅僅局限于此，你也可以擴(kuò)展其用途。

  • 追蹤分析自動統(tǒng)計信息的采樣比例
  • 分析SQL語句執(zhí)行計劃變化的原因。
  • 為手工更新統(tǒng)計信息的頻率與表對象提供數(shù)據(jù)支撐
  • 研究自動統(tǒng)計信息更新觸發(fā)的一些機(jī)制。

以上就是一文詳解SQL Server如何跟蹤自動統(tǒng)計信息更新的詳細(xì)內(nèi)容，更多關(guān)于SQL Server信息更新的資料請關(guān)注腳本之家其它相關(guān)文章！

相關(guān)文章

最新評論