I’ve been uncharacteristically quiet during this particular Summit. I picked up a cold on the way out there, which made the week a bit different than I was expecting. Still, I always take away quite a lot each time I go, both big and small, and this year was no exception. I’d like to share one of the small things here.
At some point, we’re all running ad hoc queries in a production or otherwise significant environment. We’re doing root cause analysis, looking for bad data, trying to figure out why something is slow, whatever. Our ad hoc queries get rather large sometimes, as we’re pulling in information from all sorts of places. Often, we have several stored away in a toolbox for these occasions, our “go-to” scripts when we get that inevitable call in the middle of the night.
Here’s one small change to those queries that will make one small difference. Admittedly, it’s not much, but it could add up, especially if you’re in a large shop with a lot of people who are doing the same thing. If we all changed our ad hoc to include this, we might actually have an impact. So, what’s the change? Throw in “OPTION (RECOMPILE)” at the end of all your ad hoc statements.
What does this buy us? Well, basically, it keeps those one-off ad hoc queries from bloating the plan cache. Does it really matter? Not much, in the grand scheme of things, except that I know some of the queries in my toolbox are quite lengthy. Why have SQL Server cache the plans for those?
By the way, you can also enable the Optimize for Ad Hoc Workloads server configuration option. This will save you nearly as much, at least until you hit F5 the second time.
Anyway, I might share some of the other stuff I picked up from this year’s Summit. Just not my cold, I hope. But the one thing I always share is my advice that you attend. If you’re a data-centric developer like me, definitely go. It’s excellent content by a thriving community in a beautiful city. I’m already looking forward to next year.