THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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!

 

Thanks

~Kalen

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

Comments

 

Mark Varnas said:

Kalen,

Good list.

I would also add 'SET PAGE_VERIFY CHECKSUM WITH NO_WAIT'

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.

ALTER DATABASE [db] SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE [db] SET READ_COMMITTED_SNAPSHOT ON

June 25, 2013 4:38 PM
 

wqweto said:

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

cheers,

</wqw>

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!

~Kalen

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:

Kalen,

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, http://blogs.msdn.com/b/rcormier/archive/2012/10/25/you-shall-configure-your-maxdop-when-using-sharepoint-2013.aspx.

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.

~Kalen

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: http://support.microsoft.com/kb/2778088

June 28, 2013 8:28 AM
 

Uri Dimant said:

Ola

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.

Regards

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

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement