Database Reference
In-Depth Information
Figure 13-26.
FragmentationOutput.txt file output
The output shows that the job analyzed the fragmentation of the database and identified a series of indexes
for defragmentation, specifically for reorganization. Subsequently, it defragments the index. The stored procedure
defragmented only the database object that was highly fragmented. Thus, the next run of the SQL job generally won't
identify these same indexes for defragmentation.
In addition to this script, Michelle's script, or Ola's scripts, you can use the maintenance plans built into
SQL Server. However, I don't recommend them because you surrender a lot of control for a little bit of ease of use.
You'll be much happier with the results you get from one of the sets of scripts recommended earlier.
Summary
As you learned in this chapter, in a highly transactional database, page splits caused by
INSERT
and
UPDATE
statements
fragment the tables and indexes, increasing the cost of data retrieval. You can avoid these page splits by maintaining
free spaces within the pages using the fill factor. Since the fill factor is applied only during index creation, you should
reapply it at regular intervals to maintain its effectiveness. You can determine the amount of fragmentation in an index
(or a table) using
sys.dm_db_index_physical_stats
. Upon determining a high amount of fragmentation, you can use
either
ALTER INDEX REBUILD
or
ALTER INDEX REORGANIZE
, depending on the required amount of defragmentation
and database concurrency.
Defragmentation rearranges the data so that its physical order on the disk matches its logical order in the
table/index, thus improving the performance of queries. However, unless the optimizer decides upon an effective
execution plan for the query, query performance even after defragmentation can remain poor. Therefore, it is
important to have the optimizer use efficient techniques to generate cost-effective execution plans.
In the next chapter, I explain execution plan generation and the techniques the optimizer uses to decide upon an
effective execution plan.
