October 6, 2012 by Mike Hillwig
Today is SQL Saturday 171 in my hometown of Pittsburgh, PA. THIS is the SQL Saturday that has had me excited for a long time. I’m a born and bred Yinzer. If you don’t know what that is, it means you don’t speak Pittsburghese. Yinz’ll prolly git it later n’at.
This session was born of my experience coming into environments where SQL Server was configured to use every default setting available. And that makes me angry. SQL Server is software, and it has a ton of configuration options. If you take every default setting, your experience won’t be very good. It’s also what makes Oracle DBAs think that installing and configuring SQL Server is easy.
I start with a bunch of settings that I change on every instance. And honestly, I’m just scratching the surface. After that is a section I call “Don’t touch that!” Anybody who watched season one of Bravo’s Top Chef will get my reference. A few months ago, I got to hear Brent Ozar present at SQL Saturday in New York City. He says that SQL Server is like an airplane cockpit where there is a pilot and a dog. The pilot’s job is to set the autopilot and to feed the dog, and the dog’s job is to bite the pilot when he touches anything. I believe that there are several settings where the default settings shouldn’t be touched.
UPDATE: Adam Machanic just pointed out something. He disagrees with my assertion that you should only change MAXDOP in fringe cases. He tells me that it’s something he advises changing on every instance. See his blog post. I mention that I only trust people I know and who haven’t led me astray in the past. Adam is certainly one of those people. I think he wrote a book or two on performance tuning.