Alexander Speshilov (speshuric) wrote,
Alexander Speshilov
speshuric

Category:

SQL и недостающие индексы.

В MS SQL Server начиная с версии 2005 появилась отличная возможность - на уровне движка БД накапливается статистика по тем индексам, которые бы помогли в запросах, но их не оказалось. За сбор этой статистики отвечают системные представления sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_details, sys.dm_db_missing_index_columns. В плане применимости к 1С там конечно не всё безоблачно - собранную статистику нужно еще правильно проанализировать, но я-то это умею и пользуюсь. У этого инструмента есть неприятная особенность:
Статистика собирается не более чем для 500 групп отсутствующих индексов. Когда этот порог достигнут, данные о группах отсутствующих индексов больше не собираются. Этот порог не подлежит настройке, его изменить нельзя.

А иногда после перезапуска сервер ну такой фигнёй сначала занимается, что просит потом инднексы, которых врагу не пожелаешь.


В BOL сказано что с одной стороны
Отсутствуют управляющие элементы, позволяющие включать или выключать ее, а также сбрасывать таблицы, возвращаемые при обращении с запросом к объектам DMO. При перезапуске SQL Server удаляются все сведения об отсутствующих индексах.

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

Также я обратил внимание, что при операциях изменяющих метаданные БД статистика отсутсвующих индексов тоже сбрасывается (ATTACH/DETACH, RESTORE LOG, RESTORE DATABASE, многие варианты ALTER DATABASE).
Итак, краткий список способов по убыванию "жестокости"
1. Рестарт сервера. Требуются права на остановку, запуск служб на сервере. Влияет на всех пользователей сервера. В принципе, может оказаться оптимальным вариантом для тестовой среды одной задачи для "холодных" тестов.
2. ATTACH/DETACH. Требуются права db_owner для отключения, db_creator или аналогичные для подключения. Влияет на всех пользователей БД. Не уверен, но может слететь, например, нестандартная репликация, история бэкапов и т.п. Сложно придумать, когда это будет оправдано.
3. Увод БД в оффлайн и обратно:
ALTER DATABASE [demo] SET OFFLINE;
ALTER DATABASE [demo] SET ONLINE;

Достаточно прав db_owner. Не получится делать, если пользователи работают с БД, т.е. влияет на всех активных пользователей БД. Оправданный вариант для БД разработчика на общем сервере (например, с другими разработчиками).
4. "Как будто переименование" всех объектов из missing_index (см. скрипт).
-- Скрипт работает только для текущей базы данных, это связано с особенностями работы sp_rename.
SET NOCOUNT ON;
DECLARE @print_only BIT; -- Если флаг установлен, то только вывод команд
SET @print_only = 1; -- !!!!
DECLARE @object_cursor CURSOR; 
SET @object_cursor = 
CURSOR FAST_FORWARD FOR
	SELECT DISTINCT 
		mid.statement old_name, object_name(mid.object_id) new_name
	FROM sys.dm_db_missing_index_details mid
	WHERE mid.database_id = db_id()
OPEN @object_cursor;

DECLARE @old_name NVARCHAR(776)
DECLARE @new_name SYSNAME
DECLARE @print NVARCHAR(MAX)

FETCH NEXT FROM @object_cursor INTO @old_name, @new_name;
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @print = 
	N'EXEC dbo.sp_rename @objname = N''' + REPLACE(@old_name,N'''',N'''''') + 
	N''', @newname = N''' + REPLACE(@new_name,N'''',N'''''') + N''', @objtype = N''OBJECT''';
	PRINT @print;
	IF (@print_only = 0)
	BEGIN
		EXEC dbo.sp_rename @objname = @old_name, @newname = @new_name, @objtype = N'OBJECT';
	END
    FETCH NEXT FROM @object_cursor INTO @old_name, @new_name;
END;

CLOSE @object_cursor;
DEALLOCATE @object_cursor;

Не требует даже db_owner. Требует Sch-M блокировки объектов. Оптимальный вариант для совместно используемых систем (в некоторых случаях даже для продакшн).
Tags:
Subscribe

  • Странная штука powershell...

    Странная штука powershell... Простой скрипт: $start = get-date for($i=1; $i -le 1000000; $i++){} $end = get-date ($end-$start).TotalMilliseconds #…

  • про Linux

    С IE на оперу я перелез, когда мне понадобилось сильно экономить трафик. Других достоинств у оперы нет и не было. Как появилась возможность - перелез…

  • Об эффективности отечественной государственной системы

    Только Факты: 1. Сегодня курс доллара 1$ = 28.223 (по данным rbc.ru) 2. По данным BBC: Соединенные Штаты уже потратили на участие в военной и…

  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 10 comments

  • Странная штука powershell...

    Странная штука powershell... Простой скрипт: $start = get-date for($i=1; $i -le 1000000; $i++){} $end = get-date ($end-$start).TotalMilliseconds #…

  • про Linux

    С IE на оперу я перелез, когда мне понадобилось сильно экономить трафик. Других достоинств у оперы нет и не было. Как появилась возможность - перелез…

  • Об эффективности отечественной государственной системы

    Только Факты: 1. Сегодня курс доллара 1$ = 28.223 (по данным rbc.ru) 2. По данным BBC: Соединенные Штаты уже потратили на участие в военной и…