 |
|
SQL Server Tips by Robin Schumacher
|
Extent Proximity
Like many other database engines, SQL Server has a prefetch
mechanism called the Read Ahead manager that will pull data into the
memory caches it believes will be needed for operations such as full
table scans. Having the data already present in memory ensures that
response times are the shortest possible, so the Read Ahead
capabilities are indeed good to have. However, the Read Ahead
manager’s ability to perform as efficiently as possible is somewhat
dependent on the organization of the data that it prefetches.
If the data is contiguous in nature, the Read Ahead manager can work
very well and read large chunks of data at a time. If the data is
scattered and mixed throughout extents that contain other objects,
the Read Ahead manager cannot move in one fluid direction and
instead must skip around the file(s) to obtain the data it believes
will be needed.
As a result, it is desirable to have the object data organized as
contiguously as possible. The question is: how can one tell if this
is the case? Viewing the extent fragmentation scores for each object
is one way, but another way is to view object fragment placements
throughout the database. This is best viewed through a third party
tool that can visually present the extents in a way that allows one
to easily diagnose extent proximity issues, but nonetheless, the
up_bn_storage_map procedure below is needed to get the raw
diagnostic data:
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
