If your database users have been grumbling that SQL Server performance has been lackluster lately, it’s possible you are experiencing the effects of one or more SQL Server bottlenecks. These bottlenecks occur for a variety of reasons, but they frequently happen as a result of problems with memory, I/O, or CPU.
Though it’s not always easy to determine whether performance problems stem from a SQL Server bottleneck or from another source, you can look for these common symptoms of bottlenecks to help narrow your search for the source of the issue.
Common Symptoms of SQL Server Bottlenecks
- SQL Server hogging the processor
- Longer execution times on queries
- Excessive I/O
- Application log showing out-of-memory messages
- Extreme activity on the disks
- Long wait times per I/O
The sudden appearance of one or more of these symptoms is a good indication that you have a SQL Server bottleneck somewhere in your system. Now, it’s your job to find it.
Types of SQL Server Bottlenecks
There are three main types of SQL Server bottlenecks: memory, I/O, and CPU. It’s important for DBAs to be familiar with the causes, symptoms, and fixes of each so they can identify and remove the bottlenecks quickly and minimize the impact of poor performance. We’ve also included some of the best performance counters to monitor that will help identify performance bottlenecks immediately.
Memory bottlenecks are usually a result of insufficient memory resources or SQL Server activities eating up available memory. The symptoms to look out for include longer query execution times, excessive I/O, out-of-memory messages in the application log, and frequent system crashes.
The best ways to avoid memory-related bottlenecks are to use a query optimizer to get rid of unnecessary or obsolete queries and to configure page life expectancy in conjunction with buffer cache hit ratio to limit trips to the disk. If it’s too late to avoid the bottleneck, try reviewing and tuning queries, reconfiguring memory, or adding physical memory.
Counters to monitor: memory available, total server memory, target server memory, pages/sec, buffer cache hit ratio
When there isn’t enough storage available to support regular database operations such as TempDB, I/O bottlenecks are likely to occur. Long response times, application slowdowns, and frequent task time-outs are all key indicators that you have an I/O bottleneck.
You can avoid this type of bottleneck by setting up monitoring with alarms and threshold alerts to identify which activities use excessive amounts of storage. If an I/O bottleneck does occur, limit reading and writing of database pages to and from the disk. This will require you to check for and correct frequent index scans, inefficient queries, and out-of-date statistics.
Counters to monitor: average disk queue length, average disk sec/read, average disk sec/write, % disk time, average disk reads/sec, average disk writes/sec
The leading cause of CPU bottlenecks is insufficient hardware resources. It’s fairly easy to identify this bottleneck by checking your logs to determine whether SQL Server is using excessive CPU.
In an ideal world, you could avoid CPU bottlenecks by having a dedicated SQL Server-only server and running all the other software on another machine. Because that isn’t an option for most DBAs, you will need to know how to unbottleneck your CPU.
The first step is to identify the CPU hogs. Once you know where the problem lies, you can tune queries, improve your execution plans, or reconfigure the system.
Counters to monitor: % processor time, batch requests/sec, SQL compilations/sec, SQL recompilations/sec
A Story on SQL Server Performance Bottlenecks
“We’ve got some SQL Server performance bottlenecks to deal with,” said my boss, late one Friday.
“How do you know?” I asked.
“Sales is complaining that their database has been slowing down lately. We need to see what’s going on with it.”
“OK. I’ll book a conference room so we can sit down and work on that.”
“Don’t bother,” said the boss. “It’s late on a Friday afternoon. That means that the best way to study bottlenecks is with a couple of our own. We’re going over to Pike Pub in the market.”
We walked into bar tucked away in Pike Place Market and found a small table by the window.
“What’s your favorite kind of bottleneck?” the boss asked.
I said I was partial to a Naughty Nellie in the 22-ounce bottleneck.
“Good choice,” said the boss. “You order that, and I’ll have the Citrus Summer Ale.”
While we waited for our bottlenecks to arrive, we got down to business on the SQL Server performance bottlenecks.
“We’ll need to check for problems in several places,” the boss said. “They could be problems with memory, storage or processors, but they all look about the same to users, right? Crummy performance.
“CPU problems aren’t that hard to find. If that’s the problem, then we’ll see that SQL Server is hogging the processor and causing it to spike all the time.
“If it’s a memory problem, we’ll see things like longer execution times on the queries and more I/O because the application has to keep running out to disk. We can also check the application log for out-of-memory messages.
“And if the bottleneck is in storage, we’ll see extreme activity on the disks and long wait times per I/O.”
Our bottlenecks arrived. We studied them carefully as we considered the performance problem more deeply.
The Many Potential Sources of SQL Server Performance Bottlenecks
“What if it is an I/O problem?” I asked. “We should see whether WRITELOG wait-time is too high, compared to total wait time. Or, speaking of I/O, maybe there’s a problem in the SQL itself. If there’s inefficient code, like a NESTED LOOP JOIN on a huge table, the SQL could be asking to read rows on the inner table zillions of times. That would really punish performance.”
“Could be,” said the boss. “Complex joins and sorting operations can be tough, especially when the tempdb database isn’t configured properly. Tempdb contention looks like ordinary database locks, but it’s actually latched contention because of concurrent processes all waiting for their turn on the allocation pages.”
“Which tools can we use to examine all these things?” I asked.
“SQL Server had a profiler for examining stored procedures, but it’s deprecated. Something like that is a good way to find and diagnose problem queries, even if it’s only the first step. Then there’s dynamic management views and functions that help you monitor the health of your server and database, run down problems and tune your SQL.”
“But SQL Server has so many moving parts,” I said. “I’d rather have a tool that looks at the entire picture from the outside in.”
“So would I. Preferably from the cloud, so we don’t need more hardware and software on premises to do it.”
Relieving Performance Bottlenecks in SQL Server
Pike was starting to get crowded. And, as willing as the boss and I were to sit in a pub and talk shop, it was Friday evening, after all. We had other places to go, other people to see and other things to talk about.
“What should we do once we’ve found the bottlenecks?” I asked.
“We round up the usual suspects,” said the boss. “To keep from using too much memory, we need to tell the database developers which of their code and queries are leaking memory. If we find operations that join four, five or six tables, we’ll have to give the developers some SQL Server tuning tips and best practices to redesign the database. Or, we may have too many indexes and we may be wasting cycles updating them; that’s as hard on the CPU and I/O as having too few indexes. We may have a problem with SQL Server index fragmentation, or we may have to weed out the obsolete and duplicated indexes.”
“Makes sense,” I said. “Maybe we need to throw more hardware at it. Faster disks can help with I/O bottlenecks. More and faster CPUs make a difference in query response times. And adding RAM means more SQL Server scalability, right?”
“Yeah,” the boss said, “but first I want to be sure the root cause isn’t a development or DevOps problem. Once I’m convinced that it isn’t, then I’ll play the Buy More Hardware card.”
We sat for a moment and watched the pub fill up with Friday evening revelers.
“Boss,” I asked, “do you think all these people know how carefree an existence they all lead, without the burden of dealing with blocked sessions, maximum I/O wait, page life expectancy and buffer cache hit ratios?”
“It’s a cross to bear, isn’t it?” the boss answered. “Most people have no idea what we go through. Good thing we deal with SQL Server performance bottlenecks so quietly, with so much grace under pressure and in such good taste. Speaking of good taste, how are you doing on your bottleneck?”
I checked. “My bottleneck is empty. So is my bottle.”
“Mine, too. Time to go. We’ve got work to do.”
Is a Zero SQL Server Bottleneck System Possible?
We know there are steps we can take to avoid these three common types of SQL Server bottlenecks. But is it possible to configure a SQL Server database so well that it has zero bottlenecks?
The short answer is probably not. Even the most diligent DBA will have SQL Server bottlenecks pop up every now and then. But you can take steps to proactively avoid bottlenecks and minimize their impact on performance. For example, Brent Ozar offers some great tips for monitoring Perfmon counters to tune your SQL Server, and you can use the sys.dm_os_performance_counters view to help identify bottlenecks and correct them quickly.
SQL Server bottlenecks are a fact of DBA life. Fortunately, with adequate oversight, diligent monitoring, and frequent query tuning, performance issues can be dealt with before users even know there’s a problem.