January 21, 2003 at 5:52 am
I'm in discussion with one of our DB's app suppliers who wants us to perform
an index rebuild on a very large table (82mill rows). At the same time he suggests
we alter the index (which is the PK) from a clustered to non-clustered (which I agree
with based on the tables use). However he says that deleting and rebuilding the
new non clustered index wont actually defrag the index/table that much as the data
is currently ordered. He suggests that we create a new (empty) table as well as build
a new index then copy all the data from the old to the new as this should restructure
both the index and the actual table optimally.
Can anyone give me some indication as to if and why this is a better solution...
Thank-you in anticipation !
January 21, 2003 at 6:28 am
Have you checked dbcc showcontig to check if defragmenting is neccessary, and if so considered using dbcc indexdefrag
January 21, 2003 at 7:40 am
Thanks for the response. Yes showcontig was what I used to spot the fragmentation. Unfortunately this is a SQL7 DB so we cant use indexdefrag. Plus that still doesnt really answer whether I should be copying out the table as well as rebuilding the index (incl clustered to non-clustered)
January 21, 2003 at 8:56 am
This is my weekly routine for cleanup on tables with around 5-10 million records. Do in off peak so no one sees it happen (don't know if you have an off-peak).
1) Rebuild All Indexes in Database
Using
-------------Code-------------
CREATE PROCEDURE sp_RebuildIndexes
@DBName as varchar(500)
AS
DECLARE @SQLState varchar(5000)
SET @SQLState = '
DECLARE TableCursor CURSOR FOR SELECT ''' + @DBName + '.'' + TABLE_SCHEMA + ''.'' + table_name FROM [' + @DBName + '].information_schema.tables WHERE table_type = ''base table''
DECLARE @TableName varchar(255)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT ''Reindexing '' + @TableName as DOING
DBCC DBREINDEX(@TableName,'' '',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor'
EXEC (@SQLState)
-------------Code-------------
2) Update stats information
Using dbname..sp_updatestats
3) Update Datebase Usage to keep running smooth
Using DBCC UPDATEUSAGE (DbName)
As for changing clustered to non-clustered keep in mind when you do this a rebuild of all other indexes will occurr so no need to do that yourself. Also, if the Clustered index is on a field such as an IDENTITY or other type of field that will cause the data to insert toward the end of the index then do not bother removing the clustered index. Keep in mind that removing the clustered index will leave the table to insert in heap and physical storage size is larger. Also, there is no logical order to the data when not clustered. My personal opinion keep a clustered index, pick a column to cluster on that is as unique and inserts will be at end as much as possible. If all your records can insert into the table in various places from begining to end then drop your clustered index.
January 21, 2003 at 9:38 am
Thank-you for that info and script. I shall run that by the apps provider and discuss accordingly. Thanks again, much appreciated.
January 21, 2003 at 4:04 pm
But Antares, by rebuilding the indexes actually you are rebuilding the stats as well. So the update stat should not really necessairly take place within your maintenance job.
This is what MS told me.
(BTW I'm doing exactly the same tasks)
Simonlettes:
You should have a clustered index on your table. Not necessairly on you IDENTITY column but on that where you are selecting the most often by LIKE, BETWEEN, >= etc.
Actually you can only defragment a table which has a clustered index.
Just take care when rebuilding the index on such a big table, you could run out of logspace.
When I'm usually doing my maintenance tasks on my VLDB's I am running them in batches and backing up the log in between.
Bye
Gabor
January 21, 2003 at 4:10 pm
quote:
But Antares, by rebuilding the indexes actually you are rebuilding the stats as well. So the update stat should not really necessairly take place within your maintenance job.This is what MS told me.
(BTW I'm doing exactly the same tasks)
Simonlettes:
You should have a clustered index on your table. Not necessairly on you IDENTITY column but on that where you are selecting the most often by LIKE, BETWEEN, >= etc.
Actually you can only defragment a table which has a clustered index.
Just take care when rebuilding the index on such a big table, you could run out of logspace.
When I'm usually doing my maintenance tasks on my VLDB's I am running them in batches and backing up the log in between.
I agree but I like to be safe myself and sometimes that is run without the rebuilding indexes.
Also, yes sorry should have made the note on freespace.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply