I’m speaking Tuesday morning on Understanding the Procedure Cache at DevTeach\SQLTeach in Montreal. This is the same presentation I delivered at the recent PASS European Conference. At PASS I missed being the top speaker in the Application Development track by 0.8%. Guess I’ll have to step it up here :)
This session is what you need to know after you learn how to read a query plan and improve it. I spend a lot of time talking about stored procedures vs. dynamic SQL and the different types of dynamic SQL. I talk about parameterization, compilation and why procedures run fast one time and slow the next. I do lots and lots and lots of demos to illustrate everything. Many of the demos show how the same code can perform very differently depending on how it’s called, how compilation is set and what’s already in the cache. The demos cycle through explaining the setup, showing the code, running it, evaluating the performance and query plan and explaining why it behaved the way it did. Then we change one little thing and try it again. Then we change one more thing and try it again. And again… I counted 67 SELECT statements or stored procedures that I’ll cover in the presentation.
I’ve spoken at Jean-Rene’s conference before and it’s a great experience. They take good care of their speakers and put on a good show. If you’ve considered going I encourage you to attend. Their lineup of SQL Server speakers is very impressive and includes Kevin Kline, Joe Webb, Jessica Moss, Brad McGehee, Peter DeBetta, Don Kiely and Roman Rehak.