8

In our production system, queries sometimes 'stall'. Whilst stalled, no incrementing resource use (CPU, Reads) is shown in sp_whoisactive, and there is no blocking.

In retrospective diagnosis, we can see that sys.dm_db_stats_properties shows last_updated around the time the query was 'stalled'.

What we would like to do is - when we see a stalled query - then determine what auto stats updates are in progress.

Because we want to do this ad-hoc, and also because we don't want to impact production performance, using profiler is probably not an option for us.

(If there is no way of doing an ad-hoc determination, then maybe we'll have to consider Extended Events, or some other lower-impact pre-emptive tracking).

Our version is 2014, however answers for later versions would also be useful.

0
10

SQL Server 2019 has introduced a wait stat to track this. On earlier versions, you're left to traces/XE. See my posts about the new wait stat:

To use XE on prior versions, the event you want to look for is auto_stats. A bare minimum session to get you started would look like this:

CREATE EVENT SESSION auto_stats
    ON SERVER
    ADD EVENT sqlserver.auto_stats
    ADD TARGET package0.event_file
    ( SET filename = N'auto_stats' );

Though you'd probably want to configure it with some specificity to databases or tables that you care about, and I'd definitely want to filter on duration (WHERE [duration] > 1000000 would be one second) or something, because it'd be pretty noisy otherwise.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.