sys.dm_db_index_physical_stats not accurate?

Question

Post reply

sys.dm_db_index_physical_stats not accurate?

rgarrett 82118

Old Hand

Points: 344
More actions
March 17, 2010 at 8:56 am

#221918

When using sys.dm_db_index_physical_stats to determine fragmentation levels for performance tuning, I am receiving erroneous results.
In a particualar table with approx 1 million rows, it was showing avg_fragmentation_in_percent as being just over 98% on a clustered index. Time to rebuild! So I do an Alter Index with rebuild on the index and it reports success.
So I run sys.dm_db_index_physical_stats again and now it shows 100% for avg_fragmentation_in_percent on the index. When I use the GUI, look at index properties and view fragmentation, it shows 9.47% total fragmentation.
Why would there be a difference?
btw.....when I run sys.dm_db_index_physical_stats, I used the DETAILED option for scan level.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Are you able to post the CREATE TABLE and CREATE INDEX statements please?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 2

rgarrett 82118 (3/17/2010)
When I run sys.dm_db_index_physical_stats, I used the DETAILED option for scan level.

SSMS uses the SAMPLED scan level.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

rgarrett 82118 Old Hand Points: 344 More actions · Answer 3

CREATE TABLE [dbo].[syQueueXMLMessage](

[TrackingNbr] [uniqueidentifier] NOT NULL,

[XMLMessage] [nvarchar](3000) NOT NULL,

[UserID] [int] NOT NULL,

[DateAdded] [datetime] NOT NULL,

[ts] [timestamp] NOT NULL,

CONSTRAINT [syQueueXMLMessage_TrackingNbr_U_C_IDX] PRIMARY KEY CLUSTERED

(

[TrackingNbr] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]

) ON [PRIMARY]

Using DETAILED or SAMPLED scan level options on an index that was just rebuilt could cause a 90% discrepancy in the results?

I realize that using a uniqueidentifier is a terrible clustered index key. It fragments the table very quickly with inserts. This is a 3rd party app that I am trying to improve performance of.

Paul White SSC Guru Points: 150467 More actions · Answer 4

rgarrett 82118 (3/18/2010)
Using DETAILED or SAMPLED scan level options on an index that was just rebuilt could cause a 90% discrepancy in the results?

Seems so! 😉

Sorry to be so vague, but without access to your systems...you know?

I realize that using a uniqueidentifier is a terrible clustered index key. It fragments the table very quickly with inserts. This is a 3rd party app that I am trying to improve performance of.

It is good to know you are across that: yes a non-sequential GUID is an awful choice. Good luck with the performance improvements.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

rgarrett 82118 (3/18/2010)
CREATE TABLE [dbo].[syQueueXMLMessage]( [TrackingNbr] [uniqueidentifier] NOT NULL, [XMLMessage] [nvarchar](3000) NOT NULL, [UserID] [int] NOT NULL, [DateAdded] [datetime] NOT NULL, [ts] [timestamp] NOT NULL, CONSTRAINT [syQueueXMLMessage_TrackingNbr_U_C_IDX] PRIMARY KEY CLUSTERED ( [TrackingNbr] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY] ) ON [PRIMARY]
Using DETAILED or SAMPLED scan level options on an index that was just rebuilt could cause a 90% discrepancy in the results?
I realize that using a uniqueidentifier is a terrible clustered index key. It fragments the table very quickly with inserts. This is a 3rd party app that I am trying to improve performance of.

Note that PAD_INDEX is OFF, meaning upper level pages will fragment VERY quickly initially. This could be the cause of the discrepancy if there was virtually any activity between the index maintenance and the check of fragmentation.

Also, how much free space is in the database file where this index exists? without lots of free space (again the default settings kill you here) indexes cannot necessarily have contiguous space to drop into and index rebuild operations may not be as effective.

Oh, one more thing: 95% fill factor is WAY too high for a CI on non-sequential GUID unless you are rebuilding VERY frequently! I would consider testing it at 80% or even lower.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

rgarrett 82118 Old Hand Points: 344 More actions · Answer 6

Your comments address a separate issue that I will need to take care of. Very good points. Thank you.

I had overlooked pad_index being off but did notice the fill factor was quite high. This is indicative of a prevalent issue throughout the database application tables. I'll need to start addressing the pad_index issue to help minimize the fragmentation occurring in the indexes.

Yeah! Job security! 😀