Why is SQL performance tuning so important to database management?
Because it can save you money big time. Bear with me and you’ll see how.
SQL performance tuning and database management — connecting the dots
Most database professionals spend their time keeping the lights on. They invest most of their effort in ensuring uptime by keeping an eye on resources like memory, storage and network throughput. That’s a big part of database management, but as more companies move their databases to nearly boundless cloud resources like AWS and Azure, other aspects have become more important.
SQL performance tuning is one of those aspects. Once the lights are safely kept on and you move up the hierarchy of needs in database management, the next thing you want is better performance, and that requires tuning.
First questions to ask when performance tuning in SQL
Sooner or later, many database professionals find themselves in front of a SQL Server that they didn’t build. There aren’t many how-to guides for that situation. SQL performance tuning is an exercise in digging in, figuring out what’s wrong and then iteratively fixing it.
In your first fixes, you may not even touch SQL statements at all. Some database pros start at the user/session level. They go to where the users are unhappy, listen to how their complaints sound and pose questions.
- Which screens or pages take too long to render?
- Is the application slower when they create a new ticket or open an existing one?
- Does it take a long time to save a record?
- How long is “a long time?”
Once they have those answers, they go see what in the database is causing it.
That’s better than sitting down on day one and deciding to deal with something like fragmentation, which may not affect users at all. The point is to start with what users care about.
Think also about the instance/database level. In the Microsoft world, for example, SQL Server Agent jobs are a good place to start. They are a series of actions that usually define an administrative task you can monitor for success or failure. They’re meant to be convenient, but like many things in database management, they tend to accumulate as people forget how they originated and what they do.
You may find multiple jobs doing the same thing, like running different versions of an index script or, worse yet, working against one another. Examine the already-configured jobs in light of two questions: “What does this job do?” and, more important, “If I stop that job, will anything bad happen?”
Which factors should you look for?
Once you get to the level of performance tuning SQL, it takes its cues for behavior from several factors. As described during our Ask the Experts: Database Performance Roundtable webcast, you can spend less time tuning the SQL itself if you find and correctly interpret factors like these:
- Blocking — If the server is blocking, it’s like a ticking time bomb. Suppose a script starts a transaction and doesn’t close it; that could lead to a log file that just grows and grows until space runs out. Blocking is bad news for performance, so look for it right off the bat.
- Agents — Apropos of SQL Server Agent jobs, administrators have been known to inadvertently wrap performance-impairing tasks in jobs. They may execute transactions or rebuild indexes in a job, or shrink the database in a transaction. In a case like that, consider disabling the agent temporarily to shut down all associated jobs. It’s an aggressive technique, but if it improves performance, you’ll know why.
- Wait stats — Ask yourself, “What’s the server waiting on right now?” Metrics like page life expectancy and disk queue length have some answers, but they offer only a narrow view. Wait stats show you everything through the lens of wait types and wait categories, letting you focus on the five or so wait events that consume the most time. Brent Ozar’s sp_BlitzFirst is a well-trusted stored procedure for discovering what your SQL Server queries are waiting on right now. Then, when you want to study long-term patterns in wait stats for your server, look to a performance monitoring tool.
- Administrator activity — This is also known as “pilot error,” because some performance problems arise out of what you yourself are doing. Suppose you’re running both SQL Server Activity Monitor and SQL Server Profiler at the same time, trying to learn Query Store. You can’t outrun the observer effect; when you track everything like that, you’re just asking for the database to slow down.
- Indexes — For something that’s supposed to be beneficial, indexes sure can give you a pain in the neck. In fact, they deserve more than just one bullet. Read on.
SQL performance tuning means taking a hard look at indexes
In large part, SQL performance tuning boils down to index tuning. Fortunately, if you master that for on-premises database management, your skills are easily transferable to database management in the cloud.
Index tuning is gaining importance because of the evolving variety of indexes: clustered, non-clustered, unique, filtered, columnstore, hash, memory-optimized non-clustered, XML, spatial and full-text, to name a few. But one thing that has never changed is the first column of the index, which drives the index decisions made by the database engine.
Many vendors sell and deploy applications with plenty of well-intended indexes that end up never being used or, worse yet, actually hamper performance. If you examine the unused index scripts or index consumption scripts in some software products, you’ll find a surfeit of indexes on a foreign key. If the product uses, say, 20 foreign keys, the vendors may ship as many as 20 indexes, plus ten single-column indexes, plus another ten indexes on a unique clustered index, and so on.
Whenever you have the option, the better way to approach database architecture is to start with one clustered index that you think will best represent the table. Then, let the system run itself for a while. If and as you need more indexes, create them. Adding indexes is an exercise in trading off better performance here with problems like filling up disk space and locking over there. It becomes hard to see how each additional index affects the system overall.
For that matter, consider eliminating indexes — the way a person with allergies would eliminate food groups — to see how performance changes. Try to drop every index on your dev instance and see which ones affect your top five queries.
Performance tuning in SQL Server — tools that come with it
Note that you’re not alone in this endeavor. SQL Server includes features designed to improve performance.
Plan guides let you change how SQL Server runs a given query, and even though it’s not pure SQL performance tuning, it does affect performance. Many applications contain SQL queries written by an outside vendor, and even if those queries cause poor performance, some database professionals are understandably reluctant to change them. With plan guides you can attach a query hint or a fixed plan to the query and influence how it runs.
However, the downside of plan guides is that although they don’t change over time, the environment around them does. Like a printed roadmap, they may work well in the short term and become obsolete before long, so if you’re going to rely on them, you’d better revisit them occasionally.
Related to plan guides is Query Store, a feature of SQL Server that helps you identify and tune the queries consuming the most resources in your system. Query Store is not enabled by default for new SQL Server and Azure Synapse Analytics (SQL DW) databases. But it is enabled by default in new Azure SQL Databases.
In general, it’s not difficult to enable Query Store, but not every SQL Server needs it from the outset. Some admins don’t know about Query Store, and some know about it but haven’t yet taken the time to explore it adequately; they are better off leaving it disabled. Later, when they understand how Query Store works, they can use it to find performance differences caused by query plan changes.
Finally, the Database Engine Tuning Advisor analyzes workloads and recommends indexes or partitioning strategies to improve query performance. Running Tuning Advisor on your database is a good idea; just don’t run it too soon. Make sure your database contains enough data so that index recommendations are valid. When you’re first building your application, you may have only a thousand rows in each table. Tuning Advisor’s recommendations are more useful once the database has grown.
Show me the money
As I mentioned at the outset, SQL performance tuning is important to database management because it can save you money. How?
Especially in the cloud, where scaling by credit card is popular, IT teams are finding out how expensive monthly storage really can be. What’s more, they’re beginning to understand that running poorly written queries and letting AWS and Azure manage their indexes increases their cloud computing costs. Slow queries and bad indexes cost you money.
SQL performance tuning is about getting all of those things right. That way, whether you stay in the world of on-premises OpEx or migrate to the CapEx world of the cloud, you maintain control over your spending.
