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:
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);
CREATE OR ALTER PROCEDURE dbo.ManyBranches AS
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;
SELECT 'This does not show up' AS anOpinion;
/* Turn on actual execution plans: */
/* Turn off actual plans: */
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.
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!