Query to see Index Fragmentation on all databases without using SP or temp tables

Question

I am trying to compile a index fragmentation report on all databases, in a multi-server environment. The query will be executed via an openquery command, so I would like to make it as simple as possible, without using stored procedures, temporary tables, etc.

I am aware the follow can bring back all indexes on all databases

select * from sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'SAMPLED')

but as soon as we join it to sys.indexes, sys.objects, we restrict out system wide indexes and rather focus on indexes in the currently executing database.

Is there a way around this, so I can get the following output for all databases:

Database Name, Index Name, Index Type, Number of Pages, Percentage of Fragmentation.

Answer 1

It's not possible to achieve without using temp table as the DMVs/DMFs that we are using are database scoped and requirement is server scoped, and i don't see any difficulties or complication using temp table as follows:

Declare @Tbl table (ServerName varchar(128), DBName varchar(128), SchemaName varchar(128), TableName varchar (100), IndexName varchar (100), FragPercent float, IndexType tinyint, IsPrimaryKey bit);

Insert into @Tbl
exec SP_MSforeachdb @command1 = 
            'use [?];
                select  @@Servername, 
                        DB_NAME(),
                        sc.name as SchemaName,
                        object_name (s.object_id) as TableName, 
                        I.name, 
                        s.avg_fragmentation_in_percent, 
                        I.type, 
                        I.is_primary_key
                from sys.dm_db_index_physical_stats (DB_ID (), NULL, NULL, NULL, ''LIMITED'') as S
                    join sys.indexes as I on s.object_id = I.object_id and s.index_id = I.index_id
                    join sys.objects as O on s.object_id = O.object_id 
                    join sys.schemas as sc on O.schema_id = sc.schema_id
                where o.type = ''U'' and avg_fragmentation_in_percent > 20 and (I.name is not null) 
                ORDER BY  avg_fragmentation_in_percent DESC'
select * from @Tbl
go

Answer 2

You can't get the index information by joining to sys.indexes/sys.objects as you've found because these DMVs are database specific, so only show the information related to the current database.

You can get some of the information you need using the below query - it returns Table Name instead of Index Name but that might be suitable:

SELECT DB_NAME(database_id) AS [Database Name],
    OBJECT_NAME(object_id, database_id) AS [Table Name], 
    [index_type_desc] AS [Index Type], 
    page_count AS [Number Of Pages],
    [avg_fragmentation_in_percent] AS [Percentage Of Fragementation]
FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'SAMPLED')

Otherwise, you're going to have to deploy a procedure to your SQL Servers to fetch the information for Index Name.

Answer 3

The first parameter is Database ID that you could found in sys.databases table.

Below query may help you for suggestions:

--SELECT * FROM sys.databases
DECLARE @dbid int = 9

SELECT  dbschemas.[name] as 'Schema',
        dbtables.[name] as 'Table',
        dbindexes.[name] as 'Index',
        indexstats.avg_fragmentation_in_percent,
        indexstats.page_count,
        CASE WHEN indexstats.page_count <= 100 OR indexstats.avg_fragmentation_in_percent < 10 THEN '--Do nothing. It''s good'
             WHEN indexstats.avg_fragmentation_in_percent BETWEEN 10 AND 30 THEN 'ALTER INDEX '+dbindexes.[name]+' ON '+dbschemas.[name]+'.'+dbtables.[name]+' REORGANIZE;'
             ELSE 'ALTER INDEX '+dbindexes.[name]+' ON '+dbschemas.[name]+'.'+dbtables.[name]+' REBUILD;'
        END
FROM    sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, NULL) AS indexstats
JOIN    sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
JOIN    sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
JOIN    sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id
WHERE   indexstats.database_id = @dbid
ORDER BY indexstats.avg_fragmentation_in_percent DESC