Как найти индекс в ms sql

Индексы таблиц сильно влияют на работу с базой данных. Так, отсутствие необходимого индекса может приводить к:

• • чтению большего количества строк данных
  • увеличению времени выполнения запроса
  • появлению избыточных блокировок
  • и другим негативным последствиям

В данной статье мы рассмотрим как можно найти недостающие индексы с помощью динамических административных функций MS SQL Server.

Информация о динамических функциях

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

В данной статье мы воспользуемся тремя динамическими административными функциями (DMF): dm_db_missing_index_group_stats, dm_db_missing_index_groups, dm_db_missing_index_details

Запрос поиска недостающих индексов и анализ его результата

Следующий запрос выведет всю необходимую в дальнейшем информацию:

Выражение avg_total_user_cost * avg_user_impact * (user_seeks + user_scans), использованное в запросе, соответствует оценке выигрыша при добавлении отсутствующего индекса. Файл запроса можно скачать во вложениях к статье.

Выполним запрос и проанализируем его результаты на простом примере.

Как видно, отсутствует индекс в таблице AccumRg23573, при этом запрос, для которого необходим индекс, выполняется с предикатом равенства по полю Fld23580RRef. В поле user_seeks указано что этот индекс мог бы быть использован 750 раз в целях поиска по индексу, avg_user_impact говорит о том что средний процент выигрыша равен 99,99%. Так же имеет смысл обратить внимание на поле last_user_seek, оно указывает на дату и время последнего пользовательского запроса, который мог бы использовать отсутствующий индекс для поиска. Если последний раз подходящий запрос был давно, возможно, индекс будет использовать редко и необходимости в нем нет — необходимо оценить перед добавлением индекса. Теперь воспользуемся обработкой выводящей структуру хранения базы данных в терминах 1С:Предприятия (из статьи «Получение информации о структуре хранения базы данных в терминах 1С:Предприятие и СУБД»).

С помощью обработки мы видим что индекса не хватает в основной таблице регистра накопления ДДС, а также видим что условие на равенство используется по полю ДоговорКонтрагента. При этом в таблице существуют индексы по Периоду, Регистратору, СтатьеДДС, Проекту. Индекса по полю ДоговорКонтрагента нет, хотя условие запроса именно на равенство по этому полю, а это значит что системе необходимо будет сканировать всю таблицу при выполнении запроса. Давайте добавим отсутствующий индекс.

Статистика использования индекса

И так, индекс добавлен, но теперь нам необходимо оценить результат своих действий. Для этих целей воспользуемся еще одним запросом с динамическими административными функциями, о которых будет рассказано в следующей статье. Стоит упомянуть что выполнять его стоит не сразу же, а через некоторое время работы пользователей в базе данных для того чтобы накопилась статистика.

Оценив значения в колонке user_seeks можно сделать вывод что в данной системе этот индекс используется достаточно часто, а эффективность от его использования высока (была оценена первым запросом).

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

Получить список индексов базы данных 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, фрагментация выглядит как несоответствие логического порядка индекса, физическому порядку размещения данных, и чем больший процент фрагментации в индексе, тем существеннее это отражается на производительности. В целом, это нормальный рабочий момент, за которым надо следить, настраивая планы обслуживания. Но иногда бывает полезно в целом узнать «среднюю темпиратуру по больнице», чтобы создать новые или отредактировать старые планы обслуживания.

Если выполнялось сжатие (shrink) базы данных или отдельных файлов БД, то после этого, с высокой долей вероятности, потребуется выполнение операции перестроения индексов. Иначе, можно получить существенную деградацию производительности базы данных.

Ниже, приведен пример скрипта, которые отображает список всех индексов БД в порядке по убыванию уровня фрагментации:

    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;

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

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

Иногда возникают задачи по массовой обработке индексов, например, переносу индексов на другую базу. В этой статье рассмотрим как можно получить список всех индексов, получить SQL индекса и массово перестроить индексы. 

Как получить все индексы базы данных SQL Server с их определением

declare @dbTablePrefix nvarchar(256) = '' 

SELECT 
 'if not exists( SELECT * FROM sys.indexes WHERE name='''+I.name+''' ) begin ' +    CHAR(13) + CHAR(10) +     --AND object_id = OBJECT_ID('Schema.YourTableName')
	'  CREATE ' +
       CASE 
            WHEN I.is_unique = 1 THEN ' UNIQUE '
            ELSE ''
       END +
       I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' +
       I.name + ' ON ' +
       SCHEMA_NAME(T.schema_id) + '.' + T.name + ' ( ' +
       KeyColumns + ' )  ' +
       ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +
       ISNULL(' WHERE  ' + I.filter_definition, '') + ' WITH ( ' +
       CASE 
            WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '
            ELSE ' PAD_INDEX = OFF '
       END + ',' +
       'FILLFACTOR = ' + CONVERT(
           CHAR(5),
           CASE 
                WHEN I.fill_factor = 0 THEN 100
                ELSE I.fill_factor
           END
       ) + ',' +
       -- default value 
       'SORT_IN_TEMPDB = OFF ' + ',' +
       CASE 
            WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '
            ELSE ' IGNORE_DUP_KEY = OFF '
       END + ',' +
       CASE 
            WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '
            ELSE ' STATISTICS_NORECOMPUTE = ON '
       END + ',' +
       ' ONLINE = OFF ' + ',' +
	  -- ' drop_existing = on ' + ',' +
	   CASE 
            WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '
            ELSE ' ALLOW_ROW_LOCKS = OFF '
       END + ',' +
       CASE 
            WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '
            ELSE ' ALLOW_PAGE_LOCKS = OFF '
       END + ' ) ON [' +
       DS.name + ' ] ' +  CHAR(13) + CHAR(10) + 
	   '  print ''Create index '+ I.name +'''' +  CHAR(13) + CHAR(10) + 
	   'end  -- if exists' +   CHAR(13) + CHAR(10) + 'GO '      [CreateIndexScript],
	   schema_name(t.schema_id) + '.' + t.[name] as table_view, 
	i.[name] as index_name,
	KeyColumns,
	IncludedColumns,
   -- 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],
    
    case when t.[type] = 'U' then 'Table'
        when t.[type] = 'V' then 'View'
        end as [object_type]
FROM   sys.indexes I
       JOIN sys.tables T
            ON  T.object_id = I.object_id
       JOIN sys.sysindexes SI
            ON  I.object_id = SI.id
            AND I.index_id = SI.indid
       JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , [' + C.name + ']' + CASE 
                                                                       WHEN MAX(CONVERT(INT, IC1.is_descending_key)) 
                                                                            = 1 THEN 
                                                                            ' DESC '
                                                                       ELSE 
                                                                            ' ASC '
                                                                  END
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column = 
                                                          0
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id
                                          ORDER BY
                                                 MAX(IC1.key_ordinal) 
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) KeyColumns
                           FROM   sys.index_columns IC2 
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp3
            )tmp4
            ON  I.object_id = tmp4.object_id
            AND I.Index_id = tmp4.index_id
       JOIN sys.stats ST
            ON  ST.object_id = I.object_id
            AND ST.stats_id = I.index_id
       JOIN sys.data_spaces DS
            ON  I.data_space_id = DS.data_space_id
       JOIN sys.filegroups FG
            ON  I.data_space_id = FG.data_space_id
       LEFT JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , [' + C.name + ']'
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column = 
                                                          1
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id 
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) IncludedColumns
                           FROM   sys.index_columns IC2 
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp1
                WHERE  IncludedColumns IS NOT NULL
            ) tmp2
            ON  tmp2.object_id = I.object_id
            AND tmp2.index_id = I.index_id
WHERE  I.is_primary_key = 0
       AND I.is_unique_constraint = 0
       AND (@dbTablePrefix = '' or t.[name] like  @dbTablePrefix +  '%') --Comment for all tables  I.Object_id = object_id(@dbTable)
           --AND I.name = 'IX_Address_PostalCode' --comment for all indexes 
		and t.is_ms_shipped <> 1 
		   
order by table_view
 

Если указать @dbTablePrefix, то мы получим только индексы на таблицы, начинающиеся с  заданного префикса. 

В результате выполнения скрипта мы получим скрипты вида: 

if not exists( SELECT * FROM sys.indexes WHERE name='ind_apiActions_code' ) begin 
  CREATE NONCLUSTERED INDEX ind_apiActions_code ON dbo.as_api_actions (  [code] ASC  )   
WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , 
IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , 
ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
  print 'Create index ind_apiActions_code'
end  
GO 

Копируем определения индексов и выполняем массово на другой базе. Если такой индекс был, то он пропускается. Если нет какого-то столбца или таблицы, то будет просто ошибка, но другие индексы создадутся. 

Поиск дубликатов индексов SQL Server

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

-- Ищем дубликаты индексов (создает нагрузку на процессор)
select t1.tablename,t1.indexname,t1.columnlist,t2.indexname,t2.columnlist from
   (select distinct object_name(i.object_id) tablename,i.name indexname,
             (select distinct stuff((select ', ' + c.name
                                       from sys.index_columns ic1 inner join 
                                            sys.columns c on ic1.object_id=c.object_id and 
                                                             ic1.column_id=c.column_id
                                      where ic1.index_id = ic.index_id and 
                                            ic1.object_id=i.object_id and 
                                            ic1.index_id=i.index_id
                                      order by index_column_id FOR XML PATH('')),1,2,'')
                from sys.index_columns ic 
               where object_id=i.object_id and index_id=i.index_id) as columnlist
       from sys.indexes i inner join 
    	    sys.index_columns ic on i.object_id=ic.object_id and 
                                    i.index_id=ic.index_id inner join
            sys.objects o on i.object_id=o.object_id 
      where o.is_ms_shipped=0) t1 inner join
   (select distinct object_name(i.object_id) tablename,i.name indexname,
             (select distinct stuff((select ', ' + c.name
                                       from sys.index_columns ic1 inner join 
                                            sys.columns c on ic1.object_id=c.object_id and 
                                                             ic1.column_id=c.column_id
                                      where ic1.index_id = ic.index_id and 
                                            ic1.object_id=i.object_id and 
                                            ic1.index_id=i.index_id
                                      order by index_column_id FOR XML PATH('')),1,2,'')
                from sys.index_columns ic 
               where object_id=i.object_id and index_id=i.index_id) as columnlist
       from sys.indexes i inner join 
    	    sys.index_columns ic on i.object_id=ic.object_id and 
                                    i.index_id=ic.index_id inner join
            sys.objects o on i.object_id=o.object_id 
 where o.is_ms_shipped=0) t2 on t1.tablename=t2.tablename and 
       substring(t2.columnlist,1,len(t1.columnlist))=t1.columnlist and 
       (t1.columnlist<>t2.columnlist or 
         (t1.columnlist=t2.columnlist and t1.indexname<>t2.indexname))

Далее можно ручками удалить ненужные индексы в базе. 

Реорганизация или перестройка индексов в базе SQL Server

Вы можете сделать либо reorganize либо rebuild для всех индексов в базе. 

Для этого можно использовать следующий скрипт: 

-- Скрипт реорганизует индексы по всем таблицам


IF OBJECT_ID(N'tempdb..#RowCounts') IS NOT NULL
BEGIN
	DROP TABLE #RowCounts
END
GO

declare @minRows int =400 -- перестройка индекса выполняется только для таблиц где больше N строк

CREATE TABLE #RowCounts(NumberOfRows BIGINT,TableName VARCHAR(128))

EXEC sp_MSForEachTable 'INSERT INTO #RowCounts
SELECT COUNT_BIG(*) AS NumberOfRows,
''?'' as TableName FROM ?'

select  TableName, NumberOfRows from #RowCounts where NumberOfRows>@minRows


---SELECT TableName,NumberOfRows FROM #RowCounts ORDER BY NumberOfRows DESC,TableName
declare @TableName nvarchar(256)
declare @NumberOfRows int
declare cur CURSOR LOCAL for
select  TableName, NumberOfRows from #RowCounts where NumberOfRows>@minRows

open cur

fetch next from cur into @TableName, @NumberOfRows

while @@FETCH_STATUS = 0 BEGIN
print @TableName
 -- Rebuild (блочит таблицу, но быстрее идет)    или   REORGANIZE (дольше. но не блочит таблицу)
EXEC ('ALTER INDEX ALL ON ' +@TableName + ' Rebuild  ;')
fetch next from cur into @TableName, @NumberOfRows
END

close cur
deallocate cur

DROP TABLE #RowCounts

rebuild выполняется быстрее, но блокирует таблицу. Есть режим with ONLINE,но он работает только для Enterprise версии SQL Server. Фактически это пересоздание индекса. 

reorganize выполняется дольше, но не блокирует таблицу базы данных. 

В скрипте есть настройка @minRows — минимальное количество строк в таблицах, для которых будет перестроен индекс. 

Где нужно ставить индексы в SQL Server, какие индексы можно удалить из базы данных SQL Server

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

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

--Поиск, где можно установить индексы:
select d.name AS DatabaseName, mid.*
from sys.dm_db_missing_index_details mid
join sys.databases d ON mid.database_id=d.database_id
 

--Убрать лишние индексы:
--там где user_updates больше чем user_lookup - можно удалить индексы
SELECT d.name, t.name, i.name, ius.*
FROM sys.dm_db_index_usage_stats ius
JOIN sys.databases d ON d.database_id = ius.database_id
JOIN sys.tables t ON t.object_id = ius.object_id
JOIN sys.indexes i ON i.object_id = ius.object_id AND i.index_id =
ius.index_id
ORDER BY user_updates DESC