February 8, 2012 at 12:20 pm
I am trying to wrap my head around this whole index fragmentation subject.
I am running the following query:
SELECT object_id, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(N'NNWareIncoming'), NULL, NULL, NULL, 'DETAILED') as frag
Microsoft documentation says:
Examine the value in avg_page_space_used_in_percent to determine whether the index contains internal fragmentation. Internal fragmentation is indicated when this value falls below 75.
Problem is, nearly all values I get are MUCH lower 75. For example, I get:
6.98
3.34
3.08
14.03
etc...
So, this means that all my indexes are fragmented? I didn't believe it because the indexes were recently build, but still I rebuilt all indexes - no difference.
Please explain if you can. Very confused.
Thank you,
Oksana.
February 8, 2012 at 12:27 pm
Not fragmented, it means that there is lots of free space on all of the pages. (yes, the docs refer to low page density as internal fragmentation, I personally dislike that term)
What's your fill factor set to?
Is this a heap or index?
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
February 9, 2012 at 1:04 am
Fillfactor is 0 and they are all indexes, no heaps. This adds to the confusion.
Just trying to explain what is confusing me exactly is the documentation says anything BELOW 75% means "fragmented", and almost all my values are below 75.
February 9, 2012 at 1:33 am
Well, yes. That is the measure of how full the pages are, how much data on the 8k page vs how much free space. At 75%, the page is 3/4 full, meaning 25% (about 2kb) is empty on a page.
Lower than that means that lots of space on the page is been wasted.
Are you sure that the index's fill factor and the default fill factor are 0?
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
February 10, 2012 at 5:12 am
Note that you should not concern yourself with objects with less than N pages, where N is probably 500 or even 1000 or more depending on your system.
Question: what is autogrowth for the database set at? Is it still the devastatingly bad 1MB?? Also if you proactively manage your database size, how much free space do you keep on hand?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 10, 2012 at 1:15 pm
The fill_factor column in sys.indexes has all zeros for all indexes. If this is the right place to look, then yes they are 0.
But thank you for explaining this fragmentation, I am really learning a lot about it.
February 10, 2012 at 1:21 pm
TheSQLGuru (2/10/2012)
Note that you should not concern yourself with objects with less than N pages, where N is probably 500 or even 1000 or more depending on your system.Question: what is autogrowth for the database set at? Is it still the devastatingly bad 1MB?? Also if you proactively manage your database size, how much free space do you keep on hand?
Hi Kevin,
Are you saying that I don't need to worry about fragmentation if the table size is less than 8MB (1000 pages 8 kilobytes each)? If that's the case, good news, that eliminates maybe 1/3 of my tables! There are several big tables, one has almost 2TB of warehouse shipment detail, but some only have 10-20 new records per day and they are under that size/
Yes, the autogrowth is currently "1MB, unrestricted growth" just because I didn't know it's important or why it should be something different 😀
February 10, 2012 at 2:28 pm
Oksana March (2/10/2012)
TheSQLGuru (2/10/2012)
Note that you should not concern yourself with objects with less than N pages, where N is probably 500 or even 1000 or more depending on your system.Question: what is autogrowth for the database set at? Is it still the devastatingly bad 1MB?? Also if you proactively manage your database size, how much free space do you keep on hand?
Hi Kevin,
Are you saying that I don't need to worry about fragmentation if the table size is less than 8MB (1000 pages 8 kilobytes each)? If that's the case, good news, that eliminates maybe 1/3 of my tables! There are several big tables, one has almost 2TB of warehouse shipment detail, but some only have 10-20 new records per day and they are under that size/
Yes, the autogrowth is currently "1MB, unrestricted growth" just because I didn't know it's important or why it should be something different 😀
ok, so picture this math. you create a database using default everything. it is 10mb. then you put 2TB of data into it, and it grows at 1MB at a time to get from 10 MB to 2TB. How many little, tiny, IO PERFORMANCE ROBBING 1MB pieces do you have scattered all over your disk(s)?? 2 MILLION of them if my math is correct. :w00t: So every time you need to read data the disk heads are thrashing around and platters spinning, costing HUGE amounts of time to get you your data up into RAM. BAD NEWS!!
I REALLY must STRONGLY recommend you get a performance tuning professional to give your systems a review and health check. You will undoubtedly have a BUNCH of stuff like this that is totally suboptimal.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply