It’s T-SQL Tuesday again and this month Andy Yun (b/t) is our host and he has asked us to “Just say no to defaults”.

Defaults. This is subject with a huge breadth and depth. Which as it happens, makes it a great T-SQL Tuesday subject. You could easily talk about the default database growth/size settings or server settings such as instant file initialization and lock pages in memory. Not to mention all of the database and instance settings.

I want to talk about something a little bit different.

Document your settings. Document the defaults and document every setting you change. Why is that important? Well how about a DR situation for example. You have to rebuild a instance that has gone down. If you don’t have a list of all of the settings you changed to set up the old instance you may miss one when you set up the new instance and some of these settings can be tricky to get just right. Not to mention making sure your test and dev instance configurations match your production instance. With some settings the only way I know to keep track is to manually take notes. Fortunately for most settings we can automate the process. There are so many difference ways to automate collecting data across a group of instances that I’m not going to bother going over it here. What I will say is you can collect the contents of two system tables and get a huge number of the available settings.

sys.configurations

The majority of the instance settings can be updated using sp_configure. And in fact you can use sp_configure to list out all of the settings it can update. It’s easier to use sys.configurations. If nothing else we can do a search for an exact setting name.

SELECT * FROM sys.configurations WHERE name LIKE '%adv%'
-- Returns one row for 'show advanced options'

If you pull a default list (from an instance before you make any changes) then you can actually do a comparison and see what has been changed for any given instance. If you are going to do this make sure you keep a default list for each version since new settings appear all the time. The defaults for an existing setting may change from version to version too.

Depending on how you collect the data (Policies) you may get the information any time something changes. Or you may have to schedule your data pull. I wouldn’t do anything less often than once a week though. If you made the change this week you can probably remember it or re-create it. Much longer than that however, and with a busy schedule you aren’t likely to remember under pressure (DR remember 🙂 ).

sys.databases

Why would you want to keep a list of all of the databases on an instance? Well this isn’t just a list of databases. It includes a bunch of the database settings as well. Auto Create Stats, Page Verify and Forced Parameterization just to name a very few.

This is one that you will want to keep a closer eye on, (maybe daily) since new databases appear on a semi-regular basis and the database settings change more frequently than instance settings. With a little bit of effort you can even write some reporting to let you know when new databases appear or when a setting has changed.

There are lots of settings out there and there are certainly other places you can get the information. Really there are a million ways to handle the process but the important thing to remember is to document, document, document.

Filed under: Documentation, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tuesday Tagged: database settings, Documentation, instance settings, microsoft sql server, T-SQL Tuesday