По материалам статьи Luis
Martin: How
to Identify Non-Active SQL Server Indexes
Перевод Виталия Степаненко
Для администраторов баз данных регулярной задачей является просматривание баз данных и поиск различных путей увеличения их производительности. В то время как добавление новых или улучшение индексов базы данных является одним из основных путей повышения производительности, повышение производительности через удаление неиспользуюемых индексов или определение слишком больших индексов, которые потребляют много ресурсов SQL Server. Неиспользуемые индексы снижают производительность выполнения команд INSERT, UPDATE и DELETE, и приводят к лишним дисковым операциям. Поэтому, чем больше лишних индексов мы сможем удалить, тем будет лучше.
Кроме того, часто бывает полезным определить очень большие индексы и установить, правильно ли они используются. Непродуманный состав очень большого индекса может вызывать те же проблемы, что и неиспользуемые индексы.
Возникает сложный вопрос - как мы узнаем, какие индексы используются, а какие нет? И как мы можем просто определить очень большие индексы? Здесь у SQL Server имеется не очень много автоматизированных средств, и часто приходится определять неиспользуемые или слишком большие индексы самостоятельно.
Метод, который предлагается в этой статье для определения неиспользуемых и очень больших индексов, прост, но он требует некоторого времени и терпения.
Нашим главным инструментом является Profiler. С его помощью мы соберем статистику по активности базы данных за по крайней мере 4-5 дней. Если временные рамки не критичны, то чем больше времени будет собираться статистика, тем лучше - это поможет удостовериться, что редко используемые индексы не будут определены как неиспользуемые и, таким образом, не будут случайно удалены.
События, которые мы будем собирать с помощью Profiler, включают:
Stored Procedures:
RPC:Completed
SP:SmtmCompleted
TSQL:
SQL:BatchCompleted
SQL:StmtCompleted
Также рекомендуется выбрать следующие столбцы:
ApplicationName
EventClass
TextData
Duration
LoginName
Reads
NTUserName
Чтобы уменьшить количество событий, собираемых Profiler, рекомендуется отбирать только события с продолжительностью 50 миллисекунд и дольше. Это все равно приведет к созданию достаточно большого файла трассировки, поэтому нужно удостовериться, что эти данные не будут храниться на вашем рабочем SQL Server, а также что у вас достаточно дискового пространства для файла трассировки.
Создание отчета по активным индексам
Следующим шагом нам нужно будет проанализировать полученный из Profiler файл трассировки, используя Index Tuning Wizard. Убедитесь, что используете опцию "Keep all existing indexes" и режим настройки индексов "Thorough". В "Advanced Options" уберите флажок "Limit number of workload queries to sample".
Как вы понимаете, выполнение такого анализа приведет к дополнительной нагрузке на ваш рабочий SQL Server. Убедитесь, что выполняете анализ в то время, когда ваш рабочий SQL Server не слишком загружен.
Когда работа Index Tuning Wizard завершена, нужно будет взглянуть на Index Usage Report, как показано ниже. Стоит отметить, что этот отчет показывает, какие индексы были использованы, а какие нет. Он также показывает размер каждого индекса.
На этом месте мы можем остановиться и воспользоваться этими результатами как основой для удаления неиспользуемых индексов и определения больших индексов. Но если у нас есть множество таблиц, то можно выполнить следующие дополнительные шаги.
Предполагая, что мы хотим автоматизировать дополнительный анализ, нужно сохранить этот отчет в текстовом формате, используя кнопку "Save", после этого мы сможем импортировать этот текстовый файл в таблицу SQL Server.
В любой выбранной вами базе данных создайте следующую таблицу:
CREATE TABLE dbo.Analysis ( Table to varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL, Indice to varchar (100) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL, Uso to varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL, Peso int NOT NULL, Base to varchar (15) COLLATE SQL_Latin1_General_CP1_CI_AI NULL, Date datetime NOT NULL ) ON PRIMARY
Когда таблица создана, вы можете использовать DTS Import для загрузки текстового файла в таблицу SQL. При импортировании текстового файла выберите TAB как разделитель столбцов и таблицу Analysis для импорта.
Когда текстовый файл импортирован, вы можете использовать следующую хранимую процедуру для вывода списка неактивных индексов.
CREATE PROCEDURE Indices_Inactivos AS declare @Base varchar(15), @Tabla varchar(50), @Indice varchar(100), @BaseAnt varchar(15), @TablaAnt varchar(50) declare IndicesInactivos cursor for select Base, Table, Indice from Analysis where Indice like ' [ [ ]IX% ' group by Base, Table, Indice having sum(convert(money, replace(Uso,',','.'))) = 0 open IndicesInactivos fetch next from IndicesInactivos into @Base, @Tabla, @Indice while (@@FETCH_STATUS < > -1) begin if @Base < > @BaseAnt begin Print ' BASES: ' + @Base select @BaseAnt = @Base select @TablaAnt = '' end if @Tabla < > @TablaAnt begin Print ' TABLE: ' + @Tabla Print ' INDICES: ' select @TablaAnt = @Tabla end Print ' ' + @Indice fetch next from IndicesInactivos into @Base, @Tabla, @Indice continue end close IndicesInactivos deallocate IndicesInactivos GO
Эта хранимая процедура выполняется в SQL Analyzer без параметров. В результате работы процедуры выводится отчет по неиспользуемым индексам.
BASE: EXAMPLE TABLE: [ dbo].[APLICACIONES ] INDICES: [ IXC03.10.31_APLICACIONES_NroTrans ] TABLE: [ dbo].[AWItemsAcumHistoricos ] INDICES: [ IX_AWItemsAcumHistoricosFecha ] [ IX_AWItemsAcumHistoricosItem ] [ IXC_AWItemsAcumHistoricos_Fecha_CodItm ] TABLE: [ dbo].[CAJASREG ] INDICES: [ IXCP04.01.16_CAJASREG_CodCaj2_NroTrans ] TABLE: [ dbo].[CHEQUES ] INDICES: [ IXC04.01.09_CHEQUES_FechaVto ] [ IXCP04.01.16_CHEQUES_CodCtacte_NroTrans_Secuencia_NroTransegr_Tipo_Directo ]
Ниже показана еще одна хранимая процедура. Она создана для вывода списка используемых индексов в трассировке Profiler. Мы будем использовать эти результаты чтобы узнать, какие индексы являются очень большими.
CREATE PROCEDURE Indices_Usados AS declare @Base varchar(15), @Tabla varchar(50), @Indice varchar(100), @BaseAnt varchar(15), @TablaAnt varchar(50), @Uso varchar(50) declare IndicesUsados cursor for select Base, Table, Indice, to convert(varchar, max(convert(money, replace(Uso, ', ', '.'))))as Uso from Analysis where Indice like ' [ [ ]IX% ' group by Base, Table, Indice having sum(convert(money, replace(Uso,',','.'))) > 0 Order by Base, Table, to convert(varchar, max(convert(money, replace(Uso,',','.')))) desc, Indice open IndicesUsados fetch next from IndicesUsados into @Base, @Tabla, @Indice, @Uso while (@@FETCH_STATUS < > -1) begin if @Base < > @BaseAnt begin Print ' BASES: ' + @Base select @BaseAnt = @Base select @TablaAnt = '' end if @Tabla < > @TablaAnt begin Print ' TABLE: ' + @Tabla Print ' INDICES: ' select @TablaAnt = @Tabla end if len(@Uso) = 4 begin select @Uso = ' 0 ' + @Uso end Print @Uso + ' ' + @Indice fetch next from IndicesUsados into @Base, @Tabla, @Indice, @Uso continue end close IndicesUsados deallocate IndicesUsados GO
После выполнения мы получаем отчет. Например:
BASE: EXAMPLE TABLE: [ dbo].[APLICACIONES ] INDICES: 79.20 [ IXCY04.05.03_APLICACIONES_NroTransegr_NroTrans_RefVto ] 33.30 [ IXCY04.05.03_APLICACIONES_Aplicaciones_NroTransegr_RefVto_NroTrans_FechaVto_Importe ] 20.50 [ IXC03.11.24_APLICACIONES_NroTransegr_AplNrotrans_AplRefvto ] 02.10 [ IXCP03.11.12_APLICACIONES_Nrotrans_NroTransegr_Importe_FechaVto_AplrefVto ] 100.00 [ IXC04.05.27_APLICACIONES_NroTrans_NroTranselim_AplNroTrans_FechaVto_AplRefvto ] 00.10 [ IXC04.05.27_APLICACIONES_AplNrotrans ] 00.10 [ IXCY04.05.07_APLICACIONES_AplNrotrans_AplRefvto_FechaVto_NroTrans_NroTransing ] TABLE: [ dbo].[AWItemsAcumHistoricos ] INDICES: 00.10 [ IXC03.05.16_AWItemsAcumHistoricos_CodItm_Fecha ] TABLE: [ dbo].[BANCOSCOD ] INDICES: 100.00 [ IXCY04.05.14_BANCOSCOD_CodBan_Descripcion ]
Оба результата важны.
Первый важен потому, что он показывает, какие индексы не используются. Второй важен потому, что он выдает список размеров всех индексов, возможно, давая нам информацию, какие индексы должны быть тщательно проверены. Очень большие размеры индексов могут сообщать о потенциальной возможности замедления работы.
Когда вы удалите все ненужные или очень большие индексы, вы захотите и дальше внимательно следить за производительностью вашей базы данных, чтобы убедиться, что удаленные вами индексы действительно не были нужны, а не являлись редко используемыми индексами, не попавшими в трассировку.