Most Costly Statement in a Stored Procedure

A lot of stored procedures have multiple statements and determining the most costly statement in a given proc is a very common task. After all, you want to focus your time and efforts on fixing the things that cause you the most pain. You simply don’t have the time to tune every single statement in every single procedure. So, identifying the most costly statement is vital.

Happily, Extended Events are here to help.

Finding a Costly Statement

Query tuning is initially an act of discovery. Which queries, batches, procedures are inflicting the most pain on us. That pain could be measured a bunch of ways. The three most common, in particular order, are:

1. Frequency with with a given query/batch/procedure is called.
2. Resources used by that query.
3. Length of time that it takes the query to run.

You wouldn’t want to focus only on long running queries because you may have a very short query that gets called thousands of times a minute. Tuning that query would be better than a long running one. Or, you wouldn’t want to focus only on queries that are called a lot if a particular query is consuming all the CPU or Memory or I/O. So, all three are important.

However, once you’ve identified a particular procedure as a problem child, you’re often going to find that proc has 2, 5, 350 statements in it. Which one is causing us the most pain?

sp_statement_completed is the event we’re going to use to identify costly statements. We could use this alone. Once we’ve identified which procedure we care about, you can get that object_id for the procedure and filter sp_statement_completed by that value. However, for our purposes, I’m going to combine sp_statement_completed with rpc_completed. This gives me the ability to see each statement, as well as the completed procedure.

However, this adds a wrinkle to our filtering. rpc_completed does not have the object_id. Further, there’s no Action for object_id. So we can only use the object_name with rpc_completed. What about sp_statement_completed?

sp_statement_completed comes with an optional field in the event for object_name. You can see it in the Event Fields tab in the gui:

Selecting the object_name field above would allow us to filter by object_name in both rpc_completed and sp_statement_completed. Our finished session would then look like this:

CREATE EVENT SESSION MostCostlyStatements
ON SERVER
ADD EVENT sqlserver.rpc_completed
(WHERE (
object_name = N'ProductTransactionHistoryByReference'
AND sqlserver.database_name = N'AdventureWorks'
)
),
ADD EVENT sqlserver.sp_statement_completed
(SET collect_object_name = (1),
collect_statement = (1)
WHERE (
object_name = N'ProductTransactionHistoryByReference'
AND sqlserver.database_name = N'AdventureWorks'
)
)
ADD TARGET package0.event_file
(SET filename = N'MostCostlyStatements')
WITH
(
TRACK_CAUSALITY = ON
);

You can see the ‘SET collect_object_name = (1)’ to enable the object_name. Then, it’s just filtered as any other value.

Conclusion

From there, it’s just a question of deciding how you’d like to filter or sort the data. I’ve shown in previous posts (and videos) how to consume the data. Just look for the most costly statement, based on what you’re currently defining as most costly, and you’re good to go.