or
paul holmes imported from SE
sql-server
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
Erik Darling
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: 

- [SQL Server 2019: WAIT_ON_SYNC_STATISTICS_REFRESH][2]
- [SQL Server 2019: WAIT_ON_SYNC_STATISTICS_REFRESH Redux][1] 

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.

  [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/
Determining if auto stats update is in progress

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.