SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Homework – February 2019 – Instance configurations

This homework series really has two purposes. It’s a primer for beginners. These are tasks I think most if not all database developers and DBAs should know how to do. And giving it at least one shot means you’ll be more comfortable when the task inevitably comes up. On the other hand, there are also a number of tasks that you may not need often, but you absolutely want to try them at least once before doing them even in a test environment. This one is the second type.

In a stable environment, you should be changing the instance configurations rarely if ever. That said, it does happen. And for new environments or even when you upgrade your environment you may need to change something. Heck, something as simple as adding a new application to an existing instance may require changes.

So let’s give this a shot. Remember you want to be making these changes in a private sandbox, or your home lab.

Change the following settings (read the rest of the blog before making any changes):

  • Minimum and maximum memory
  • Maximum degree of parallelism (MAXDOP)
  • Minimum threshold for parallelism
  • Default fill factor
  • Cross database ownership chaining
  • Enable CLR
  • Remote Admin Connections
  • Turn on database mail
  • Enable xp_cmdshell

Things to think about here:

  • Some of these are considered advanced settings.
  • Some will require a reboot, some won’t.
  • What are the defaults?
  • How do I tell what the current settings are?
  • What are some of the other options?
  • And of course what does each of these do.

Now, before you make any change you always want to make a note of what the previous setting was. (Always have a backout plan.) And of course you always want to know exactly what the setting you are changing is going to do. For example the minimum memory setting does not mean that SQL is going to start up with that amount of memory.

And as always, none of this is particularly hard, but just because it’s not hard doesn’t mean that you’ve done it before. There is absolutely nothing wrong if you haven’t. No time like the present right?


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...