A stored procedure is a named, executable database program that packages operations for reuse. In administration work, it can support performance analysis or automate a multi-step task. section 10.3 section 10.6.3
Useful roles include:
- Analyzing execution count, average elapsed time, CPU time, and logical reads to find procedures needing optimization. section 10.3
- Accepting parameters so one procedure can perform a repeatable task for different databases or file paths. section 10.6.3
Performance triage
Run a query that lists the ten procedures with the highest average execution time, then inspect execution count, CPU, and logical reads before optimizing. section 10.3
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,
ps.total_elapsed_time / ps.execution_count AS AvgElapsedTime,
ps.total_worker_time / ps.execution_count AS AvgCPU,
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 the listing as a compact SQL Server starting point for ranking procedures by average execution time. section 10.3
