Sql-server – Where has STATMAN gone in SQL Server 2014

sql serversql server 2014statistics

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 sp_whoisactive that 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!

UPDATE
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.

Best Answer

I still see StatMan operations captured if I use SQL Sentry Plan Explorer PRO against SQL Server 2014:

enter image description here click to enlarge

So, not exactly sure what sp_whoisactive is doing differently in this case (I've never used it to analyze statistics creation), but I can assure you that SQL Server 2014 still uses StatMan.