Index Fragmentation Monitoring: Can I make this query faster?

Question

I have a query that is run against each database on two of my servers using a PowerShell script to initiate the script and throw the results into a CSV thus the need to have temp tables.

Is there a way I can limit what I am returning any further then I already have? I tried adding my WHERE clauses to the temp table I am iterating through, as WHERE clauses on sys.dm_db_index_physical_stats do not actually work as a predicate, but rather just a filter after the fact according to MS docs.

DECLARE @object_id int
DECLARE @index_id int
DECLARE @partition_number int
DECLARE @DatabaseID int
SET @DatabaseID = DB_ID()

SELECT [object_id], [index_id], [partition_number], [rows] INTO #tempTest FROM (
SELECT indexes.[object_id], indexes.[index_id], partitions.[partition_number], partitions.[rows] FROM sys.tables AS tables
INNER JOIN sys.indexes AS indexes on tables.[object_id] = indexes.[object_id]
INNER JOIN sys.schemas AS schemas on tables.[schema_id] = schemas.[schema_id]
INNER JOIN sys.partitions AS partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.[index_id] = partitions.[index_id]
WHERE schemas.[name] <> 'Audit'
AND indexes.[type] IN(1,2,3,4)
AND indexes.is_disabled = 0
AND indexes.is_hypothetical = 0 
AND partitions.[rows] > 1000000) AS x

CREATE TABLE #tempTest2 (NK varchar(35), ServerName varchar(35), TimeStamp datetime, DatabaseName varchar(50), SchemaName varchar(50), ObjectName varchar (250), IndexName varchar (250),
ObjectType varchar(30), IndexType varchar (30), PartitionNumber int, PageCount bigint, avg_fragmentation_in_percent float)


DECLARE db_cursor CURSOR FOR

SELECT [object_id], [index_id], [partition_number] FROM #tempTest

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @object_id, @index_id, @partition_number

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO #tempTest2 (NK, ServerName, TimeStamp, DatabaseName, SchemaName, ObjectName, IndexName, ObjectType, IndexType, PartitionNumber, [PageCount], avg_fragmentation_in_percent)

       SELECT CONCAT(objects.[object_id], indexes.[index_id]) AS NK,
       @@SERVERNAME AS ServerName,
       GETDATE() AS TimeStamp,
       DB_NAME(@DatabaseID) AS DatabaseName,
       schemas.[name] AS SchemaName,
       objects.[name] AS ObjectName,
       indexes.[name] AS IndexName,
       objects.type_desc AS ObjectType,
       indexes.type_desc AS IndexType,
       dm_db_index_physical_stats.partition_number AS PartitionNumber,
       dm_db_index_physical_stats.page_count AS [PageCount],
       dm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFragmentationInPercent
FROM sys.dm_db_index_physical_stats (DB_ID(), @object_id, @index_id, @partition_number, 'LIMITED') dm_db_index_physical_stats
INNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] AND dm_db_index_physical_stats.index_id = indexes.index_id
INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0
--AND indexes.[type] IN(1,2,3,4)
--AND indexes.is_disabled = 0
--AND indexes.is_hypothetical = 0
AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
AND dm_db_index_physical_stats.index_level = 0
AND dm_db_index_physical_stats.page_count >= 1000
--AND schemas.name <> 'Audit'
    FETCH NEXT FROM db_cursor INTO @object_id, @index_id, @partition_number
END

CLOSE db_cursor
DEALLOCATE db_cursor

SELECT * FROM #tempTest2

I think I am going to try moving the WHERE objects.[type] IN('U','V') AND objects.is_ms_shipped = 0 to the temp table above and comment it out from the below section.

I have already read:

• SQL Server Index Maintenance – You’re Doing It Wrong by Sean Gallardy
• Stop Worrying About SQL Server Fragmentation by Brent Ozar
• How much will it cost or save to rebuild that index? by Jeff Moden

Answer 1

I would suggest reducing the number of joins in your loop. You are already hitting sys.indexes and sys.schemas in the your first query to build your temp table. It would be advisable to just grab whatever additional fields you need and throw them into your temp table as well.

You want to make your query that you are looping through in your cursor as efficient as humanly possible. You might even want to try and get the data you needed from sys.objects in there too.

Speaking of cursor you should consider using a fast_forward cursor as well. It has a smaller footprint and is a bit more efficient.

Answer 2

Have you got timings for just getting everything from the database in one swoop

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')

and store everything into a table ? Probably better than running the query again and again inside the cursor.