Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 232

80,000 locks (page level). That's almost a one-to-one ratio of rows to locks. In fact, row-level locking in
this example wouldn't have performed that much worse than page-level.

So, a colleague and I redesigned the database using the principles described earlier. We normalized
where we could, and most importantly we used the right data types for the columns. The end result was
a database that was about 30 GB in size. Also the 6-hour load process was reduced to about 45 minutes.

Indexing
Poorly designed indexes or missing indexes can be a major contributor to bottlenecks. There are several
factors that go into designing proper indexes. However, the task is generalized by the need to balance fast
executing queries with fast updates. This section covers some general techniques for designing indexes.
It also describes the different types of indexes and provides some examples.

GeneralStructure
An index is a B-tree structure. In general it looks something like Figure 8-21.

Root
Node

Root Level

Leaf Level(s) node node

Non-leaf
level

node node node

Figure 8-21

The top level is known as the root level. The intermediate levels are known as the leaf levels. The bottom
level is known as the non-leaf level.

GeneralTechniques
Tables generally fall into two categories. One is a table that experiences a great deal of data changing (or
writing). The second type of table is one that is primarily used to read data from.

For write-oriented tables, indexes must be used carefully. Every time an insert, update, or delete occurs
on a table all the indexes associated with that table will need to be updated as well. As you can imagine,
a write-oriented table that is laden with indexes may well perform poorly.

For read-oriented tables with large amounts of data, the opposite is true. Copious use of indexes is usually
beneficial. Since this table doesn't experience high amounts of changes, it can have more indexes.

Another issue with indexes is table size. For small tables, say a few hundred rows or less, indexes are
unnecessary. In fact they can actually impede performance. Traversing an index on a table with few rows
can take longer than simply allowing SQL Server to perform a table scan on the table. Also, as always,
the indexes have to be maintained whenever an insert, update, or delete occurs.