SQL Server Query – Finding Plan Details for a Specific Stored Procedure by Name

dmvsql serversql-server-2008-r2stored-procedures

I'm trying to write a query to find information about query stats and the query plan for a specific stored procedure, but I can't manage to find the right DMV or query to look for a specific stored procedure.

So far I have:

select
    qs.sql_handle
    , qs.statement_start_offset
    , qs.statement_end_offset
    , qs.plan_handle
    , execution_count
    , st.text
    , substring(st.text, (qs.statement_start_offset/2)+1,
        ((case qs.statement_end_offset
            when -1
                then datalength(st.text)
            else
                qs.statement_end_offset
            end - qs.statement_start_offset) / 2 + 1)) as [Filtered text]
    , qp.query_plan
from sys.dm_exec_query_stats as qs
    cross apply sys.dm_exec_sql_text (qs.sql_handle) as st
    cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
where st.text like '%myProcedure%'
order by qs.sql_handle
    , execution_count desc

But this query doesn't return any information. Ideally I would replace the st.text like condition with something like object_name(procedure_id) = 'myProcedure', but I can't find the right way to do so.

Is there a better way to do so?

I could replace the condition in the WHERE clause with a qs.sql_handle = 0x000004004040400..etc. but how can I find out the sql_handle for the procedure? (this information isnt' found in sys.objects or in sys.procedures).

Any information is greatly appreciated.

Update:

I've executed the procedure (exec myProcedure ...) in a test environment and now it is returned by the above query and so I can get the plan handle to use the where sql_handle = 0x0034300.., but I'd like to run the same query from above in production, without having to run the procedure in production (just to find out its plan handle).

This is the reason why I'm trying to write the query in this way, to get the information based on name, not sql_handle (just because in prod I don't know what is its sql_handle).

Best Answer

Given that the sys.dm_exec_query_stats DMV returns data for the entire instance, you need to be able to get object Schema and name info across all Databases. Using ST.objectid = OBJECT_ID(N'dbo.ProcedureName'), as has been suggested, is error-prone since it will only resolve the name relative to the database in which the query is being executed:

  1. If the object name does not exist in that Database, then it will return NULL, which will filter out all rows.
  2. If the object name does exist in that Database but you are actually wanting that object name from a different database, then you will get an object_id value that might be correct, but could just as well point to a different object and might return one or more incorrect rows.
  3. If an object_id value is returned, whether correct or incorrect, that particular value can exist across multiple databases and so you might get multiple rows back for different objects in different databases.

At the very least, you would need to provide a fully-qualified object name (i.e. three-part name). However, even if you do use OBJECT_ID(N'DatabaseName.SchemaName.ObjectName') the query still has the potential to run into problem #3 noted above since that object_id, even being correct, can exist across multiple Databases. So you need a second condition using the DB_ID() function to narrow down to the intended Database.

WHERE st.[objectid] = OBJECT_ID(N'DatabaseName.SchemaName.ObjectName')
AND   st.[dbid] = DB_ID(N'DatabaseName')

HOWEVER, my preference is to use the OBJECT_NAME and OBJECT_SCHEMA_NAME functions as they both accept an optional 2nd parameter for database_id. The advantage here is that you can use them in the SELECT clause when you are not narrowing down to one specific object, so that you can see the object names for all of the rows in the DMV. It also allows for filtering down to the same object across multiple Databases, which is useful when you have the same Stored Procedure in multiple Databases and want to see rows for it across all Databases, even though it likely has different object_id values across those Databases.

Hence, you could optionally add the following to the SELECT clause (I use this when Cross Applying sys.dm_exec_sql_text to DMVs such as sys.dm_exec_requests, etc):

DB_NAME(st.[dbid]) AS [DatabaseName],
OBJECT_SCHEMA_NAME(st.[objectid], st.[dbid]) AS [SchemaName],
OBJECT_NAME(st.[objectid], st.[dbid]) AS [ObjectName]

And you would then update your query to have the following WHERE clause:

WHERE OBJECT_NAME(st.[objectid], st.[dbid]) = N'my_proc_name'
AND   OBJECT_SCHEMA_NAME(st.[objectid], st.[dbid]) = N'dbo' -- or whatever schema
-- AND  DB_NAME(st.[dbid]) = N'DatabaseName' -- optionally narrow down to specific DB
Related Question