Why is My SQL Server Slow? 14 Common Reasons

“Why is my SQL Server slow?” Have you ever asked that question? Have your users?

Unfortunately, that’s a commonly asked question. Fortunately, though, something can usually be done about it.

Here are fourteen reasons a SQL Server instance could be underperforming. I’ve divided them into four broad categories.

• Slowness due to resource constraints
• Slowness due to configuration issues
• Slowness due to maintenance issues
• Slowness due to programming

Slowness due to resource constraints

Often the first place many people look when the SQL Server is slow is at the hardware resources. Adding resources can help, especially if the SQL Server is truly underpowered for its workload. However it’s best to do some research first so you’ll know what’s really happening.

Here are some things to check when it appears that SQL Server is resource constrained.

1. Misaligned CPU presentation

For SQL Server 2016 and later, Standard Edition supports the lesser of 4 sockets or 24 cores. The key word there is “lesser.” Let’s look at an example.

Let’s say that you’ve licensed SQL Server Standard Edition for 16 cores. But those cores are presented to the VM as 8 sockets, each with 2 cores. Unfortunately, SQL Server Standard will only be able to use 8 cores. Why? Because it can only access cores on 4 sockets. 4 sockets times 2 cores per socket is 8 cores total.

To fix that, realign the presentation so that 4 sockets are presenting 4 cores each. See Editions and supported features of SQL Server 2019 – SQL Server | Microsoft Learn for more information about SQL Server Standard resource limitations.

Note: SQL Server Enterprise Edition is only limited by what the operating system can support.

2. Disk input/output (I/O)

SQL Server relies on the underlying disk I/O subsystem. That’s where it stores its data and log files, along with the binaries. If the SQL Server is slow, it may be because the disks are slow. It’s worth checking. See Identify Disk I/O Performance Issues for Your SQL Server Using DiskSpd to check your disk I/O metrics.

You can also ask SQL Server about the disk I/O performance – Identifying SQL Server Disk Latency. But before you use those metrics to blame the SAN, you should check some other things, such as the most resource intensive queries. They may be asking SQL Server to do a lot of unnecessary reading or writing.

3. Noisy neighbor syndrome

Another potential issue known as the “noisy neighbor.” It comes in a few variations.

• Two databases on the same SQL Server instance. One consumes a lot of a resource, such as CPU or disk I/O, and that affects the performance of other database.
• Two SQL Server instances installed on the same virtual machine. The two instances are independent. They do not coordinate the use of key resources such as memory. If one instance grabs a lot of memory before the other can, its unlikely to relinquish the memory, even if the second instance is memory starved.
• Two virtual machines residing on the same host. The activities of one virtual machine can affect the performance of the other, especially if each are over-provisioned.

4. Insufficient resources

Of course, it may be that your SQL Server simply does not have enough computing power to handle the workload. Maybe you need more CPU, more memory, or faster disks.

See Should I Add Hardware Resources to My SQL Server? Do This First for more information in determining if your SQL Server is actually underpowered for its intended purpose.

Slowness due to configuration issues

SQL Server is really easy to install. You double-click the install media, click Next, Next, Next, etc, and after a few minutes you have a fully capable SQL Server ready for use.

Unfortunately, that’s where many stop, though. That leaves SQL Server with its default settings. The defaults are typically not optimized for your intended workload. Here are a few configuration issues we commonly see.

5. Windows Power Plan

This one is not a SQL Server setting but it does affect SQL Server. The Balanced power plan is the default Windows Power Plan setting. Unless you’ve intentionally changed the setting, that’s the scheme your Windows Server is using. It’s a good setting for many servers since it balances performance with power consumption. Typically, this setting is not good for SQL Servers. A reduction in processing power translates into a reduction in performance.

For more information, including how to check the power plan using the GIU or command line, see SQL Server Performance and Windows Power Plan.

6. SQL Server settings

Microsoft SQL Server has quite a few settings that allow you to dial it in for your workload. For example, MAXDOP and Cost Threshold for Parallelism.

By setting MAXDOP, you can control the maximum number of processors SQL Server should use to resolve a single query. Cost Threshold for Parallelism gives SQL Server an indication of what constitutes a “big” query that could benefit from running across multiple processors. The default settings for these in SQL Server 2019, and earlier, were far from the best setting. SQL Server 2022 does a better job with the defaults, but its still something you’ll want to pay attention to.

You should also pay attention to default settings for maximum and minimum memory, along with data and log file auto-growth settings.

7. Suboptimal disk arrangements

Where you store your SQL Server data and log files matters. Separating the operating system from the data files from the log files from the tempdb files is a good practice. Each of these have different usage patterns and, in many cases, could benefit from separation . Some DBAs even put the SQL Server binaries on a separate drive.

Generally speaking, formatting the storage drives with 64KB blocks helps to improve I/O performance. Additionally, for newer SQL Server instances, the Windows Resilient File System (ReFS) tends to be a good choice.

Slowness due to maintenance issues

Microsoft’s SQL Server development team has done a good job in making a product that works well, even if neglected. That said, you really shouldn’t neglect your SQL Server. It has data that’s important to your organization, and to your users. So give it some attention. See SQL Server Maintenance: 10 Things to Keep Your SQL Server Running Well for more information on proactive maintenance.

Here are a couple of maintenance-related issues that could cause performance problems.

8. Missing index or statistic maintenance

SQL Server creates and uses statistics when determining how to resolve queries. Accurate statistics will produce good query plans which result in better performance. Bad statistics produce suboptimal plans and result in inefficient queries. The good news is that by default SQL Server will keep those statistics up to date. In some cases that may be OK. In other cases, you may not want to rely on the automatic updates. In those situations, a scheduled job that updates statistics can be helpful. See UPDATE STATISTICS (Transact-SQL) – SQL Server | Microsoft Learn for more information.

Likewise, fragmented indexes can cause poor query performance. Fragmentation comes in two types Internal and External. Both can cause SQL Server to work a lot harder than necessary to retrieve data. Proactively maintaining your indexes can help performance. For more information check out What’s the Difference in Index Defrag and Rebuild?

9. Scheduled file shrinking

Sometimes, a data file or a log file can grow unnecessarily large. Maybe a really large transaction caused the log file to balloon? Or perhaps an excessive amount of data was added and then removed from the database? In either case, the files are larger than needed and can be shrunk.

That’s OK.

But regularly shrinking data or log files probably shouldn’t be part of your maintenance plan. Shrinking a data file or log file in SQL Server can cause fragmentation. Fragmentation occurs when data pages are not stored contiguously on disk. This can cause queries to take longer to execute. Additionally, shrinking data or log file may require SQL Server to autogrow the file again, a process that take some time and hurt performance.

Slowness due to programming

The way we ask SQL Server for information matters, and can affect performance. A well designed query will out perform a poorly designed query. Here are a couple of examples.

10. Joining and nested views

Views can be used for several purposes – segmenting data, obscuring complex queries, limiting access to base tables, etc. However, views can also hurt query performance in SQL Server if you’re not careful.

Joining two views that have common base tables can cause extra work for SQL Server. It must access the base table twice, once for each view, whereas querying the base table directly may allow the query optimizer to make one pass over the table. Check out How do Views Affect SQL Server Performance? for more information and some examples.

Nested views, a view that calls another view, can similarly have an adverse affect. We’ve seen both of these conditions in production environments.

11. Poorly designed queries

Transact-SQL is a relatively forgiving and flexible language. Often there are several ways to accomplish a given task. Unfortunately, not all of the ways perform equally well. And to make matters worse, what performs well on a small data set may not perform as well when as the database grows.

There are a lot of examples of poorly designed queries. Each could be a blog post itself. A few examples include:

• Using NOT IN (….)
• Using User-Defined Functions, particularly those that query data
• Not paying attention to datatypes (see How Do Implicit Type Conversions Affect SQL Queries?)
• SELECT * FROM dbo.table
• Triggers
• Cursors
• and many, many more

By reviewing the longest running and most expensive queries on a regular basis, you can identify and update them, making the more efficient.

12. Excessive or insufficient indexing

Indexes can help SQL Server performance. But too much of a good thing is not helpful. Too many indexes, or the wrong indexes, can actually cause performance to dip, especially for inserts, updates, and deletes.

Likewise, too few indexes can cause SQL Server to resort to table scans to resolve queries.

Examine the query plans for your most expensive queries. Look for opportunities to optimize them. Are there table scans that could be answered with an index? (See Using Covering Indexes to Improve Query Performance – Simple Talk (red-gate.com) for more information.)

Review your indexes regularly, looking for missing, duplicate, and unused indexes. We include this index analysis as part of SEROShield Insights.

13. Inefficient database design

Data modeling and is one part science, one part art, and one part knowledge of the business. A good database design supports the business requirements and can help pave the way for a well-performing application. An overly complex, excessively denormalized (or normalized for that matter) database can cause performance to suffer.

For example, a GUID that’s used a primary key is not going to help performance.

14. Overly complex queries from applications

Many applications are designed with efficiency in mind. Sometimes, however, applications can create excessively complex queries that make it difficult for SQL Server. This is often true for Object-relational mapping (ORM) tools that allow developers to define the database objects in code. These tools, while helpful during development, often require some performance tuning as the size of the database grows.

SQL Server performance vampires

A poorly performing SQL Server can be frustrating. It’s frustrating for users who have to wait while doing their busy jobs. And it’s frustrating for IT staff who tire of hearing about the poor performance.

“Why is my SQL Server slow?” The fourteen items listed here are certainly not comprehensive. Other performance vampires exist. But there are common ones that you should check.

Want some assistance with a slow SQL Server?

If one of your applications is notoriously slow and you’ve been struggling with a poorly performing SQL Server, we can help. A good place to start is a SQL Server Health Check.

If that’s something you’d like to learn more about, let’s have a conversation.