April 28, 2011 at 3:29 am
Dear All,
Regarding Index rebuilding, I can see same fragmentation percentage before and after the index rebuilding.
Can some one please advise what would be the reason for this.
Thanks and Regards,
Ravichandra.
April 28, 2011 at 4:19 am
How big is the index? How many 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
April 28, 2011 at 4:37 am
It's a non clustered index.
Total_Pages 114.
Please advise how to check the size of the index.
In the below query, if index name shows NULL , what it indicates?
SELECT
db_name() AS DatabaseName
, B.name AS TableName
, C.name AS IndexName
, C.fill_factor AS IndexFillFactor
, D.rows AS RowsCount
, A.avg_fragmentation_in_percent, A.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN sys.objects B ON A.object_id = B.object_id
INNER JOIN sys.indexes C ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D ON B.object_id = D.object_id AND A.index_id = D.index_id
Thanks and Regards,
Ravichandra.
April 28, 2011 at 4:53 am
ravisamigo (4/28/2011)
It's a non clustered index.Total_Pages 114.
Too small to worry about. Around 1000 pages is about the point where fragmentation makes noticeable differences
In the below query, if index name shows NULL , what it indicates?
The item is a heap, a table without a clustered 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
April 28, 2011 at 5:11 am
in case if it has more than 1000 pages, what would be the reason please
Thanks and Regards,
Ravi.
April 28, 2011 at 5:17 am
Reason for what?
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 28, 2011 at 5:30 am
if index is big, will it be fragmented or not? if still shows same, after index rebuild how to reduce?
Thanks and Regards,
Ravi.
April 28, 2011 at 5:54 am
A big index can be fragmented, yes. Rebuilds should reduce fragmentation of bigger indexes, it's very small indexes that it often doesn't.
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 2, 2011 at 7:07 pm
Gail is Right. Rebuilding doesn't have any impact on smaller indexes with pages usually < 1000.
What is the Index depth for the index ?
Thank You,
Best Regards,
SQLBuddy
May 3, 2011 at 7:01 am
An index can still be fragmented after a rebuild. The most common reason is because virtually every server in existence has autogrowth managing the database sizes. I advise my clients to figure out how much space they will need (data AND index) in their databases for the next 12 to 18 months - and then make the files large enough to house that NOW.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply