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 |
|
sandesh_moghe
Constraint Violating Yak Guru
310 Posts |
 Posted - 2005-12-30 : 16:14:18
|
| -------------------------------------------------------------------------------- Hi all,I would like to defrag index of a table having 2 columns of Image datatype. It has taken 15000000 pages for storing these texts. It is having uniqidentifier primary key. Total rows in this table are 230000. Show contig show following values:Scan Density: 14%Logical Frag: 50%Avg Page Density:60%Fill Factor: 90%Now my questions are, from above information, 1. can we estimate time for DBCC DBREINDEX? 2. While reindexing does it consider the text columns as well? I will have to perform this on production box, so need to calculate downtime. Please advice.Thanks in advance ---------------------------Sandesh - The Messanger |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-12-30 : 16:29:22
|
| 2.Do you have textinrow set for the table?OBJECTPROPERTY can give you that information.If you don't have text in row set for the table it will not need to move the text data while reindexing.rockmoose |
 |
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2005-12-30 : 16:31:39
|
| If you compare DBREINDEX with INDEXDEFRAG then DBREINDEX is any day faster. As per my opinion it should not take more than 30 Mins. But it all depends on your DISK I/O speed.>>2. While reindexing does it consider the text columns as well? Defraging database using DBREINDEX means droping all the indexes from the table and recreating it. And thats why it requires double the space on disk before you start defraging. To avoid this change the recovery model to simple to avoid the log growing.RegardsSachinDon't sit back because of failure. It will come back to check if you still available. -- Binu |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-30 : 17:18:34
|
"... then DBREINDEX is any day faster"... but INDEXDEFRAG can be interrupted, doesn't lock up the whole world, and doesn't move the whole bang shooting match to some other part of the disk (which can extend MDF size etc.) - but I'm only saying that in defence of the fact that its my tool of choice for objects bigger than a few hundred rows  Kristen |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-01-01 : 12:30:36
|
quote:
Originally posted by sachinsamuel
If you compare DBREINDEX with INDEXDEFRAG then DBREINDEX is any day faster. As per my opinion it should not take more than 30 Mins. But it all depends on your DISK I/O speed.
Sigh. Complete misconception. The amount of time INDEXDEFRAG takes depends on how fragmented the index is in the first place. There's a cut-off point where INDEXDEFRAG is always faster than DBREINDEX as it has a whole lot less work to do - DBREINDEX always has to rebuild the entire index.Speed also depends on CPU, memory, concurrent workload on the IO system....quote:
>>2. While reindexing does it consider the text columns as well? Defraging database using DBREINDEX means droping all the indexes from the table and recreating it. And thats why it requires double the space on disk before you start defraging. To avoid this change the recovery model to simple to avoid the log growing.
Nonsense. Rebuilding an index requires building the new index before dropping the old one - hence the double amount of data disk space needed. The whole point of rebuilding an index with DBREINDEX is to avoid having to drop and recreate indexes.You can avoid using so much log space by switching recovery models, but it doesn't prevent the double-data space requirement.ThanksPaul RandalLead Program Manager, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-01-01 : 13:05:24
|
quote:
Originally posted by sandesh_moghe
-------------------------------------------------------------------------------- Hi all,I would like to defrag index of a table having 2 columns of Image datatype. It has taken 15000000 pages for storing these texts. It is having uniqidentifier primary key. Total rows in this table are 230000. Show contig show following values:Scan Density: 14%Logical Frag: 50%Avg Page Density:60%Fill Factor: 90%Now my questions are, from above information, 1. can we estimate time for DBCC DBREINDEX? 2. While reindexing does it consider the text columns as well? I will have to perform this on production box, so need to calculate downtime. Please advice.Thanks in advance ---------------------------Sandesh - The Messanger
In SQL Server 2000 and before, there's no way to compact text data without BCP out/in or selecting into another table. In SQL Server 2005 text data is compacted during INDEXDEFRAG and shrink, but not during index rebuild.To help choose between INDEXDEFRAG and DBREINDEX, read the whitepaper below.http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspxThanksPaul RandalLead Program Manager, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
|
|
|
|
|
DBCC DBREINDEX on Text Columns