Firstly, I recommend watching this How to Think Like the Engine - YouTube by Brent Ozar on YouTube (all parts). It is incredibly helpful foundational material before you begin your SQL performance tuning journey. Throughout my SQL Server Index series, I have emphasized the importance of accurate statistics in helping SQL Server choose the best execution plan. I've mentioned that if a query has accurate indexes and correct cardinality estimates, the optimizer's choices are usually optimal.
However, I haven't discussed the scenario when row estimates are inaccurate, leading the optimizer to make suboptimal choices. This post will cover some examples of that.
To demonstrate, I will use the StackOverflow2010 database. Feel free to follow along with my examples.
(If you haven't, please checkout Tools I Used During Query Performance Troubleshooting to see how I setup to obtain execution plan and logical reads info.)
Problem
Consider the following query:
The query first aggregates the scores of users, filtering for those with score greater than 10,000.
These users are then joined with the Users table to retrieve their names and with the Badges table to get their badges.
The inner query is where the row misestimation starts. As I observed, there are two statistics in the system: one for the OwnerUserID column and one for the Score column.
However, there is no statistic that considers both OwnerUserID and Score together. More importantly, the inner query filters rows based on the aggregated values that are only available during runtime (HAVING SUM(Score)>10000).
Therefore, the optimizer uses some hard-coded value for the row estimate.
Assume we have the following index:
CREATE INDEX IX_UserID
ON dbo.Badges(UserId)
Running the query, I obtained this execution plan:
The filter operation estimates 1 HighScoreUser, but in reality, there are 196 qualified users.
The misestimation leads to an awfully inaccurate estimate of returned badges.
The optimizer assumes that "Well, because there are only 10 badges, I can seek records on the IX_UserID index. But since the IX_UserID index isn't a covering index, I will perform key lookup to get additional columns required by the select query. 10 key lookups are fine to me."
However, the optimizer is wrong, there are actually 39,793 badges, requiring 39,793 key lookups. As a result, the number of logical reads for the Badges table is 120,032.
What if I force a scan on the Badges table?
Now, let's see the impact on logical reads when a scan is forced.
The number of logical reads drops from 120,032 to 6,637. A scan in this case is more efficient than seek following with a large number of key lookups.
Key takeaways:
- The optimizer isn't perfect; it can misjudge row counts and sometimes chooses inefficient operations.
- When cardinality estimates are incorrect, the optimizer may select a less efficient execution plan.
- To testify if we can trust the optimizer's choice, examine the row estimates for accuracy.
- In this case, the cardinality misestimates stem from filtering rows based on aggregated values only available at runtime. However, out-of-date table statistics or complex query structures can also lead to miscalculations.
Solutions:
While query hint can help, I generally avoid them during performance tuning. Data distribution can change overtime, rendering the hint can be suboptimal.
A more robust solution is to create a temporary table to store results from the HighScoreUsers query and then join it to the main query.
Once the query "INSERT INTO #HighScoreUsers" completes, the row count of the temporary table is available to the second query, allowing the optimizer to employ a better execution plan for the SELECT query.
The optimizer's choice is exactly the same as when we use the query hint:
Cleanup
DROP INDEX IX_UserID
ON dbo.Badges(UserId)
Comments
Post a Comment