SQL Server Performance Forum – Threads Archive
DBCC reindex takes more than 12 hours
HI, My DBCC REINDEX(‘tablea’) command takes about 12 hours and it is still running.
This table has 6 Indexes and each of them are fragmented between 40% to 60%
The volume is 34Million records No other process is running in the server. If I kill the process, will there be any Issues? or will it complete? Thanks for your help
It is better to perform reindex index by index than allowing all at once, I would expect to take some more time taking the volume into consideration. Also confirm the server hardware and any other jobs that are overlapping with this process. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
This is a dedicated server. One thing I noted is the database files are spread across three data files Does this one makes any difference?
I don;t see any issue in completing the process, btw have you performed the same before and what was the completion timings. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Before it was an automated job running every weekend.
When the volume is less it ran successfully ( say in 6hours for the whole database).
I don’t have exact statistics of the volume on successful run. As the volume increases, it took long time to complete the job. The production guys stopped this job due to time constraints two months before.
That’s why it is too much defragmented. Now I am doing this only for one table alone.
Here is the OUTPUT of sp_who2 statuscommandcpu diskio
sleeping DBCC 21609718 1644312
sleeping DBCC 4842344 1644312
sleeping DBCC 4686390 1644312
sleeping DBCC 4784359 1644312
sleeping DBCC 4667079 1644312
sleeping DBCC 4677719 1644312
sleeping DBCC 4785000 1644312
sleeping DBCC 4823765 1644312
sleeping DBCC 4819828 1644312 ( I don’t know why the DISKIO is same for all threads) CPU utilization is 10-20%
SQL Server.exe 13-18%
Monitor the SP_WHO2 constantly and ensure the diskio and process value is changing, running a PERFMON willbe a good idea to check what kind of process is running & server usage. Even if you stop the job it will take sametime to rollback the operation and may risk the database status during this operation. http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx is your bible to keepup the performance. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Thanks sathya. If we backup and restore a highly fragmented database, the defragmentation will be there?
SATHYA, It completed after 17.5 hours I have one more partition to be reindexed, which is of same volume Now, if I want to do index by index, which index will I have to choose first I have one clustered index, one nonclustered unique index and three nonclustered index. Should I have to use
Create index … with drop_existing ?
quote:Originally posted by ramasubbup
Thanks sathya.
If we backup and restore a highly fragmented database, the defragmentation will be there?
A restore is a block for block restore, it will not defragment the database.
I think you need to use some sp to improve time.
Take a look one article by Tom Pullem. He wrote an sp to defrag only those indexes below some value.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
Books Online also contains a great sample script to get started with. Search DBCC SHOWCONTIG and you’ll find it…
Haywood’s reference is correct, use DBCC SHOWCONTIG and decide which has highest defragmentation. Wherever necessary use DBCC INDEXDEFRAG and use DBREINDEX when logical fragmentation is high.
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp for your reference on SHOWCONTIG. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>