SQL Homework – February 2019 – Instance configurations
5February 4, 2019 by Kenneth Fisher
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?
SQL Studies 
Maximum degree of parallelism (MAXDOP)
I always find this one hard. Setting it is easy. What the best setting should be is not so easy.
It is foolhardy to test on production in real-time. Theory, or, at least, my understanding of wait-types, will lead to one prediction of what the best setting will be. Although I often think that if it doesn’t run well under a parallel system, then the problems lie elsewhere.
It can be easily tested on a test server, but how will it behave under load? The program SQL Query Stress can help with concurrent executions. Traces can be helpful too.
The best practice is number of processors up to 8 and then 8. Increase by increments of 4 as needed. And yea, this is one of those things you just have to do in production unless you can get a good workload going on a comparable test system, which is pretty unusual. That said, once you have the MAXDOP set I’d take a good look at “threshold for parallelism”. The default value is 5 and the best practice is generally considered around 50 these days, and has been going up steadily the last few years.
Fill Factor: If you deal with apps from vendors, do you bother to modify this value and risk losing support?
RAC: How often do people use remote login to troubleshoot SQL Server? I’ve had a vendor asked for this access so that they can decrypt a stored procedure. We eventually worked around this by decrypting the stored procedure for them using SQL decryptor
It’s pretty unusual for a vendor to have a problem with a setting like Fill Factor, and it can have a pretty significant impact on performance. It’s one of those things that can be a helpful change when you aren’t allowed to touch the code.
As far as DAC (I’m assuming that’s what you meant?) goes, I’m a BIG proponent of getting this activated and practicing using it. No, I don’t have to use it often, but it’s vital when I do. That said I would NEVER grant access to it to a vendor. That kind of thing should be kept in house unless they happen to be supporting the application.
[…] little while back we tried configuring the instance. Sometimes though, instance settings are a bit heavy handed, so this time round let’s try […]