A Database Administrator (DBA) is like a racecar technician. Building and optimizing a high-performance vehicle requires minuscule fine-tuning and attention to detail. A tweak here and an adjustment there could shave just a fraction of a second from your lap time. Of course, that fraction of a second could determine whether you win or lose the race.
As a DBA, you are responsible for fine-tuning your SQL queries so that they have extra horsepower. Even a small improvement in speed could put you ahead of your competition and make you a winner in your client’s (or your boss’s) eyes.
While SQL performance tuning isn’t something you become an expert at overnight, there are a few guidelines to follow as you’re getting started as a cloud DBA. This guide will give you a basic framework to build on as you grow your skill set and knowledge base. You will learn a few vital tricks of the trade, like how to think in buckets and to group correlated metrics.
Also included is a general checklist of database maintenance tasks and a free demo of SQL tuning software.
Browse a specific category by clicking on any of the quick links below:
1. What is SQL Performance Tuning?
It may be helpful to begin with a clear definition of what SQL performance tuning is and an understanding of what you are trying to accomplish.
SQL performance tuning is the process of improving the queries executing within your database with the intention of returning the desired results as efficiently and quickly as possible. There may be dozens of ways to return the same results, and SQL performance tuning is all about finding the fastest/least expensive one.
There is no one silver bullet in SQL tuning. What may be the best practice in one instance wouldn’t work for another situation. All databases are not created equal in terms of purpose (think OLTP vs OLAP/data warehouse) or size (think gigs vs teras). This is a continual process of testing and recording the results, then testing again.
When conducting performance tuning tasks, it’s important to:
- Think in buckets
- Focus on correlated metrics
- Measure results over time
- Conduct regular database maintenance
2. Think in Buckets
When presented with the task of database performance tuning, it can be hard to decide where to start. You may not yet have an idea of what aspect of your database requires attention. When talking about administering a database in the cloud, you may have to bring into consideration things you didn’t have to worry so much about when your database was on-premises.
The best way to formulate your plan of action is to think in buckets. Look at your database like a mechanic analyzing a car that just came into the shop. It’s not very helpful to look at everything at once, so the mechanic looks at issues based on their function and location. Is it an issue with suspension? The engine? Or does it require body work? You could call these the “buckets” of automotive repair.
When it comes to database performance tuning, separating potential issues into buckets means organizing everything based on commonality. For databases and tuning SQL, the buckets can be thought of as:
- Resources (physical or virtual)
- Indexing/Data model
- Query structure
- Concurrency (blocking – multiple threads requesting locks on the same database resource)
From there, you can drill down into other “buckets” to identify the source of the problem. Take resources for example, since it may be the one thing you have to focus on a bit more if your database lives in the cloud. Below are some of the resource categories to consider when looking at making SQL statements run better:
- Memory
- Disk I/O (volume and latency)
- CPU
- Network
The same further breakdown can be used for the other “buckets,” but we won’t dive deeper into those as they don’t significantly change when thinking about on-premises vs cloud.
3. Focus on Correlated Metrics
When you test your queries and fine-tune your database, you’ll be tracking specific metrics in order to chart improvement over time. These metrics could include:
- Wait states (where the database is spending time executing the query for the end-user)
- CPU utilization and signal waits
- Commit response time
- SQL statement compilations
- Transaction throughput
- Memory pressure (latch contention and data flushed out of memory quickly)
- Disk I/O latencies
- Network bandwidth and usage (this is especially important for supporting databases in the cloud)
Use wait states as a guide for what metrics to use for correlation. If your database is spending all its time doing disk reads, then looking at memory pressure (why does it keep having to go back to disk?) and disk performance (read latency) as correlating metrics to see if those specific resources are causing performance issues. Another great example is blocking. If your database is spending all of its time waiting for locks to be released, you cannot correlate CPU, memory, disk, or network contention or pressure.
When it comes to performance metrics, database analysis tools are your best friend. The dashboard function in Database Performance Analyzer provides a valuable window into how metrics are correlated. Multi-dimensional performance analysis gives you the right context, so you can look at your data from multiple angles and triangulate the root cause of performance issues. The multi-dimensional view provides you with the who, what, when, where, and why answers you need.
Using SolarWinds® SQL Sentry, DBAs can get even deeper visibility across your entire Microsoft data estate with quick views of real-time and historical events and performance data. SQL Sentry can also show execution plan diagrams, query history, and more, so you can find and fix potentially problematic queries fast.
4. Measure Results Over Time
To understand how your SQL queries are performing, you need to track your metrics over time. Collecting data over the course of a few weeks or even months will give you a big picture view of your database’s overall performance.This will help you see the aggregate as opposed to a snapshot of a single event. It helps you answer the question, “Is my performance worse now than it has been historically?” With the ability to chart performance over time, that can be used as a baseline to determine if performance for any given time frame is outside the norm. An example might be comparing end-of-month activity to the end of last month instead of just last week.
5. Database Maintenance Tasks
Database maintenance is essential to SQL tuning because this task enables you to find potential issues and solve problems you may have never known existed.
Maintenance tasks may vary by environment, but there are some common or general tasks that are recommended.
Before going into the specific maintenance tasks, here are a few acronyms that every DBA should be familiar with:
- SLA – Service Level Agreements (what you and the business end-user agree upon)
- RPO – Recovery Point Objective
- RTO – Recovery Time Objective
- MTTI – Mean Time To Innocence (the amount of time it takes to prove that the issue doesn’t rest with the database)
Keeping these acronyms in mind, here are the main areas where you need to perform routine maintenance as a DBA:
1. Backups – This needs to be done in a way that meets RPO/RTO/SLA objectives, but it will vary by system. One possible approach would be to conduct full weekly backups, daily differential/incremental backups, and hourly transaction log backups (for SQL Server®) during business hours. This will give you a decent amount of protection out of the gate, but is not appropriate to all systems.
For example, a 30PB database is not likely to need weekly full backups. Also, it’s very important that you know how long it will take to recover to yesterday, last week, last month, or last year. You need to build a recovery plan and make your backups fit that plan.
Remember, you may not get a raise if you can’t tune your database environment, but you WILL NOT HAVE A JOB if you can’t guarantee you can restore.
2. Index maintenance – Your indexes need to be checked frequently, most likely daily. However, you may not want to rebuild indexes every night. Again, it depends on the system and the length of time needed to rebuild. It’s possible that the level of fragmentation in the indexes is such that the updating of stats or reorganizing the index are better options than rebuilding the index.
3. Update statistics – Statistics ideally need to be checked daily. These checks depend on the system, the length of time to update stats, the amount of churn within the database objects (tables and indexes), and the queries that will be forced to recompile the next time they are run.
4. Corruption checks – You should make checking for corruption a top priority, at least once a month.
5. Capture configuration details – For both the database and the server, you want to grab details of the configuration settings daily, and track when changes are made. You could extend this to capture other things like logins/users created or deleted, permissions for those users, etc. The level of detail is up to the diligence of each individual DBA, and their desire to keep MTTI low.
6. Helpful Resources
In addition to the Maintenance Task Checklist, here are some helpful resources to aid in your development as a Database Administrator:
- For writing better SQL queries, dive into the book T-SQL Querying
- Free trial of the Database Performance Analyzer
- 3 Helpful Tips for SQL Server Performance Tuning
- Download a free trial of SQL Sentry
Conclusion
As you can see, being a cloud DBA encompasses a lot of the same concerns and activities as a traditional DBA. We can use a lot of the same tools and approaches for troubleshooting performance issues as we have in the past. DBAs of the cloud and future will likely be required to have a broader understanding of technologies. So, dust off those network skills and get your head in the cloud.
Download this simplified, single-page SQL database maintenance checklist and tape it to your office wall as a reminder of these maintenance tasks.