We’ve all been there in the trenches. You get the call ‘the app is slow’, and within 10 seconds management are breathing down your neck. Sometimes it feels like you need a crystal ball to figure out if there is indeed a sql issue, or rather its raining in somewhere and someone is just miserable.
Working out which proc (if not ad hoc) is outside the scope of this post, but for this series I will explain how I dive into the tools available from within SQL Server to look at proc performance.
Part 1 looks at the procedure cache and query cache to look at execution performance such as below.
These DMV’s used to return stats are:
- sys.dm_exec_procedure_stats
- sys.dm_exec_query_stats
Sys.dm_exec_procedure_stats returns aggregate performance statistics for cached stored procedures. The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view. At that time, a Performance Statistics SQL trace event is raised similar to sys.dm_exec_query_stats.
Sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
The first part of the sql I use returns performance statistics for the stored procedure from sys.dm_exec_procedure_stats. I will demonstrate below with some dummy and real procs using the AdventureWorks2022 database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
create procedure dbo.proc_perf_test (@stmt1 int, @stmt2 int) as –Stmt1 select top(@stmt1) * from Person.Person –Stmt2 select top(@stmt2) * from Person.Person –exec proc exec dbo.proc_perf_test 10, 10000 |
You can see this took 2 seconds to execute on my environment, and we can confirm this by looking at the proc cache with sp_procperf.
If you execute the test proc a few more times you can see the averages present themselves :
You can also see the cached time and some compilation time parameters
The real key to this is seeing which part of the proc is the slow part, to do this I am filtering the query cache by the proc’s object ID.
|
1 2 3 4 5 6 |
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) est CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) qp left JOIN sys.objects o ON o.object_id = est.objectid left JOIN sys.schemas s ON s.schema_id = o.schema_id WHERE OBJECT_NAME(est.objectid, est.dbid) = @proc |
Bringing this all together you have one easy view to see where it hurts in the proc, as well the execution plan for each statement:
If you mark SP_ProcPerf as a system proc you can then run it from the context of any database.
The proc can be found here https://github.com/oflindall/DBACentral/blob/main/SP_ProcPerf.sql
Comments
One response to “How to analyse Stored Procedure performance: part 1”
Great article!