Во время анализа состояния базы данных, и возможного анализа ее производительности, стоит обратить внимание на состояние ее индексов и их количества.

Получить список индексов базы данных MS SQL

Для получения полного списка индексов базы данных, можно использовать System View sys.indexes, которое - покажет список всех индексов в выбранной базе данных. Результат запроса вида: select * from sys.indexes выглядит не очень читаемо, но если добавить информацию из других предствлений (View), то можно получить список с наглядной информацией. Например, запрос: 

USE <Имя БД>

select i.[name] as index_name,
    substring(column_names, 1, len(column_names)-1) as [columns],
    case when i.[type] = 1 then 'Clustered index'
        when i.[type] = 2 then 'Nonclustered unique index'
        when i.[type] = 3 then 'XML index'
        when i.[type] = 4 then 'Spatial index'
        when i.[type] = 5 then 'Clustered columnstore index'
        when i.[type] = 6 then 'Nonclustered columnstore index'
        when i.[type] = 7 then 'Nonclustered hash index'
        end as index_type,
    case when i.is_unique = 1 then 'Unique'
        else 'Not unique' end as [unique],
    schema_name(t.schema_id) + '.' + t.[name] as table_view, 
    case when t.[type] = 'U' then 'Table'
        when t.[type] = 'V' then 'View'
        end as [object_type]
from sys.objects t
    inner join sys.indexes i
        on t.object_id = i.object_id
    cross apply (select col.[name] + ', '
                    from sys.index_columns ic
                        inner join sys.columns col
                            on ic.object_id = col.object_id
                            and ic.column_id = col.column_id
                    where ic.object_id = t.object_id
                        and ic.index_id = i.index_id
                            order by key_ordinal
                            for xml path ('') ) D (column_names)
where t.is_ms_shipped <> 1
and index_id > 0
order by i.[name]

Вернет результат подобный тому что показан ниже:
а при необходимости этот запрос успешно редактируется, добавлением необходимых полей.

Получить состояние фрагментации индексов базы данных MS SQL

При работе с данными (при вставке, обновлении или удалении), также происходит изменение информации хранящейся в индексах. С точки зрения SQL, фрагментация выглядит как несоответствие логического порядка индекса, физическому порядку размещения данных, и чем больший процент фрагментации в индексе, тем существеннее это отражается на производительности. В целом, это нормальный рабочий момент, за которым надо следить, настраивая планы обслуживания. Но иногда бывает полезно в целом узнать "среднюю темпиратуру по больнице", чтобы создать новые или отредактировать старые планы обслуживания.Ниже, приведен пример скрипта, которые отображает список всех индексов БД в порядке по убыванию уровня фрагментации:

    declare @DB sysname = <Имя БД>;

    select s.name schema_name, t.name TableName, i.name IndexName, d.avg_fragmentation_in_percent Fragmentation
    from   sys.dm_db_index_physical_stats( DB_ID(@DB), null, null, null, null) d
           inner join sys.tables  t on d.object_id = t.object_id
           inner join sys.schemas s on t.schema_id = s.schema_id
           inner join sys.indexes i on d.object_id = i.object_id AND d.index_id = i.index_id
    where  d.index_id > 0 and d.page_count > 8
    order by fragmentation desc

Результат:

Перестроить (Rebuild) все индексы в базе данных

Для перестроения всех индексов базы данных, можно использовать чуть более сложный скрипт, ниже

begin
    declare @databaseName sysname = N'IPScan';
    declare @rebuildFloor float = 40;
    declare @schemaName sysname;
    declare @tableName  sysname;
    declare @indexName  sysname;
    declare @fragmentation float;
    declare @command nvarchar(500);

    print N'Начало перестроения индекса: ' + convert( nvarchar(100), SYSDATETIME(), 20 );
    print N'-------------------------------------------------------------------------';

    declare indexCursor cursor fast_forward local for
    select s.name schema_name, t.name table_name, i.name index_name, d.avg_fragmentation_in_percent fragmentation
    from   sys.dm_db_index_physical_stats( DB_ID(@databaseName), null, null, null, null) d
           inner join sys.tables  t on d.object_id = t.object_id
           inner join sys.schemas s on t.schema_id = s.schema_id
           inner join sys.indexes i on d.object_id = i.object_id AND d.index_id = i.index_id
    where  d.index_id > 0
           and d.avg_fragmentation_in_percent > 10
           and d.page_count > 8

    open indexCursor;

    while( 1=1 )
    begin
        fetch next from indexCursor into @schemaName, @tableName, @indexName, @fragmentation;
        if @@FETCH_STATUS <> 0 break;

        begin try
            set @command = N'ALTER INDEX ' + @indexName + N' ON ' + @databaseName + N'.' + @schemaName + N'.' + @tableName;
            if @fragmentation < @rebuildFloor
            begin
                set @command = @command + N' REORGANIZE;';
                set @command = @command + N' UPDATE STATISTICS ' + @databaseName + N'.' + @schemaName + N'.' + @tableName + N' ' + @indexName + N';';
            end
            else
            begin
                set @command = @command + N' REBUILD WITH (ONLINE = ON); ';
            end;
           
            --print @command;
            exec (@command);


            print N'INDEX ' + @indexName + N'ON ' + @databaseName + N'.' + @schemaName + N'.' + @tableName + N' Обработан';
        end try
        begin catch
            print N'ERROR REBUILD INDEX ' + @indexName;
            print N'ERROR MESSAGE: ' + ERROR_MESSAGE();
        end catch
    end;

    print N'-------------------------------------------------------------------------';
    print N'Перестроение индексов завершено: ' + convert( nvarchar(100), SYSDATETIME(), 20 );
    close indexCursor;
    deallocate indexCursor;
end;

Результат выполнения скрипта:


Скрипт можно скопивать из текста выше или скачать файлом по ссылке: