Performance Tuning
[Video] “Actual” Execution Plans Lie for Branching Queries
In which I show why you can’t trust “actual” execution plans for branching stored procedures, and you’re better off using sp_BlitzCache to figure out what branches you need to tune:
Demo code:
|
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 37 38 39 40 |
CREATE TABLE dbo.TableA (ID INT); CREATE TABLE dbo.TableB (ID INT); CREATE TABLE dbo.TableC (ID INT); CREATE TABLE dbo.TableD (ID INT); CREATE TABLE dbo.TableE (ID INT); CREATE TABLE dbo.TableF (ID INT); GO CREATE OR ALTER PROCEDURE dbo.ManyBranches AS BEGIN SELECT TOP 1 'This shows up' AS aFact; IF DATEPART(SECOND, GETDATE()) < 10 SELECT * FROM dbo.TableA; ELSE IF DATEPART(SECOND, GETDATE()) < 20 SELECT * FROM dbo.TableB; ELSE IF DATEPART(SECOND, GETDATE()) < 30 SELECT * FROM dbo.TableC; ELSE IF DATEPART(SECOND, GETDATE()) < 40 SELECT * FROM dbo.TableD; ELSE IF DATEPART(SECOND, GETDATE()) < 50 SELECT * FROM dbo.TableE; ELSE IF DATEPART(SECOND, GETDATE()) < 60 SELECT * FROM dbo.TableF; END SELECT 'This does not show up' AS anOpinion; GO /* Turn on actual execution plans: */ EXEC dbo.ManyBranches; GO /* Turn off actual plans: */ DBCC FREEPROCCACHE; GO EXEC dbo.ManyBranches; GO sp_BlitzCache |
This is one of the reasons you won’t catch me using the “Edit Query Text” in query plans during this week’s Mastering Query Tuning class.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
First, I’m impressed to learn that you do these videos in more than a single take.
Second, I want to register my vote for more champagne fueled instruction sessions.
Steve – hahaha, thanks. Yeah, it’s really funny – sometimes, the videos work flawlessly on the first take, but some days, not so much. On that day, everything that could go wrong, kept going wrong, heh. Had one perfect take early on, and realized I’d forgotten to start the recording!
Thank you very much!