The 90/10 Query Rule
In this lesson, you will learn how to focus your performance tuning efforts on the small subset of queries that dictate your database's overall speed.
Working Smarter, Not Harder
When you look at a database system with thousands of possible queries, it is tempting to try to optimize everything. However, database performance usually follows a Pareto distribution: 90% of your total system resource consumption (CPU, I/O, and memory) is caused by only 10% of your queries.
If you spend your time tuning a query that runs once a day and takes 0.001 seconds, you aren't making a meaningful impact on your users' experience. Instead, you need to identify the "heavy hitters"—those high-frequency or high-cost queries that put the most strain on the engine.
Identifying the 10%
To apply this rule, you must monitor your workload to find which statements are consuming the most cumulative time. Consider a scenario where you have a slow-running report:
-- This query runs 5,000 times a minute and takes 0.5s each time.
-- Total cost: 2,500 seconds of CPU per minute.
SELECT * FROM Orders WHERE CustomerID = @id AND OrderDate > '2023-01-01';
-- This query runs once a day and takes 30 seconds.
-- Total cost: 0.5 seconds of CPU per minute.
SELECT * FROM Archive_Table;
Even though the Archive_Table query takes longer to finish, it is not your bottleneck. The Orders query is the one consuming 90% of your resources. By adding a targeted index on (CustomerID, OrderDate), you resolve the actual performance drain, whereas tuning the archive query would have been a waste of effort.
How to Apply It
- Analyze: Use execution plans and monitoring tools to see which queries appear at the top of the "Total CPU" or "Total Logical Reads" lists.
- Prioritize: Ignore the long-tail of queries that run infrequently.
- Optimize: Focus your indexing, rewriting, and hinting efforts exclusively on that top 10%.
By ignoring the "noise," you ensure that every minute you spend tuning results in a faster, more responsive application for your users.
Key Takeaway: Stop trying to tune every query; identify and optimize the 10% of queries responsible for 90% of your resource usage.