T-SQL Tuesday #68: Just Say No to Defaults

TSQL2sDay150x150_388014A5This month’s topic for T-SQL Tuesday is hosted by Andy Yun (blog | twitter) with the subject of defaults

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.


If this doesn’t look wrong to you…well I’m sorry.

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?


This angers me so much!

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.

StatusBarI also change the defaults under Tab Text to only Include File Name. Everything else is just fluff that I can find on the status bar.



3 thoughts on “T-SQL Tuesday #68: Just Say No to Defaults

  1. Pingback: T-SQL Tuesday #68: Round-Up « Every Byte Counts

  2. I know it’s a bit late in the game, but I imagine the bit about adding TempDB log files is actually about adding TempDB data files, since there’s no advantage to adding multiple log files.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s