Really, cannot think about query tuning without these two DMVs – sys.dm_exec_query_stats and sys.dm_exec_procedure_stats. For the sake of simplicity, I will refer to them as QS and PS, respectively. Query Stats (QS) DMV will give you query-level metrics and Procedure Stats (PS) DMV will give you stored procedure-level metrics. DMV stands for Dynamic Management Views. Simply put, they give performance and internal metrics about the SQL Server engine. After a query completes execution, you can find how much CPU time it has taken, execution time, how much IO it has caused, etc, in QS DMV, along with many other metrics. Likewise, for a stored procedure, you can find similar metrics in PS DMV. Now let us say your SP has five SQL statements inside it. We are assuming five SELECT queries. For each SELECT statement, you will find its metrics in the QS DMV. And the overall metrics for the SP itself will be available in the PS DMV. So the QS DMV will have five records and the PS DMV will have one record. When an SP runs slow, essentially, a statement inside it might be running slow. So troubleshooting the SP performance is actually troubleshooting the statement/query performance. So that’s the correlation between these two DMVs. It is important to extract the right information from them to be able to make sense if your query tuning efforts. To Know more watch the video below.
Get new content directly in your inbox – Click here
You can also visit DataPlatformGeeks (DPG) Video Library to watch more videos and to download video resources (presentation & demo code).
Hope the video was worth your time.
Happy Learning.
Leave A Comment