| Author |
Topic |
|
john.burns
Posting Yak Master
100 Posts |
 Posted - 2009-11-30 : 14:04:28
|
| I've got a 4 billion row table with 1 clust and 1 nonclust index.These were defragged on Friday and frags were 0 and now the clust is still 0 but NC index is nearly 60% fragmented already. Granted insertions are heavy over t he weekend but still only a small percentage of total rows are new inserts. Any Ideas?thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-11-30 : 14:06:02
|
| Sounds normal to me. We defrag nightly anything over 50% fragmentation. Microsoft's recommendation is actually even higher. They say not to bother until it's around 75% fragmented since it doesn't impact performance until then.To avoid fragmentation, you'd have to design your indexes and tables differently.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-11-30 : 14:08:38
|
quote:
Originally posted by john.burns
Granted insertions are heavy over the weekend but still only a small percentage of total rows are new inserts.
Can you re-read that again and tell me if that makes sense to you?If it does, can you tell me what new inserts are as compared to non-new inserts?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-30 : 14:12:04
|
| what fill factor are you using for the non-clustered index? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-11-30 : 14:27:49
|
| Regarding fill factors, they should be set to 0 starting in SQL Server 2005 and up. If you drop it down to avoid fragmentation, it will kill performance. Test it out and you'll see. I did extensive testing regarding it and was surprised how much performance was impacted when I dropped it down.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
john.burns
Posting Yak Master
100 Posts |
Posted - 2009-11-30 : 14:30:39
|
| -new inserts are new since last defrag-ff is default 0%Tara, thanks for info... so if the nc index is is a four part composite index (consisting of 2 ints and 2 smallints) orderedquite different than CI than frag will occur that quickly?? How can that happen when new records are 2% of table?thanks |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-30 : 15:09:51
|
quote:
Originally posted by tkizer
Regarding fill factors, they should be set to 0 starting in SQL Server 2005 and up. If you drop it down to avoid fragmentation, it will kill performance. Test it out and you'll see. I did extensive testing regarding it and was surprised how much performance was impacted when I dropped it down.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong."
Did you ever publish your results? I'd be interested to see them. Much easier than setting up my own tests  lol |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-11-30 : 15:50:32
|
| @russell - I didn't publish the results, but I did discuss my findings with Microsoft and found that Microsoft's recommendation is 0 for the fill factor in almost all cases. @john - You may want to consider rearranging your composite index so that the fragmentation isn't so quick, but rearranging it can have a negative performance impact depending upon the selectivity of the columns. Fragmentation is based upon data pages and not row counts. For systems with heavy inserts, fragmentation can and will happen quickly. You should determine if the fragmentation is causing performance issues, and if it is then defragment more often.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
john.burns
Posting Yak Master
100 Posts |
Posted - 2009-11-30 : 16:46:42
|
| Thanks for your responses |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Index Frag