Throughout the years, I have become convinced that the default settings used in SQL Server are often wrong for systems that need scale or are properly managed. There is a series of settings I find myself consistently changing when I audit or install a new server. They are “my defaults”

I thought I would share those here. Bear in mind that these are setting that assume a certain level of rational behaviour in the organisation you find yourself in. If you are working in a bank, they may not apply to you.

Server Configuration

Here is what I always do

Configuration

Reason

Grant Lock Pages in Memory

Paging the buffer pool is bad.

Set Max Memory

So you don’t take too many of above

Enabled remote DAC

Unless you plan to terminal into the server to admin it in a crisis. For SQL Server facing the Internet, you might want to leave this off

Backup Compression Default

Why wouldn’t you use compression for backups? CPU is cheap and you can over allocate that with much less penalty than over allocating IOPS.

Prevents new users from allocating a database that he has not consciously made good backup choices on

Add new filegroup named DATA to model, set this to the default

All my database have at least two filegroups (more about this later)As pointed out in comments (Thanks commenters), this has to be done the hard way, during database creation time

Enabled Async Stats in Model

I have yet to find a case where async stats are not better than the default

Named Pipes off, TCP/IP on

I never found a real use case for named pipes. Turning them off is one less surface area for malicious attackers to exploit

max worker threads

On a modern 2 socket, I generally set this to 8192. Typically, you can run more threads than the default configuration allows. Threads that are un-used only burn a bit of memory, which is cheap.

-T1118

Mixed extends are a joke. Off with them!

-T1117

If you are managing you data files right, you need them to grow at the same rate. Failure to do so means you are doing something wrong – and I don’t configure for people who do things wrong

-T4199

Always enable the latest optimiser fixes. There is enough bad stuff happening with the optimiser all the time (nature of the beast). Being on the latest version is much better than being stuck in the past

-T3226

Logging successful backups to the error log is a royal pain. That log is reserved for error conditions and other things you need to carefully manage. Logging of backup status belongs in the agent or in the backup system you run.

Database / Table Configuration

In addition to server configuration, there are a series of settings I automatically change on databases and tables

Setting

Reason

Async stats

As described above. In my experience, always the superior choice. You need a good reason to have it off, not the other way around.

New filegroup: DATA, set it to default

I never put objects in the PRIMARY filegroup. PRIMARY contains the metadata, and needs extra protection (I normally put it on the TLOG drives). The additional filegroups allow me to freely reallocate new tables and move things around between disk

sp_autostats OFF on large tables

In nearly every case I have seen on large tables, auto stats are harmful. They always kick in at the most inconvenient times. And when they kick in, the cause the one thing that is worse than bad performance and data loss: Unpredictability.I leave auto create stats enabled as this happens rarely enough to be useful and not disruptive.

Table lock escalation OFF on large tables

Same problem as auto stats, when lock escalation kicks in it nearly always spells trouble. If you are running out of locks, you are doing something wrong that needs to be addressed. Lock escalation is not the solution – it typically makes the problem worse or hides the underlying issue.See background details on the trade offs: http://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx

The lock manager often becomes the bottleneck at scale. For small tables, getting rid of page locks help. For large tables, make sure you can live with the the impact on table scans before turning it off

#files = #cores

Yes, even for user databases!

Server Configuration

Here is what I always do

Configuration

Reason

Grant Lock Pages in Memory

Paging the buffer pool is bad.

Set Max Memory

So you don’t take too many of above

Enabled remote DAC

Unless you plan to terminal into the server to admin it in a crisis. For SQL Server facing the Internet, you might want to leave this off

Backup Compression Default

Why wouldn’t you use compression for backups? CPU is cheap and you can over allocate that with much less penalty than over allocating IOPS.

Prevents new users from allocating a database that he has not consciously made good backup choices on

Add new filegroup named DATA to model, set this to the default

All my database have at least two filegroups (more about this later)

As pointed out in comments (Thanks commenters), this has to be done the hard way, during database creation time

Enabled Async Stats in Model

I have yet to find a case where async stats are not better than the default

Named Pipes off, TCP/IP on

I never found a real use case for named pipes. Turning them off is one less surface area for malicious attackers to exploit

max worker threads

On a modern 2 socket, I generally set this to 8192. Typically, you can run more threads than the default configuration allows. Threads that are un-used only burn a bit of memory, which is cheap.

-T1118

Mixed extends are a joke. Off with them!

-T1117

If you are managing you data files right, you need them to grow at the same rate. Failure to do so means you are doing something wrong – and I don’t configure for people who do things wrong

-T4199

Always enable the latest optimiser fixes. There is enough bad stuff happening with the optimiser all the time (nature of the beast). Being on the latest version is much better than being stuck in the past

-T3226

Logging successful backups to the error log is a royal pain. That log is reserved for error conditions and other things you need to carefully manage. Logging of backup status belongs in the agent or in the backup system you run.

Database / Table Configuration

In addition to server configuration, there are a series of settings I automatically change on databases and tables

Setting

Reason

Async stats

As described above. In my experience, always the superior choice. You need a good reason to have it off, not the other way around.

New filegroup: DATA, set it to default

I never put objects in the PRIMARY filegroup. PRIMARY contains the metadata, and needs extra protection (I normally put it on the TLOG drives). The additional filegroups allow me to freely reallocate new tables and move things around between disk

sp_autostats OFF on large tables

In nearly every case I have seen on large tables, auto stats are harmful. They always kick in at the most inconvenient times. And when they kick in, the cause the one thing that is worse than bad performance and data loss: Unpredictability.I leave auto create stats enabled as this happens rarely enough to be useful and not disruptive.

Table lock escalation OFF on large tables

Same problem as auto stats, when lock escalation kicks in it nearly always spells trouble. If you are running out of locks, you are doing something wrong that needs to be addressed. Lock escalation is not the solution – it typically makes the problem worse or hides the underlying issue.See background details on the trade offs: http://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx

The lock manager often becomes the bottleneck at scale. For small tables, getting rid of page locks help. For large tables, make sure you can live with the the impact on table scans before turning it off

#files = #cores

Yes, even for user databases!

#Files = #Cores

For some reason, this battle still rages. If you have SSD, you need many files. Here are the numbers on a 48 core machine (SuperMicro, 4 sockets, Fusion-io Drives).

Server Configuration

Here is what I always do

Configuration

Reason

Grant Lock Pages in Memory

Paging the buffer pool is bad.

Set Max Memory

So you don’t take too many of above

Enabled remote DAC

Unless you plan to terminal into the server to admin it in a crisis. For SQL Server facing the Internet, you might want to leave this off

Backup Compression Default

Why wouldn’t you use compression for backups? CPU is cheap and you can over allocate that with much less penalty than over allocating IOPS.

Prevents new users from allocating a database that he has not consciously made good backup choices on

Add new filegroup named DATA to model, set this to the default

All my database have at least two filegroups (more about this later)As pointed out in comments (Thanks commenters), this has to be done the hard way, during database creation time

Enabled Async Stats in Model

I have yet to find a case where async stats are not better than the default

Named Pipes off, TCP/IP on

I never found a real use case for named pipes. Turning them off is one less surface area for malicious attackers to exploit

max worker threads

On a modern 2 socket, I generally set this to 8192. Typically, you can run more threads than the default configuration allows. Threads that are un-used only burn a bit of memory, which is cheap.

-T1118

Mixed extends are a joke. Off with them!

-T1117

If you are managing you data files right, you need them to grow at the same rate. Failure to do so means you are doing something wrong – and I don’t configure for people who do things wrong

-T4199

Always enable the latest optimiser fixes. There is enough bad stuff happening with the optimiser all the time (nature of the beast). Being on the latest version is much better than being stuck in the past

-T3226

Logging successful backups to the error log is a royal pain. That log is reserved for error conditions and other things you need to carefully manage. Logging of backup status belongs in the agent or in the backup system you run.

Database / Table Configuration

In addition to server configuration, there are a series of settings I automatically change on databases and tables

Setting

Reason

Async stats

As described above. In my experience, always the superior choice. You need a good reason to have it off, not the other way around.

New filegroup: DATA, set it to default

I never put objects in the PRIMARY filegroup. PRIMARY contains the metadata, and needs extra protection (I normally put it on the TLOG drives). The additional filegroups allow me to freely reallocate new tables and move things around between disk

sp_autostats OFF on large tables

In nearly every case I have seen on large tables, auto stats are harmful. They always kick in at the most inconvenient times. And when they kick in, the cause the one thing that is worse than bad performance and data loss: Unpredictability.I leave auto create stats enabled as this happens rarely enough to be useful and not disruptive.

Table lock escalation OFF on large tables

Same problem as auto stats, when lock escalation kicks in it nearly always spells trouble. If you are running out of locks, you are doing something wrong that needs to be addressed. Lock escalation is not the solution – it typically makes the problem worse or hides the underlying issue.See background details on the trade offs: http://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx

The lock manager often becomes the bottleneck at scale. For small tables, getting rid of page locks help. For large tables, make sure you can live with the the impact on table scans before turning it off

29Comments

Kris Cook • 2015-04-09T18:20:39+00:00

Thomas,

Great article. A couple things I hadn’t seen before, and great to have all in one place rather than consulting a dozen documents.

A question on #files=#cores: are there any caveats on this in cases of multiple instances running on a server? If there are 10 SQL instances on one 8-core Windows server, for example, can having 8 files for each tempdb cause a problem (say if some silly predecessor created ONE GIANT DISK and put all of the tempdb’s on it)?

Great article, thanks, a number of little things I wasn’t aware of. But about that tempdb, let me ask the question from both sides. First, what about the numbers if your tempdb is still on spinning disks? And now the other way – is there any reason why tempdb *should* still be on spinning disks? I actually just built my new home workstation with nothing but SSDs (and I’ll plug a spinning disk in the USB3 port if I need to simulate a slow disk!), but I’m still not clear on the safety in production of using SSDs for tempdb and/or log files that are both written a lot. Or is it just so much better that you stock spares and do it anyway? Thanks.

With modern SSD – they are no so cheap that spinning disk really belongs in the past for databases of moderate size (would I define as less than 10TB of data). Designing a database optimised for spinning media seems like a waste of time to me – sooner or later, you are going to be moving to SSD anyway.

Sorry for posting sooo late:
Regarding #files = #cores – both Microsoft and Paul Randal changed it to logical processor cores like in:
(SELECT COUNT(*) AS Cores FROM sys.dm_os_schedulers WHERE status = ‘VISIBLE ONLINE’)
before it was
select count(*)/2 from sys.dm_os_schedulers
where status = ‘VISIBLE ONLINE’ and is_online = 1
And you don’t need one to one any more. You might be OK with 1/4 or 1/2.
I still stick with the old way but I wonder about machines with 60 processors.

I have tested the number of files vs cores all the way up to 48 cores on heavy OLTP insert style workloads. There continues to be a benefit of having 1 file per core. The same was true for the ETL world record (on a 96 core box) where we used 96 data files to get the fastest possible speed.

So yes, you still need one to one. There is a sharp drop in the benefit gained once you hit 8 files, but the it keeps on giving.

Interesting, I had similar results(an IBM M3 with a Virident card). Same sharp drop, but still getting a slope after 4 files. Is it possible for you to publish more details about the hardware/software configuration? Mainly, were you spinning rust or SSD?

I have seen the effect on a 4 socket, 48 core AMD system (SuperMicro) with Fusion-io drives. I have also seen it on a Unisys ES 7000 96 core system with three dedicated SANs attached (one which has some SSD cache the others with a heavy dose of DRAM backed cache).

I would add setting the power plan to maximum performance, configuring tran logs to be 8Gb in size ( initially ) ad grow in equal 8 Gb increments ( as recommended by Kimberley Tripp ) in the absence of any empirical evidence on which to configure these, good advice. Regarding hints, there have been some scenarios under which QUERYTRACEON(2301) can yield some very impression performance gains. You mention Adam Machanic and the TOP hint, this can also be used to “Brute force” the cardinality of SELECT statements in the source query for merge statements ** and ** in the absence of a cardinality hint which Oracle has, this can be used to achieve something similar.

On the how clever / dumb you should assume people are, a lot of my engagements are at places where there is a mind set of: “Its a Microsoft product therefore developers who can do everything from the GUI right to the back end can do the job”, another chestnut was “I don’t believe that working with SQL Server is real work, as its just a bloated version of Access”. I agree with your sentiment, however the reality of a lot of places, the small medium sized businesses beneath the CAT teams radar, plays into the scenarios Brent mentions.In fact I see so many recurring problems from one site to another, that I could create a standard report and only have to change the name of the customer in order to make it applicable to a specific customer.

There is a wider debate as to whether Microsoft should enforce a certain number of “Best practices” on people out of the box, i.e. not allow people to create databases on the system drive, not allow people to specify auto growth settings as percentages, not allow people to set max memory to the maximum memory in the server ( where does the thread stack go ? ).

On you your comment about the land of uncle Sam, its also the land of some institutions which are absolute temples to our trade, Carnegie Melon, MIT and Berkeley. I also believe that Donald Knuth hailed from there and John Von Neumann carried most his pioneering work in the states.

The really funny thing about blogs in general is that the comments sections can sometimes be more interesting and insightful than the actual articles that precipitates them.

I am no a big fan of setting the power plan to high performance. While it does yield some performance, the cost in power is non trivial. For a high performance, guaranteed response time, system I agree, but not as a generic recommendation.

With regards to the clever bit on MS: The “best practice” mindset and catering to the lowest common denominator only reinforces this image – it does nothing to dispel it. The defaults could obviously be better than they are, so there are steps MS can take to avoid too many common mistakes. In particular, putting recommendations in place that favor scale at the sacrifice of performance would greatly help refocus DBA on what matters.

I am happy to see you like the comment – always good with some additional entertainment on the blog. It is indeed true that a lot of great computer science has come out of the states. This makes taking the lower common denominator particularly insulting to the pioneers and their hard work.

Is auto grow on TLog in very large increments not dangerous? Even with perform volume maint tasks this only applies to data files. A very large TLog extend will take time to initialise. 8gb would result in lengthy waits storage systems and surely trip some timeouts.

A properly configured log should be able to grow with at least 100MB/sec (and more if on SSD or a good RAID controller). So that is around 1.5 minutes to grow it. Depending on your HA requirement, you may pick a small number (but I would go with at least 1GB). However, IF you are concerned about that, more than likely you would not want autogrowth at all, but instead careful monitoring and a generous bit of extra space.

Many thanks for this. I have a few questions about the trace flag recommendations, in the server configuration section:

Traces are a bit new to me. I’ve found that the following syntax works:
DBCC TRACEON (4199, -1)

However, you’ve included -T in all cases. Presumably, with the command above, the trace is lost if the SQL Server service is restarted?

So some further searching suggests that I should:
– run cmd
– navigate to C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBinn (or thereabouts)
– and (for each flag) run: sqlservr.exe -T4199

Hi Thomas,
Interesting post, few things I am not doing and few that I am too. I have always wanted to add an additional file group to model, especially on development servers. I have tried in the past and generated an error. Just tried again…

You are right, it is not possible to modify Model with a filegroup. We recently changed our database layouts to use the additional filegroup, seems we must have included it in the defaults of the DB create script instead.