April 15, 2015 at 6:19 am
Hi All,
First time poster.
I'm just seeking your thoughts really.
I have a clustered index which shows as having a fragmentation level of 66% according to sys.dm_db_index_physical_stats.avg_fragmentation_in_percent.
But no matter what I try the fragmentation level doesn't budge. And yes I'm updating the statistics after each attempt.
Its not a huge issue the table only has 348 records. I'm testing a fixing fragmentation maintenance script. In Ironing out the syntax of my script I've fixed the fragmentation of indexes of over 65 % anyway
and I'm not going to spend too much more time on this issue, but I'm a curious I've come across and index that I can't defragment. Others have been fixed but not this one.
I've tried
ALTER INDEX ALL ON [GRIDINFO] REBUILD WITH (FILLFACTOR = 90)
I've tried
ALTER INDEX ALL ON [GRIDINFO] REORGANIZE
I've tried
DBCC INDEXDEFRAG (MYDATABASE, 'GRIDINFO', PK__GRIDINFO__3214EC2721F5FC7F);
The command complete successfully yet the avg_fragmentation_in_percent doesn't change. The table also has a nonclustered index. I've gleaned through all the statements of 'this will have no effect if' but so far I've not spotted a reason why this index won't defrag.
It's no biggy just curious of the thoughts of the guru's 🙂 I'm sure I'm missing something straightforward 🙂
The script has been modified to remove schema and database names for the forum. Not sure what's customary.
dbcc showcontig('GRIDINFO','IDX_GRIDINFO1') with tableresults , all_levels
go
DBCC SHOWCONTIG ('GRIDINFO')
go
sp_helpindex 'GRIDINFO'
go
SELECT object_name(object_id), name, index_id, allow_row_locks, allow_page_locks FROM sys.indexes WHERE name = N'PK__GRIDINFO__3214EC2721F5FC7F'
select * from sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)
where object_id = '537768973'
Results attached.
April 15, 2015 at 6:28 am
An index with only 3 pages likely won't be able to be defragmented because it is on a mixed extent and there isn't a way to move the rows onto contiguous pages within the extent.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 15, 2015 at 6:31 am
SQL Bandit (4/15/2015)
Its not a huge issue the table only has 348 records.
And there's the reason.
Until a table hits at least one extent in size (8 pages, 64kB), a defrag will likely have no effect. The rough threshold as to where defragging has a point is 1000 pages (8MB). A 3 page table isn't worth bothering about.
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
April 15, 2015 at 6:42 am
That's awesome , thanks for that , that makes sense.
Thank you so much 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply