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?



  • parental advisory

    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_id column 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 sql_handle or plan_handle column.

    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 dbid attribute, which can be used as a second argument in OBJECT_NAME.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2