Implementing Indexes - Microsoft SQL Server 2012 Performance Tuning - page 205

3. Now, it is time to compare both the execution plans, which will generate results in a

screenshot. By comparing I/O Cost, CPU Cost, Operator Cost, and other parameters,
a big difference will be seen in the cost figures, and that will prove that the second
execution plan, which has been generated after creating a non-clustered index, is
much better.

How it works...

In our case, all the data resides in a clustered index. So, any query you execute will get a
clustered index scan, if it doesn't fall under the clustered or non-clustered index seek. The first
query, which was run before the non-clustered index was created, had an OrderDate ield
in the WHERE clause, but there was no index for OrderDate , and hence SQL Server Query
Optimizer decided to scan the complete clustered index.

After creating a non-clustered index on the OrderDate field, SQL Server Query Optimizer
finds the row locator of the clustered index data page (leaf node) from the leaf node of the
non-clustered index. This is because the non-clustered index's leaf node would have the
OrderDate entry with a row locator to the clustered index's leaf node, where the actual data
resides. So now, Optimizer doesn't need to scan all the leaf pages of the clustered index and
only needs to perform the index seek operation on the non-clustered index, which reduces
I/O, CPU, and other costs.

Here is the cost comparison before the non-clustered index's execution plan in (Step 1) and
after the non-clustered index's execution plan in (step 2):

Operator Cost before non-clustered index Cost after non-clustered index

Estimated I/O Cost 0.379421 0.0073226

Estimated Operator Cost 0.489578 (100%) 0.00975 (100%)

Estimated CPU cost 0.110157 0.0024274

Estimated Subtree Cost 0.489578 0.00975

There's more...

While creating a non-clustered index, keep in mind that it will need additional space to store
itself, along with the key column and row locator of the clustered index or heap. So, keep an
eye on the hard drive for space, as a bigger table would need more space to accommodate
the non-clustered index. We can define the non-clustered index on a separate database file
or filegroup, which reduces the I/O hit on the same file.