Master of None

I found a useful script to find executions of stored procedure. This will give you a good idea of what is utilization. Run it multiple times to compare how often a procedure of group of procedures executes within certain time periods.

Stored Procedure Executions

For example, you have a under performing billing process. Your system fires multiple procedures multiple times. You could use something like this to get an idea of execution counts, worker time, average elapsed time and max logical reads. It will point you in the right direction procedure that can be optimized. Tweak this to fit your needs.

 

/*
 Object Executions
 1/6/2016 JCD
 Adapted from http://www.databasejournal.com/features/mssql/article.php/3687186/Monitoring-Stored-Procedure-Usage.htm
 */

SET NOCOUNT ON
 GO
 SELECT CAST(SERVERPROPERTY('ServerName') AS VARCHAR(60)) [instance]
 ,CAST(DB_Name() as VARCHAR(128)) [database]
 ,OBJECT_SCHEMA_NAME(qt.objectid ,dbid)+'.'+OBJECT_NAME(qt.objectid ,dbid) [object]
 ,(SELECT type from sys.sysobjects where id = qt.objectid) [object_type]
 ,CONVERT(VARCHAR(20),qs.last_execution_time, 22) [last_execution_time]
 ,qs.total_worker_time
 ,qs.execution_count
 ,ISNULL(qs.total_elapsed_time/qs.execution_count, 0) [avg_elapsed_time]
 ,qs.max_logical_reads
 ,CONVERT(VARCHAR(20),GETDATE(), 22)[sample_time]
 FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
 CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
 WHERE OBJECTPROPERTY(objectid, 'ISMSShipped')= 0 -- Exclude system objects
 AND qt.[dbid] = DB_ID() -- Filter by current database
AND qt.objectid = OBJECT_ID('dbo.CleanBrokenSnapshots')-- Filter by specific object
 ORDER BY 3,4
 GO

Have fun.

Tiny URL for this post:
 Copy