September 16, 2008 at 9:30 am
I think I'm encountering a fundamental gap in my understanding of index fragmentation, and I'm hoping you smart people can shine a little light. I run the following query:
SELECT stat.object_id, stat.index_id, stat.index_type_desc, stat.avg_fragmentation_in_percent,
ob.name
FROM sys.dm_db_index_physical_stats (DB_ID(N'CE_DATA'), NULL, NULL, NULL , 'DETAILED') stat
INNER JOIN sys.objects ob
ON stat.object_id = ob.object_id
WHERE stat.avg_fragmentation_in_percent > 0
ORDER BY ob.name
My understanding is that this will show me the fragmentation levels for my indexes (at least the ones that are fragmented). There are a whole bunch, with avg_fragmentation_in_percent values ranging from 33.3 to 87.5. I assume my goal is to get those values as low as possible, but so far nothing I've done has had any effect on them.
I've tried running...
ALTER INDEX ALL ON TableName
REBUILD
...on each table. When I run the first query, the results are identical. I've also tried running Tara Kizer's isp_ALTER_INDEX script, and again the results are identical.
I've even dropped an index entirely, run the first script to confirm it no longer appears in the list, and then recreated it. When I re-ran the first script, the index I just created appeared with its previous fragmentation value.
So either I'm misinterpreting the results of the first query, or I don't have something set right, or I'm just a clueless newb. Any guidance would be greatly appreciated.
Thanks!
Ron Moses
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
September 16, 2008 at 11:04 am
How big are the tables in question? How many pages? (you can see that from the full output of index physical stats)
My guess is that the tables are very small and for very small tables fragmentation doesn't mean much and, due to the way the first 20 or so pages are allocated, the fragmentation % may be higher than expected.
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
September 16, 2008 at 11:10 am
All our tables are relatively small -- the biggest has about 266,000 rows. So you think I shouldn't be too worried about it then? I appreciate the feedback.
Thanks!
Ron Moses
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
September 16, 2008 at 11:15 am
I wouldn't call 200000 rows small. 🙂
Check the number of pages in the table. Don't worry too much about tables with < 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
September 16, 2008 at 11:27 am
I was thinking in terms of giant terabyte dbs, I guess. 🙂 Anyway, no index has a page_count higher than 21 from what I can see in sys.dm_db_index_physical_stats.
Thanks for the advice!
Ron Moses
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply