So we've recently upgraded our environment to SQL Server 2014 (Enterprise on CU5) and are having some problems diagnosing performance issues, compounded by the problem that we no longer seem to be able to tell when SQL Server is updating/creating statistics.
Previously, when compiling a plan, if SQL Server needed additional statistics (we have autostats on) we could see in
STATMAN was busily working away doing his thing. Now all we get is a blank
sql_text column, no query plan and a huge number of reads and only by looking at the locks do we see that there are some statistics somewhere being generated (but not which columns on what table).
Does anyone have any insight into the changes here? Is there any way to tell what stats the optimizer has asked for? I know we can once the plan is compiled (thank you Paul White) but we have a fairly large data warehouse, so sometimes the stats collection operation takes a significant amount of time!
It looks like while
sp_whoisactive no longer shows you the stats being created/updated, you can use a dmv to find out what's going on with async stats:
SELECT time_queued, OBJECT_NAME(object_id1), ss.name, sc.name, auto_created, user_created, no_recompute, has_filter, is_temporary, is_incremental FROM sys.dm_exec_background_job_queue bq JOIN sys.stats ss ON ss.object_id = bq.object_id1 AND ss.stats_id = bq.object_id2 JOIN sys.stats_columns stc ON stc.object_id = ss.object_id AND stc.stats_id = ss.stats_id JOIN sys.columns sc ON sc.column_id = stc.column_id AND sc.object_id = stc.object_id WHERE in_progress = 1
The way to kill the background jobs is via the
KILL STATS JOB command to terminate the stats process.