“Doc, I’m worried about my SQL Server performance.”
It wasn’t the kind of thing you hear from most patients. But then, as a paid professional, I’ve been trained to deal with it all — even the hard times of being a database administrator.
“Really? Let’s explore that, shall we?”
“Sure, Doc. I mean, sometimes it feels so overwhelming. I thought everything would just sort of work itself out on its own once I’d committed to it. But before I knew it, I started having performance issues in SQL Server 2012, then 2014, then 2017. I don’t even want to think about SQL Server 2019.”
“I see. Well, a good relationship with SQL Server doesn’t just happen on its own. You have to work at it. Tell me, have you worked on your SQL Server performance tuning techniques?”
“Er, no, Doc. I don’t really know any of those techniques.”
“Don’t worry. We can work on them. Your SQL Server is probably feeling neglected. You have to keep an eye on it to make sure you’re staying ahead of the game. That takes SQL Server monitoring.”
“Monitoring? How do you do that?”
“You have to show SQL Server that you care. You have to pay attention to certain metrics. We can work on that too.”
“OK, Doc. Whatever you say. I’m willing to try anything at this point. Things couldn’t get much worse than they are now.”
“Good, then. Let’s begin.”
SQL Server performance metrics — Lots of moving parts
The patient was right: monitoring your SQL Server performance can feel overwhelming. You can’t stop paying attention to it once it’s up and running. You have to continue showing it that you care.
SQL Server has lots of moving parts that are constantly generating metrics. Knowing which ones to watch and then actually taking the time out of your busy day to monitor them can be a lot of work for a DBA.
So I went through some of the main areas of SQL Server performance metrics with the patient.
One of the first places to look when you’re having performance problems with SQL Server is the indexes. Your data is always growing, so your indexes are always growing, too. But they’re subject to conditions like fragmentation and page splits that can slow down the response to queries.
What’s going on in a database day in and day out? Users are creating, editing and deleting records. The indexes keep up with where all the pieces of the records are, but over time, index fragmentation hampers performance.
Then there’s index fill factor, a parameter you can configure in SQL Server to control the number of page splits and increase query efficiency. But the balance between more and fewer page splits affects performance in other ways.
Watching metrics in fill factor, I/O and fragmentation is a good way to keep your fingers on the pulse of SQL Server index health.
Let’s make this simple: Disk, slow; buffer cache, fast. The buffer cache is an in-memory copy of recently used database pages. If SQL Server doesn’t find what it’s looking for there, then it has to go to disk for it, which slows performance.
SQL Server lets you specify the amount of system memory to allocate to the buffer cache, but you trade off against memory left for other tasks. Your goal is to allocate as much as you can without hampering SQL Server performance in other areas.
Also important is page life expectancy, the amount of time a page of information from the database spends in the buffer without being accessed again. SQL Server is continually evicting pages from the buffer cache to make room for more recently used ones. But the fewer useful pages it finds there, the more it has to read from disk, which slows performance.
Metrics like page life expectancy and ratio of successful hits in the buffer cache help you make decisions about evicting pages less frequently or increasing the size of the cache.
SQL Server uses a query language called T-SQL. Rather than run SQL statements ad hoc, SQL Server tries to improve performance by batching them, compiling them as an execution plan and caching them. It also tries to minimize the frequency of compilation and reuse execution plans as often as possible. If it can’t reuse the execution plan — say, because the database has changed too much — then it will recompile the plan. It’s best to keep the number of SQL statement re-compiles as low as possible, because the process can consume large amounts of CPU and degrade performance.
The need to compile and re-compile is a function of good coding practices, like using stored procedures and parameterizing queries. DBAs who monitor metrics like the rate of SQL compilations and SQL re-compilations can modify SQL Server query hints to improve performance.
Locks, waits and blocked processes
It’s frustrating to discover that somebody else is modifying something at the same time you are. So databases automatically lock things like rows and tables to keep multiple cooks out of the kitchen. The trade-off for that protection, of course, is that all other users must wait until the resource is unlocked. And, it’s hard to make sure that locking is taking place only at the level it needs to.
With metrics that show how often and how widely locks are affecting other operations, DBAs can determine the need for more physical system resources to process transactions more quickly. It may also be that SQL Server is locking at an unnecessarily low level. The frequency of lock waits and, more broadly, the number of blocked processes can help in locating bottlenecks.
Eliminate bottlenecks with SQL performance tuning
“Gosh, Doc, you’re right about all the moving parts. Now I see how I can’t just install SQL Server, set it and forget it. I need to nourish the relationship. But this all still feels overwhelming. How am I ever going to keep so many different things straight and stay ahead of the game?”
“That’s the best part. There are tools that can help with your SQL Server performance. You don’t have to go it alone.”
“Whew! That’s reassuring. I didn’t know about that.”
“That’s right. The tools monitor your SQL server performance and report all these metrics so you can apply your tuning techniques and eliminate bottlenecks.”
“Sure. And we can work on those right — oh, my, we’re out of time for this week. Please make an appointment with my receptionist and we’ll pick up next week.”
“Boy, those four hours went by fast, Doc! Time sure flies when you’re working out performance problems around fragmentation, resource usage and buffer cache hit ratio, doesn’t it?”
“Yes, and I’m sure that once you communicate openly about those issues with your SQL Server, all of your performance problems will be history.”
Heartened by our session, the patient left. Next time, we’ll work on monitoring SQL Server performance bottlenecks. I’ll blog about it, so keep an eye out.
Meanwhile, trust me. I’m a paid professional, and I encourage you to try these things in your own relationship with your database. Make your SQL Server feel wanted. Pay attention to its performance metrics.
It’s never too late to try.