May 22, 2012 at 12:40 pm
I would like to know about Fragementation in percent vs Page count. Whenever I tried to reorganize or rebuild the query, I see the following changes
IndexTypeAvgPageFragmentationPageCounts
CLUSTERED INDEX 66.6666666666667 3
NONCLUSTERED INDEX 66.6666666666667 3
HEAP63.7976346911958 12107
Can someone explain me more detail way. If I further do any rebuild on clustered index, it remains same. Some times, the page count won't get reduced for example:
IndexTypeAvgPageFragmentationPageCounts
CLUSTERED INDEX 0.41958041958042 715
What exactly this page count does and how can we reduce it or is it required to pay attention onto this page counts.
May 22, 2012 at 1:29 pm
Have a look at the stairways and indexing, there is plenty of information for you on there.
as for pages, data is stored on pageses therefore if you have a page count of 10 then that is how many pages the data is held across
***The first step is always the hardest *******
May 22, 2012 at 1:56 pm
Page count is the number of pages that the data in the table takes up, each page is 8kb. The only reliable way to decrease that is to delete data.
Don't fuss over indexes with 3 pages, they won't defrag. The general recommendation is to worry about fragmentation once a table is over 1000 pages or so.
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
May 22, 2012 at 2:14 pm
So, if the table has more than 1000 pages, so how can we reduce it.
May 22, 2012 at 2:17 pm
Reduce what? Fragmentation or page count?
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
May 22, 2012 at 2:24 pm
Reduce page count
May 22, 2012 at 2:28 pm
Delete data from the table. That's the only reliable way to reduce the size of the data in the table, which is what page count is.
Why are you fixated on page count? If a table has 8MB of data in it, it will have at least 1000 pages because 8MB of data, 8kb per page, 1000 pages.
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
May 22, 2012 at 2:28 pm
DBA_SQL (5/22/2012)
Reduce page count
Delete data.
Trying to understand why you are focusing on page count. As data is added to the database the page count is going to go up.
May 22, 2012 at 3:04 pm
I think I am confusing. So, if we have more data, we get more pages...and vice versa right...So, at this point i think it is good to focus on only fragmentation part rather than pages, because it all depends on data.
May 22, 2012 at 3:06 pm
Yes. As Gail said earlier, don't even worry about fragmentation until you hit about 1000 pages in the table.
May 22, 2012 at 3:06 pm
DBA_SQL (5/22/2012)
I think I am confusing. So, if we have more data, we get more pages...and vice versa right...So, at this point i think it is good to focus on only fragmentation part rather than pages, because it all depends on data.
Ok, but if the index is less than 1000 pages (or whatever you see fit), then don't worry about fragmentation.
Jared
CE - Microsoft
August 13, 2018 at 1:55 pm
I realize this is a very old post, but a few things. If your index is getting scanned then ignore that 1000 pages business. I've seen scans on indexes choke with around a 300 page_count, so check your index scan stats. If you don't think you should be having scans, make sure whatever operation/query is sargable, no implicit_conversion, etc. Also if you have a higher page_count than you think should be required (considering offset/header) you might be dealing splitting while updating data bigger than the original slot so your page density isn't full when that occurs. Rebuild operations should handle this or you could go full tilt and take the db offline, defrag the mdf (to reduce physical fragmentation by hopefully getting contiguous mapping), rebuild indexes (sort in tempdb=on maxdop=1 for serial builds done on a separate disk than destination), and possibly mess around with fillfactor to mitigate splits.
August 13, 2018 at 2:02 pm
nmcquillen - Monday, August 13, 2018 1:55 PMI realize this is a very old post, but a few things. If your index is getting scanned then ignore that 1000 pages business. I've seen scans on indexes choke with around a 300 page_count, so check your index scan stats. If you don't think you should be having scans, make sure whatever operation/query is sargable, no implicit_conversion, etc. Also if you have a higher page_count than you think should be required (considering offset/header) you might be dealing splitting while updating data bigger than the original slot so your page density isn't full when that occurs. Rebuild operations should handle this or you could go full tilt and take the db offline, defrag the mdf (to reduce physical fragmentation by hopefully getting contiguous mapping), rebuild indexes (sort in tempdb=on maxdop=1 for serial builds done on a separate disk than destination), and possibly mess around with fillfactor to mitigate splits.
Absolutely. Even the person who originally came up with the "1000 pages" admitted it was just a round number, with no real analysis to it. Determine the best clustering index and apply it, no matter how many rows the table (currently) has. You can also force a table(s) with less than 8 total pages to be put into a single extent, which for a busy, small table can reduce I/O.
As to ways to reduce data size, you have other options besides just deleting data (which usually isn't a viable business option at all);
1) if you have an edition of SQL that supports it, compress the data.
2) if you don't, encode data, esp. character data. That is, use a numeric id/code in place of a longer string value.
There are some others, but those should give you the biggest payback for the least overall effort.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply