SQL SERVER – Stored Procedure Timing and Cached Measurements

SET STATISTICS TIME reports CPU and elapsed execution time for Stored Procedures. A customer wanted to measure a run without adding statements manually.

-- Substitute an approved procedure and arguments; it may change data.
-- SET STATISTICS TIME ON;
-- EXEC dbo.YourProcedure;
-- SET STATISTICS TIME OFF;
SELECT DB_NAME(database_id) AS DatabaseName,OBJECT_NAME(object_id,database_id) AS ProcedureName,
       execution_count,last_elapsed_time/1000.0 AS LastElapsedMs,
       total_elapsed_time/1000.0/NULLIF(execution_count,0) AS AverageElapsedMs
FROM sys.dm_exec_procedure_stats WHERE database_id=DB_ID()
ORDER BY AverageElapsedMs DESC;

Enable the setting for a controlled run with known parameters, then inspect Messages and disable it afterward. The four old timing lines were illustrative. Nested statements and compilation can produce multiple messages. There is no universal four-line output contract.

Waits can increase elapsed time. Parallel-worker CPU can exceed wall-clock duration. Network transfer, rendering and application processing also need separate end-to-end measurements.

The DMV reports cached procedure history in milliseconds. Entries and counters last only while the plan remains cached. It does not retain permanent history. Use completed events or suitable persisted monitoring when a retained execution record is required.

Reference: Cached stored-procedure timings.

Related reading

• Comprehensive Database Performance Health Check
• SQL in Sixty Seconds

Cached procedure history is not a measurement of one new execution, it is accumulated evidence while the plan remains cached.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.