Monitoring SQL Server Resources with System Monitor - Professional SQL Server 2005 Performance Tuning - page 54

faster storage and longer-term slower storage. In situations where you're seeing performance degrade
and you have one very large table, you could consider partitioning as a method to spread I/O across
multiple partitions.
Index/TableFragmentation
Index fragmentation occurs over time as data is added, changed, and deleted from a table. It's part of
everyday life as a database and is unavoidable. When fragmentation is severe, it will impact performance
becauseinsertsrequirepagesplitswhichareI/Ointensive and reads become ineffective because there is
little valuable data held in each 8 KB data page retrieved from disk.
Fragmentation occurs in two forms; internal and external. Internal fragmentation refers to 8 KB data
pages that have lots of free space (meaning they don't contain much data). External fragmentation
refers to discontiguous data pages, which is to say that the data pages are not side by side on the
disk. This results in increased movement in the disk head, which is slow. The DMV sys.dm_db_index_
physical_stats will provide all the information you'll need about current fragmentation levels.
Indexes can only be rebuilt on tables with clustered indexes. Fragmentation can't be removed from heaps.
Non-clustered indexes can be rebuilt at the same time or separately. The best way to remedy index
fragmentation is with an ALTER INDEX command. This should be appended with the fully qualified index
and schema names and then either REBUILD or REORGANIZE . The reorganize operation is more lightweight,
but it can't resolve inter-leaved data and index pages (external fragmentation). An index rebuild is an
I/O intensive operation that will resolve both internal and external fragmentation and can be carried out
online with Enterprise or Developer editions of SQL Server 2005. Other editions allow the same result
but require the table to be taken offline during the index maintenance operation.
Proactive and regular index reorganization will maintain indexes in a good state and should reduce
the frequency with which index rebuilds are required. It's possible to put together a script which will
interrogate the DMV mentioned previously and take the necessary corrective action — either a reor-
ganize or a full index rebuild based on fragmentation levels identified by the DMV. This approach,
together with some sort of audit trail for the activity history, is best practice and will mean you no longer
have to worry about index or table fragmentation. Books Online for SQL Server 2006 contains a sample
script to rebuild or reorganize indexes based on fragmentation levels. This may provide a useful
starting point to customize for your environment.
Best Practice
Index maintenance is essential for continued SQL Server performance. Fragmentation
occurs through everyday inserts, updates, and deletes. Use the DMVs and index main-
tenance operations mentioned above to ensure your indexes are properly maintained.
Monitoring Database Mirroring Performance
There are a number of counters which can be revealing when investigating performance problems with
database mirroring. You can measure the performance of each database as well as mirroring within the
entire server. When investigating performance problems with mirroring, you'll probably be interested in