Tuning Indexes - Professional SQL Server 2005 Performance Tuning - page 355

❑ usp_deathDelete: Deletes a boy and a girl record from the people table.

❑ usp_loopDeathDelete: Runs usp_deathDelete 1000 times. This default can be overriden by

passing a value to the @people parameter.

Section 1: Indexing Review
Indexes are used to provide fast access to data and are very often the first area to look at when tuning a
system. This is because the performance gains for well-designed and maintained indexes can be consid-
erable and they can be implemented relatively quickly. Adding, removing, or rebuilding an index can be
a quick win for you in terms of performance so it is definitely worth your time to learn more about what
they are and what indexing strategies SQL Server professionals employ.

There are two basic types of indexes: clustered and nonclustered. A clustered index defines the
physical order that the data in a table is stored. You can only have one per table and it can be compared
to the contents page at the beginning of this topic. For example, to get to this chapter you might have
read the contents page to get the page number and come straight here, or you might just have flicked
through all the pages until you got here. In SQL Server terms these relate to a clustered-index seek and a
clustered-index scan, respectively.

A non-clustered index does not affect the physical ordering of the data and can be compared to the index at
the back of this topic. If you wanted to know which page discussed ''Fill Factor,'' for example, you could
look at the index, get the exact page number, and go straight there. You can have up to 249 non-clustered
indexes on single table but it's difficult to imagine a scenario where that would be a better choice than
normalizing the table. See Chapter 8 for details on normalization.

B-Trees
SQL Server implements indexes as balanced trees, or b-trees for short. The idea behind a balanced tree is
to ensure that it costs the same to read any data page in the tree. It consists of a single root page, multiple
intermediate-level pages, and leaf-level pages. The intermediate-level (or non-leaf) pages are split and
new levels created when new data is added to ensure that every page requires the same number of reads
to find. Figure 11-1 shows an example of a b-tree with three levels.

Each level grows in width as large as the pages at the next level up can keep track of and a new level is
created when the existing tree can't track any more pages. The size of each index record is affected by the
size of the indexed column, so the narrower the indexed column the more you can fit on a page and
the fewer levels you'll have in your index. Each level takes 1 logical read (index trees will usually be
cached) so the shallower your index tree the better. You can use the following code to view the depth of
all the user-created indexes in the current database:

SELECT object_name(i.object_id) AS 'table name',
i.[name] AS 'index name',
indexproperty(i.object_id, i.[name], 'indexdepth') as 'indexdepth'
FROM sys.objects o (nolock),
sys.indexes i (nolock)
WHERE o.object_id = i.object_id
AND index_id between 1 and 250
AND o.type = 'u'