CREATE INDEX WITH DROP EXISTING taking more time than DBCC DBREINDEX

Question

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 Please click the Mark as Answer button if my post solves your problem.

Answer 1

Rajesh,

Notes:

1. Reindexing and index defrag are logged activities, it is helpful if you have fast transaction log device (in the RAID world for example dedicated RAID 1).

2. I would not do INDEXDEFRAG unless 24/7 online is a requirement.  Simply takes too long.

3. USE FILL FACTOR : 80% for extremely dynamic tables, 90% for dynamic tables.  You can experiment to find the optimal settings.

4. IF CREATE INDEX WITH DROP EXISTING  taking longer, why are you using it? Changing indexes? Or just trying it?

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 2

Ho Kalman Toth,

Thanks for your reply

4. IF CREATE INDEX WITH DROP EXISTING  taking longer, why are you using it? Changing indexes? Or just trying it?

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.

---

Rajesh Kasturi Please click the Mark as Answer button if my post solves your problem.

Answer 3

Are the indexes using long keys? Composite indexes?

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 4

I will verify and let you know

---

Rajesh Kasturi Please click the Mark as Answer button if my post solves your problem.

Answer 5

Hi SQLUSA,

Are the indexes using long keys?
What do you mean my long keys, is it composite indexes.

Composite indexes?
Yes some indexes has

Today I monitored the reindex job the job rebuild only one clustered index using CREATE INDEX WITH DROP_EXISTING.

Table size is 19.72 GB and Index size is 4.28 GB after the rebuilding the index using CREATE INDEX WITH DROP_EXISTING data file grown by 13.52 GB.

Table has one clustered index with four non clustered indexes and one non clustered index has composite key as (col1,col2,col3)

My expected result was no data file growth after rebuilding the indexes with CREATE INDEX WITH DROP_EXISTING but sill the files are growing and facing space issues.

Please help me to resolve this issue.

---

Rajesh Kasturi Please click the Mark as Answer button if my post solves your problem.

Answer 6

Hi SQLUSA,

What do you mean my long keys, is it composite indexes.

......

Table size is 19.72 GB and Index size is 4.28 GB after the rebuilding the index using CREATE INDEX WITH DROP_EXISTING data file grown by 13.52 GB.
......

Table has one clustered index with four non clustered indexes and one non clustered index has composite key as (col1,col2,col3)
.....

---

Rajesh,

While you may and you should try some of the indexing techniques below, my feeling is that you need more disk space. More memory maybe beneficial as well if that is an option.

EmailID int (4 bytes) is short key, Email varchar(75) is a long key.

The 19GB data and 4 GB index sizes appear to be usual.

Take a good look at the composite keys, are they really necessary? Do they have measurable benefits in performance?

Are you shrinking the db? Growing and shrinking repeatedly is not a good idea. How about adding extra disk and just leave the db alone? Is that an option? Just explain management that you need "elbow" room in a DB, you can't just fill it up to capacity and hope to stay operational.

You may consider using the WITH SORT_IN_TEMPDB option.  You need FAST tempdb with sufficient space for it.

While disk is "cheap" in these days, if it is not an option for you, consider archiving rarely used data onto a secondary database and/or secondary server and removing some indexes with marginal benefits.

Related link: Calculate disk space required for indexes

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 7

Thanks for your reply

I also recommended and requested to add more disk space, We have couple of problems there is some hardware issues like no slots to insert another disk and this db is going to retire after 1 year so they are not much worried about this.

I can't use WITH SORT_IN_TEMPDB because I am using SQL Server 2000.

Thanks a lot for you expertise suggestions.

---

Rajesh Kasturi Please click the Mark as Answer button if my post solves your problem.