One Key Mistake When Installing SQL Server

There’s one sure-fire way to set yourself and your applications up for failure when using SQL Server. I’ve seen all kinds of people do this.

It will cost you performance and create maintenance headaches down the road.

Let me explain what it is and why it causes trouble.

Hit next, next, next on the installer, accepting all the defaults, and leave it that way.

Racing through the installer accepting defaults will:

·         Put the core install and all the system and user databases on the same drive, the C drive. What else is on C? Yep, you got it. The operating system.

This creates a host of problems all by itself. Now OS patching will eat up space that your database install also will need to consume, putting you in danger of freezing the entire server when the C drive fills up.

You will be introducing IO contention between the OS and SQL Server and between the various types of IO that happen in SQL Server files, and you will be putting all that contention onto one disk. Want to slow… everything…down? Put it all on one disk. This is especially relevant for a point I make later about tempdb.

·         On older versions of SQL Server when you accept the defaults you end up with a SQL Server with a HUGE default setting for Max Server Memory. This can result in the OS and SQL Server fighting it out for RAM. This too will slow…everything…down. SQL Server may page out work it would have done in RAM to the disk. You could strangle the operating system by not leaving it enough RAM.

·         On older SQL Server versions, you end up with 1 tempdb file. You want to slow…everything…down? Leave that default on a multi-core system, especially a larger one, and watch page contention kill your tempdb performance because it can’t make allocations fast enough. Combine this with a busy workload on tempdb that is installed on the same drive with the user databases and the OS and that will seriously slow down your queries.

·         The “Next, next, next and you’re done” approach on older versions will leave you with MAXDOP set inappropriately at zero because it wasn’t until recently that the installer detected the number of CPUs and made a best practice recommendation in the install process. This very well could create performance problems around parallelism. Lower cost, small queries will go parallel and use all available cores when it isn’t truly necessary. Get enough of this happening and you will see slowness because of competing resources for parallelism for queries.

·         Some folks, when they “next, next, next” through the installer, check all the components for SQL Server and install everything because they figure they’ll put everything on there because they don’t really know what’s needed. Yes, people really do that. I’ve seen it and had the conversation about it with them after the fact. Well, that eats up resources on the machine for SQL Server services that you aren’t even going to use. This contributes to resource contention which, you guessed it – slows…everything…down.