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 |
|
RonsSQL
Starting Member
1 Post |
 Posted - 2005-11-03 : 17:23:19
|
Hello,I'm looking for the query command that will go out to all the user Tables and will tell me what Indexes need to be reindexed.We are having a problem with some of the tables and we don't know when our tables need to be reindexed other than when operations are stopped for our company.   Thanks,Ron |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-11-03 : 23:28:12
|
| The most important thing is to know which indexes will actually benefit from having fragmentation removed. Those that are used for range scans will benefit the most.Assuming you're on SQL 2000, you have two realistic choices for removing fragmentation - DBCC DBREINDEX and DBCC INDEXDEFRAG. The first is offline, the second is online. DBCC SHOWCONTIG will tell you which indexes are highly fragmented - look at the Logical Scan Fragmentation.Here's a whitepaper I helped write that I strongly recommend you read which goes into a lot more detail on what I describe above.http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspxAny questions, please let me know - I wrote both SHOWCONTIG and INDEXDEFRAG.Regards.Paul RandalLead Program Manager, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-06 : 08:38:03
|
| We store the results of SHOWCONTIG into a table, and if the fragmentation exceeds a threshold we rebuild it - using DEFRAG if the table it large, otherwise REINDEX.Tara has lots of relevant material in Blog:http://weblogs.sqlteam.com/tarad/category/95.aspxKristen |
|
|
|
|
|
|
Reindexing tables on a databse that is 36GB