Adding indexes to a table to improve data access paths is one of the most effective ways to optimize workload performance. I’m sure you’ve all witnessed first-hand how a carefully crafted index is capable of dropping the duration of a query from hours to just a few seconds. This technique is extremely effective, and relatively low-risk, compared to the alternatives of rewriting the query, redesigning the process, making schema changes or even upgrading the hardware and software. It is so effective, that sometimes we overlook the risks involved with it. In this short article, I would like to focus on an issue that one of my clients experienced recently - an issue that nearly brought their production environment to a complete halt.
In this series of short articles, we lift the hood of the SQL Server Optimizer to examine a few of the many clever tricks used to optimize query performance. You'll see that the Optimizer does not limit itself to using only the instructions provided by the written query syntax, but can correctly deduce a more efficient approach from information it gleans from the schema, the query and data statistical information.
In the second part of this series on physical join operators we looked at the Merge Operator. In the final part of the series we turn our attention to the Hash operator.
For this article series I am using an analogy of two sets of standard playing cards. One set with a blue back and another with a red back that need to be 'joined' together according to various join conditions. The two sets of cards simply represent the rows from the two joined tables, the red input and the blue input - "Now Neo - which input do you choose"?
In Part I of this series on physical join operators we looked at Nested Loops. We now turn our attention to the Merge operator.
For this article series I am using an analogy of two sets of standard playing cards. One set with a blue back and another with a red back that need to be 'joined' together according to various join conditions. The two sets of cards simply represent the rows from the two joined tables, the red input and the blue input - "Now Neo - which input do you choose"?
SQL Server implements three different physical operators to perform joins. In this article series we will examine how each operators works, its advantages and challenges. We will try to understand the logic behind the optimizer's decisions on which operator to use for various joins using (semi) real life examples and how to avoid common pitfalls.
SQL Server trace, the most common tool DBAs use to evaluate query performance, provides the 'logical reads' counter on which many DBAs rely for evaluating a query's I/O performance. In this article, we will examine this counter's true meaning and provide examples that prove it can sometimes be quite misleading...