SQLskills Insider Session 4: Stored Procedures, Part 2 - Caching and Recompilation
In this fourth and final session on statement execution, caching, and plan cache management – we’re going to wrap up with stored procedure optimization techniques. When a developer KNOWS how stored procedures work, they ARE the BEST option for centralized logic, code reuse, AND PERFORMANCE.
In our prior session, we discussed “how they work” as it’s definitely not intuitive. If a developer adopts “plan sensitivity testing” they can determine EARLY and (most importantly) EASILY that their stored procedures might not be optimal in production. Armed with this information there are multiple ways to FIX this problem. Each one has pros and cons from one fix being a great / easy / early fix (I call it a band-aid fix as it’s mostly just covering up the problem)… to more complicated fixes being flexible for long term reuse and MUCH MORE resource efficient.
In this session we’ll look at how to recompile only when necessary and cache when a plan is stable. This will create the best balance between great performance and resource utilization.
This truly is the way to better system scalability; attend and let me show you how!
Session #4: Resources
Thank you! Ha! No Webex weirdness today and originally I thought I didn’t have enough content to fill a full “2nd session” on procedures but you all asked some fantastic questions and we went on some incredibly useful tangents! Thanks, that was fun!
Now, do we still have more to talk about – of course! Be sure to come for our next session – I’m going to have a summary slide for each of these 4 sessions AND I’ll include any of your questions submitted beforehand. You can submit your questions on the Caching Q&A page. And, I’m sure we’ll take a few more tangents next week too!
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/