Extracting a Query Execution Plan from the Procedure Cache

From SQL Server 2005 forward, it has been possible to view the execution plan of a query that has been executed by someone other than yourself. This is extremely useful on procedures that you, as a DBA, would not want to execute in production because of data modification logic, but are used everyday as a part of the application. Finding out how the procedure is running and why it is running so poorly without having to execute it also saves a lot of setup time since many procedures require an extensive set of input parameters that we may or may not know.

First, a little setup. We need to create a test stored procedure that we will be looking for in the procedure cache.

CREATE PROCEDURE KevinsTestProc

AS
BEGIN
  SELECT TOP 1 * FROM sys.objects
END
GO

After we’ve created the stored procedure, execute it a couple times so that SQL Server will cache it. Once it is there, we can start querying various execution related DMV’s that will eventually show us the execution plan.

EXEC KevinsTestProc

GO

Since there can be an enormous amount of plans in the procedure cache, the first thing we need to help narrow down the search is some text that is unique to that procedure, like the procedure name.  By querying the sys.dm_exec_cached_plans DMV and using the sys.dm_exec_sql_text DMF we can get the plan handle will be used to extract the exection plan.

SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, query_plan

FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE usecounts > 1
AND text LIKE '%KevinsTestProc%'
AND objtype = 'Proc'
ORDER BY usecounts DESC;

Results:

We can see from here that we have the plan handle,  how many times this store procedure has been executed, how large the plan is, the text inside the cached plan, and an XML representation of the Execution Plan generated at compile time.

Now that we have this we can click on the XML hyperlink in the results window to open up the execution plan in SSMS.

Finding Open Cursors (And Their Locks) In SQL Server

If you need information regarding open cursors on your SQL Server, you can query the sys.dm_exec_cursors DMV to get information on when they were created and their various properties. With that DMV along with the sys.dm_exec_sessions DMV and the sys.dm_exec_sql_text DMF to get the SQL that was used to create the cursor.

SELECTc.session_id
,c.cursor_id
,c.properties
,c.creation_time
,c.is_open
,c.fetch_status
,c.dormant_duration
,s.login_time
,t.text
FROM
sys.dm_exec_cursors (0) c
JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(c.sql_handle) t

If you need to see if one of those cursors is holding locks on your system, you can use the sys.dm_tran_locks and sys.dm_exec_sessions DMV 's along with the sys.partitions catalog view to see the cursor and the table it has locked.


SELECTOBJECT_NAME(P.object_id) AS TableName
,L.resource_type
,L.resource_description
,L.request_session_id
,L.request_mode
,L.request_type
,L.request_status
,L.request_reference_count
,L.request_lifetime
,L.request_owner_type
,s.transaction_isolation_level
,s.login_name
,s.login_time
,s.last_request_start_time
,s.last_request_end_time
,s.status
,s.program_name
,s.login_name
,s.nt_user_name
--,c.connect_time
--,c.last_read
--,c.last_write
--,t.text
FROM
sys.dm_tran_locks L
JOIN sys.partitions P
ON L.resource_associated_entity_id = P.hobt_id
JOIN sys.dm_exec_sessions s
ON L.request_session_id = s.session_id
--JOIN sys.dm_exec_connections c
--ON s.session_id = c.most_recent_session_id
--CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE
L.request_owner_type = 'CURSOR'
ORDER
BY L.request_session_id

Monitor Backup and Restore Progress Using DMV's

When using SQL Agent jobs or a third party tool to control database backups on a scheduled basis, you may lose the ability to monitor the progress of the backup job. 

For instance, if we executed the following BACKUP command in a T-SQL window, we have the ability to monitor its progress in the messages window.

BACKUP DATABASE <DBName> TO DISK = N'<File>'
WITH
NOFORMAT,NOINIT,
NAME = N'<Name>',
SKIP,NOREWIND,NOUNLOAD,
STATS = 10
 GO

But when jobs are executed via the SQL Agent, that information is abstracted away from us since it is happening in the background. Luckily, there are a couple Dynamic Management Views (DMV) that can tell us the BACKUP commands progress as well as the exact command issued.

By executing the following query, we can get an idea of how far along our backup, restore or rollback operation has progressed. Unfortunately, this will not work for queries.

 SELECT er.percent_complete
                ,db_name(er.database_id) AS [DB]
                ,st.text
                ,er.command
 FROM sys.dm_exec_requests er
         CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
 WHERE percent_complete <> 0

WHAT SQL TABLES AND INDEXES ARE TAKING UP SPACE IN MEMORY?

Ever have a SQL Server run low on memory? Unless you’re buying “big iron” for every server or never consolidate SQL instances, chances are you have. Ever wonder “What’s taking up all the memory?”, Probably. Luckily, with DMV’s introduced in SQL 2005, we can get a snapshot of the tables and indexes that are occupying memory inside the SQL Buffer Pool.

As we all know, indexes are a blessing for reading data from SQL, but not much thought is given to overhead associated with indexes. Not only is there more work to be done when an insert, update or delete on the base table (or clustered index), the newly created index will also start residing in memory as it is used in queries. Also, an index can become less useful over time as the columns in the index become less unique; resulting in index scans rather than index seeks. While this may cause query performance to suffer; in the context in memory, this causes more 8KB index pages to be read into memory, consequently bloating your Buffer Pool.

To view the tables and indexes that are taking up residence in you SQL Server memory, the following query can be ran from the master database. The query makes use of the sp_msforeachdb stored procedure to get the names of the objects in memory from each database . Below are some notes on the result set returned by the query:

·         This query will only return tables and indexes in memory from User databases. Master, Tempdb, MSDB, and Model are omitted.

·         Indexes with an ID of 1 are the clustered index for the table. Indexes with an ID of 0 are tables that do not have a clustered index and are referred to as Heaps. All others are non-clustered indexes.

·         Not only will you be able to see the size of each object in memory by megabytes, I’ve also included some usage statistics on those indexes. These statistics will give you an idea on how your index is being used and how effective it is.

·         Documentation on the system tables and DMV’s used in the query can be found below

o   sys.dm_db_index_usage_stats

o   sys.indexes

o   sys.partitions

o   sys.dm_os_buffer_descriptors

o   sys.allocation_units

·         This query does not return information on other objects in memory such as the ones that live in the Procedure Cache or those governed by the Lock Manager.

Query Below:

IF OBJECT_ID('tempdb..#Objects') IS NOT NULL
  DROP TABLE #Objects

 CREATE TABLE #Objects

    (
      ObjectName SYSNAME ,
      ObjectID INT ,
      IndexName SYSNAME ,
      IndexID INT ,
      BufferSize INT ,
      BufferCount INT ,
      DBName SYSNAME
    )
 GO

 EXEC sp_msforeachdb 'use ?;

 IF DB_ID(''?'') > 4
 insert into #Objects(ObjectName,
                      ObjectID,
                      IndexName,
                      IndexID,
                      BufferSize,
                      BufferCount,
                       DBName)
 SELECT OBJECT_NAME(P.Object_ID),
        P.Object_ID, 
        Coalesce(I.Name,''**Heap**''),   
        P.Index_ID,
        COUNT(*)/128, 
        COUNT(*),
        db_name()    
   FROM sys.allocation_units AS AU INNER JOIN sys.dm_os_buffer_descriptors AS OSB    
    ON AU.allocation_unit_id = OSB.allocation_unit_id    
                                     INNER JOIN sys.partitions AS P    
    ON AU.container_id = P.hobt_id 
                                    INNER JOIN sys.indexes AS I
    ON P.index_id = I.index_id
   AND P.object_id = I.object_id     
 WHERE OSB.database_id = DB_ID()    
 GROUP BY P.object_id, P.index_id,Coalesce(I.name,''**Heap**'')'
 GO

SELECT  #Objects.DBName ,

        #Objects.ObjectName AS TableName ,
        #Objects.IndexID,       
        #Objects.IndexName ,       
        #Objects.BufferSize ,
        #Objects.BufferCount ,
        IUS.user_seeks ,
        IUS.user_scans ,
        IUS.user_lookups ,
        IUS.user_updates ,
        IUS.last_user_seek ,
        IUS.last_user_scan ,
        IUS.last_user_lookup ,
        IUS.last_user_update ,
        IUS.system_seeks ,
        IUS.system_scans ,
        IUS.system_lookups ,
        IUS.system_updates ,
        IUS.last_system_seek ,
        IUS.last_system_lookup ,
        IUS.last_system_update ,
        IUS.last_system_scan
FROM    #Objects INNER JOIN sys.dm_db_index_usage_stats IUS
     ON #Objects.DBName = DB_NAME(IUS.database_id)
    AND #Objects.objectid = IUS.OBJECT_ID
    AND #Objects.indexid = IUS.index_id
ORDER BY 5 DESC

GO