| Author |
Topic |
|
niranjankumark
Posting Yak Master
164 Posts |
 Posted - 2007-10-31 : 08:58:02
|
| Like dbccreindex ... dbccindexdefrag will drop and create index ???fragmentation also done in dbccreindex ???after doing dbccreindex ldf file will get increased??is it good after this process it can be shrink??? |
|
|
pootle_flump
1064 Posts |
Posted - 2007-10-31 : 11:23:40
|
| Definately do not shrink after defragging - you will actually introduce fragmentation doing that.dbreindex rebuilds the index entirley. indexdefrag resolves logical fragmentation (i.e. puts the pages in order) but does not touch the content of the pages. So dbreindex will (probably) leave you with more or less pages (depending on the starting point and fill factor) than you started with whereas defragindex will leave you with exactly the same number of pages in exactly the same state as they were but in logical order. |
 |
|
|
pootle_flump
1064 Posts |
Posted - 2007-10-31 : 12:16:36
|
| Ah - just reread - you are talking about shrinking the ldf. This is not as bad as shrinking the mdf but I wouldn't do it anyway. Why? Because it will just grow again next time you defrag. Shrinking & growing files is expensive. Just leave it as it is. |
|
|
|
niranjankumark
Posting Yak Master
164 Posts |
Posted - 2007-11-01 : 06:52:22
|
| For a specific table fragmentation exist or not .. is it possible to find ???to increase performance ...dbccreindex alone enough .. or defragmentation also has to do after dbccreindex. ??? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-01 : 07:44:50
|
"is it possible to find ???"DBCC SHWOCONTIG"defragmentation also has to do after dbccreindex."No, not necessary.Note that DBREINDEX is liable to move the whole of the index to the end of the database file, and thus potentially extend the file and then leave a big hole in the middle ...... shrinking will then re-fragment the file  so DEFRAG is usually the preferred solution for large tables in SQL 2000.You need to UPDATE STATISTICS after DEFARG (REINDEX includes Update Statistics)Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-01 : 23:30:29
|
| Should rebuild index if table is highly fragmented. |
|
|
|
niranjankumark
Posting Yak Master
164 Posts |
Posted - 2007-11-02 : 04:14:08
|
| kirsten .."Note that DBREINDEX is liable to move the whole of the index to the end of the database file, and thus potentially extend the file and then leave a big hole in the middle ..."will this also reduce performance ????defragment will make order and will not create index..IN that scenario is it neccesary to do reindex???by doing reindex size will get increase ?? this will also affect any performance??? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-02 : 04:36:49
|
| "will this also reduce performance ????"No, the index will now be contiguous on the disk, in addition to having been reorganised internally, so will perform better. There is an issue of how much disk space that operation might need though!This behaviour may have changed in SQL 2005."defragment will make order and will not create index..IN that scenario is it neccesary to do reindex???"No, you make a choice whether to Defrag OR to reindex. You don't need both. But if you use Defrag you ALSO need to Update Statitics; that isn't necessary with Reindex."by doing reindex size will get increase ??"Yes, but only if there is not already enough space in the database. Your database will be carrying some slack, but that space can be reused next time you reindex."this will also affect any performance???"No. However, a database which has excessively large MDF/LDF files will take longer to Restore. So it may be an issue to consider for speed of Disaster Recovery.Kristen |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2007-11-09 : 08:39:11
|
| The other advantage of defrag is it does 1 row at a time , so if the operation stops at any time , it will just stop at that point , whereas reindex will rollbackJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-11-10 : 09:32:17
|
| Yes - although it doesn't actually touch the rows, just the pages. But the principal that it does not roll back is a good point. |
|
|
|
|
dbccreindex vs dbccindexdefrag