SQL Server. Работа с индексами через запросы SQL
Иногда возникают задачи по массовой обработке индексов, например, переносу индексов на другую базу. В этой статье рассмотрим как можно получить список всех индексов, получить 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 #RowCountsrebuild выполняется быстрее, но блокирует таблицу. Есть режим 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
Что еще посмотреть по SQL Server
SQL Server. Оптимизация запросов SQL. MS SQL Медленно работают запросы SELECT
Как найти медленные проблемные запросы SQL Server. Оптимизация быстродействия SQL Server
Где ставить индексы SQL Server
SQL Server. Оптимизация SQL запросов, план запроса в SQL Server. Советы по оптимизация запросов SQL
Решение проблем с большим кешем запросов SQL Server
Инструмент для анализа производительности запросов SQL Server Query Store
SQL Server Как найти запросы с проблемами (warning) в плане выполнения запроса (SQL Plan)
Выгода от использования Falcon Space
В 2-3 раза экономнее и быстрее, чем заказная разработка
Более гибкая, чем коробочные решения и облачные сервисы
Используйте готовые решения и изменяйте под свои потребности
- Шаг 1. Создать концепт проекта
- Шаг 2. Получить оценку бюджета (КП)
- Шаг 3. Заключить договор
- Шаг 4. Создать совместно техническое задание
- Шаг 5. Поэтапная реализация проекта