Several Methods to collect SQL Server Stored Procedure Execution History

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:

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.

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).

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.

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.

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).

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):

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:

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

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

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).

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):

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
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

Thank you for the excellent post, Svetlana. Just wanted to add an example of tracking execution of a specific stored procedure. A procedure can be run with a remote procedure call (RPC) or as part of a SQL batch. In the former case, we need rpc_starting/rpc_completed objects, in the latter module_start/module_end. If you also want to monitor individual statements within the procedure, you can use sp_statement_starting/sp_statement_completed, as in your example. Bear in mind that in the case of filtering statements, you cannot use the object name as a filter as it is not present in the data (though the column appears to be available). To filter individual statements, you must use the id of the object, which is obviously specific to a particular database and will be different between environments. The object_id needs to be retrieved before creating the session, for example: SELECTOBJECT_ID('SchemaName.usp_StoredProcedureName')

An example of a session script with a filter for a particular store procedure can look as follows:

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows. [SQLSTATE 42000] (Error 8672). The step failed.

Dear, in my environment I have several databases and I need when I create a new database I will automatically create a DATABASE AUDIT SPECIFICATION. When I run the audit command SQL returns the error below.

Msg 574, Level 16, State 0, Line 2

CREATE AUDIT SPECIFICATION statement can not be used inside a user transaction.

The statement has been terminated.

Could you please let me know if the error is an audit limitation or if I am wrong in something and how could I solve this.

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?

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?

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

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.