SQL Server 2016 – New Columns in sys.dm_exec_query_stats

Hello Friends,

There are lots of features and improvements announced by Microsoft in SQL Server 2016, one of them is adding new columns in sys.dm_exec_query_stats. These new columns capture information about threads and memory. First, I’ll show you the new added columns for collecting threads and Degree of parallelism level information.

Select * from sys.dm_exec_query_stats

dm_exec_query_stats

dm_exec_query_stats

Here total_dop represent the total degree of parallelism used after the plan compilation. In the similar way you also have the information about the degree of parallelism for the last execution (last_dop), minimum and maximum degree of parallelism used in a query (min_dop and max_dop). In the first image above, you can see that all rows except row number 5, run with serial plans while row number 5 run with a parallel plan where MAXDOP used is 4.

We know that similar to other resources, SQL Server also reserved threads for query execution. You can see all such thread level information from columns shown in the second image. This information will play a very important role during the troubleshooting of thread level issues like when a single query plan reserved lots of threads while using very less.

Another important section is for about memory grant and memory uses. As shown in below image:

dm_exec_query_stats

In the above image, the first section is showing the information about total memory grant since plan compilation, memory grant for last execution, minimum and maximum memory grant in kb. Second section has shown the information about the actual memory uses since last plan compilation, memory used for last execution, minimum and maximum granted memory used. Below are the columns which will give you the information about the ideal memory grant.

dm_exec_query_stats

Where Ideal Memory = Required Memeory * DOP + Additional Memory.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

One Comment on “SQL Server 2016 – New Columns in sys.dm_exec_query_stats”

Leave a Reply

Your email address will not be published. Required fields are marked *