Index rebuilds - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Index rebuilds

Author

Topic

JamesAx
Starting Member

4 Posts

Posted - 2010-01-29 : 12:23:41

Am after some advice on an issue we have been experiencing with a customer.
They have a 500Gb db which has some really badly fragmented indexes (some 100%). To identify levels of fragmentation I have been using the following:

SELECT o.name, d.index_id, index_level, index_depth, avg_fragment_size_in_pages,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent, page_count, record_count, avg_record_size_in_bytes,
max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats (10,
NULL, NULL, NULL , 'DETAILED') d
join sys.indexes o on o.object_id = d.object_id
and o.index_id = d.index_id

My plan was to rebuild all indexes on a table where the avg_fragmentation_in_percent > than say 30%

The rebuild would be done using:

DBCC DBREINDEX("db.tablename", "indexname",100) WITH NO_INFOMSGS
GO

Does this look like a sensible way of going about this or can someone point me at a beter way of doing it?

The reason I ask is a similar task was done fairly recently and it was observed that after the DBREINDEX was done a large number of the indexes did not decrease in avg_fragmentation_in_percent, so maybe I am not understanding something correctly about the process.

Thanks

JamesAx
Starting Member

4 Posts

Posted - 2010-01-29 : 12:26:34

Oops, I am using SQL Server 2005 sorry.

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 13:50:05

We use (or more correctly "used", under SQL 2000, but still valid under SQL 2005 I think)


DBCC SHOWCONTIG () WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS

to get the fragmentation.

Then we use either

DBCC DBREINDEX ('dbo.MyTableName', 'indexName') WITH NO_INFOMSGS

(we don't appear to use a third parameter)

or

DBCC INDEXDEFRAG ('dbo.MyTableName', 'indexName') WITH NO_INFOMSGS

We use REINDEX on small tables, and DEFRAG on large tables.

My understanding (but based on SQL 2000, so might be better under SQL 2005) is that REINDEX creates a new "index" (i.e. in available free space within the MDF file) and copies all the entries across, then releases the original index.

My feeling is that this may Extend the DB, and if someone does a SHRINK, as a consequence, with "move pages to front of file" that will wreck the carefully de-fragmented pages!

We do this if ScanDensity < 90

For very small tables there will still be apparent fragmentation.

One problem you will have on a large database is that a) REINDEX will make the table unavailable and b) It will take quite a time! so you may want to build in rebuilding tables A-F one night, G-N the next night, etc.

"it was observed that after the DBREINDEX was done a large number of the indexes did not decrease in avg_fragmentation_in_percent"

Doesn't sound right (unless very small tables), perhaps something else was amiss? You sure those indexes were actually rebuilt?

You should perhaps have a read up on ALTER INDEX for the new commands available in SQL 2005, and if you have Enterprise Version the ONLINE=ON option that allows you to make an REINDEX without the index becoming unavailable.

If you use DEFRAG you will need to UPDATE STATISTICS too (REINDEX does that). Note that REINDEX does not update other statistics, so you kinda need to Update Stats anyway. Consider using WITH FULLSCAN rather than using Sample - which I have found doesn't give accurate enough Query Plans

JamesAx
Starting Member

4 Posts

Posted - 2010-02-01 : 10:13:29

Nice answer thanks, lots to digest so will try some of this out.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-01 : 10:55:39

Tara's alter index is probably a drop in for what you need.

DBREINDEX et al. have been superseded in 2005 (they locked tables) ALTER INDEX no longer needs to do this (for most indices) because it can use snapshot isolation.

Here's the link.

http://weblogs.sqlteam.com/tarad/archive/2007/04/17/60176.aspx

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources