sql-server add tag
paul holmes (imported from SE)
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.
Top Answer
meme (imported from SE)
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:

        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.

  [1]: https://www.erikdarlingdata.com/2019/09/sql-server-2019-wait_on_sync_statistics_refresh-redux/
  [2]: https://www.erikdarlingdata.com/2019/08/sql-server-2019-wait_on_sync_statistics_refresh/

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.