August 21, 2008 at 10:30 am
I'm using SQL 2005 and I ran a query (FROM sys.dm_db_index_physical_stats) to obtain the fragmentation of my indexes/tables. I've read many places where it says to ignore framentation (at 100% if the table has less than 9 pages) although I haven't seen it documented anywhere on msdn. If anyone has a link to this information please post it.
But my problem is in the results from sys.dm_db_index_physical_stats, I have a table with 8000 rows. It is 13 pages and the fragmentation is 50% and the avg_page_space_used_in_percent is 98%. This table only has one column and there is a clustered index on this one column. This table NEVER gets updated, it's only used for selects. I dont understand why the fragmentation is so high. The fillfactor is at 100 because it is NEVER updated. I used alter index to rebuild the index and the fragmentation did not change.
Also, I thought there was a correlation between avg_page_space_used_in_percent and avg_fragmentation_in_percent. The closer avg_page_space_used_in_percent is to 100% the less fragmentation you have. But this is not the case. My avg_page_space_used_in_percent is 98.8 and the avg_fragmentation_in_percent is 50%. Please help.
August 21, 2008 at 10:49 am
What type of column is it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 21, 2008 at 11:14 am
It's because the table has a small number of pages. Don't worry too much about the fragmentation on small tables )less than about 100 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
August 21, 2008 at 11:46 am
The column type is int, not null. And it has the primary key, which is the index.
August 21, 2008 at 12:03 pm
New_to_being_DBA (8/21/2008)
Also, I thought there was a correlation between avg_page_space_used_in_percent and avg_fragmentation_in_percent.
There isn't a direct relationship. The fragmentation % refers to the percentage of tables 'out of order'. Out of order page is one that is earlier in the file, but later in the index key order.
A low percentage used (unless a very low fill factor is specified) usually implies a lot of page splits which implies a lot of fragmentation.
Defragmenting an index will not change the percentage of page space used.
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
August 21, 2008 at 1:42 pm
New_to_being_DBA (8/21/2008)
I've read many places where it says to ignore framentation (at 100% if the table has less than 9 pages) although I haven't seen it documented anywhere on msdn. If anyone has a link to this information please post it.
Microsoft has a whitepaper on this.
"Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)."
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Ola Hallengren
August 21, 2008 at 1:53 pm
Is there any documentation for 2005?
August 21, 2008 at 2:00 pm
I changed the fillfactor to 80 and the fragmentation went down to 41% but the page size went up to 17. Can someone please explain. This table will never change. Why is it fragmented at all?
August 21, 2008 at 2:01 pm
I haven't seen a whitepaper for SQL Server 2005. My guess is that the SQL Server 2000 whitepaper is valid on SQL Server 2005 too. Kalen Delaney referred to it in her blog earlier this year.
http://sqlblog.com/blogs/kalen_delaney/archive/2008/02/28/fragmentation-revisited.aspx
Ola Hallengren
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply