“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.”
2 DBAs Walk into a Bar (a pub, really), talking about SQL Server performance bottlenecks
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.”