SQL Server - Finding Parent Source from T-SQL Snippet
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?
You can use the https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-procedure-stats-transact-sql?view=sql-server-ver15 view, and if you're on SQL Server 2016 or better, the https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-function-stats-transact-sql?view=sql-server-ver15 view as well, to track down scalar UDFs.
Both of those views have an
object_idcolumn that can be used to resolve procedure and function names with the https://docs.microsoft.com/en-us/sql/t-sql/functions/object-name-transact-sql?view=sql-server-ver15 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 https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-plan-attributes-transact-sql?view=sql-server-ver15 view to grab the
dbidattribute, which can be used as a second argument in