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 sproc with
SET STATISTICS TIME statements, and I will then get a list like this in the
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?