One of the powerful tools that the DBA can use when tuning queries is the “sys.dm_exec_query_stats” dynamic management view. This view display statistics about cached statements. It is important to understand that a cached object in the procedure cache represents a complete batch, while each row in “sys.dm_exec_query_stats” represents a single statement. If a batch contains 3 statements, it will be cached once, but it will have 3 corresponding rows in “sys.dm_exec_query_stats”.
Now, the statistics in this view in a particular row describe the specific statement, which is fine. But there are two columns in this view that correspond to the whole batch and not to the specific statement: “sql_handle” and “plan_handle”. You can use these columns as input parameters to the “sys.dm_exec_sql_text” and “sys.dm_exec_query_plan” functions respectively, in order to return the batch text and the batch execution plan.
But since these columns represent the whole batch, they will return the same text and plan for each statement in the batch, and it might be difficult to determine which statement corresponds to the statistics in a specific row.
This script creates a view that solves this problem by returning the text and execution plan of the specific statement represented by each row in “sys.dm_exec_query_stats”. The view uses two functions that are also created in this script.