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 |
|
stephe40
Posting Yak Master
218 Posts |
 Posted - 2007-09-25 : 11:21:32
|
| I have a nice script that will look at the index fragmentation by using the DMV (sys.dm_db_index_physical_stats) and if its above a specificed threshhold, it will rebuild or reorg the index. What I have noticed is that even after reorging and/or rebuilding the index, the fragmentation percent in the DMV does not change for some indexes. Other indexes are updated just fine.Is this a result of updating statistics? Why would the fragmentation change for some indexes and not others? Why does it seem no matter how much rebuilding or reorging is done, the fragmentation percent for some indexes does not change?- Eric |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-25 : 12:03:55
|
| Perhaps that occurs when the table is small. Do you have specs on the tables where this happens?My script can ignore smallish tables for just this reason: http://weblogs.sqlteam.com/tarad/archive/2007/04/17/60176.aspxTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2007-09-25 : 13:36:23
|
| Yeah, my script also skips indexs on tables with few number of rows. It does seem to happen more frequently on smaller tables now that you mention it. Currently I have my script skipping tables with less than 100 rows, maybe I should increase this to 400 or 500? Thanks for the response Tara, I have already checked out your script. I referenced it when building mine, thanks!- Eric |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-25 : 13:42:32
|
| I typically skip tables smaller than 1000 or 10000 rows. They just don't span enough pages for me to worry about fragmentation. You'll need to test what number works best for you though. Increase the number slowly and see if the tables get removed from the DMV report.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-26 : 06:14:26
|
| I've seen that too (under SQL 2000) and we based our maintenance on the number of pages, rather than rows, in the index. Dunno if that was a good choice, or not, but it seemed like a good idea at the time!Kristen |
|
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2007-10-01 : 14:15:37
|
| Well, the DMV does return page counts, I could switch to using that. What page limit would you recommend as the cutoff?- Eric |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-01 : 14:31:32
|
| We use 10000, but that may not be appropriate for you, on the grounds that below that we can tolerate a REINDEX with bocking, and above that we want a concurrent operation. |
|
|
|
|
|
|
|
|
Index Fragmentation in the DMV