Query Processing and Execution - SQL Server 2012 Internals and Troubleshooting - page 137

Starting at the bottom right, you can see that the i rst operator is the clustered index scan operator.
While the query doesn't need, or get any benei t from, a clustered index, because the table has a
clustered index and is not a heap, this is the option that SQL Server chooses to read through all the
rows in the table. If you had removed the clustered index, so that this table was a heap, then this
operator would be replaced by a table scan operator. The action performed by both operators in
this case is identical, which is to read every row from the table and deliver them to the next operator.
The next operator is the hash match. In this case, SQL Server is using this to sort the rows into
buckets by i rst name. After the hash match is the compute scalar, whereby SQL Server counts the
number of rows in each hash bucket, which gives you the count (*) value in the results. This is fol-
lowed by the sort operator, which is there to provide the ordered output needed from the T-SQL.
You can i nd additional information on each operation by hovering over the operator. Figure 5-13
shows the additional information available on the non-clustered index scan operator.
FIGURE 5-13
While this query seems pretty trivial, and you may have assumed it would generate a trivial plan
because of the grouping and ordering, this is not a trivial plan. You can tell this by monitoring the
results of the following query before and after running it:
select *
from sys.dm_exec_query_optimizer_info
where counter in (
'optimizations'