Reindexing Tables

Question

Reindexing Tables

Chris Poole

Mr or Mrs. 500

Points: 555
More actions
May 17, 2004 at 12:09 pm

#161335

I have a SQL 2000 Server on Win 2k all fully service packed.
Here is my problem. I have a database that is 37 GB in size and one table in it, according to the GUI and scripts that I have run against it, is 41 GB. Aside from this impossibility, the mdf file is located on a separate drive ( with other databases) and has approiximately 13 GB free space. When I try to reindex this database it runs out of space trying to reindex this one table. There are two indexs on the table that together total about 4 GB of space.
What space does SQL use to rebuild the indexs. The Tempdb is located on a separate drive with 30 GB of free space. It appears that it is using the data drive to rebuild the index, and if so how to I start to try to tell my boss how much space to add to cover index rebuilds.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

How are you attempting to do the reindex?

There are two methods normally used. First is to drop all the indexes and then recreate them (DBCC DBREINDEX), the second is to do a defrag (DBCC INDEXDEFRAG).

The second method is easier (in my opinion) as the system can be online and in use when it is running.

I do the defrag version. In this method you do one index at a time. The index is defragged using the data drives. Then the free space is returned to the OS (DBCC SHRINKDATABASE) and this uses the TEMPDB drives.

-SQLBill

Terry L Broadbent Valued Member Points: 67 More actions · Answer 2

Try running sp_spaceused with the updateusage parameter set to TRUE. It may run for along time but should correct the size inconsistencies you see.

EXEC sp_spaceused @objname = 'TableName', @updateusage = 'TRUE'

TL Broadbent

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 3

1. Fix up your incorrect space usage reporting with:

DBCC UPDATEUSAGE ('MyDatabase')

2. Specify the WITH SORT_IN_TEMPDB option in your CREATE INDEX statement. This tells SQL Server to use your TempDB to while building the index, which if you have 30GB free space there is probably a good idea, especially if your TempDB drive is a separate set of disks to your target database.

for example:

CREATE CLUSTERED INDEX [IX_MyTable_CustomerOrder] WITH DROP_EXISTING, SORT_IN_TEMPDB ON dbo.MyTable(Customer,Order)

btw, DROP_EXISTING will make reindexing faster for a clustered index on a table that also has non-clustered indexes. SQL Server will not rebuild the non-clustered indexes, and will also no resort the data. BOL states:

DROP_EXISTING

Specifies that the named, preexisting clustered or nonclustered index should be dropped and rebuilt. The index name specified must be the same as a currently existing index. Because nonclustered indexes contain the clustering keys, the nonclustered indexes must be rebuilt when a clustered index is dropped. If a clustered index is recreated, the nonclustered indexes must be rebuilt to take the new set of keys into account.

The DROP_EXISTING clause enhances performance when re-creating a clustered index (with either the same or a different set of keys) on a table that also has nonclustered indexes. The DROP_EXISTING clause replaces the execution of a DROP INDEX statement on the old clustered index followed by the execution of a CREATE INDEX statement for the new clustered index. The nonclustered indexes are rebuilt once, and only if the keys are different.

If the keys do not change (the same index name and columns as the original index are provided), the DROP_EXISTING clause does not sort the data again.

Julian Kuiters
juliankuiters.id.au

John Matear Mr or Mrs. 500 Points: 515 More actions · Answer 4

I'm guessing that you are using DBREINDEX to reorg your table. I've had similar problems when trying to reorg a 15GB clustered table. The datafile had to be sized to about 30GB before the DBREINDEX would work. DBREINDEX needs enough space in order to recreate the clustered and non clustered indexes. At the end of the DBRINDEX process I ended up with about 15GB of freespace.

Don't be tempted to SHRINK it to try and reclaim the space because this undoes (fragments) a lot of what the reorg did in the first place. SQL Server just loves a lot of space.

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 5

If you really want to run DBREINDEX and keep transaction logging to a minimum switch to bulk log mode first. It does save you quite a bit of space (30% + I have found).

In addition you need to look at your indexing strategy more carefully. Set a realistic fill factor for high transaction tables and think about reindexing each table just once a week. Try dividing your tables into 7 groups and reindex one group per day.

By my calculation that will use up 3 gig per day (30 gig/100*70)/7, assuming the groups are of equal measure.

------------------------------
The Users are always right - when I'm not wrong!

Chris Poole Mr or Mrs. 500 Points: 555 More actions · Answer 6

Thanks everyone. I think the Create/Drop_ Existing and Sort_in_Temdb will work out best for this situation. We use an EMC data storage system and Tempdb is on a different group of spindles with replication turned off.

Also thanks for the update usage for the db size. Reminds me of the way 6.5 would always lie about the amount of space.