Lesson 2: Optimizing Index Strategies in Visual Studio .NET

Printing UPC-A Supplement 5 in Visual Studio .NET Lesson 2: Optimizing Index Strategies

Table 7-8

SELECT o.PKCol, i.Col2 FROM Test.OtherTable AS o INNER JOIN Test.IncludedColumnsTest AS i WITH(INDEX(IncludedColumns)) ON o.Col1 = i.Col1 WHERE o.PKCol BETWEEN 1 AND 10000; SELECT o.PKCol, i.Col2 FROM Test.OtherTable AS o INNER JOIN Test.IncludedColumnsTest AS i WITH(INDEX(NoIncludedColumns)) ON o.Col1 = i.Col1 WHERE o.PKCol BETWEEN 1 AND 10000; SELECT o.PKCol, i.Col2 FROM Test.OtherTable AS o INNER JOIN Test.IncludedColumnsTest AS i WITH(INDEX(NotCovered)) ON o.Col1 = i.Col1 WHERE o.PKCol BETWEEN 1 AND 10000;

Query # Query #1 Index: IncludedColumns The execution plan is shown in Figure 7-5.

Query #2 Index: NoIncludedColumns The execution plan is shown in Figure 7-5.

Query #3 Index: NotCovered The execution plan is shown in Figure 7-5.

7

Query #1, with the IncludedColumns index, is the best-performing query with 32,726 page reads. Query #2, with the NoIncludedColumns index, used 53,994 page reads. As you can see, the difference in the number of page reads between the two indexes is roughly the same as the difference in index levels (40 percent). Query #3, with the NotCovered index, is the worst-performing query with 62,617 page reads because of the extra reads necessary to fetch the data that was not found in the index from the table. (Note the extra Nested Loops Join in the execution plan of Query #3.)

Because a clustered index is the actual table reading from the clustered index, it never results in lookups. Therefore, the clustered index should generally be defined on columns that are often queried and typically return a lot of data. This is because the problem of lookups and fetching a large number of rows doesn t exist. Two good candidates for the clustered index are either the most frequently queried foreign key column of the table (a search on a foreign key

typically returns many rows) or the most frequently searched date column. (Date searches generally return a large number of rows as well.) Another important consideration when selecting the column or columns on which to create the clustered index is that the key size of the clustered index should be as small as possible. If a clustered index exists on a table, all non-clustered indexes on that table will use the key of the clustered index as the row pointer from the non-clustered index to the table. If a clustered index does not exist, the Row Identifier is used, which takes up eight bytes of storage in each row of each non-clustered index. This can significantly increase size for larger tables. Consider the following:

You have a table with 40,000,000 rows. The table has five non-clustered indexes. The clustered index key is 60 bytes wide. (This is not uncommon when you have clustered indexes that span a few columns.)

The total size of all row pointers from the non-clustered indexes on this table (only the pointers) would be: 40,000,000 * 5 * 60 = 12,000,000,000 bytes (close to 12 gigabytes) If the clustered index was changed to be on only one column with a smaller data type, such as an integer for a foreign key, each row pointer would be only four bytes. Because four bytes is added to all duplicates of the clustered index key to keep it unique internally, the calculation uses eight bytes as the new clustered index key size. 40,000,000 * 5 * 8 = 1,600,000,000 bytes (close to 1.5 gigabytes) The difference in storage needed is more than 10 gigabytes. Exam Tip

For the exam, it s important to know the difference in non-clustered indexes depending on whether a clustered index exists on the table.

The addition of indexes typically only helps boost read performance. Write performance is typically degraded because the indexes must be kept up-to-date with the data in the table. If a table has five non-clustered indexes defined on it, an insert into that table is really six inserts: one for the table and one for each index. The same goes for deletes. With update statements, only indexes that contain the columns that are updated by the update statement must be touched.