Data Fragmentation in SQL Server 2000

Although SQL Server is a database management system, it will not do everything for you automatically. In many cases, even the most basic administration tasks are neglected by the average user & can result in a decrease in performance. For the purposes of this article we will take a beginners look into maintaining indexes on SQL Server 2000.

To start with, you have the ability to produce a fragmentation report for the data and indexes of the specified table with in SQL Server. Run DBCC SHOWCONTIG using SQL Query Analyser. This command returns a report for either the entire database or for specified tables. It is especially useful to run this if your database has suffered a gradual decrease in performance over periods of time. A gradual decrease will indicate index fragmentation. By scanning through this report you will be able to determine the primary tables that have been affected. Two important areas of this report to look at are:

1. Extents scanned & Extent Switches – in an ideal world you should have one less switch than extents

2. Extent scan fragmentation levels

Once you have diagnosed to problem tables/indexes, you can run DBCC DBREINDEX. This command rebuilds one or more indexes for a single table. Generally, your best bet is to rebuild all indexes on a given table or rebuild the primary index (rebuilding the primary one will automatically rebuild all others). This command can only be run on a table by table basis – i.e. it can not be run on the entire database. In addition to this, the DBCC DBREINDEX command should be run as an offline operation i.e. single user mode. The following example rebuilds all indexes on the PRODUCTS table using the default fillfactor they were originally created with:

DBCC DBREINDEX (PRODUCTS, '', 0)

For more in-depth information about these DBCC commands, including parameter options, please refer to resources such as SQL Books Online.

Oliver Cox

Comments

[Sam Gompels]

Great to have some meaty technical tips on here. Keep them coming, we need some reminders of what needs looking after.

[Jeff Maultby]

Another way to do this but to all tables, resolve space fragementation at same time and force an update to db stats is:

sqlmaint -S LC21535 -U sa -P sa -D AccessJeff -RebldIdx 25 -UpdOptiStats 25

where LC21535 is Server name
sa is user name
sa is password
AccessJeff is database name
-RebldIdx is command to do indexes rebuild
25 is free space to allow for.
-UpdOptiStats is command to update stats
25 is sample size

[Sam Gompels]

Jeff's way looks to be better, rather than table by table - could Access confirm (maybe Jeff is Access!) that using these settings we could do the whole database at once and achieve the same result, is 25 correct for all tables ?.....or could it be added as a facility in data utilities for those of us who are a bit less technical.

[Oliver Cox]

Jeff's method is a more global approach to the topic and will certainly provide you with a quick & easy way to reindex your entire database using, what appears to be, a single command. It all depends on whether you have the time or the need to reindex everything or not.

Another way to do it would be to use the undocumented sp_MSforeachtable stored procedure which, as its name suggests, allows you to perform tasks across all tables in the database e.g.

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', '', 0)"

Remember though, that the DBCC REINDEX command should be performed as an off-line operation and the more indexes you rebuild, the longer it will take.

The figure of 25% relates to the fill factor of data pages. To get the correct setting for each table/index is fairly tricky but what we are aiming for is optimum performance for both reads & writes. Getting an incorrect fill factor can degrade performance. It all depends on ratios of data reads & writes, what type of table it is i.e. lookup or transactional, database growth etc & also how often reindexing can be performed. With this in mind, setting a global fill factor of 75% (i.e. 25% free space) may not be the best option. In the T-SQL examples I have given, I have simply used a fill factor or 0 which points SQL Server to use default values & basically make its own mind up in terms of free space per data page. This does seem to be the recommended approach to follow and works well for us, especially as we have the ability to run reindexing routines on a weekly basis.