This month, I’d like to ask everyone to blog about SQL Server Defaults. While SQL Server CAN be run with just the default settings, doing so is far from ideal. As we progress through our careers, many of us build lists of things we change. Let’s blog about those! And don’t think this month’s topic is constrained to just the SQL Server engine. SSIS & SSAS are absolutely fair game as well. SQL Server developer tools – sure! Want to take a different spin on “defaults,” go right ahead and be creative!
My job almost revolves around building out new SQL servers, so having an organized method is imperative. The list of security changes we make is a mile long. I may not even be exaggerating…However, there are a few changes from the default settings that I feel are the most important. I’m only going to touch on a few avoid an impossibly long read, and the likely redundant mentions.
Add TempDB Data Files
While Aaron Bertrand has some amazing ideas for how TempDB configuration should look during installation, at least SQL 2016 is making some improvements to encourage multiple data files. No one should be using a single data file by default. A good start is to have one data file per core, up to eight. Afterwards, it gets a bit more complicated.
Adjust the Autogrowth Settings
One of my biggest pet peeves when I look at a new SQL Server and see default autogrowth settings. It just drives me crazy. Just yesterday, I found a database that was about a gigabyte in size, and yet the autogrowth was still set to 1MB. Can you imagine how many times it has to autogrow? That’s a resource hit every single time. Increase your autogrowth to a reasonable size so that it doesn’t have to autogrow every hour or more! Percent autogrowth is even more dangerous, especially for a log file. Logs can grow quickly, especially during something like index maintenance. While 10% of 100 MB may not seem like much, 10% of 1 TB can be temporarily devastating during high traffic times. Even worse, what do you do when your 10% growth triggers but your drive isn’t big enough to handle that?
Change Max Server Memory
There is a lot to be said about changing the maximum memory allowed by SQL Server. I’ve previously shared a way to get it for all your servers and some suggestions on what to set max memory to. The amount to limit it by varies, but the most important thing is to limit it, otherwise your OS may be memory starved and bring everything to its knees.
Change Cost Threshold for Parallelism & Max Degree
I listed these together because they go hand in hand. There are innumerable debates on what these should be, and it really depends on your server, but almost everyone agrees that the default settings are too relaxed. I’ve seen arguments for a Cost Threshold of 30-60 for a new default. Starting at a number in that range is a good idea. You can adjust it after testing your more problematic queries. However, as more people start using Columnstore indexes, it could mean you need lower thresholds or force serial plans. Perhaps someday we will need a specialized setting to set default cost thresholds for ColumnStores only?
As for the max degree, even Microsoft agrees the default is bad. Changing this appropriately requires knowing your system hardware and maybe doing a bit of math. It should be based on your number of CPUs, but you’ve got some wiggle room depending on how busy your system is. Many queries firing at once indicate a smaller Max DOP, while the inverse is also true.
Change the Default SQL Server Port
Everyone knows that the default port for SQL Server is 1433 and that the Browser uses 1434. You can change the default port. It’s a very easy way to prevent very simple hacking attempts. Sure, you can still be port scanned, but why skip an easy configuration change if it can help your security even by a little bit? You can even take it a step further and blacklist (or whitelist only what you want) any address that attempts to connect through the default port. That requires a bit more planning though.
Make Management Studio Yours
There are a lot more Server settings out there, but I want to mention something about Management Studio. Configure it so that it works for you. Personally, I’m OK with the color scheme, light on dark bothers my eyes, but some people love it.
I always dock my toolbars using AutoHide. I want to get to my registered servers, etc., quickly, but I still need full screen real estate when using the toolbars. Too much stuff.
Finally, my favorite change is to move the Status Bar to the top of the screen. It felt weird the first time, but now it’s distracting each time I open Management Studio for the first time and the bar is back on the bottom. It’s much more natural to see what is going on when the bar is at the top. The problem is, I have to change this so rarely, I normally spend 5 minutes hunting for the correct setting. You can find this under Tools -> Options -> Text Editor -> Editor Tab.