SQL 2016 – sys.dm_exec_query_stats upgraded version

This is one the most frequently used DMVs for DBA when troubleshooting performance issues and MS has been adding more columns to the DMV with every release. In SQL 2016 there are an additional 26 columns being added. The best part is that these 26 columns help break down the CPU and Memory utilization even further.

Broadly the column are broken down into

DOP ( total , last , min and max)

Memory grant reserved ( total , last , min and max)

Memory grant used ( total , last , min and max)

Memory Grant ideal used ( total , last , min and max)

Reserved Thread ( total , last , min and max)

Used Threads ( total , last , min and max)

These columns will really help dive deep into how much resources a query is assigned vs how much is actually used and will really help trouble shoot Parameter sniffing and other common performance issues.

A small thing to watch out for in this case is that User threads will show 0 for anything that is not a parallel plan.

References

https://msdn.microsoft.com/en-us/library/ms189741.aspx