Signs Your SQL Server is Running with Scissors (Dear SQL DBA Episode 24)

Does your team know what it’s doing with SQL Server? Learn what a consultant looks for when assessing a team, and signs that SQL Server may be badly configured.

Watch the 23 minute video, or scroll down to read a summary of this episode. If you enjoy the podcast, I’d appreciate your review on iTunes! Learn how to ask your own Dear SQL DBA question and subscribe.

Dear SQL DBA,

When sitting down for the first time with an existing SQL Server environment, what do you look for as an indication that they know what they’re doing? This is along of the lines of Van Halen and the brown M&Ms (http://www.npr.org/sections/therecord/2012/02/14/146880432/the-truth-about-van-halen-and-those-brown-m-ms).

Perceptive in Pittsburgh

What a fun question!

I’ve checked out new SQL Servers a few different ways. Sometimes I’ve looked at the SQL Server for the first time live, while talking to the customer. And sometimes I’ve had a bunch of information about the SQL Server’s configuration and performance metrics to review before I ever talk to anyone.

What I learned was not to judge a team by their SQL Server. Some configurations may look problematic, but make a lot more sense when I talk to the team and dig into problems they’re facing.

For instance, there’ve been many times when a team was facing a performance issue, and at first glance their SQL Server looked stupidly underprovisioned in terms of memory. Upon digging into the problem I found that adding more memory wouldn’t solve their particular problem. One size doesn’t usually fit all.

But there are definitely things that make me think a team has their act together. And there are specific configurations that make a DBA team look like they may need to have a little bit of an intervention.

How I identify a capable DBA team

There are two traits that make me think a team is capable of doing a good job managing a SQL Server, regardless of how much they know about the specifics of what SQL Server needs at the time:

  1. Taking ownership and responsibility for the configuration
  2. Being willing to discuss and challenge any element of the configuration

This doesn’t mean being willing to change any setting at the drop of the hat. It just means being open to learn and discuss changes, whatever they are, and evaluate the benefits, risks, and costs.

If you have these two traits, you can get your SQL Server into great shape and care for it over time. It doesn’t matter if you know nothing about SQL Server, because taking ownership means you’re willing to learn.

If you don’t have these traits, you’re going to struggle, regardless of what you know — because what works well will change, and you won’t.

A litmus test: is your team capable?

some-teamsDo you ever find yourself saying or thinking something like this?

There’s no way that we can do (some change), because I can’t get the (money / permission / other resource)

If you have that kind of mental block, you’re not really taking ownership and responsibility for the configuration.

This is a matter of perspective, but it’s a perspective that makes a huge difference. We all work with funding and resource constraints. If you see a change in configuration that you think would be beneficial, the perspective of the capable team is that you want to:

  • Identify scenarios where the change will make an improvement
  • Research and find resources to back up your idea
  • Design a way to measure the current state and potentially create a testing scenario to prove that it will work
  • Make a plan to make your case to business owners

It’s possible that the change won’t be approved. That’s OK — you file away your information, and if more incidents come up around the problem you’re trying to solve, you’re prepared with a possible solution.

Essentially, the capable team has the perspective, “What can we do to make this better?” They ask this question even if they can’t implement all the solutions.

If you and your team don’t have this perspective, this is something you can change. You don’t have to try to change others: just work to change your own perspective. If you can successfully change your approach and viewpoint, it will naturally spread to your team. (And as a big bonus, it’s likely to make you a happier employee.)

Five signs that your SQL Server is running with scissors

sql-underpantsAll this being said, there are some settings that make it look like you don’t know what you’re doing with a SQL Server.

1. Priority Boost is enabled

There’s been a warning on the Books Online entry for Priority Boost for years which says:

Raising the priority too high may drain resources from essential operating system and network functions, resulting in problems shutting down SQL Server or using other operating system tasks on the server.

It’s also deprecated. But it’s still enabled on plenty of SQL Servers.

Finding this is like seeing the sky turn green. You know that you’re in for a colorful time, because who knows what else is going on.

2. Processor affinity masking is configured

This is a sign that the SQL Server isn’t properly licensed. Some folks think that they can use processor affinity to reduce SQL Server’s licensing costs: they want to limit SQL Server to use only a specific number of cores, and pay for that exact number.

SQL Server licensing doesn’t work that way. You need to license all the cores that are provisioned to the Virtual Machine at the guest level, or license the physical cores installed in the server at the host level.

Finding this is really not fun, because it’s a very expensive piece of bad news to deliver.

3. Backup Log with TRUNCATE_ONLY / NO_LOG / DISK =’NUL:’

This one is becoming more rare because Microsoft removed the syntax for log backups with TRUNCATE_ONLY and NO_LOG in SQL Server 2008. That seems to have helped people understand that throwing away a log backup isn’t a good idea. If you don’t need point in time recovery and incremental restores, use the SIMPLE recovery model instead.

But some people still use the workaround which Microsoft left in place, where you can back up a log to NULL. This usually gets left in place if people were trying random fixes from the internet and didn’t understand what they were doing.

4. Auto-shrink is on when performance is important

It’s very well documented on the internet that shrinking can cause problems in SQL Server, and that auto-shrink is bad. It can cause blocking, random performance problems, and if you feed auto-shrink after midnight then your SQL Server will wreck the datacenter.

But I still sometimes find auto-shrink enabled.

The funny thing about this one is that usually people know they shouldn’t have it on, and are surprised by it. Finding this is usually a sign that the team is stuck in such a reactive fire-fighting mode that they haven’t scheduled any time to evaluate basic configuration errors.

5. Multiple instances in the same Windows install when performance is important

This is an example of finding old-school thinking that doesn’t make sense anymore. “Instance stacking” is when you install multiple SQL Server instances into the same Windows install. SQL Server lets you do it, and it’s supported to do so.

But when performance is important, this is a nightmare to manage. It’s like having a house full of hungry teenagers where any one of them can eat all the food in the fridge IN AN INSTANT, and then the other teenagers get really upset if they are awake.

If performance is important, the better approach is to either consolidate the instances (thereby simplifying troubleshooting by letting one SQL OS schedule resources across the whole server), or separate your teenagers into their own apartments using virtualization. If you have security reasons to keep ’em separated, virtualization gives you much more flexibility over time.

Finding this often means that the team used some really old information when they designed the environment, because instance-stacking was popular when Avril Lavigne first came out.

What do I do to take control of my SQL Server?

scissorsI don’t mean to shame anyone who realizes they’ve got a problem in these areas, or who isn’t sure.

It’s not a personal failing. It doesn’t mean you’re dumb. It means you have a prioritization problem. You need to make some time soon to step back and take charge of your configuration.

Talk to your manager about planning some time soon for you to perform a SQL Server health check. Look at free tools available for this online, and practice using them in a test environment. Then take a look at your production server.

Here are some free tools that may help you out — check them out, test em, and pick your favorite:

Don’t rush in to randomly changing settings. Document everything first, and then use a good change control process. If you don’t have change control in place, start with my free Change Control Template.

Previous Post
Learn to Tune Indexes in Lisbon on March 9, 2017
Next Post
When Did SQL Server Last Update That Statistic? How Much Has Been Modified Since? And What Columns are in the Stat?

Related Posts

8 Comments. Leave new

Hi Kendra,

Instance stacking:
What about stacking on a virtual machine if you have additional ressources for each instance and seperate discs for each data/tlog/tempdb?

Would you ” recommend ” this if performance is not the Most important topic?

Reply

    I don’t recommend instance stacking. It’s just particularly bad if performance matters — even if performance isn’t the #1 concern.

    With instance stacking in the same OS, there is no good way to share memory or CPU resources between the instances. And when it comes time to answer the question, “Why were things slow?” the more instances you have, the more problems you have. What were the wait stats on each instance? What queries were running on each instance? What were the file wait stats on each instance? Plus, it’s virtualized, so now you need to look at the host and the VM as well.

    Even when performance doesn’t matter, I wouldn’t recommend instance stacking. It’s still painful, it’s just not quite excruciating.

    Let’s say that one of your instances starts freezing up, and it’s hitting a crash dump. Do you have a test instance that’s set up the exact same way as that production instance, with the same number of instances and the same SQL Server versions, so you can repro the problem? Probably not. And even setting it up is a pain, because you can’t install those instances in just any order — it wants it done from lowest to highest.

    And even with regular maintenance like patching, instance stacking can be frustrating. When you patch the OS, you have to take everyone offline — you could be more strategic if they were separate. And if you want to upgrade just one of the SQL Server instances, you’re stuck deciding whether or not to reboot, because it impacts everyone.

    So I don’t recommend instance stacking in production.

    Reply

OK, thank you for your explaining answer.
Normally I would be with you but many of our customers don’t wanna pay for a additional server and licenses. We always tell/recommend him that it would be the best option to install a complete new server with only one instance.

But if our customer asks us if it is possible to do instance stacking we have to say “Yes, but…” most of our customer doesn’t doesn’t run into performance problems with their normal applications. And we’re talking about really small instances with max. 4cores and 16GB RAM each, there is a lot of space for increasing resources before having pain with a deep-dive into waits analysis.

Patching and downtime for any problems or interferences between the instance were explained to the customer before we do such instance stacking and if the customer says “Yes, I want that instance stacking with all that pain…”

For a e.g. Datawarehouse or a online application we always recommend not to use instance stacking because of your given examples!

Many thanks for explaining your point of view.

Reply

Hey Kendra, I enjoyed watching the video! I have a health check script I created (https://sqlhealthcheck.net), and I will add a couple of lines to review for priority boost being enabled, I definitely think it’s a very big no-no.

Reply
Tony Covarrubias
December 12, 2016 9:08 am

Yikes! I thought instance stacking was a good thing. I have an instance stack where performance is not that important, and the systems they support are not heavily utilized. If I leave them this way, what is the best way to set max server memory? 75% now seems ambiguous since both instances will try to use 75% … is that right?

Reply

    There’s not a great way to set max server memory– that’s one of the problems with instance stacking.

    If you set both instances to max out at 45% (or whatever is appropriate to make sure you leave some amount of memory for the OS), you have a chance of wasting memory if they both don’t need that much — or if at times one of them would benefit from more when the other isn’t doing much.

    If you set them both at 75%, they will try to balance out, even if both are busy. You can set the ‘min server memory (mb)’ setting on the instances to specify how LOW each is willing to go. But you do run the risk of them both wanting a lot of memory at the same time, and each giving up memory and working hard to balance, while hopefully leaving enough for the OS at the same time.

    Reply

Hello Kendra,
I installed multiple instances in the same Windows because the price of the licences.

And i have it done because of the technet information for SQL Server 2014 and 2016.

I’m working in the public administration and cost saving is much more important than…
And the new licence model from MS isn’t very nice. 🙁

Thank you for your videos

Kind regards

Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu