Skip to main content
added 64 characters in body
Source Link
Zikato
  • 5.5k
  • 1
  • 16
  • 34

The Extended Event is (unsurprisingly) called auto_stats

Occurs when index and column statistics are automatically updated. This event can be generated multiple times per statistics collection when the update is asynchronous.

You might want to experiment with the filters. Your best bet is to start with the object_id and database_id of your table and then try out the different statuses.

This should get you started

CREATE EVENT SESSION [TableName_AutoStats] ON SERVER 
ADD EVENT sqlserver.auto_stats
(
    SET collect_database_name=(0)
    ACTION
    (
        sqlserver.client_app_name      /* if you want to know what's causing the auto_stats */
        ,sqlserver.sql_text            /* if you want to know what's causing the auto_stats */
        ,sqlserver.tsql_stack          /* if you want to know what's causing the auto_stats */
    )
    WHERE 
        [object_id] = <your object id> /* order of conditions matters - pick the most selective first */
        AND [database_id] = <your database id>
        AND [package0].[not_equal_uint64]([status], 'Loading stats without updating')
)
ADD TARGET package0.event_file
(
    SET filename=N'StatementCompilation'filename='TableName_AutoStats'
    , max_file_size = 5
)

ALTER EVENT SESSION [TableName_AutoStats] ON SERVER
STATE = START

Example of the output enter image description here

The Extended Event is (unsurprisingly) called auto_stats

Occurs when index and column statistics are automatically updated. This event can be generated multiple times per statistics collection when the update is asynchronous.

You might want to experiment with the filters. Your best bet is to start with the object_id and database_id of your table and then try out the different statuses.

This should get you started

CREATE EVENT SESSION [TableName_AutoStats] ON SERVER 
ADD EVENT sqlserver.auto_stats
(
    SET collect_database_name=(0)
    ACTION
    (
        sqlserver.client_app_name      /* if you want to know what's causing the auto_stats */
        ,sqlserver.sql_text            /* if you want to know what's causing the auto_stats */
        ,sqlserver.tsql_stack          /* if you want to know what's causing the auto_stats */
    )
    WHERE 
        [object_id] = <your object id>
        AND [database_id] = <your database id>
        AND [package0].[not_equal_uint64]([status], 'Loading stats without updating')
)
ADD TARGET package0.event_file
(
    SET filename=N'StatementCompilation'
    , max_file_size = 5
)

ALTER EVENT SESSION [TableName_AutoStats] ON SERVER
STATE = START

Example of the output enter image description here

The Extended Event is (unsurprisingly) called auto_stats

Occurs when index and column statistics are automatically updated. This event can be generated multiple times per statistics collection when the update is asynchronous.

You might want to experiment with the filters. Your best bet is to start with the object_id and database_id of your table and then try out the different statuses.

This should get you started

CREATE EVENT SESSION [TableName_AutoStats] ON SERVER 
ADD EVENT sqlserver.auto_stats
(
    SET collect_database_name=(0)
    ACTION
    (
        sqlserver.client_app_name      /* if you want to know what's causing the auto_stats */
        ,sqlserver.sql_text            /* if you want to know what's causing the auto_stats */
        ,sqlserver.tsql_stack          /* if you want to know what's causing the auto_stats */
    )
    WHERE 
        [object_id] = <your object id> /* order of conditions matters - pick the most selective first */
        AND [database_id] = <your database id>
        AND [package0].[not_equal_uint64]([status], 'Loading stats without updating')
)
ADD TARGET package0.event_file
(
    SET filename='TableName_AutoStats'
    , max_file_size = 5
)

ALTER EVENT SESSION [TableName_AutoStats] ON SERVER
STATE = START

Example of the output enter image description here

Source Link
Zikato
  • 5.5k
  • 1
  • 16
  • 34

The Extended Event is (unsurprisingly) called auto_stats

Occurs when index and column statistics are automatically updated. This event can be generated multiple times per statistics collection when the update is asynchronous.

You might want to experiment with the filters. Your best bet is to start with the object_id and database_id of your table and then try out the different statuses.

This should get you started

CREATE EVENT SESSION [TableName_AutoStats] ON SERVER 
ADD EVENT sqlserver.auto_stats
(
    SET collect_database_name=(0)
    ACTION
    (
        sqlserver.client_app_name      /* if you want to know what's causing the auto_stats */
        ,sqlserver.sql_text            /* if you want to know what's causing the auto_stats */
        ,sqlserver.tsql_stack          /* if you want to know what's causing the auto_stats */
    )
    WHERE 
        [object_id] = <your object id>
        AND [database_id] = <your database id>
        AND [package0].[not_equal_uint64]([status], 'Loading stats without updating')
)
ADD TARGET package0.event_file
(
    SET filename=N'StatementCompilation'
    , max_file_size = 5
)

ALTER EVENT SESSION [TableName_AutoStats] ON SERVER
STATE = START

Example of the output enter image description here