Database Reference
In-Depth Information
Note
Indexes are an important tool in a DBA or developer's toolbox for
improving overall database performance. It's important that periodic
maintenance routines be put in place to keep those indexes operating
optimally.
Database Indexes and Memory
Earlier in this chapter, we talked about database pages. For the purposes of this
example, I am going to simplify things by not taking into account the overhead
associated with a table or index within the SQL Server database.
Let's imagine you have a table within SQL Server with 10 columns, and each column is
defined as char (100). As you can see in
Figure 7.2
, each column uses 100 bytes, which
in turn means each row of data in that table requires 1,000 bytes. A 1KB page contains
1,024 bytes, and an 8KB page contains 8,024 bytes. Each page in the database buffer
pool will contain up to a maximum of eight rows of that table. One hundred pages of that
same table within the database buffer pool will contain up to a maximum of 800 rows of
data.
Figure 7.2
A table filling an 8KB page, and an index based on the first two columns
filling the 8K page.
Think of an index as a subset of the table itself. If you create an index on the first two
columns of the table used in the example (and assuming no compression), then each row
of the index would use 200 bytes. As you can see in
Figure 7.2
,
each 8KB page within
the database buffer pool would contain up to 40 index rows. One hundred pages would
contain up to a maximum of 4,000 rows of data.
