I would like to monitor my slow running SQL Server stored procedures

Question

I have a long list of a number of slow running stored procedures in one of our databases which I would like to monitor. We are running SQL Server 2012 Enterprise edition. I am aware that SQL Server profiler is no longer the recommended tool to use for this, any recommendations?

Thanks in advance

Answer 1

SQL Server supports collecting execution statistics for natively compiled stored procedures, both on the procedure level and the query level. Collecting execution statistics is not enabled by default due to performance impact.

You can enable and disable statistics collection on natively compiled stored procedures using sys.sp_xtp_control_proc_exec_stats (Transact-SQL).

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-xtp-control-proc-exec-stats-transact-sql

Answer 2

Extended Events is the replacement for Profiler/SQL Trace. Many of the events available with Profiler are also available in Extended Events, plus many others.

If you currently have a filtered SQL trace including rpc_completed and sql_batch_completed events writing to a rollover file, the DDL below creates an XE trace to capture these same events to an extended event file with rollover.

CREATE EVENT SESSION [cache] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    WHERE ([sqlserver].[like_i_sql_unicode_string]([statement],N'proc1') 
    OR [sqlserver].[like_i_sql_unicode_string]([statement],N'proc2') 
    OR [sqlserver].[like_i_sql_unicode_string]([statement],N'proc3'))),
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([batch_text],N'proc1') 
    OR [sqlserver].[like_i_sql_unicode_string]([batch_text],N'proc2') 
    OR [sqlserver].[like_i_sql_unicode_string]([batch_text],N'proc3')))
ADD TARGET package0.event_file(SET filename=N'selected_proc_trace',max_file_size=(1024),max_rollover_files=(5))
WITH (
    MAX_MEMORY=4096 KB
    ,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS
    ,MAX_DISPATCH_LATENCY=30 SECONDS
    ,MAX_EVENT_SIZE=0 KB
    ,MEMORY_PARTITION_MODE=NONE
    ,TRACK_CAUSALITY=OFF
    ,STARTUP_STATE=OFF);
GO

Extended Event traces can be created, managed, and data viewed with SSMS Object Explorer under Management-->Extended Events-->Sessions. The tool also includes templates for the equivalent Profiler templates.