Analyzing Stored Procedure Performance with SQL Server Query Statistics

 Stored procedures are a powerful feature of SQL Server that can help you improve query performance, reduce code complexity, and enhance security. However, to fully realize the benefits of stored procedures, you need to be able to analyze their performance and identify potential issues that may be impacting your application's performance.

One way to analyze stored procedure performance in SQL Server is to use the sys.dm_exec_query_stats dynamic management view (DMV). This DMV contains performance statistics for all SQL Server queries that have been executed since the last time the server was restarted. By querying this DMV and joining it with other system views, you can gather a wealth of information about how your stored procedures are performing and where they may be encountering performance bottlenecks.

Here's an example SQL Server query that uses the sys.dm_exec_query_stats DMV to gather performance statistics for all stored procedures in a database:

SELECT OBJECT_NAME(qt.objectid) AS [Stored Procedure Name], SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS [Query Text], qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.total_worker_time/1000000.0 AS [Total CPU Time (s)], qs.last_worker_time/1000000.0 AS [Last CPU Time (s)], qs.total_elapsed_time/1000000 AS [Total Elapsed Time (s)], qs.last_elapsed_time/1000000 AS [Last Elapsed Time (s)], qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE OBJECT_NAME(qt.objectid) IS NOT NULL ORDER BY qs.total_worker_time DESC -- CPU time