I'm fixing performance issues on a multistatement stored procedure in SQL Server. I want to know which part(s) I should spend time on.
I understand from How do I read Query Cost, and is it always a percentage? that even when SSMS is told to Include Actual Execution Plan, the "Query cost (relative to the batch)" figures is still based on cost estimates, which can be far off actuals
I understand from Measuring Query Performance : “Execution Plan Query Cost” vs “Time Taken” that I can surround invocation of the stored procedure with SET STATISTICS TIME
statements, and I will then get a list like this in the Messages
pane:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
[etc]
SQL Server Execution Times:
CPU time = 187 ms, elapsed time = 206 ms.
with one output message for each statement.
I can 'easily' (though not conveniently) associate the time statistics output with the statement-by-statement execution plans in the Execution plan pane, by counting them: The fourth SQL Server Execution Times
message output corresponds to Query 4
in the Execution plan pane, and so on.
But is there a better way?