Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 27
Стоит сказать, что перед перестроением индекса настоятельно рекомендуется отключать все триггеры, которые есть в базе.
Никогда о таком не слышал. С чем это связано?
Это связано с тем, что могут быть(и должны быть) триггеры, которые срабатывают при изменении определенной таблички. Например есть таблица со справочником — spr, у справочника много полей. Как можно отследить, что конкретный пользователь что конкретно изменил? Сделать триггер который бы в таблицу spr_his писал все изменения.
1) Ну и причем здесь перестройка индексов? Кто вам мешает перестраивать индексы при наличии триггера, вы же не думаете, что он будет при этом срабатывать? ;)
2) Триггеры никому ничего не должны. Ну только разве что у вас 2-звенка. А у меня все изменения данных в базе проходят через слой сервера приложений, оттуда и логгирую (отдельными запросами), если что мне нужно.
2) Триггеры никому ничего не должны. Ну только разве что у вас 2-звенка. А у меня все изменения данных в базе проходят через слой сервера приложений, оттуда и логгирую (отдельными запросами), если что мне нужно.
1) Потому что у таблицы spr_his, как правило, тоже есть индекс. Перестроение индекса блокирует все изменения. Получается замыкание, как вам уже ответил автор AlanDenton.
2) Просто вы еще не почувствовали кайф переноса части логики приложения с сервера приложения на сервер базы данных.
2) Просто вы еще не почувствовали кайф переноса части логики приложения с сервера приложения на сервер базы данных.
1)
a) В статье упоминалась возможность WITH ONLINE=ON
б) Если использование WITH ONLINE=ON невозможно, подразумевается, что перестроение индекса выполняется в момент времени, когда с базой никто не работает. А по вашему сценарию вы просто отрубаете нафиг логирование изменений при перестройке индекса на spr_his, а возможность внесения этих изменений оставляете. Ну замечательно, кто-то поменяет справочник именно в этот момент, а вы об этом уже никогда не узнаете.
2) Я обычно чувствую кайф при обратном переносе (из базы в приложение), т.к. TSQL все-таки не полноценный язык программирования и приятнее реализовывать логику на нормальном алгоритмическом языке, а не на языке управления данными. Хотя часть логики в виде хранимых процедур в нашей системе тоже присутствует, но скорее в целях оптимизации (для ускорения работы).
a) В статье упоминалась возможность WITH ONLINE=ON
б) Если использование WITH ONLINE=ON невозможно, подразумевается, что перестроение индекса выполняется в момент времени, когда с базой никто не работает. А по вашему сценарию вы просто отрубаете нафиг логирование изменений при перестройке индекса на spr_his, а возможность внесения этих изменений оставляете. Ну замечательно, кто-то поменяет справочник именно в этот момент, а вы об этом уже никогда не узнаете.
2) Я обычно чувствую кайф при обратном переносе (из базы в приложение), т.к. TSQL все-таки не полноценный язык программирования и приятнее реализовывать логику на нормальном алгоритмическом языке, а не на языке управления данными. Хотя часть логики в виде хранимых процедур в нашей системе тоже присутствует, но скорее в целях оптимизации (для ускорения работы).
1)
2)Тут дело вкуса. Для меня централизованное хранение логики намного удобнее/практичнее/быстрее в обслуживании и т.д. и т.п. Просто те же «хранимки» нужно применять не повсеместно, а только там где это будет нужнее/необходимее/проще.
WITH ONLINE=ON. Вторая опция позволяет пересоздать индекс не блокируя при этом запросы к объекту для которого этот индекс создается.Здесь имеются ввиду только SELECT запросы.
2)Тут дело вкуса. Для меня централизованное хранение логики намного удобнее/практичнее/быстрее в обслуживании и т.д. и т.п. Просто те же «хранимки» нужно применять не повсеместно, а только там где это будет нужнее/необходимее/проще.
Здесь имеются ввиду только SELECT запросы.
А вот уж только выдумывать отсебятину не надо. Читайте BOL, проверяйте, потом и пишите.
Вот тут ясно сказано:
online index operations permit concurrent user update activity.
То, что там есть 100500 ограничений, когда это не сработает — это другой вопрос.
Если вас смутила фраза
the underlying table cannot be modified, truncated, or dropped while an online index operation is in process,
то она относилась, видимо, к тому, что нельзя в таблице поля добавить/удалить, пока индекс перестраивается (т.е. что метаданные самой таблицы нельзя менять).
Никакой отсебятины. Вы вырвали эту фразу из контекста в параграфе про производительность. Там как раз сказано, что ресурсов на UPDATE/INSERT/DELETE тратится намного больше. Знаете почему? Потому что все эти запросы ставятся в очередь в буфер и только после перестроения индекса эти изменения вносятся в базу. Поэтому использование этой опции может сильно нагрузить сервер.
Хотелось бы увидеть некий пруф к вашим словам. Если бы это было так, как вы пишете, то ONLINE INDEX OPERATIONS не позиционировалось бы как супер мега крутая фича, которая есть в отнюдь не дешевой ENTERPRISE редакции SQL сервера (может и еще в каких редакциях есть, точно не помню).
Несмотря на выдергивание фразы из контекста, по ссылке выше сказано лишь о том, что «Because both the source and target structures are maintained during the online index operation, the resource usage for insert, update, and delete transactions is increased, potentially up to double.» Т.е. ресурсов тратится больше просто потому, что серверу приходится поддерживать целостность сразу двух структур (исходных данных и перестраиваемого, но еще не до конца перестроенного индекса).
Возможно (но далеко не факт, т.к. никаких подтверждений не видел) ONLINE INDEX OPERATIONS ведут себя по-разному в зависимости от того, включена ли опция READ_COMMITTED_SNAPSHOT. Т.к. когда она включена, параллельные транзакции с уровнем изоляции READ_COMMITTED ведут себя очень по-разному (в одном случае незакоммиченный UPDATE блокирует READ другой транзакции, а в другом — не блокирует). Не знаю, имеет ли данная опция какое-либо отношение к перестройке индекса (с архитектурной точки зрения должна иметь).
Несмотря на выдергивание фразы из контекста, по ссылке выше сказано лишь о том, что «Because both the source and target structures are maintained during the online index operation, the resource usage for insert, update, and delete transactions is increased, potentially up to double.» Т.е. ресурсов тратится больше просто потому, что серверу приходится поддерживать целостность сразу двух структур (исходных данных и перестраиваемого, но еще не до конца перестроенного индекса).
Возможно (но далеко не факт, т.к. никаких подтверждений не видел) ONLINE INDEX OPERATIONS ведут себя по-разному в зависимости от того, включена ли опция READ_COMMITTED_SNAPSHOT. Т.к. когда она включена, параллельные транзакции с уровнем изоляции READ_COMMITTED ведут себя очень по-разному (в одном случае незакоммиченный UPDATE блокирует READ другой транзакции, а в другом — не блокирует). Не знаю, имеет ли данная опция какое-либо отношение к перестройке индекса (с архитектурной точки зрения должна иметь).
Вот как раз по ссылке, где про операцию, и написано:
ONLINE = { ON | OFF }
Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF.
А статья довольно старая (2010-го года), с тех пор M$ многое могли поменять. К тому в статье отсылка на BOL вообще от 2005-го MS SQL.
ONLINE = { ON | OFF }
Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF.
А статья довольно старая (2010-го года), с тех пор M$ многое могли поменять. К тому в статье отсылка на BOL вообще от 2005-го MS SQL.
Присоединяюсь к alan008. Возможно Вы имели ввиду триггера уровня базы/сервера, которые отслеживают события ALTER_INDEX?
Насколько я знаю, перестройка индексов со срабатыванием триггеров before / after / instead of вообще никак не связана, так что мысль товарища servekon пока не понятна.
Возможна ситуация когда есть триггер, который логирует изменения на базе либо делает еще что-то:
В такой ситуации мы вообще не сможем изменить никаких индексов:
CREATE TRIGGER ddl_Server
ON ALL SERVER
FOR ALTER_INDEX
AS
BEGIN
RAISERROR('Error', 16, 1)
END
GO
ALTER INDEX ... ON .... REORGANIZE
В такой ситуации мы вообще не сможем изменить никаких индексов:
Msg 50000, Level 16, State 1, Procedure ddl_Server, Line 7
ErrorНу я то про обычные индексы говорил, на таблицах :)
Признаться, не знал/не использовал DDL триггеры. Вообще, прикольно. Но думаю для их создания нужна как минимум роль serveradmin, если не sa )))
Признаться, не знал/не использовал DDL триггеры. Вообще, прикольно. Но думаю для их создания нужна как минимум роль serveradmin, если не sa )))
Да именно такие триггеры я имел ввиду. В хорошем приложении, на критические и важные изменения всегда нужно вешать такие триггеры. Очень помогает при отладке.
Мы используем такую процудуру, которую кто-то любезно написал на sqlservercentral.com
-- =============================================
-- Description: Интеллектуальная перестройка индексов
-- =============================================
CREATE PROCEDURE [dbo].[OptimizeDBIndexes]
AS
BEGIN
-- http://www.sql-server-performance.com/2012/performance-tuning-re-indexing-update-statistics/
-- Для индексов с фрагментацией > 10% выполняется REORGANIZE
-- Для индексов с фрагментацией > 30% выполняется REBUILD
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
SELECT * FROM #work_to_do;
-- Declare the cursor for the list of partitions to be processed.
DECLARE cr_partitions CURSOR FOR SELECT * FROM #work_to_do;
OPEN cr_partitions;
WHILE (1=1)
BEGIN
FETCH NEXT
FROM cr_partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
CLOSE cr_partitions;
DEALLOCATE cr_partitions;
DROP TABLE #work_to_do;
END
GO
Оригинал тут. Там же и про обновление статистики написано.
Спасибо за ссылку. Интересно было почитать.
Советую там зарегистрироваться, тогда на почту будет приходить почти каждый день рассылка с интересными статьями. Я уже больше года там подписан, время от времени натыкаюсь просто на «бриллианты» :).
В общем я там уже зарегистрирован. Если будет интересно: www.sql-server-performance.com/2013/how-to-optimize-sql-server-query-plan/
Запрос без курсора не позволит выйти из перестроения индексов в случае, если перестроение может не убраться в технологическое окно (с большими базами такое бывает). Лучше поместить результаты во временную таблицу, и перебирать ее по одной строке, добавив условие на проверку по времени — если время технологического окна закончилось, то выполнение скрипта необходимо прекратить.
Кроме операций обслуживания структур данных непосредственно самого MS SQL (индексов, статистик) мы при резервном копировании выполняем еще часть задач, относящихся скорее к уровню бизнес логики, т.е. к нашим структурам данных, но таких, о которых пользователям знать не обязательно. Например, мы чистим неиспользуемые значения в справочниках (если справочник автопополняемый), удаляем обработанные сообщения из очередей (имею в виду, у нас в базе есть своя таблица а-ля очередь сообщений, в которой обработанные сообщения помечаются как обработанные, но сразу не удаляются, чтобы проще было отладить ситуацию, когда при обработке сообщения возникает какая-то ошибка), удаляем какие-то устаревшие данные (например, логи за неинтересующие нас периоды и т.п.). Можем перепаковать какие-то бинарные данные (например, можно завести столбец-признак, сжаты ли данные, при обычной работе запихивать туда данные в несжатом виде, а при бэкапе паковать — тут конечно есть возможность паковать страницы с помощью самой СУБД, но не всегда этот вариант подходит).
позвольте поинтересоваться: под какой проект потребовалось так обрабатывать базу и каков её вес на диске?
PS: у себя в конторе максимальный эффект дала установки нормальной дисковой полки(много-много шпинделей, пара контролеров, кеш с батарейкой, рейд...). Всякие дефрагментации индексов и очистки статистики давали увеличение производительности на величину, умещающуюся в рамки погрешности. Сервер MSSQL для 1С
PS: у себя в конторе максимальный эффект дала установки нормальной дисковой полки(много-много шпинделей, пара контролеров, кеш с батарейкой, рейд...). Всякие дефрагментации индексов и очистки статистики давали увеличение производительности на величину, умещающуюся в рамки погрешности. Сервер MSSQL для 1С
Решил чуток обновить статью, потому что недавно сделал бесплатную тулу по обслуживанию индексов. Надеюсь она будет полезной :)
Ссылка на исходники программы:
github.com/sergeysyrovatchenko/SQLIndexManager
Обсуждение нового функционала:
www.sql.ru/forum/1312218/sql-index-manager-besplatnaya-utilita-po-obsluzhivaniu-indeksov-dlya-sql-server-i-azure
Ссылка на исходники программы:
github.com/sergeysyrovatchenko/SQLIndexManager
Обсуждение нового функционала:
www.sql.ru/forum/1312218/sql-index-manager-besplatnaya-utilita-po-obsluzhivaniu-indeksov-dlya-sql-server-i-azure
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
План обслуживания «на каждый день» – Часть 1: Автоматическая дефрагментация индексов