Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Several Methods to collect SQL Server Stored Procedure Execution History


By:   |   Read Comments (13)   |   Related Tips: More > Stored Procedures

Problem

One of our developers after reviewing the SQL Server database objects of an old database found that the database has too many stored procedures to reasonably manage them all. He thinks that most of the procedures are not in use, but he wants to get more information about the stored procedure usage before we delete them. Our task is to provide the developers with a list of stored procedures for specific databases with the date when the stored procedures was last executed.  What are the options to accomplish this task?

Solution

In this tip we will provide several monitoring methods that could be utilized by DBAs to determine stored procedure execution history.

Capture SQL Server Stored Procedure History with Profiler

The quick and simple way to monitor the stored procedures execution is setting up a Trace with SQL Server Profiler. This is probably good for a quick check when a user runs a process in Test and you want to capture what is he/she running. Unfortunately, this method has several disadvantages:

  • Profiler adds performance overhead to the server
  • A single session does not "survive" a server reboot
  • Profiler is being replaced with Extended Events

To access SQL Server Profiler navigate to Start | All Programs | SQL Server 2012 | Performance Tools | SQL Server Profiler.

To start a new session, navigate to File | New Trace.  Connect to your SQL Server instance when prompted.  On the Trace Properties screen, click on the Events Selection tab and select the SP:Completed counter in the Stored Procedures grouping of counters.  Click on the General Tab to save the results to a table or file.

If you do not see this counter, check the "Show all events" check box on the bottom right of the interface.

Profiler - select columns and events

Set he columns filter to capture only the database that you are monitoring, which is accessed by clicking on the "Column Filters" button on the bottom right of the interface (see image above).

Profiler filter

Run the trace by clicking on the Run button.

Stop it after the period of time required for the monitoring.

Review the results:

Using Profiler

Read more tips about SQL Server Profiler.

Capture SQL Server Stored Procedure History with Trace

This method described in this section is similar to SQL Profiler, but is more flexible and could be scheduled to start automatically after SQL Server restarts.  It is also script based rather than using a GUI.

SQL Server Trace Script

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 10 

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 2, N'c:\MyFolder\MyTrace\SP_usage', 
     @maxfilesize, NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 8, @on
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 34, @on
exec sp_trace_setevent @TraceID, 43, 35, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 35, 0, 6, N'_DemoDB1'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

Read more about SQL Trace here.

Both methods above should be used in older environments (for example, SQL Server 2005) that do not support Extended Events. Starting with SQL Server 2008 Microsoft introduced Extended Events that will replace Profiler and server side traces. Keep in mind that both of these will be deprecated in future versions of the SQL Server.

Using DMVs and SQL Server Agent Job to Capture Stored Procedure History

This method utilizes the sys.dm_exec_procedure_stats dynamic management view. It requires a table that will be populated with the DMV data.

To create the table use the following script:

CREATE TABLE dbo._demo_sp_exec_stats 
    (object_id INT,
    database_id INT,
    proc_name NVARCHAR(128),
    last_execution_time DATETIME)

Then create a SQL Server Agent Job that will run every minute with this step:

MERGE INTO dbo._demo_sp_exec_stats STAT
USING 
    (SELECT  d.object_id, d.database_id, 
  OBJECT_NAME(object_id, database_id) AS proc_name, 
  d.last_execution_time
  FROM sys.dm_exec_procedure_stats AS d
  WHERE d.database_id = DB_ID('_DemoDB')  ) AS SRC
    ON STAT.object_id = SRC.object_id
WHEN MATCHED 
 AND STAT.last_execution_time <> SRC.last_execution_time THEN
    UPDATE SET
        last_execution_time = SRC.last_execution_time
WHEN NOT MATCHED THEN
INSERT  (object_id,
 database_id,
 proc_name,
 last_execution_time)
VALUES (SRC.object_id, SRC.database_id, 
 SRC.proc_name, SRC.last_execution_time) ; 

To view the results, query the dbo._demo_sp_exec_stats table:

SQL Agent monitoring results

Disadvantages of this method:

  • It requires a table that will hold historical statistics (but you may already have a database that stores historical statistics and performance data)
  • Execution statistics on a very busy SQL Server could be removed from the DMV before the job runs and therefore you miss data. The row with procedure execution statistics will be removed after the stored procedure is removed from the SQL Server cache (read more here).

Using a Custom Data Collector to Capture SQL Server Stored Procedure Usage

You can use this this solution if you already have Management Data Warehouse (MDW) setup in your environment. Read more tips about Management Data Warehouse here.

Using a query similar to the one that we used in the SQL Server Agent Job above, we can create a Custom Collection Set that uses the generic T-SQL Query Collector type. The advantage of this method is that you do not lose procedure execution history when this collector is in cache mode.

Here is the script for the custom data collector setup (the last statement will start the data collector):

USE msdb
GO
Begin Transaction
Begin Try
Declare @collection_set_id_15 int
Declare @collection_set_uid_16 uniqueidentifier
EXEC [msdb].[dbo].[sp_syscollector_create_collection_set] 
 @name=N'Stored Procedure Usage - "_DemoDB" database', 
 @collection_mode=0, -- cached mode
 @description=N'Collects information about stored procedures use.', 
 @logging_level=1, 
 @days_until_expiration=30,-- set to the number of days you need to keep the history
 @schedule_name=N'CollectorSchedule_Every_30min', -- collector upload schedule
 @collection_set_id=@collection_set_id_15 OUTPUT, 
 @collection_set_uid=@collection_set_uid_16 OUTPUT

Select @collection_set_id_15, @collection_set_uid_16

Declare @collector_type_uid_19 uniqueidentifier
Select @collector_type_uid_19 = collector_type_uid 
 From [msdb].[dbo].[syscollector_collector_types] 
 Where name = N'Generic T-SQL Query Collector Type';

Declare @collection_item_id_20 int
EXEC [msdb].[dbo].[sp_syscollector_create_collection_item] 
 @name=N'Stored Procedure Usage Statistics', 
 @parameters=N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType"><Query><Value>
SELECT  d.object_id , 
        d.database_id AS [db_id], 
        db_name(d.database_id) AS [db_name], 
        OBJECT_NAME(object_id, database_id) AS [sp_name],
        d.last_execution_time, 
        d.execution_count 
FROM sys.dm_exec_procedure_stats AS d 
WHERE d.database_id = DB_ID(''_DemoDB'') </Value>
 <OutputTable>sp_usage_stats</OutputTable></Query>
 <Databases><Database>_DemoDB</Database></Databases></ns:TSQLQueryCollector>', 
 @collection_item_id=@collection_item_id_20 OUTPUT, 
 @frequency=5, 
 @collection_set_id=@collection_set_id_15, 
 @collector_type_uid=@collector_type_uid_19
Select @collection_item_id_20

Commit Transaction;
End Try
Begin Catch
Rollback Transaction;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorNumber INT;
DECLARE @ErrorLine INT;
DECLARE @ErrorProcedure NVARCHAR(200);
SELECT @ErrorLine = ERROR_LINE(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE(),
       @ErrorNumber = ERROR_NUMBER(),
       @ErrorMessage = ERROR_MESSAGE(),
       @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR (14684, @ErrorSeverity, 1 , @ErrorNumber, @ErrorSeverity, 
   @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);

End Catch;

GO
USE msdb;
GO
DECLARE @collection_set INT

select @collection_set = collection_set_id from dbo.syscollector_collection_sets 
WHERE name = 'Stored Procedure Usage - "_DemoDB" database'

EXEC sp_syscollector_start_collection_set @collection_set_id = @collection_set;

This will create the following:

  • A table in the Management Data Warehouse database (which could be on a different SQL Server). The table will be created under the "custom_snapshots" schema:


  • MDW collection table

    Where the name of the table is from sp_syscollector_create_collection_item stored procedure's parameter (@parameters, OutputTable tag):

     ...</Value><OutputTable>sp_usage_stats</OutputTable></Query>...
     
  • A data collection set:
    custom collection


    Note in the Data Collection Set properties that the data collection mode is cached, the account is "Run as: UtilityAgentProxy") and the data retention is 30 days:

    custom collection properties

  • Two SQL Server Agent jobs (where "10" is the data collection ID):
  • jobs

In order to review historical data you will need to query the custom_snapshots.sp_usage_stats table in MDW database:

USE sysutility_mdw
GO
SELECT sp_name,
      max(last_execution_time) as last_execution_time     
  FROM custom_snapshots.sp_usage_stats
  GROUP BY sp_name
GO 

results

The disadvantages of this method:

  • MDW is required (follow the steps in this article to configure it in your environment). The database for the MDW can grow very fast.
  • Large amounts of data in the sp_usage_stats table (each snapshot will create a separate row) compared to the SQL Server Agent job method.

Using Database Audit to Capture SQL Server Stored Procedure Usage

In SQL Server 2008, the SQL Server Audit feature was introduced. With this option, we can use database-level auditing to monitor the stored procedure executions.

The advantage of this method is that it utilizes the Extended Events feature (which means less load on the server), this method will capture all execution events and also there will be only one record for each execution (much less data compared to the data collector method).

Read this tip about the SQL Server Auditing Feature.

First, we need to create Server Audit object:

USE [master]
GO
CREATE SERVER AUDIT [SrvAudit_sp_execution]
TO FILE 
( FILEPATH = N'E:\DBA_Audit'
 ,MAXSIZE = 20 MB
 ,MAX_ROLLOVER_FILES = 50
 ,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000  -- equal to 1 second
 ,ON_FAILURE = CONTINUE
)
GO

The Server Audit object is created, but not started:

Server Audit

Now we need to create a Database Audit Specification:

USE [_DemoDB]
GO
CREATE DATABASE AUDIT SPECIFICATION [DBAudit_sp_execution]
FOR SERVER AUDIT [SrvAudit_sp_execution]
GO

The Database Audit Specification is created, but not started:

Database Audit Specification

To generate a query for adding all of the stored procedures in the database to the audit we will use the following query:

USE [_DemoDB]
GO
SET NOCOUNT ON
SELECT '
ALTER DATABASE AUDIT SPECIFICATION [DBAudit_sp_execution]
FOR SERVER AUDIT [SrvAudit_sp_execution]
    ADD (EXECUTE ON OBJECT::dbo.'+name+ ' BY [public]) ' FROM sys.objects WHERE type='P'
GO

This is useful when the database has hundreds of stored procedures.

Copy the results and execute it in SQL Server Management Studio:

USE [_DemoDB]
GO
ALTER DATABASE AUDIT SPECIFICATION [DBAudit_sp_execution]
FOR SERVER AUDIT [SrvAudit_sp_execution]
    ADD (EXECUTE ON OBJECT::dbo.proc1 BY [public]) ;

ALTER DATABASE AUDIT SPECIFICATION [DBAudit_sp_execution]
FOR SERVER AUDIT [SrvAudit_sp_execution]
    ADD (EXECUTE ON OBJECT::dbo.proc2 BY [public]) ;
GO

Here is how it looks in the Database Audit Specification now:

Database Audit Specification properties

Run the following script to enable the Server Audit:

USE [_DemoDB]
GO
ALTER SERVER AUDIT [SrvAudit_sp_execution]
WITH (STATE = ON);
GO

Run the following script to enable the Database Audit Specification:

USE [_DemoDB]
GO
ALTER DATABASE AUDIT SPECIFICATION [DBAudit_sp_execution]
FOR SERVER AUDIT [SrvAudit_sp_execution]   
    WITH (STATE = ON);
GO

To view the results you can use 2 methods:

  • Right click on Server Audit object and click on "View Audit Log":

  •  
    results


    results

  • Query the audit files directly to limit the number of columns/rows or to filter by the stored procedure name:

    SELECT MAX(event_time) last_exec_time, [object_name] 
    FROM sys.fn_get_audit_file 
    ('E:\DBA_Audit\SrvAudit_sp_execution_20898025-...0.sqlaudit',default,default) 
     WHERE action_id = 'EX'
     GROUP BY [object_name]
    UNION ALL
    SELECT MAX(event_time) last_exec_time, [object_name] 
    FROM sys.fn_get_audit_file 
    ('E:\DBA_Audit\SrvAudit_sp_execution_20898025-...0.sqlaudit',default,default) 
     WHERE action_id = 'EX'
     GROUP BY [object_name]
     

    Note: If you monitor the stored procedures execution for a long period of time you may have several audit files. You will need to run a "UNION" query for all these files similar to the query above if you want to use a T-SQL query.

The disadvantages of this method:

  • SQL Server Enterprise Edition is required in order to use Database Audit Specification
  • Unable to track client host names
  • Does not capture new stored procedures executions, you will need to add them to the audit manually after the stored procedure is created

Using Extended Events to Capture SQL Server Stored Procedure Usage

The last method we will explore in this tip for capturing stored procedure execution is using Extended Events. Extended Events were introduced in SQL Server 2008. Read more here about Extended Events.

The advantages of this method:

  • Extended Events is a light weight performance monitoring system that has very little impact on the database server
  • This is relatively new monitoring system that in the future will replace SQL Server Profiler, so DBAs need to replace trace monitoring with Extended Events.

Create the new event session and start it with the following script (replace "[source_database_id]=(9)" and files locations with your values):

CREATE EVENT SESSION [EXEC_SP] ON SERVER 
ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1), 
     collect_statement=(0)
    ACTION(sqlserver.client_app_name, 
     sqlserver.client_hostname,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.username)
    WHERE (([object_type]=(8272)) 
     AND ([source_database_id]=(9)))) -- replace with your database ID
 ADD TARGET package0.asynchronous_file_target
  (
    SET FILENAME = N'E:\DBA_Audit\SP_Exec.xel',
    METADATAFILE = N'E:\DBA_Audit\SP_Exec.xem'
  );
GO

ALTER EVENT SESSION [EXEC_SP] ON SERVER
  STATE = START;
GO

To review events data you can run this query:

;WITH ee_data AS 
(
  SELECT data = CONVERT(XML, event_data)
    FROM sys.fn_xe_file_target_read_file(
   'E:\DBA_Audit\SP_Exec*.xel', 
   'E:\DBA_Audit\SP_Exec*.xem', 
   NULL, NULL
 )
),
tab AS
(
 SELECT 
  [host] = data.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(400)'),
  app_name = data.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(400)'),
  username = data.value('(event/action[@name="username"]/value)[1]','nvarchar(400)'),
  [object_name] = data.value('(event/data[@name="object_name"]/value)[1]','nvarchar(250)'),
  [timestamp] = data.value('(event/@timestamp)[1]','datetime2')
 FROM ee_data
)
SELECT DISTINCT [host], app_name, username, MAX([timestamp]) as last_executed, 
  COUNT([object_name]) as number_of_executions, [object_name]
  FROM tab 
  GROUP BY [host], app_name, username, [object_name] ;

The results will look similar to this:

results

Next Steps
  • Evaluate each of these options, then select the best method based on your needs to monitor stored procedure executions when you inherit a database or need to tidy up old stored procedures.


Last Update:






About the author
MSSQLTips author Svetlana Golovko Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, June 19, 2017 - 2:32:41 PM - Svetlana Golovko Back To Top

 No. The systems that have this type of audit requirements already have logging capability.

The SP execution history in our case was required for the procedures usage and cleanup afterwards.


Monday, June 19, 2017 - 2:04:17 PM - Leif Peterson Back To Top

Very nice overview.  Thanks for that.  We are weighing the SS Database Audit against writing a custom in-house logging method to embed in all the procs that would also capture the params and values so we could use the log as a workload for stress tests and baseline performance metrics.  Was that a considertation for your team?


Friday, June 09, 2017 - 2:09:14 PM - Svetlana Golovko Back To Top

 

 Hi Eric

 

Yes. And we use it (sys.dm_exec_procedure_stats) in one of our examples.

 

Thanks for reading,

Svetlana


Thursday, June 08, 2017 - 6:47:32 PM - eric81 Back To Top

 Aren't there DMV's to track this information as well?

 


Wednesday, December 07, 2016 - 7:57:12 PM - Reza Back To Top

 

 

Thanks for sharing this comprehensive document. you have done the amazing job.


Saturday, January 23, 2016 - 4:34:48 PM - Svetlana Back To Top

Hi Rafael,

 

If you are using extended events then you need to use "module_end" extended event instead of "sp_statement_completed".

I am not an expert in Extended Events, but I think this should work in your case.

CREATE EVENT SESSION [EXEC_SP_Test1] ON SERVER
ADD EVENT sqlserver.module_end(
    ACTION(sqlserver.client_app_name,
 sqlserver.client_hostname,
 sqlserver.database_id,
 sqlserver.database_name,
 sqlserver.request_id,
 sqlserver.username)
    WHERE ([sqlserver].[database_id]=(49)))
ADD TARGET package0.asynchronous_file_target
  (
    SET FILENAME = N'E:\DBA_Audit\SP_Exec_Test1.xel',
    METADATAFILE = N'E:\DBA_Audit\SP_Exec_Test1.xem'
  );
GO

Cheers,

Svetlana


Monday, January 11, 2016 - 8:47:00 AM - Rafael Dontal Goncalez Back To Top

 Awesome post, Svetlana, thanks for that.

I am trying to use your code as base and added the column duration to the output. I have a problem, for procedures with more than one transaction, the output shows a row for each transaction inside this sp. It is ok, so I though I'd simply group by a column and sum the duration.  I've tried using transaction id, but as I said, I have more than one id for each proper execution. If I group by session id I will be grouping more rows than it was suppose to. Therefore I don't have an accurate procedure duration. Do you know any way to group all these rows in just one? A column that make that whole execution unique?

Thanks a million.

 


Wednesday, August 19, 2015 - 7:26:33 PM - Svetlana Back To Top

Thank you, Abrar!


Tuesday, August 18, 2015 - 3:15:33 AM - Abrar Back To Top

Superb Post!


Thursday, May 07, 2015 - 9:47:30 PM - Svetlana Back To Top

The script for the Extended Event above is for the SQL Server 2012 and higher. For SQL Server 2008 you will need to make the following modifications:

  • Comment these lines in "create event" script

--SET collect_object_name=(1),
--collect_statement=(0)
--sqlserver.database_name,

  • replace object_name with object_id in "SELECT" script

Wednesday, April 29, 2015 - 6:08:59 AM - BuahahaXD Back To Top

The create event session code produces the following error:

Msg 25623, Level 16, State 3, Line 1

The event action name, "sqlserver.database_name", is invalid, or the object could not be found

 

I'm new to Extended Events and don't know how to fix it. I'm using SQL Server 2008 R2 Standard.


Sunday, July 13, 2014 - 6:03:34 PM - Svetlana Golovko Back To Top

Absolutely agree. And thanks for the comment, Piquet.


Thursday, July 10, 2014 - 6:23:29 PM - Piquet Back To Top

Database Audit Specification Tip: Rather than creating many separate Audit Actions (one for each stored-procedure), it would be easier and result in better audit capture to create a single "EXECUTE" Audit Action on the "DATABASE" Object Class for each database you wish to Audit. This audit specification would capture any EXECUTE activity within the specified database (i.e. execution of all stored procedures in the database).

This approach is not only much easier and simpler to setup, but also picks up any executions of Stored Procedures that may be created after the Audit has been setup.

I also expect that auditing a single database-wide event would be more efficient / less server load than auditing many actions (i.e. one audit action per stored-procedure in the database.

Piquet


Learn more about SQL Server tools