The environments for which I’m responsible have some pretty substantial plan non-reuse challenges. I’ve run across the following query (h/t Brent Ozar blog commenter Michael J Swart) that does a fine job of itemizing the worst offenders:
WITH cte AS ( SELECT COUNT(*) [count], query_hash, min(sql_handle) [sql_handle_example] FROM sys.dm_exec_query_stats GROUP BY query_hash ) SELECT cte.*, t.text [query_text_example] FROM cte CROSS APPLY sys.dm_exec_sql_text(sql_handle_example) t WHERE [count] > 100 ORDER BY [count] DESC
My challenge is taking a snip of the [query_text_example] text and efficiently identifying whether it’s originating from a sproc, and if so which one in which database. I’ve done some Googling and testing and it’s been puzzlingly difficult to find a solution that takes a snippet of query text, whether it was dynamically built or not, and accurately identifies its parent sproc. Does anyone have any suggestions?
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
You can use the dm_exec_procedure_stats view, and if you’re on SQL Server 2016 or better, the dm_exec_function_stats view as well, to track down scalar UDFs.
Both of those views have an
object_id column that can be used to resolve procedure and function names with the OBJECT_NAME function. You can match those to various other views on the
Unless you are doing this scoped to a single database that you care about, you may also need to use the dm_exec_plan_attributes view to grab the
dbid attribute, which can be used as a second argument in
dm_exec_sql_text supplies a
objectid column which is
NULL for ad-hoc batches. You can just left-join it with
WITH cte AS ( SELECT COUNT(*) [count], s.query_hash, min(s.sql_handle) [sql_handle_example] FROM sys.dm_exec_query_stats s GROUP BY s.query_hash HAVING count(*) > 100 ) SELECT cte.*, t.text [query_text_example], o.name FROM cte CROSS APPLY sys.dm_exec_sql_text(cte.sql_handle_example) t LEFT JOIN sys.objects o ON o.object_id = t.objectid ORDER BY [count] DESC;
This only works for a single database. To join it across multiple unknown databases you need dynamic SQL. Each database has a separate
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0