In a Nested Loops Join, for example, the first accessed table is called the outer table and the second one the inner table. In a Hash Join, the first accessed table is the build input and the second one the probe input.
Stream Aggregate and Merge Join, require data to be already sorted. To provide sorted data, the Query Optimizer may employ an existing index, or it may explicitly introduce a Sort operator.
hashing is used by the Hash Aggregate and Hash Join operators, both of which work by building a hash table in memory. The Hash Join operator uses memory only for the smaller of its two inputs, which is defined by the Query Optimizer.
Queries using an aggregate function and no GROUP BY clause are called
scalar aggregates, as they return a single value, and are always implemented by the Stream Aggregate operator.
Stream Aggregate operator is to aggregate values based on groups, its algorithm relies on the fact that its input is already sorted by the GROUP BY clause, and thus records from the same group are next to each other.
The Query Optimizer can select a Hash Aggregate for big tables where the data is not sorted, there is no need to sort it, and its cardinality estimates only a few groups.
The input shown at the top in a Nested Loops Join plan is known as the outer input and the one at the bottom is the inner input. The algorithm for the Nested Loops Join is very simple: the operator used to access the outer input is executed only once, and the operator used to access the inner input is executed once for every record that qualifies on the outer input.
the Query Optimizer is more likely to choose a Nested Loops Join when the outer input is small and the inner input has an index on the join key. This join type can be especially effective when the inner input is potentially large.
One difference between this and a Nested Loops Join is that, in a Merge Join, both input operators are executed only once. You can verify this by looking at the properties of
both operators, and you'll find that the number of executions is 1. Another difference is that a Merge Join requires an equality operator and its inputs sorted on the join predicate. In this example, the join predicate has an equality operator.
given the nature of the Merge Join, the Query Optimizer is more likely to choose this algorithm when faced with medium to large inputs, where there is an equality operator on the join predicate, and their inputs are sorted.
In the same way as the Merge Join, the Hash Join requires an equality operator on the
join predicate but, unlike the Merge Join, it does not require its inputs to be sorted. In addition, its operations in both inputs are executed only once, which you can verify by looking at the operator properties as shown before. However, a Hash Join works by
creating a hash table in memory. The Query Optimizer will use a cardinality estimation to detect the smaller of the two inputs, called the build input, and will use it to build a hash table in memory. If there is not enough memory to host the hash table, SQL Server can use disk space, creating a workfile in tempdb. A Hash Join will also block, but only during the time the build input is hashed. After the build input is hashed, the second table, called the probe input, will be read and compared to the hash table. If rows are matched they will be returned. On the execution plan, the table at the top will be used as the build input, and the table at the bottom as the probe input.
Finally, note that a behavior called "role reversal" may appear. If the Query Optimizer is not able to correctly estimate which of the two inputs is smaller, the build and probe roles may be reversed at execution time, and this will not be shown on the execution plan.
In summary, the Query Optimizer can choose a Hash Join for large inputs where there is an equality operator on the join predicate. Since both tables are scanned, the cost of a Hash Join is the sum of both inputs.
The SQL Server Query Optimizer is a cost-based optimizer, and therefore the quality of the execution plans it generates is directly related to the accuracy of its cost
estimations.
Statistics contain three major pieces of information: the histogram, the density information, and the string statistics, all of which help with different parts of the cardinality estimation process.
A cardinality estimate is the estimated number of records that will be returned by filtering, JOIN predicates or GROUP BY operations. Selectivity is a concept similar to cardinality estimation, which can be described as the percentage of rows from an input that satisfy a predicate.
Statistics are created in several ways: automatically by the Query Optimizer (if the default option to automatically create statistics, AUTO_CREATE_STATISTICS, is on); when an index is created; or when they are explicitly created, for example, by using the CREATE STATISTICS statement. Statistics can be created on one or more columns, and both the index and explicit creation methods support single- and multi-column statistics.
However, the statistics which are automatically generated by the Query Optimizer are always single-column statistics.
Both histograms and string statistics are created only for the first
column of a statistics object, the latter only if the column is of a string data type.
Density information is calculated for each set of columns forming a prefix in the statistics object.
The Query Optimizer always uses a sample of the target table when it creates or updates statistics, and the minimum sample size is 8 MB, or the size of the table if it's smaller than 8 MB. The sample size will increase for bigger tables, but it may still only be a small percentage of the table.
String statistics contain the data distribution for string columns, and can help to estimate the cardinality of queries with LIKE conditions.
Density information can be used to improve the Query Optimizer's estimates for GROUP BY operations.
GROUP BY queries can benefit from the estimated number of distinct values, and this information is already available in the density value.
In SQL Server, histograms are created only for the first column of a statistics object, and they compress the information of the distribution of values in those columns by partitioning that information into subsets called buckets or steps. The maximum number of steps in a histogram is 200, but even if the input has 200 or more unique values, a histogram may still have less than 200 steps.
The purpose of the Query Optimizer, as we're all aware, is to provide an optimum
execution plan and, in order to do so, it generates possible alternative execution plans through the use of transformation rules. These alternative plans are stored for the duration of the optimization process in a structure called the memo.