Database Reference
In-Depth Information
If you run this query against the table as it is currently configured, you'll see an execution plan that looks
like Figure 9-14 .
Figure 9-14. A clustered index scan and hash match aggregate for a GROUP BY query
The reads and execution time for the query are as follows:
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0,
Table 'TransactionHistoryArchive'. Scan count 1, logical reads 628
CPU time = 16 ms, elapsed time = 126 ms.
There are a large number of reads, and this query uses quite a bit of CPU and is not terribly fast to execute. We
have two types of columnstore indexes to choose from. If you want to just add a nonclustered columnstore index to an
existing table, it's possible.
CREATE NONCLUSTERED COLUMNSTORE INDEX ix_csTest
ON Production.TransactionHistoryArchive
(ProductID,
Quantity,
ActualCost);
With the nonclustered columnstore index in place, the optimizer now has the option of using that index to satisfy
the foregoing query. Just like all other indexes available to the optimizer, costs are associated with the columnstore
index, so it may or may not be chosen to satisfy the requirements for any given query against the table. In this case,
if you rerun the original aggregate query, you can see that the optimizer determined that the costs associated with
using the columnstore index were beneficial to the query. The execution plan now looks like Figure 9-15 .
Figure 9-15. The columnstore index is used instead of the clustered index
As you can see, the basic operations of the query are the same, but the columnstore index is scanned instead of
the clustered index. The real differences are seen in the reads and execution times for the query.
Table 'Worktable'. Scan count 0, logical reads 0
Table 'TransactionHistoryArchive'. Scan count 1, logical reads 48
CPU time = 0 ms, elapsed time = 122 ms.
Search WWH ::




Custom Search