Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Rebuilds Still Leave Fragmentation


Index Rebuilds Still Leave Fragmentation

Author
Message
SQL Dude-467553
SQL Dude-467553
SSC-Addicted
SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)

Group: General Forum Members
Points: 473 Visits: 856
Hello All,

I am running SQL Server 2005 SP2 Standard Edition on a few servers. I run SQL Agent Jobs every week that REBUILD all of the user table indexes i.e. (ALTER INDEX ALL ON [Table] REBUILD) I also run another job that UPDATES STATISTICS. The jobs complete without error and rebuild most all of the indexes removing fragmentation then updating any statistics. Though, there are quite a few indexes that even after running the rebuild script still have the same fragmentation % i/e 67% or fragments as prior to the REBUILD command. Are there some reasons why indexes will remain fragmented even after rebuilding them? I've tried as a test on some tables to rebuild the index in question from SSMS, SQL Agent Job, and the GUI point and click "Rebuild", all results are the same, command competed successfully, but fragmentation remains the same, no change.

Any tips or pointers would be greatly appreciated! There must be some caveat I'm not thinking of..
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47165 Visits: 44346
How big are the tables in question? How many pages do the indexes take up (visible in sys.dm_db_index_physical_stats)

Edit: btw, a separate update statistics is not necessary (and may even be counter productive) if you're doing an index rebuild. When the index gets rebuilt, the stats get updated as if an update stats with full scan was run.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQL Dude-467553
SQL Dude-467553
SSC-Addicted
SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)

Group: General Forum Members
Points: 473 Visits: 856
Hi Gail,

There are quite a few examples but I will stick to one or two. The others are very similar, and / or only average 1 fragment per page.

Stats from the DMV

EXAMPLE
Table Data Size .516 MB
Index Space .719 MB
Table record count : 7012
# of Pages : 14
# of Fragments 8
Avg Frag 50%

EXAMPLE
Table Data Size .039 MB
Index Space .016 MB
Table record count : 568
# of Pages : 5
# of Fragments 5
Avg Frag 80%
Eric Desch
Eric Desch
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 219
See if this helps...

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

EKD
Paul Randal
Paul Randal
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2161 Visits: 1714
These indexes aren't getting touched - you basically shouldn't bother with index fragmentation unless your index has more than 1000 pages as it'll most probably be entirely contained in cache already and so fragmentation is irrelevant. I thought I'd put that into BOL for DBCC SHOWCONTIG and index_physical_stats but it looks like I didn't.

Checkout this blog post on rebuilds and stats updates - http://www.sqlskills.com/blogs/paul/2008/01/27/SearchEngineQA10RebuildingIndexesAndUpdatingStatistics.aspx. Weird how questions seem to come up in clusters - this week it's index rebuilds.

Hope this helps.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
SQL Dude-467553
SQL Dude-467553
SSC-Addicted
SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)

Group: General Forum Members
Points: 473 Visits: 856
Thanks, yeah I wasn't worried about performance reasons, as your right there isn't much to the table. It was a curiosity question, as there must be a reason ie size of table, total number of data pages why they were not being touched. The ones that are critical were being rebuilt..

The reason we do the statistics FULL SCAN afterwards is to catch the other statistics that were user created or auto created by the engine in some situations that were not part of an index column. On our databases we have a good maintance window so time is no issue and it really only takes 5-10 min for rebuilds and statistics as a high number. Sometimes we find that one of those statistics should be made into an index and other times they are better served as just statistical info for the engine.
Adam Bean
Adam Bean
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1215 Visits: 2160
GilaMonster (1/28/2008)
How big are the tables in question? How many pages do the indexes take up (visible in sys.dm_db_index_physical_stats)

Edit: btw, a separate update statistics is not necessary (and may even be counter productive) if you're doing an index rebuild. When the index gets rebuilt, the stats get updated as if an update stats with full scan was run.


Gila, I was always under the same impression until I had several phone conversations with Microsoft revolving around performance issues we experienced. Two different SQL Server support techs informed me that issuing a DBCC DBREINDEX, ALTER INDEX REBUILD/REORGANIZE, will NOT update the statistics with fullscan. They recommended having a separate task in our maintenance scripts to update the statistics.

Now I'm curious ... It wouldn't be the first time Microsoft has given me faulty information.

---
SQLSlayer
Making SQL do what we want it to do.
Paul Randal
Paul Randal
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2161 Visits: 1714
The quality of support has gone way down - I used to constantly battle incorrect support engineers (a lot easier to do when you're running one of the dev teams they're giving incorrect info about).

They're wrong - and that's pretty annoying. Trust me - I used to own all that code. Reorganize doesn't but rebuild does update stats.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
free_mascot
free_mascot
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2875 Visits: 2235
I also face the similar kind of problem:

When the table size is very small and contain a few rows only at that time this kind of problem happen. In addition to this when there is heavy insert/update/delete happen to the database i.e. few transactions per second at that time also you won't get Scan Density 100%.

One shouldn't bother much about the Scan Density on the small table.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47165 Visits: 44346
Adam Bean (1/29/2008) Two different SQL Server support techs informed me that issuing a DBCC DBREINDEX, ALTER INDEX REBUILD/REORGANIZE, will NOT update the statistics with fullscan. They recommended having a separate task in our maintenance scripts to update the statistics.


Paul Randal (1/29/2008)
Trust me - I used to own all that code. Reorganize doesn't but rebuild does update stats.


And it's very easy to prove, if you want to see for yourself Adam.
Run DBCC Show_statistics on an index. Check the date the stats were updates. Rebuild the index, then run show statistics again.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search