on December 1, 2016
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:
- Taking ownership and responsibility for the configuration
- 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?
Do 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
All 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 know__s 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?
I 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:
- Brent Ozar Unlimited’s sp_Blitz script from the First Responder Kit: https://www.brentozar.com/blitz/
- SQL Skills’ Diagnostic Queries: http://www.sqlskills.com/blogs/glenn/category/dmv-queries
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.
REGISTER