sp00n Oct 24 2012 at 13:15

Дефрагментация индексов со сбором статистики MS SQL 2008 R2

5 min

47K

SQL*Microsoft SQL Server*

From sandbox

Comments 10

EllaVS Oct 24 2012 at 13:29

Спасибо. Вовремя, как раз искала что-то подобное. В закладки :)

djonik1562 Oct 24 2012 at 13:31

Да, по-любому в закладки. Попозже попробую у себя

Ne0lite Oct 24 2012 at 13:52

thx

CLaiN Oct 24 2012 at 15:13

ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Также есть самописный скрипт на павершеле, который отправляет результат на почту.

unfilled Oct 25 2012 at 05:59

Я может быть сплю ещё, но вот курсор смущает:

DECLARE defragCur CURSOR FOR
    SELECT 
        [object_id], 
        index_id, 
        table_name, 
        index_name, 
        avg_frag_percent_before, 
        fill_factor, 
        partition_num 
    FROM dba_tasks.dbo.index_defrag_statistic
    ORDER BY [object_id], index_id DESC --Сначала не кластерные индексы

Вы не накладываете отбор по @currentProcID, соответственно, в результате выполнения запроса вы получите вообще все записи из index_defrag_statistics и, следовательно, будете дефрагментировать/перестраивать все индексы, когда либо записанные в неё. Причём вполне возможна ситуация когда один и тот же индекс сначала перестроится, потом дефрагментируется, потом снова перестроится и т.д.
Если вы перед запуском этой процедуры очищаете таблицу — то зачем вам @currentProcID?

Ну и вообще — вопрос с дефргаментацией/перестройкой индексов достаточно сложный. Не понятна «полезность» этой процедуры. Ну точнее полезность понятна — индексы становятся ровные, красивые и т.д. Но, я ни разу не видел СЕРЬЁЗНЫХ проблем с производительностью, вызванных фрагментированием индексов.

Степень фрагментации индекса никак не учитывается оптимизатором, соответственно и не влияет на план запроса. Фактически, если используется поиск по индексу (INDEX SEEK) — степень фрагментации никак не повлияет на выполнение запроса — головка и так будет носиться по диску, выцепляя отдельные страницы индекса. Если же при выполнении запроса используется INDEX SCAN на очень большой таблице — вот тут может быть проблема из-за того, что SQL Server не сможет читать данные последовательно большими кусками. Но такие запросы и так достаточно медлительны и фрагментированность индекса на производительность, в итоге, влияет не сильно.

Так же, бывает, что запрос «тормозит», админ выполняет перестройку (ALTER INDEX REBUILD) индекса и запрос начинает летать — тут дело не столько в самой перестройке индекса, сколько в попутном обновлении статистики, которое почему-то часто забывают — как и автор топика, не добавил её в скрипт обслуживания индексов, что, имхо, неправильно.

unfilled Oct 25 2012 at 07:36

И ещё вот момент увидел:

page_count > 5 – я считаю, что перестраивать индексы с малым количеством страниц не имеет смысла

Перестраивать индексы с малым количеством страниц действительно не имеет смысла, только границу надо поднять как минимум до 8. До тех пор пока индекс содержит меньше 8 страниц, он размещается в смешанных экстентах и его дефрагментация/перестройка может не принести желаемого результата (msdn).
Я для себя выбрал границу в 128 страниц, т.е. только те, которые «весят» больше мегабайта. Сейчас вот правда задумался — не поднять ли планку ещё :).

sp00n Oct 25 2012 at 10:58

Условие по proc_id поставил, спасибо за ценное замечание.
Что касается полезности этой процедуры, то даже с теоретической точки зрения она полезна (с практической же, ее полезность бывает разной, я согласен), ведь уменьшая фрагментацию, мы увеличиваем плотность данных на страницах, значит страниц надо будет прочесть меньше, меньше работа диска.
Про статистику я не забыл, msdn утверждает, что обновление статистики произойдет автоматически, если после rebuild мы явно не напишем STATISTICS_NORECOMPUTE = ON, я явно не писал.

unfilled Oct 25 2012 at 11:28

уменьшая фрагментацию, мы увеличиваем плотность данных на страницах

Из-за fill factor = 80 вы местами плотность данных не увеличиваете, а уменьшаете :). (это я занудствую, в общем-то, вы же писали зачем это делаете, но кто-то может удивиться, увидев, что после процедур, направленных на увеличение плотности данных, файл данных вырастает)

Про статистику я не забыл, msdn утверждает, что обновление статистики произойдет автоматически, если после rebuild мы явно не напишем STATISTICS_NORECOMPUTE = ON, я явно не писал

И это верно, но статистика обновится только по тем индексам, для которых был сделан REBUILD. Для индекса, «подвергшегося» дефрагментации (REORGANIZE), статистика не обновится. И также не обновится статистика, созданная SQL Server'ом автоматически.

sp00n Oct 25 2012 at 11:36

Из-за fill factor = 80 вы местами плотность данных не увеличиваете, а уменьшаете :). (это я занудствую, в общем-то, вы же писали зачем это делаете, но кто-то может удивиться, увидев, что после процедур, направленных на увеличение плотности данных, файл данных вырастает)

Я думаю, из этого можно раздуть холивар, но дефрагментацию делать надо, почему бы не следить за тем как она делается, и получать информацию о состоянии индексов :)

И это верно, но статистика обновится только по тем индексам, для которых был сделан REBUILD. Для индекса, «подвергшегося» дефрагментации (REORGANIZE), статистика не обновится. И также не обновится статистика, созданная SQL Server'ом автоматически.

Согласен. Но я не ставил перед собой цель описать весь план обслуживания БД, статья касается только индексов. А статистика у меня обновляется отдельным шагом.

AlanDenton Dec 24 2013 at 14:52

Небольшое замечание — при пересоздании таблицы, впрочем, как и любого другого объекта — меняется его object_id.
Попробуйте несколько раз выполнить этот скрипт, чтобы увидеть описанную ситуацию:

IF OBJECT_ID('dbo.test1', 'U') IS NOT NULL
	DROP TABLE dbo.test1
GO

CREATE TABLE dbo.test1
(
	ID INT,
	CONSTRAINT PK_test1_ID PRIMARY KEY (ID)
)
GO

SELECT OBJECT_ID('dbo.test1')

Поэтому, во избежание коллизий, желательно привязываться не к object_id, а к полному имени объекта (схема + имя).

Show the best of all time