SQLskills Insider Session 3: Stored Procedures, Part 1 - Execution and Caching
In this continuing series on statement execution, caching, and plan cache management – we’re going to move to stored procedures. Ultimately, when a developer KNOWS how these work, they can be the BEST option for centralized logic, code reuse, AND PERFORMANCE.
However, “how they work” is not entirely intuitive. And, developers who work with decent hardware and small (or unrealistic) data sets often see “good enough” performance in inadequate testing environments. They do code coverage testing but do they know about “plan stability testing.” Plan Stability testing isn’t difficult but it’s not often done. What is it? Well, I made up the term. So, let me show you my “plan stability testing” methodology that can help you to determine whether or not you stored procedures are going to be prone to terrible performance problems (often known as parameter sensitivity problems / parameter sniffing problems). And, the best part, you can even tell that you have these problems when you have decent hardware and poor testing environments. No, really!
In this first of two parts specifically focused on stored procedures, learn about this SIMPLE process to add to your testing and to determine EARLY whether or not your code is going to be prone to performance problems. Once identified, we’ll look at a variety of ways to FIX the problem!
Attend this session to get a better handle on your centralized code and ultimately better scalability!
Session #3: Resources
Thank you! We had some Webex weirdness outside of the US but ended up finding that if you connected through the browser (instead of the Webex App), the problems went away. Phew! As for the session, it was all about determining whether or not a stored procedure would be parameter sensitive! The most important thing you can have your developers learn – Plan Sensitivity Testing. And, luckily, it’s relatively easy to do and easy for which to test!
How do you fix it – stay tuned for next week (sorry for the cliffhanger) but we’ll look at even better solutions then. I did give one that will work well in the interim. 😉
Resources for this SQLskills Insider Session: download
See our upcoming Insider Sessions here.
Kimberly L. Tripp
President and Founder, SQLskills
Read my full bio here: Kimberly L. Tripp and about my Celebrating 25 Years of SQLskills here: https://www.sqlskills.com/about/meet-the-team/celebrating25years/