SQL: Получить список всех индексов БД и процент фрагментации
Во время анализа состояния базы данных, и возможного анализа ее производительности, стоит обратить внимание на состояние ее индексов и их количества.
а при необходимости этот запрос успешно редактируется, добавлением необходимых полей.
Скрипт можно скопивать из текста выше или скачать файлом по ссылке:
Получить список индексов базы данных 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;
Результат выполнения скрипта:Скрипт можно скопивать из текста выше или скачать файлом по ссылке:
rebuild-index-db.zip
[983 b] (cкачиваний: 185)
Похожие статьи:
24 ноябрь 2021, Среда
SQL: Saving Changes is not permitted
04 декабрь 2020, Пятница
PowerShell: Получить список DNS имен по списку IP адресов (и наоборот)
03 апрель 2019, Среда
SQL: Как определить размер таблиц в БД MS SQL
13 октябрь 2018, Суббота
Как определить размер таблиц в БД MySQL
01 ноябрь 2017, Среда
SQL: Перенос логинов и паролей SQL сервера
Комментарии: