A dynamic management view (DMV) is a SQL Server system view that exposes current database-engine activity and performance information. When a database has suffered performance problems without historical monitoring, DMVs provide a practical starting point for investigation. section 10.1
Finding poorly performing stored procedures
Ask for a T-SQL statement that identifies the worst-performing stored procedures. The resulting query uses sys.dm_exec_procedure_stats, returns the top 10 procedures with database, object, execution-count, elapsed-time, CPU, and logical-read metrics, and orders them by average elapsed time. Verify the results before acting: the statistics cover procedures currently in the cache, so removed entries may be absent. section 10.1
-- Top 10 worst-performing stored procedures based on total elapsed time
SELECT TOP 10
DB_NAME(ps.database_id) AS DatabaseName,
OBJECT_NAME(ps.[object_id], ps.database_id) AS StoredProcedure,
ps.execution_count AS ExecutionCount,
-- Avg elapsed time in microseconds
ps.total_elapsed_time / ps.execution_count AS AvgElapsedTime,
-- Avg CPU time in microseconds
ps.total_worker_time / ps.execution_count AS AvgCPU,
-- Avg logical reads
ps.total_logical_reads / ps.execution_count AS AvgReads
FROM sys.dm_exec_procedure_stats AS ps
ORDER BY ps.total_elapsed_time / ps.execution_count DESC;
Use this reusable query to inspect cached stored-procedure performance and compare execution time, CPU, and logical reads. section 10.1
