| Author |
Topic |
|
Shastryv
Posting Yak Master
145 Posts |
 Posted - 2005-08-26 : 09:31:03
|
| I am just curious how many of you agree/disagree on this. Your input is appreciated. It's about index defrag. Would you normally check the de fragmentation using Showcontig and then rebuild/defrag the index or directly rebuild/defrag index with out running the showcontig. I prefer the first one, rebuild indexes, showcontig is waste of time to me.Thanks |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-08-26 : 09:48:22
|
| I agree, but it depends on whether you have a huge honkin' table that takes an hour to defrag. You might not want to defrag it unless it was necessary. |
 |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-27 : 02:05:45
|
| We defrag if the index is large, otherwise reindex. But only if the index is bigger than a small-thing (I forget exactly, but some really small tables appeared frag'd immediatly after reindex), and is more than 20% fragmentated.Our DBs are 24/7, so we need a minimally-invasive strategy. Otherwise you could just reindex the lot, but you'll take a hit on log size / disk space.Kristen |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-27 : 10:31:34
|
quote:
Originally posted by Kristen...but some really small tables appeared frag'd immediatly after reindex), and is more than 20% fragmentated.
Logical scan fragmentation?Paul RandalDev Lead, 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 - 2005-08-27 : 10:35:40
|
quote:
Originally posted by Shastryv
I am just curious how many of you agree/disagree on this. Your input is appreciated. It's about index defrag. Would you normally check the de fragmentation using Showcontig and then rebuild/defrag the index or directly rebuild/defrag index with out running the showcontig. I prefer the first one, rebuild indexes, showcontig is waste of time to me.Thanks
You should definitely read the whitepaper. In a nutshell - don't remove fragmentation unless you have to otherwise you're wasting resources. My main reason for writing the DBCC INDEXDEFRAG command was to give an online way of removing fragmentation but it has a high cost in transaction log and it can't update statistics - that's the trade-off you make when comparing with DBCC DBREINDEX.And don't base your decision on Scan Density or Extent Fragmentation - always use Logical Fragmentation.RegardsPaul RandalDev Lead, 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-08-28 : 03:01:58
|
"Logical scan fragmentation?"Doesn't look like it, no :-(I'm making a temporary table from the results of:DBCC SHOWCONTIG (@MyTableID) WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGSI'm then rebuilding where: ScanDensity <= 90AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0AND CountPages > 10 If CountPages < 10000 I rebuild, otherwise I defrag (we have an override to periodically force Reindex on the bigger indexes)Kristen |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-28 : 10:47:34
|
quote:
Originally posted by Kristen
"Logical scan fragmentation?"Doesn't look like it, no :-(I'm making a temporary table from the results of:DBCC SHOWCONTIG (@MyTableID) WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGSI'm then rebuilding where: ScanDensity <= 90AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0AND CountPages > 10 If CountPages < 10000 I rebuild, otherwise I defrag (we have an override to periodically force Reindex on the bigger indexes)Kristen
Do you have multiple files in your filegroups? If so, Scan Density doesn't work and you need to use Logical Scan Fragmentation as your trigger.Paul RandalDev Lead, 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-08-29 : 05:58:26
|
| "Do you have multiple files in your filegroups"No, but we might easily do in the future.What level should I set for logical fragementation? Persumably its pointless defragging for just the odd one or two fragemented blocks in a large file?So if I'm currently using ScanDensity <= 90 would LogicalFrag > 10 be a good plan?Checking my logs I find I am currently rebuilding lots of tables/indexes where ScanDensity is around 50 - 70, but the LogicalFrag is <10, and in many case 0.Kristen |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-29 : 12:30:30
|
quote:
Originally posted by Kristen
"Do you have multiple files in your filegroups"No, but we might easily do in the future.What level should I set for logical fragementation? Persumably its pointless defragging for just the odd one or two fragemented blocks in a large file?So if I'm currently using ScanDensity <= 90 would LogicalFrag > 10 be a good plan?Checking my logs I find I am currently rebuilding lots of tables/indexes where ScanDensity is around 50 - 70, but the LogicalFrag is <10, and in many case 0.Kristen
It depends. Unless you've actually correlated increasing fragmentation with decreasing performance, there's no point removing fragmentation. To pick the threshold to use, you need to get some empirical data to help you decide. 10% fragmentation may have negligible effect on your performance, for instance. The whitepaper has a bunch of info on doing this.Paul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-08-29 : 16:39:02
|
quote:
Originally posted by paulrandal(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
I think that pretty much sums up the post...  I guess I'll need to review the white paper that you're speaking of because I don't understand how fragmentation would be ok or not degradate performance. If you're looking up data and SQL Server has to get pointers to go to other pages that then have pointers to other pages which also have pointers to the pages that actually contain the data that has to be slower at least by a nano second.DanielSQL Server DBAwww.dallasteam.com |
|
|
|
nduckste
Starting Member
2 Posts |
Posted - 2006-11-30 : 18:43:45
|
Paul,Is there you could explain ScanDensity and Extent Fragmentation -- or point me to an article where it is already explained. There are some on our team that say ScanDensity is a better measure for determinining fragmentation but I don't have a good explanation why.Thanks,Nickquote:
Originally posted by paulrandal
quote:
Originally posted by Shastryv
I am just curious how many of you agree/disagree on this. Your input is appreciated. It's about index defrag. Would you normally check the de fragmentation using Showcontig and then rebuild/defrag the index or directly rebuild/defrag index with out running the showcontig. I prefer the first one, rebuild indexes, showcontig is waste of time to me.Thanks
You should definitely read the whitepaper. In a nutshell - don't remove fragmentation unless you have to otherwise you're wasting resources. My main reason for writing the DBCC INDEXDEFRAG command was to give an online way of removing fragmentation but it has a high cost in transaction log and it can't update statistics - that's the trade-off you make when comparing with DBCC DBREINDEX.And don't base your decision on Scan Density or Extent Fragmentation - always use Logical Fragmentation.RegardsPaul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
|
|
|
|
|
Index Defrag