When you have a stored procedure or application query that has multiple queries inside of it, how can you tell which query to focus on?
For starters, do NOT believe the percentages you see in execution plans.
Let’s take a classic performance tuning issue: scalar functions. I’ll start with the Stack Overflow database – if you want to play at home, best to use the smallest database you can because this query performs terribly – then create a function and a stored procedure:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
CREATE OR ALTER FUNCTION dbo.fnGetPostType ( @PostTypeId INT ) RETURNS NVARCHAR(50) AS BEGIN DECLARE @PostType NVARCHAR(50); SELECT @PostType = [Type] FROM dbo.PostTypes WHERE Id = @PostTypeId; IF @PostType IS NULL SET @PostType = 'Unknown'; RETURN @PostType; END; GO CREATE OR ALTER PROC dbo.usp_TwoStatementsOneBatch @DisplayName NVARCHAR(40) AS BEGIN SELECT u.AboutMe, c.Text, p.Body, b.Name, v.BountyAmount FROM dbo.Users u CROSS JOIN dbo.Posts p CROSS JOIN dbo.Comments c CROSS JOIN dbo.Badges b CROSS JOIN dbo.Votes v WHERE u.Reputation + u.UpVotes < 0 AND u.DisplayName = @DisplayName ORDER BY u.AboutMe, c.Text, p.Body, b.Name, v.BountyAmount; SELECT p.Title AS QuestionTitle, dbo.fnGetPostType(p.PostTypeId) AS PostType, c.CreationDate, c.Score, c.Text, CAST(p.CreationDate AS NVARCHAR(50)) AS QuestionDate FROM dbo.Users u LEFT OUTER JOIN dbo.Comments c ON u.Id = c.UserId LEFT OUTER JOIN dbo.Posts p ON c.PostId = p.ParentId WHERE u.DisplayName = @DisplayName; END GO |
Then I’ll call the stored procedure, which takes 30 seconds even on the smallest Stack Overflow database:
|
1 |
EXEC usp_TwoStatementsOneBatch N'Brent Ozar'; |
And then use sp_BlitzCache to ask which queries ran the longest:
|
1 |
EXEC sp_BlitzCache @StoredProcName = 'usp_TwoStatementsOneBatch', @SortOrder = 'duration'; |
I’ve rearranged the sp_BlitzCache output columns in this screenshot to tell the story better:
In the result sets:
- The stored procedure itself shows up first with a total duration of 33,365 milliseconds
- One specific statement in the proc took 33,221 milliseconds of the above
- Another statement took just 143 milliseconds
When I click on any query plan in the “Query Plan” column, I’m going to get the query plan for the entire batch, not that specific statement. It doesn’t matter whether I click on the plan in line 1, 2, or 3 – I’m going to get the entire stored proc’s plan. That gets a little confusing because now, looking at the plan, we have to figure out which query took 33 seconds, because that’s the one we need to tune. Can you guess which one it is?
I’ll zoom in a little to help:
You might guess that it’s Query 1.
It has the biggest arrows and shows 100% of the cost.
Except that’s wrong.
That’s 100% of the estimated cost, calculated before the query executed.
Take another look at the sp_BlitzCache output, and pay particular attention to the Cost column:
The top query is the stored procedure, which took 33.3 seconds in total. Its total cost is so large that SQL Server resorted to scientific notation. The second query, which took the vast majority of the time, only cost 726 query bucks – way, way less than line 3.
Hover your mouse over the root node in each plan (in this case, the selects) and look at their costs. The one that cost 726 query bucks is actually Query 2, not Query 1:
To find the right query to tune,
listen to sp_BlitzCache.
When you’re looking at sp_BlitzCache’s output for multi-statement queries, pay particular attention to the “Cost” column.
Don’t look at the highest cost – instead, just look at the statement that ranks first in sp_BlitzCache’s output, then make a note of its cost. When you open up the query’s execution plan, look for that statement & that cost number – and that’s the real query that you need to focus on tuning. It’s often very different than the one with the highest estimated cost.
People who liked this post also liked Erik Darling’s technique for getting specific query plans from long stored procedures. He edits the proc to turn on SET STATISTICS XML ON right before the specific query runs, and then turning it back off again immediately afterwards. Slick!
Want to learn more query tuning tricks?
You’ll love my 3-day Mastering Query Tuning class. I give you live, running workloads hitting the Stack Overflow database, and you have to investigate & solve the issues using tricks I taught you in class. The upcoming rotations are:
- Dec 11-13: Mastering Query Tuning – iCal (weekend)
- Jan 12-14: Mastering Query Tuning – iCal
And you can join in live anytime if you have a Live Class Season Pass, or watch the Instant Replays.
5 Comments. Leave new
Why aren’t you using sys.dm_exec_text_query_plan? That way you could cut out relevant xml part as text and try cast it into xml
You mean in sp_BlitzCache? We do check in there, actually – you can read the source code for that. We don’t render plans from there, though, since they can be pretty large.
You mean they can have more than 128 levels of nested nodes? I guess I forgot about that. Thanks for reminding me.
Hi Brent, just want to say a quick thank you for putting in the effort to write and sharing this knowledge for free!
Glad you liked it, Luke!