slow dm_db_index_physical_stats query

Question

slow dm_db_index_physical_stats query

Steve Malley

Ten Centuries

Points: 1204
More actions
October 16, 2012 at 11:34 am

#283100

I have a query I use to report on index fragmentation.
SELECT TOP 25
db.nameAS 'DatabaseName'
, SCHEMA_NAME(obj.schema_id)AS 'SchemaName'
, OBJECT_NAME(ps.OBJECT_ID)AS 'TableName'
, db_id()AS 'DatabaseID'
, ps.OBJECT_IDAS 'ObjectID'
, ps.index_idAS 'IndexID'
, ps.partition_numberAS 'PartitionNum'
, cast(ps.avg_fragmentation_in_percent as [dec](16,2))AS 'FragPercent'
, cast(ps.page_count*8192.0/1048576 as dec(16,2))AS 'PagesMB'
FROM sys.databases db
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'Limited') ps
ON db.database_id = ps.database_id
INNER JOIN sys.objects obj ON ps.object_id = obj.object_id
WHERE ps.avg_fragmentation_in_percent >= 10
ORDER BY
cast(ps.page_count*8192.0/1048576 as dec(16,2))DESC
,cast(ps.avg_fragmentation_in_percent as [dec](16,2))DESC
In some environments this runs quickly. In others it is *slow*. Does anyone know why this would be the case? Had similar experience

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Size of tables, throughput of IO subsystem.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 233821 More actions · Answer 2

Number of indexes?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Steve Malley Ten Centuries Points: 1204 More actions · Answer 3

Since I am only accessing the system catalog and a DMV, I would expect that performance should be consistent and fast. I find that it is not. It may just be the volume of records, but it is a bit weird that these objects are responsive in some environments, but not responsive in others.

Sean Pearce SSCoach Points: 15999 More actions · Answer 4

Steve Malley (10/17/2012)
Since I am only accessing the system catalog and a DMV, I would expect that performance should be consistent and fast. I find that it is not. It may just be the volume of records, but it is a bit weird that these objects are responsive in some environments, but not responsive in others.

http://www.sqlskills.com/blogs/paul/post/Inside-sysdm_db_index_physical_stats.aspx

The idea of the DMV is to display physical attributes of indexes (and the special case of a heap) - to do this it has to scan the pages comprising the index, calculating statistics as it goes. Many DMVs support what's called predicate pushdown, which means if you specify a WHERE clause, the DMV takes that into account as it prepares the information. This DMV doesn't. If you ask it for only the indexes in the database that have logical fragmentation > 30%, it will scan all the indexes, and then just tell you about those meeting your criteria. It has to do this because it has no way of knowing which ones meet your criteria until it analyzes them - so can't support predicate pushdown.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Steve Malley (10/17/2012)
Since I am only accessing the system catalog and a DMV, I would expect that performance should be consistent and fast.

Except that DMV is not a view into memory (as most of them are), but a way to go and read the non-leaf levels of every table in the DB and calculate results based off that. Hence, the larger the DB and the slower the IO subsystem, the slower that query will run

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Malley Ten Centuries Points: 1204 More actions · Answer 6

Thanks. That makes sense now. I appreciate your replies.