Get Index Fragmentation Cheaply
-
Is there a cheaper way to write this (from a query cost perspective)? It seems to churn quite a bit of DTU's on our Azure DB's and ends up on our Top Resource Consuming queries in Query Store.
SELECT '' AS 'Database',
dbschemas.[Name] AS 'Schema',
dbtables.[Name] AS 'Table',
dbindexes.[Name] AS 'Index',
indexstats.alloc_unit_type_desc AS 'AllocUnitTypeDesc',
indexstats.avg_fragmentation_in_percent AS 'AvgFragmentationPercent',
indexstats.page_count AS 'PageCount'
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.avg_fragmentation_in_percent > 70
AND indexstats.page_count > 1000Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/ -
I don't know if cutting down on the number of joined tables will help but it can't hurt. Here's an example of what I use pared down to what you seem to need. I changed the column names because I hate reserved word column names and quoted identifiers. Please feel free to change them to suit you. I also added one extra column. I suspect (I don't use the cloud) it's still going to be high in DTUs because it looks at the B-Tree of every index and blob you have.
SELECT DbName = QUOTENAME(DB_NAME(stat.database_id))
,SchemaName = QUOTENAME(OBJECT_SCHEMA_NAME(stat.object_id))
,TableName = QUOTENAME(OBJECT_NAME(stat.object_id))
,IndexName = QUOTENAME(idx.name)
,IndexType = stat.index_type_desc
,AllocType = stat.alloc_unit_type_desc
,FragPct = stat.avg_fragmentation_in_percent
,PageCnt = stat.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS stat
JOIN sys.indexes AS idx
ON idx.object_id = stat.object_id
AND idx.index_id = stat.index_id
WHERE stat.avg_fragmentation_in_percent > 0 --Change this to suit
AND stat.page_count > 1024 --(8MB)
;--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff, I’ll give it a shot
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/ - Jeff Moden - Thursday, June 7, 2018 6:22 PM
Yeah, this dropped the query cost by more than half. Thanks for your advice!
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/ -
Great. Ya just gotta love some of the intrinsic functions. Thanks for the feedback.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply