SQL Server Configuration
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. |
| Grant Perform Volume Maintenance tasks to the SQL Server account | Allow instant file initialisation. See: http://technet.microsoft.com/en-us/library/ms175935(v=sql.105).aspx |
| tempdb #files = #cores | I have shown that this is the most scalable configuration |
| model in simple mode and autogrowth off | 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. |
| -T835 | Enable Locked Pages on Standard Edition |
For a documentation of many of these trace flags, see this: http://technet.microsoft.com/en-us/library/ms188396.aspx
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 |
| ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = ON for small, and sometimes large, tables | 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. |
| Grant Perform Volume Maintenance tasks to the SQL Server account | Allow instant file initialisation. See: http://technet.microsoft.com/en-us/library/ms175935(v=sql.105).aspx |
| tempdb #files = #cores | I have shown that this is the most scalable configuration |
| model in simple mode and autogrowth off | 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. |
| -T845 | Enable Locked Pages on Standard Edition |
For a documentation of many of these trace flags, see this: http://technet.microsoft.com/en-us/library/ms188396.aspx
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 |
| ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = ON for small, and sometimes large, tables | 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. |
| Grant Perform Volume Maintenance tasks to the SQL Server account | Allow instant file initialisation. See: http://technet.microsoft.com/en-us/library/ms175935(v=sql.105).aspx |
| tempdb #files = #cores | I have shown that this is the most scalable configuration |
| model in simple mode and autogrowth off | 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. |
| -T835 | Enable Locked Pages on Standard Edition |
For a documentation of many of these trace flags, see this: http://technet.microsoft.com/en-us/library/ms188396.aspx
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 |
| ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = ON for small, and sometimes large, tables | 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! |