Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2003-08-05 : 14:27:19
|
| Hi,Q1. Which one should I run first, Index Rebuild Or Defrag? Does it matter?Here is the situation: Every now and then we have some tax update or fix applied to our payroll module. After application, we run rebuild indexes for CERTAIN tables suggested by the fix. Next time when we process our payroll, it takes long time to run, this continues for a couple of weeks and then it is back to normal until we apply another fix!!!!!!!!!Typically, we apply our fixes on Wednesday, Run Defrag (entire db each week) on Friday and Rebuild index (for entire database every week) on Sunday.I am trying to find out sql table which is used by the process but since it is cobol, it will take time not to mention there are at least 25 tables used by this process.Q2. Is it possible that the index script which comes with fix is not enough so I should run index rebuilding for entire db after every fix followed by defrag.Thanks,Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-05 : 15:41:02
|
| If you can afford (meaning can have the application be rather slow due to locking and blocking) to run DBCC DBREINDEX after the fixes our applied, then I would definitely suggest running it instead of DBCC INDEXDEFRAG. After DBCC DBREINDEX has completed, you will NOT need to run DBCC INDEXDEFRAG as the indexes are no longer fragmented due to the rebuilding of them.Tara |
 |
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-08-06 : 17:25:46
|
| Well I build indexes every week. Are you suggesting not to do defrag if i am rebuilding every week or may be I alternate these but make sure that everytime I apply a fix, I do a reindex that night. How do you handle at your end?Thanks,Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-06 : 17:37:21
|
| We perform the rebuilding of the indexes once per week. If it is needed more often, we schedule it to occur more often. I do not run DBCC DBREINDEX after a fix/patch is applied, but I guess I would run it if lots of data had been changed.I have never needed to run DBCC INDEXDEFRAG as DBCC DBREINDEX is run often enough in our environments. From what I understand, organizations that use DBCC INDEXDEFRAG do so because it does not block queries and hold locks long term as will DBCC DBREINDEX. But if the application is not affected very much (due to the time of day that it is run most likely and the size of the indexes), then you might as well run DBCC DBREINDEX.Tara |
|
|
|
|
|
|
|
|
Which first: Index Rebuild Or Defrag?