Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

slow dm_db_index_physical_stats query Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 11:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 8, 2013 8:10 AM
Points: 30, 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



Post #1373432
Posted Tuesday, October 16, 2012 12:40 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:18 PM
Points: 42,449, Visits: 35,504
Size of tables, throughput of IO subsystem.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1373468
Posted Tuesday, October 16, 2012 12:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 6,175, Visits: 13,319
Number of indexes?

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

"Ya can't make an omelette without breaking just a few eggs"
Post #1373478
Posted Wednesday, October 17, 2012 7:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 8, 2013 8:10 AM
Points: 30, 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.


Post #1373778
Posted Wednesday, October 17, 2012 7:36 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:51 AM
Points: 825, Visits: 2,659
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
Post #1373786
Posted Wednesday, October 17, 2012 7:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:18 PM
Points: 42,449, Visits: 35,504
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 2008, MVP
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

Post #1373801
Posted Thursday, October 18, 2012 7:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 8, 2013 8:10 AM
Points: 30, Visits: 135
Thanks. That makes sense now. I appreciate your replies.


Post #1374324
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse