December 18, 2018 at 1:59 pm
Hi experts,
I got below query but how to get more columns such as Text and query plan etc other good thhings to pin point which statement or store procedure causing the recompilation
using sys.dm_exec_sql_text and sys.dm_exec_query_plan DMFs?
SELECT query_hash, COUNT(plan_handle) FROM sys.dm_exec_query_stats GROUP BY query_hash
ORDER BY COUNT(plan_handle) DESC
Thanks
December 19, 2018 at 4:15 am
Look through the system views and you ought to be able to figure it all out. sys.dm_exec_query_stats has a number of values like sql_handle for finding statements and the plan_handle for finding plans. Then, you combine it with other system views, sys.dm_exec_query_plan and sys.dm_exec_sql_text and you'll find the information you need.
You can also look to Glen Barry for example scripts.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 19, 2018 at 9:56 am
Thanks for your reply, but since above query has 'group by' not able to add any other columns!
December 19, 2018 at 1:15 pm
Change the query. Add the columns to the GROUP BY clause, or make it a sub-select or...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply