It Does Not Depend

A common refrain in the SQL Server community is the phrase, “It depends.” And, truthfully, that’s the correct answer to many questions. There are so many variables in any given situation that what works great 99 times will not work on the 100th, due to some slight difference.

But here’s one piece of advice that – even if it’s not perfectly 100% – has a high enough success rate that I would say “It does not depend.”


EXECUTE sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
EXECUTE sp_configure 'Optimize for Ad hoc Workloads', 1;
RECONFIGURE;
GO

I have yet to come across a situation in which this shouldn’t be enabled. And I’ve heard others say the same.

By the way, in case you have the same question as an attendee at my SQL Saturday session today in Salt Lake City, no, it will not wipe the plan cache when you turn it on.

Yay #SqlFamily

It’s a big #SqlFamily week for me. Tonight, IndyPASS is having my co-worker, Eddie Wuerch, present on SQL Server v.Next. It’s the first IndyPASS meeting I’ve been able to attend in a while, and I’m looking forward to it.

And then I’m leaving for Salt Lake City this weekend – my first trip to Utah! – where I’ll be presenting a pair of sessions for their SQL Saturday. Both are a result of the great feedback from the #SqlFamily on past sessions I’ve done. Adding Lightness expands on a segment of a previous session that was very well received, and Ad Hoc Rally takes a slightly more focused approach to material that I’ve also covered before.

So, if you’re in Indy, come join us tonight. The pizza’s free and the material should be excellent. Eddie is a great presenter, and I’m sure he has plenty of good info to share.

And if you’re in the SLC area, this Saturday is your chance to get some outstanding database training. In addition to the 24 scheduled PASS sessions, it’s also the Intermountain Big Data Conference, so there should be plenty to learn.

Godspeed!

SELECT TOP 1 * FROM PASS.Summit2015;

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.