Database Bottlenecks: How to Identify the Most Important Ones

tomas.kuty over 1 year ago 3 minute read time

Optimize Your Top 100 SQL Queries by Highest Total Wait Time: A Tip for Identifying Real Database Bottlenecks

It’s difficult to do proper database sizing and to design your database schema appropriately. That’s why it’s important to iteratively analyze and fine-tune your database performance in production.

Before starting database schema modifications, you should prove you’re hitting real bottlenecks in your database. The database may have hundreds of bottlenecks, but which once are really important? Which bottlenecks are worth fixing? Which bottlenecks are affecting user experience most?

To find out, start with a tool that can identify which SQL statements cause the most client waits. SolarWinds^® Database Performance Analyzer (DPA) works great for this analysis. When connected to your production database, DPA gives you the ability to watch and analyze the real load.

That’s exactly what I did when I was tasked to improve application performance by optimizing the database throughput. I first used DPA as a database monitor to analyze and identify the top 100 queries (SQL statements) suffering from highest total wait time.

Here’s the workflow I followed and recommend:

Utilize a tool which can identify real database bottlenecks for your database type
- DPA works for most RDBMs
Identify the top N SQL statements with the highest total wait time
- In DPA, navigate to your database “Trends” page and open the “SQL Text” tab (below the charts) to list the top N SQL statements
Optimize the SQL statements identified in the previous step
- DPA may give you advice on how to optimize specific queries or overall performance:
  - Click the SQL hash to navigate to the Query Detail page, which collects all information about a single SQL statement in a single page, including performance tips.
  - Navigate to the “Waits” tab in the “Trends” page to see top Waits. Click individual waits to get advice on improving overall performance.
  - Navigate to the “Tuning” tab to see top Query and Table Advisors, which may help an individual query or overall performance.
Repeat until you meet your performance requirements

Hint: DPA shows the top 15 queries in the Trend charts by default. Change the NUMBER_OF_ITEMS_IN_TIMESERIES_CHARTS advanced option to see more (max 100) queries in trends.

Doing this exercise prevented me from making some wrong assumptions, such as:

My database stores rows where timestamp is part of the logical key. If I partition the table by timestamp, it should give me at least a 10% performance boost overall.
My database stores rows where entity id is part of the logical key. If I organize the data into shards by the entity id, it should give me at least a 10% performance boost overall.
My database contains large tables with several large indexes, which can be avoided if I switch to column store. This should give me at least a 10% performance boost overall.

Instead, identifying the top 100 queries by highest total wait time—in this example—showed little benefit that could be gained from partitioning, sharding, or column store techniques. There was nothing about tables in the top queries, which I initially thought would benefit from optimization.

Now, before I start thinking about implementing partitioning, sharding, and column store in my database, I optimize the queries with highest total wait time first. This prevents me from putting a lot of effort into redesigning the database schema when the overall performance gains would be minimal. Furthermore, after optimizing the top 100 queries using this technique, I’ve often found that I may not need to optimize the database any further—as performance requirements have already been met.

To get started optimizing your database, you can download DPA free for 14 days to try out the optimization features for yourself.