You Can’t Trust “Edit Query Text” in SSMS’s Execution Plans.
When there are IF branches or conditional logic, “Edit Query Text” only shows you the branches that actually executed. It’s easy to miss whole swaths of code that didn’t happen to execute one particular time.
To demo it, let’s create a simple stored procedure with an IF branch that sometimes executes, and sometimes doesn’t:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE OR ALTER PROC dbo.ProcessUser @DisplayName NVARCHAR(40) AS BEGIN UPDATE dbo.Users SET Reputation = Reputation + 1 WHERE DisplayName = @DisplayName IF @@ROWCOUNT = 0 /* Uh oh, we didn't find a user, let's find the closest match */ UPDATE dbo.Users SET Reputation = Reputation + 1 WHERE DisplayName LIKE @DisplayName + N'%'; SELECT TOP 10 * FROM dbo.Users ORDER BY Reputation DESC; END GO EXEC dbo.ProcessUser N'Brent Ozar' |
Because there’s a user named Brent Ozar, a row is updated, which means the IF branch doesn’t execute. The actual plan shows the first & last statements executed:
Click on the query plan, which shows all 3 statements, including the IF branch that didn’t execute, which is good:
If you’re doing performance tuning based on that estimated plan, at least you’ll get a full picture of what could happen depending on what values were passed into the proc. You can see the IF branch in the plan’s XML, too, if you right-click on the query plan and click Show XML:
But if you’re one of those poor souls who relies on the Edit Query Text button when looking at plans, boy, have I got some bad news for you:
The IF branch’s contents don’t show up – and hell, the END statement doesn’t even show up! The “warning” up at the top about truncation really pisses me off because it leads people to believe that only stuff at the end might be truncated, like the “END” statement. However, even stuff in the middle can be missing in action! That’s wild.
So me personally, I try to make it a habit to never use Edit Query Text. Go to the source of the query instead – script out the proc, the function, or go to the app code to see what it’s doing.
I’m trying to be a better community member, and log SSMS issues whenever I blog about them, so if you’d like to keep updated on Microsoft’s work to fix this issue, here’s the feedback item. I’m not asking you to upvote it – we don’t need a storm of upvotes here since it’s not really critical, and I trust the process to do its thing. I just wanted to make you aware of it because most of you aren’t updating your SSMS on a regular basis, so you wouldn’t get a fix quickly if it comes out, and this bug has been this way for as long as I can remember.
