November 2, 2009 at 7:58 am
All
I have a db with 450 GB in size, In this db I have 13 tables where application will perform more DML operations.
SQL Server Version : SQL Server 2000 with SP4
OS : Windows 2000 Advanced Server
SAN Storage.
Monthly twice I have rebuild index task on only these 13 tables, these individual table size vary from 25 MB to 150 GB.
There was a job which Rebuild the tables by using the below script
-----------------------------------------------------
dbcc dbreindex(Table1,'',0,sorted_data_reorg)
go
sp_recompile tblremark
.
.
.
.
dbcc dbreindex(Table1,'',0,sorted_data_reorg)
go
sp_recompile tblremark
-----------------------------------------------------
The above script was taking 10 to 12 hours
After the reindex job ran we have the below issues.
My database data files (.mdf and .ndf) files used to grow huge and our disks filled up and had to shrink the data files.
This space issue come on alternative monday.
When we use DBCC DBREINDEX, behind the screens SQL Server will create a new index and drops the old one that's why we need
more space and data files will grow.
To avoid data files growth while reindexing and shrinking I writen the script as follows
Run DBCC SHOWCONTIG on 13 tables and get the results to temp table then
If logical fragmentation is > 40 go for CREATE INDEX WITH DROP EXISTING.
If logical fragmentation is between 20 to 40 go for DBCC INDEXDEFRAG
After implementing new script as above, I am facing the below issues
Data files did't grow that much but job has taken inacceptable time that is 35 hours to complete.
DBCC INDEXDEFRAG has taken more time for huge table ( I know INDEXDEFRAG has will take more time for huge tables)
Here I have the below questions
1) Old script (that is DBCC DBREINDEX for all 13 tables) was able to compelte in 12 hours and when I implement the new script
with CREATE INDEX WITH DROP EXISTING has taken huge time, Why it is taking that much time
Note:I know DBCC INDEXDEFRAG will take more time compare to DBREINDEX but script in this case I found only one index to
defrag that has taken 10 hrs what about the rest 25 hours what might be the reason tt has taken that much time?
2) Will CREATE INDEX WITH DROP EXISTING will take more time than DBCC DBREINDEX?
3) Upto what level of logical fragmentation we can ignore, What is the best values to go for DBCC DBREINDEX and DBCC
INDEXDEFRAG.
Example: After running DBCC SHOWCONTIG if the logical Fragmentation is <30 can we leave it like that or if the logical
fragmentation is >40 we should go for drop and recreat like that.
Pleaase share your ideas to reduce the job execution time and avoid data file growth and space issues.
Thanks in advance.
Rajesh Kasturi
November 7, 2009 at 4:08 am
can any one reply on this question.
Rajesh Kasturi
November 9, 2009 at 7:57 am
Do include a check wherein only indexes with more than 1000 pages are considered for index defrag or reindex.
MJ
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply