THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Did You Know? What settings to always change


A week ago, I taught my SQL Server 2012 Internals class to a great group of very interactive students. Even though a dozen of them were taking the class remotely, there were still lots of really great questions and and lots of discussion.

One of the students asked if I could summarize all the settings that I recommended changing from the default, right out of the box. I said I’d try to put a list together by the end of the week, but I didn’t make it. So I said I would put it together and blog it.

I think it sounded during the week like there were more changes than there really are. Going back through my notes, I only found three settings, all of them instance-wide configuration settings, that I recommend always changing. Of course, depending on your hardware, your workload and your data, you may make more changes. But the short list here contains the options that I always make sure are changed from the default on any system I am working on.  So here they are:

1. Remote admin connections

This option doesn’t apply to all remote administrative connections, despite the name, but only to connections made using the DAC (Dedicated Administrator Connection).  This option has a value of either 0 or 1, and 0 is the default. I recommend changing it to 1, which allow someone working at a machine other than the machine where your SQL Server is installed to make a DAC connection.  It might be too late to change it to 1 once you realize you need it! This value also needs to be set to 1 when you are connecting to a clustered SQL Server, which is always considered a remote connection.

2.  Optimize for ad hoc workloads

This also is a two-valued option, with a default of 0. I recommend changing it to 1.  There may be some very edge-case scenarios for leaving it at 0, but they’re pretty rare so you’re better off changing it to 1. In most cases, this can save you lots of memory, because single-use ad hoc query plans will now only use 300 bytes of memory instead of a minimum of 16K. Some plans use more, a lot more. I’ve seen SQL Server instances with tens of thousands of single-use ad hoc plans. I’ll let you do the math.

3. Show advanced options

By default, only about 16 of the configuration options are viewable and settable with sp_configure. If you want to see all your options, including ‘optimize for ad hoc workloads’, you need this option set to 1. The default is 0. 


As a bonus, I’ll give you another list. These are options that usually should be left at the default value, so you should verify regularly that no one has changed them.  Two of them are instance-wide options, and two are database options.

1. Auto create statistics and auto update statistics

These are database options that have the default value of ON, and should be left that way. There may be cases you want to update statistics more often than auto statistics provides for, but that doesn’t mean you should turn the automatic updating off.  And there may be a few edge cases where you want to turn off the automatic update of statistics. Make sure you have a very good reason if you do so. But before you do, try turning on auto update statistics async to see if that helps whatever problems are leading you to consider turning automatic updates off. Also, take a look at the procedure sp_autostats. It allows to turn off automatic updates just for particular tables or indexes.  

2.  Max degree of parallelism

I’m not going to tell you what you should set this configuration option value to. There are a lot of recommendations out there, on other people’s blogs. But I’m just going to say make sure it is not set to 1 if you have more than one processor available to your SQL Server.  The default is 0, which means ALL SQL Server’s processors are available for parallel queries. In some (if not most?) cases, leaving it at the default is ok, but again, don’t set it to 1.  If you find particular queries do not perform well when run in parallel, you can use the MAXDOP hint for those queries. But don’t turn all parallelism off across the entire instance.

3. C2 audit mode and common criteria compliance enabled

Keep these configuration options set to 0 unless your business is mandated to have one of these options on. C2 audit mode has been deprecated but’s it still around for now. If you’re mandated to have these options enabled, you should know it. So otherwise keep these set to 0. You WILL notice performance degradation if you enable this.

4. Autoshrink

This is a database option that should never have been invented. Pretend it doesn’t exist, unless someone has set it to ON in one of your databases, then set it back to OFF. 


So these are the options that I specifically call out in my class as having general best practice values. I talk about other options as well, but most of the others have an ‘it depends’ answer for what values you should use. And what ‘it depends’ on is what I spend a lot of my class talking about.

Feel free to let me know if there are options on your list… that you always change, or always make sure are unchanged!




Published Tuesday, June 25, 2013 12:21 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Mark Varnas said:


Good list.


Mark Varnas

June 25, 2013 4:05 PM

Kalen Delaney said:

Thanks, Mark! Good idea.

June 25, 2013 4:11 PM

Mark Varnas said:

Great to see a quick response!

I would add one more to the list - read committed snapshot. This one depends on db use and environment, of course.



June 25, 2013 4:38 PM

wqweto said:

@Mark: It's set in model database already.



June 25, 2013 4:40 PM

Kalen Delaney said:

Mark, as you say, the snapshot isolation options are really an 'it depends' situation. I would never suggest that everyone or every database should have either option on. And in fact, I recommend NOT turning on the ALLOW_SNAPSHOT_ISOLATION, because it starts using the version store and other resources even if nobody ever actually sets their isolation level to SNAPSHOT!

And, it does looks like CHECKSUM is set by default, but it would be good to add it my second list, of things to verify. The NO_WAIT option only applies when you actually are making the change; it isn't something that is stored as part of the database property.

Thanks for your input!


June 25, 2013 5:01 PM

Rob Farley said:

Sadly, if you have SharePoint databases on the instance, they require the max degree of parallelism setting to be 1, and auto create statistics to be off.

Yeah, I know. It's worth considering for consolidation exercises though.

June 25, 2013 6:00 PM

Kalen Delaney said:

Thanks for the data point, Rob. I guess that shows how much experience I've had with SharePoint!

June 25, 2013 6:07 PM

Uri Dimant said:

Hi Rob

Interesting about SharePoint...I am having SP on the instance and did not notice that it needs maxdop=1..What dbs have you looked at? Is it documented anywhere?

BTW I fully agree about auto statistics to be off.

June 26, 2013 12:54 AM

IJeb Reitsma said:


It seems like you are advising to set AUTO_UPDATE_STATISTICS_ASYNC ON in cases where AUTO_UPDATE_STATISTICS is set to OFF.

BOL has this information about AUTO_UPDATE_STATISTICS_ASYNC:

Setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is set to ON.

Can you please explain this further?

June 26, 2013 8:09 AM

Ola Ekdahl said:

@uri, Microsoft has been recommending MAXDOP=1 for SharePoint dedicated instances for some time. It's documented in various places and here's one good recap,

June 26, 2013 11:41 AM

Kalen Delaney said:

Hi IJeb

Thanks for catching that. I must have fallen asleep before I wrote that part. You are right, you can't have the ASYNC option ON unless AUTO_UPDATE_STATISTICS is also on. I have rewritten that part.


June 26, 2013 7:12 PM

Dmitry Geyzersky said:

Please be aware of possible memory leak in SQL Server after turning on AUTO_UPDATE_STATISTICS_ASYNC. It is documented here:

June 28, 2013 8:28 AM

Uri Dimant said:


I have seen only one client who has a dedicated instance to SP....

June 30, 2013 1:31 AM

Paul Brewer said:

The default extended events health session in SQL Server 2012 is really useful but it only keeps 4 days worth by default. On new installs we change this to 30 days.


July 8, 2013 12:37 PM

Kalen Delaney said:

Thanks for the info, Dmitry!

And thanks, Paul. I haven't used XE much yet and haven't been responsible for it on a production system so haven't looked into this. But your suggestion certainly sounds reasonable.

July 8, 2013 1:04 PM

Adam Kreul said:

Hi Kalen,

In your post above, you mentioned 'edge-case' scenarios for leaving the 'Optimize for ad hoc workloads' setting at 0.  I'm wondering if you have a couple examples of these scenarios?  Thanks for the time, and great post.

April 22, 2015 8:59 AM

Kalen Delaney said:

Hi Adam

The one case I can think of, which was actually mentioned to me by a Microsoft engineer when I asked why this isn't ON by default is this:

Suppose you have an app that runs lots and lots of very complex queries, needing lots of compile time, and each one is run exactly TWICE. So you need to spend the time to compile each time you run, because the plan won't be saved the first time, but then you never run again to take advantage of the saved plan.

Yeah, I said it was a real edge case!

Have fun!


April 22, 2015 2:41 PM

Adam Kreul said:

Wow - that is very interesting and a edge case indeed!  This definently helps us make our decision around this setting.  I'm planning to blog about this setting soon - do you mind if I quote your response to my original question?  Thanks!


April 22, 2015 4:26 PM

Kalen Delaney said:

Hi Adam ... Please feel free.

April 23, 2015 6:17 PM

king said:



January 16, 2018 11:34 PM

aaaa said:

mt0417 mt0417

April 16, 2018 8:39 PM

chenyingying said:


May 6, 2018 11:30 PM

shenyuhang said:


June 1, 2018 7:09 PM

kanker paru paru bisa sembuh tanpa operasi said:

June 28, 2018 6:39 PM

kesehatan said:

July 4, 2018 9:56 PM

kesehatan said:

July 6, 2018 9:02 PM

kesehatan said:

July 9, 2018 12:41 AM

obat kanker payudara terbaik said:

July 10, 2018 8:51 PM

obat meningkatkan kecerdasan otak said:

July 13, 2018 9:43 PM

linying123 said:



July 16, 2018 8:55 PM

dongdong8 said:



July 23, 2018 11:32 PM

shenyuhang said:

July 23, 2018 11:41 PM

obat diabetes said:

August 1, 2018 11:17 PM

obat bronkitis anak secara alami said:

August 7, 2018 7:49 PM

obat sinusitis said:

August 10, 2018 7:22 PM

yaoxuemei said:


August 15, 2018 2:12 AM

qqq said:

August 16, 2018 12:24 AM

masker wajah alami untuk menghilangkan jerawat said: Masker wajah alami untuk menghilangkan jerawat Obat liver alami aman dan terbaik Obat herbal sinusitis kronis terbaik Obat bronkitis anak secara alami Obat Kanker Payudara terbaru 2018 Cara meningkatkan nafsu makan secara alami Obat herbal diabetes menurunkan gula darah Obat herbal kusta terbaik Obat benjolan di leher tanpa operasi Obat Herbal Kanker Otak tuntas hingga akar Obat TBC alami aman tanpa efek samping Obat radang amandel sembuh tanpa operasi Obat asam urat alami tanpa efek sampinfg Obat tetes Sariawan secara alami Cara mengobati mata bengkak dengan cepat Obat tradisional kanker lambung terbaik Obat kanker paru paru Obat usus buntu alami tanpa operasi Obat asam lambung naik cara alami

August 20, 2018 8:59 PM

chenjinyan said:


August 22, 2018 11:11 PM

shenyuhang said:


August 23, 2018 10:24 PM

obat herbal koresterol terbaik said:

August 23, 2018 10:58 PM

obat benjolan di tubuh said:

August 30, 2018 7:40 PM

obat wasir said:

September 4, 2018 8:26 PM

obat katarak said:

September 16, 2018 5:46 PM

obat limfoma said:


September 23, 2018 6:50 PM

xiaojun said:

20180928 junda

September 27, 2018 10:49 PM

kakakaoo said:

October 8, 2018 2:19 AM

chenjinyan said:



October 9, 2018 6:51 PM

chenqiuying said:


October 10, 2018 6:28 PM

kakakaoo said:


November 8, 2018 2:03 AM

qqq said:

March 27, 2019 1:09 AM

yanmaneee said:">">

June 29, 2020 11:05 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement