What Goes Wrong
I still see people calling into this trap when writing stored procedures. What no one seems to realize until it’s too late data grows past a toy data base size, is that SQL Server’s query optimizer doesn’t respect IF branches as boundaries in the way you’d expect.
Sure, the control-flow logic boundaries are respected, but the big surprise to most people comes from how the optimizer treats query plans in IF branches: It compiles them all no matter what.
In the video below, I’ll show you exactly what that means, and how it can really screw up performance. This video is a small part of my paid training offering, and if you enjoy it, there’s a link at the end of the post to get 75% off.
IF Branching Video!
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
Related Posts
- Software Vendor Mistakes With SQL Server: Dealing With Bad Parameter Sniffing
- Software Vendor Mistakes With SQL Server: Writing And Optimizing Paging Queries
- Software Vendor Mistakes With SQL Server: Handling Optional Parameters
- Software Vendor Mistakes With SQL Server: Using Left Joins To Find Rows That Don’t Exist
Very nice work!. I have 2 questions:
1) I heard another trick: create dummy variables on top of the stored procedure which gets the value of the parameters. Instead of “WHERE xx = @parameter” it will be “WHERE xx = @var” is this a good replacement for dynamic SQL?
2) On which SQL version are you running these tests? I thought these “issues” were fixed a little in SQL2019
1. You’re hurting my feelings, here.
2. No, not at all. Not even close.
sorry to hurt your feelings. About question 1, I forgot to add “a friend of my said …”