Should I Add Hardware Resources to My SQL Server? Do This First.

“The server is seriously slow! Can’t we add memory or something?” We’ve all heard this before. You may have even thought it yourself. Adding resources could be an easy fix to a troublesome performance problem. After all, SQL Server hungers for memory. The more the better. And a side of processor goes a long way, too.

I’m reminded of a classic IBM consulting commercial from many years ago. [IBM – Throw Money At It? – Catapult Ad – YouTube]. In it, Consultant Ned advises Sir Arthur to build a giant catapult to fire the “greatest of projectiles” at the attacking sloth. If you haven’t seen it, it’s worth 30 seconds of your time.

The cost of adding resources to your SQL Server

Adding hardware may seem like a good idea. It could be a quick fix that will make users happy, at least for a while. But it’s not a panacea. It can come with a hefty price tag. So it shouldn’t be the first tool out of the bag.

Compute time

With the turn of a knob, you can add a couple of processers to the VM. A click of a mouse can increase the memory available to SQL Server. And in Azure, AWS, and other IaaS providers, faster solid-state disks are there for the asking. That’s definitely an Easy button.

However, these are not free, especially in a cloud environment. In a model where you pay by the resource and compute, these changes come with a very real and direct cost.

Even in a on-prem environment, the more resources you allocate to one VM, the fewer you’ll have available for the other VMs residing on the host.

SQL Server licenses

SQL Server licensing is, in a word, complex. So, I’m not going to get into the nuances of the different licensing models. However, SQL Server is licensed by processor. And it’s not cheap. The price for SQL Server 2019 Enterprise Edition is currently $13,748 for a 2-core pack, whereas the Standard Edition is $3,586, according to Microsoft. So adding four processors to a SQL Server Enterprise Edition system can have a memorable price tag.

Additionally SQL Server Standard Edition has some built-in resource limitations. Once those are reached, you’ll need to either upgrade to Enterprise Edition, or scale out to other SQL Server Standard Edition instances. Obviously both have some hard and soft costs.

For more information about the differences in the editions of SQL Server, see Do I Really Need SQL Server Enterprise Edition?

Before adding resources to your SQL Server

As enticing as throwing resources at your SQL Server problem may be, it’s certainly a good idea to look at other options to solve your performance problems first. In doing so, you may, indeed, find that SQL Server needs more resources. But, at least, you’ll know exactly what resources are needed, and have confidence that you are making the right decision.

So, before you turn the magical resource knob, let’s look a few areas to examine first.

Define the problem

Albert Einstein purportedly said “If given an hour to solve a problem, I would spend 55 minutes defining the problem and then five minutes solving it.”

Although there is some serious doubt around whether he actually said that, the point is still a good one. Before rushing in to solve a problem, it’s best to truly understand what you’re actually trying to accomplish. Define the problem before attempting to solve it is sound advice, even if Einstein didn’t say it.

In the SQL Server world, we’ve seen a wide array of “database problems” that weren’t actually “database” problems. So it’s a good idea to actually make sure the issue doesn’t reside somewhere else. For example, could it be a networking issue? Or perhaps a DNS issue? Maybe it’s a Citrix issue?

This is where benchmarking your systems can be of tremendous help. Have a look a the key metrics now and compare them to the metrics captured last month or last year. See if there’s a noticeable difference in response times, etc.

Clarifying the problem up front can save you time and money in the long run.

Assess the SQL Server configuration

Once you’ve determined that the problem may actually be with the SQL Server, it’s worth your time to do a high-level assessment of the configuration. This is especially important if you’ve inherited the SQL Server, didn’t set it up yourself, or it’s been a long time since you’ve looked at it.

In our SQL Server Assessments, we do a thorough analysis of the system configuration and settings. We look to see how it compares to industry best practices from a lot of perspectives, including those that would affect performance.

You may not have to go to that level of detail at this stage, though. A cursory glance at some of the more commonly misconfigured settings may prove fruitful. At a minimum, it’s worth ensuring that:

• tempdb is configured properly. Verify you have the right number, the right location, and the right sizes. See 3 Ways to Find Your SQL Server tempdb Data Files and How Many tempdb Data Files Should My SQL Server Have?
• MAXDOP is not left at the default setting. That’s seldom the best choice.
• Cost Threshold for Parallelism is not set to the default. This never the right choice.

You can also look for other areas that could cause contention, such ensuring data and log files are separated, etc.

This quick assessment may not reveal the solution, but as long as you’re looking at the SQL Server anyway, it’s a good idea to do a quick check. Here’s some more information that may be helpful as well – Is My SQL Server Configured Properly?

Also, check out our SQL Server Configuration: 5 Common Issues That May Be Putting Your Data at Risk free PDF.

Confirm maintenance plans

When a new query comes in, the SQL Server query optimizer decides how to resolve it. The optimizer considers quite a few alternatives for gathering the data. It assigns a cost to each approach and selects the plan with the lowest cost. This is known as cost-based optimization.

Cost-based optimization is predicated on having good, accurate, and up-to-date statistics. If the statistics are stale, bad decisions are made. And bad decisions lead to poor performance.

Verify that maintenance plans are in place to keep the statistics up to date. Verify that indexes are being maintained as well.

Examine SQL Server wait statistics

As SQL Server goes about its job of receiving and responding to queries, it actively manage its key resources. For example, memory, CPU, disk i/o and network throughput are all critical resources for SQL Server.

SQL Server tracks what each thread is doing, and more importantly for this discussion, what each thread is waiting on when it’s waiting. This is information is commonly called wait stats.

Best of all, SQL Server makes this information available to us. We can use the Dynamic Management Views (DMVs) to ask SQL Server what it’s waiting on.

There are a LOT of wait stats. Paul Randal has a some great information about them on the SQL Skills web site. Here’s a great place to start reading some of his detailed writing on the subject. And, of course, Glenn Berry has a ton of DMV queries.

If you’re more of a point-and-click person, you can also find some useful information in SQL Server Management Studio’s Performance Dashboard. It’s not nearly as comprehensive as going right to the DMV source, but it may provide enough information to point you in the right direction.

Identify the most resource intensive queries

Another approach consider before adding hardware resources, is to identify the most expensive or problematic queries on your SQL Server and see if you can do something about them.

The most expensive queries are not always the ones that take the longest to complete. A query for a report delivered nightly at 2:00 AM may take 10 minutes to complete. And you may not care because the SQL Server is not heavily used in the wee hours of the morning.

On the other hand, you may find a that the application login procedures take 10 seconds to complete. Each morning from 7:00 AM to 9:00 AM while users are logging in, the system is S-L-O-W! If you can get those 10 seconds down to 10 milliseconds, you’ve got huge win.

The DMVs will be your friend in searching for these. SSMS also has some standard reports built in, including:

• Top Queries by Average CPU Time
• Top Queries by Total CPU Time
• All Blocking Transactions
• Service Broker Statistics
• Top Queries by Average IO
• Top Queries by Total IO
• and much more

With “home grown” applications, those applications designed and developed by your company, you may have the freedom to make some pretty substantive changes. You can recommend database design changes and tune long running queries. You can create indexes and remove nested views. (See How do Views Affect SQL Server Performance?)

For purchased applications, on the other hand, such as electronic medical records, accounting packages, job costing applications, etc, it’s best to coordinate with the vendor. Typically, it’s not a good idea change to their application. It’ll break support and probably hinder your ability to upgrade in the future.

Other tools and resources

Here are a few other tools and resources that may help.

• Performance Monitoring and Tuning Tools
• SQLPerformance.com
• SQL Sentry
• SQL Server Performance and Windows Power Plan
• Identify Disk I/O Performance Issues for Your SQL Server Using DiskSpd
• Why 9 Out of 10 SQL Servers Aren’t Configured with Best Practices

Want to work with The Sero Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation. 

Schedule a call with us to get started.