Use extended events to track autoupdate statistics on a specific table

Question

I'm trying to find a way to track a table that has auto update statistics on, I'm trying to track how often it auto updates and at what intervals.

The table in question is used quite often and because the statistics are updated alot, on the auto update, because it doesn't sample the whole table its mostly giving me a bad execution plan.

I want to track it so I can run a job that updates the statistics on a fixed interval, taking full sample

Thanks in advance for the help.

Answer 1

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

Answer 2

If you want to have statistics with specific sample rate persist, you can just use PERSIST_SAMPLE_PERCENT. For example:

UPDATE STATISTICS YourTable(YourColumn) WITH FULLSCAN PERSIST_SAMPLE_PERCENT = ON

This will update column statistics with fullscan and any subsequent statistics update will also use fullscan, unless specified otherwise.

Do check the SQL Server version you are using. This option is available since 2016 SP1 CU4, but the sample rate didn't persist at index rebuild. It was fixed in SP2 CU17. Similar limitations applied to other versions too. Best is to be up to date with your cumulative updates here.