When I run SP_askbrent@asof = '2014-05-20 16:00' a period where the database was timing out, I'm getting dozens of CREATE PROCEDURE xxx entries.

When I run the following query, the results show the query was created in 2003, and last updated over a year ago.

select d.name, p.name, e.last_execution_time, p.create_date, p.modify_date
from sys.DM_exec_procedure_stats as e
  right join sys.databases as d on d.database_id = e.database_id
  right join sys.procedures as p on p.object_id = e.object_id
WHERE is_ms_shipped = 0
order by e.last_execution_time asc, p.modify_date desc, p.create_date desc

What am I missing? Why am I seeing create procedure calls nonstop while the procedures are seemingly very old and unmodified. As a reference, the DB has 700+ stored procedures, while being only 6,5GB in size, and the query above shows that 675 of them have not been used since the last reboot (unless I'm misinterpreting what last_executed = NULL means).

  • 1
    Whenever a proc is executed, you'll see the full CREATE PROCEDURE recorded in the DMVs, which SP_askbrent will be querying. – Mark Sinkinson May 21 '14 at 8:15
  • Ok that explains all the "create procedure" calls. Can I safely remove all the ones with no execution date? – Reaces May 21 '14 at 8:18
  • 1
    No...the last_execution_time is wiped each time the server is restarted, or you clear the cache with DBCC FREEPROCCACHE; DBCC DROPCLEANBUFFERS;. So you could delete something that is actually still important – Mark Sinkinson May 21 '14 at 8:20
  • Ok, thanks, I'll just look into optimizing the stored procedures that are called frequently then, as a first step. – Reaces May 21 '14 at 8:23
  • Posted some code in an answer below to help you find where to start – Mark Sinkinson May 21 '14 at 8:29

Whenever a proc is executed, you'll see the full CREATE PROCEDURE recorded in the DMVs, which SP_askbrent will be querying.

Executions of each individual statement is also recorded, in sys.dm_exec_query_stats, and you can retrieve the exact text of what has been executed, by CROSS APPLY sys.dm_exec_sql_text(sql_handle) and applying the start and end offsets from the sys.dm_exec_query_stats DMV.

This is the code I use to identify what queries on my server are placing quite a load on the database in terms of IO, Duration and CPU.

It only works on SQL Server 2008+ though (unless you exclude the plan_hash and query_hash), but gives a nice indication of where I should start to look in terms of refactoring.

Each individual query within a stored procedure has the performance recorded (providing it completes and does not timeout)

     deqs.plan_handle AS PlanHandle
    ,deqs.query_hash AS QueryHash
    ,DB_NAME(dest.dbid) AS DatabaseName
    ,OBJECT_SCHEMA_NAME(dest.objectid, dest.dbid) AS SchemaName
    ,OBJECT_NAME(dest.objectid, dest.dbid) AS ObjectName
    ,last_execution_time AS LastExecuted
    ,deqs.creation_time AS PlanCreationTime
    ,SUM(deqs.execution_count) AS Executions
    ,SUM(total_elapsed_time) AS SumDuration
    ,CAST(100.0 * SUM(total_elapsed_time)
                / SUM(SUM(total_elapsed_time)) OVER() AS NUMERIC(5,2)) AS PctDuration
    ,SUM(total_worker_time) AS SumCPU
    ,CAST(100.0 * SUM(total_worker_time)
                / SUM(SUM(total_worker_time)) OVER() AS NUMERIC(5,2)) AS PctCPU
    ,SUM(total_logical_reads + total_logical_writes + total_physical_reads) AS [SumIO]
    ,CAST(100.0 * SUM(total_logical_reads + total_logical_writes + total_physical_reads)
                / SUM(SUM(total_logical_reads + total_logical_writes + total_physical_reads)) OVER() AS NUMERIC(5,2)) AS PctIO
    ,SUBSTRING(dest.text, (deqs.statement_start_offset/2)+1,
                            ((CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
                                  ELSE deqs.statement_end_offset
                                END - deqs.statement_start_offset)/2)+1) AS statementtext      
    ,deqs.query_plan_hash AS QueryPlanHash                                    
  FROM sys.dm_exec_query_stats AS deqs 
  CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

  WHERE deqs.last_execution_time > DATEADD(dd, -1, GETDATE()) 
    -- Exclude where the dbid is NULL
    AND dest.dbid IS NOT NULL
    -- Exclude the non-user databases  
    AND DB_NAME(dest.dbid) NOT IN ('master', 'msdb')  
        dest.text, deqs.statement_end_offset, deqs.statement_start_offset, deqs.plan_handle, deqs.last_execution_time, deqs.creation_time, 
          deqs.query_hash, dbid, dest.objectid, deqs.query_hash, deqs.query_plan_hash
| improve this answer | |
  • Thanks, I'll be sure to follow up on your query and delve deeper into what is happening on our DB. TBC! – Reaces May 21 '14 at 8:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.