SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


slow dm_db_index_physical_stats query


slow dm_db_index_physical_stats query

Author
Message
Steve Malley
Steve Malley
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 135
I have a query I use to report on index fragmentation.

SELECT TOP 25
db.name AS 'DatabaseName'
, SCHEMA_NAME(obj.schema_id) AS 'SchemaName'
, OBJECT_NAME(ps.OBJECT_ID) AS 'TableName'
, db_id() AS 'DatabaseID'
, ps.OBJECT_ID AS 'ObjectID'
, ps.index_id AS 'IndexID'
, ps.partition_number AS '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



GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86883 Visits: 45264
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
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19850 Visits: 17242
Number of indexes?

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
Steve Malley
Steve Malley
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 135
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
Sean Pearce
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1768 Visits: 3432
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

@SeanPearceSQL

About Me
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86883 Visits: 45264
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
Steve Malley
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 135
Thanks. That makes sense now. I appreciate your replies.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search