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.


PowerShell Remote Copy Workaround

Recently I ran into an unexpected problem when trying to copy some files remotely via PowerShell. I could have sworn that I have done this exact copy scenario in the past successfully, but this time, the computers just would not talk. The solution was fairly simple, but not at all intuitive.


Copy a file remotely from one computer to another, both drives being local to their respective computers. Network connectivity, proper permissions, etc., exists between the computers. I wanted to do this completely remote, issuing the command from a third computer.

Script #1 (Remote Copy to Remote) – Failed

This should have been a very fast copy. I knew the cmdlet I needed and even the syntax. I wanted to copy a file from one computer to another, connecting to each from my computer.

$From = "\\Server1\C$\MyFile.txt"
$To = "\\Server2\C$"
Copy-Item -Path $From -Destination $To

However, that did not work. Instead I got an error indicating that my path was bad! “Invalid Path” isn’t a lot of help though. I know it’s valid!


OK, so maybe I had a typo. After quadruple-checking my paths though, I was finally led to believe that it was a double hop permissions issue causing the remote copy to fail. I decided to try connecting directly to one of the computers before attempting the copy.

Script #2 (Local Copy to Remote) – Failed

I used the same script here, the only difference is I no longer need to make a remote connection to the source file, so the $From parameter is no longer a remote call. Other options that I thought would work here were Invoke-Command or EnterPsSession.

$From = "C:\MyFile.txt"
$To = "\\Server2\C$"
Copy-Item -Path $From -Destination $To

However, it still did not work.


The error was different this time, and even when recreating the error, I got confused and started wondering if I had a valid problem. Even though the error now says “Source and destination path did not resolve to the same provider”, it’s still the same problem and the resolution will be the same. My final attempt was a lot better.

Script #3 (Remote Copy to Remote, Fully Qualified) – Success!

After poking around the internet, I learned what the fully qualified name for PowerShell directories should be.  Providing the fully qualified name is required for any remote call. In other words, if your location starts with \\ then you need to add Microsoft.PowerShell.Core\FileSystem:: as a prefix. If you reference a local drive, you shouldn’t prefix the local drive. This worked perfectly, even remoting to both computers.

$From = "Microsoft.PowerShell.Core\FileSystem::\\Server1\C$\MyFile.txt"
$To = "Microsoft.PowerShell.Core\FileSystem::\\Server2\C$"
Copy-Item -Path $From -Destination $To

Hopefully this will save someone an hour of frustration.