Sql дефрагментация индексов ошибка

Продолжим знакомится с производительностью базы данных MS SQL Server и как ее улучшить и сегодня я решил рассказать про две примерно смежные темы — фрагментация индексов и статистика. Обе темы объединяют как раз индексы и они влияют на их производительность, поэтому я решил рассмотреть их одновременно. 

Фрагментация индексов и некорректная статистика может повлиять на производительность базы данных и как SQL Server выполняет запросы. Сервер по умолчанию собирает статистику сам и нам не нужно заботиться о ней. При создании индексов можно указать – обновлять статистику автоматически или нет и по умолчанию как раз все обновляется.

Начнем со статистики, которая показывает, как много данных определенного типа и это может сильно повлиять на выполнение запроса. Например, есть запрос:

Select * 
From Member m
   Join Address a on m.MemberID = a.MemberID
Where m.LastName = 'Иванов'

Статистика скорей всего покажет, что после фильтрации по фамилии в результате получиться всего несколько записей и проще будет для каждой из них найти адрес в таблице адресов – то есть выполнить loop join.

Select * 
From Address a 
   Join City c on a.CityID  = c.CityID
Where a.CountryName = 'Лапландия'

Здесь уже поиск по первой таблице может вернуть множество строк и для каждой из них искать город во второй будет невыгодно. В зависимости от ситуации, сервер может выбрать hash или merge join.

Сколько строк может вернуться говорит нам статистика и чем лучше статистика, тем точнее результат.

Если честно, я никогда не отключал обновление статистики и не знаю, в каких случаях это может пригодиться, поэтому из личного опыта нечего рассказать. Но что я могу сказать, так это мне приходилось встречаться с случаями, когда статистика была неверна и это значительно влияло на производительность, в негативную сторону. Средние по размеру запросы с большим количеством логики и Join начинали сильно тормозить.

Чаще всего с потерей корректности статистики мне приходилось сталкиваться сразу после восстановления большой базы данных. Сразу после этого очень даже рекомендую обновить статистику на базу данных и это легко делается выполнением команды:

sp_updatestats

В зависимости от размера базы данных эта команда может выполняться достаточно долго, но это очень важно. Если необходимо работать над оптимизацией запроса и вы делаете это при не оптимальных индексах – это трата времени, потому что в реальности вы будете оптимизировать запросы под не оптимальную систему, а после обновления статистики может произойти как улучшение производительности, так и падение.

Чтобы обновить статистику только для определенной таблицы, можно выполнить следующий запрос:

UPDATE STATISTICS ИмяТаблицы

Этот запрос уже более безопасный и достаточно быстрый и я его без проблем выполнял на огромных таблицах на рабочем сервере, правда под минимальной нагрузке. При сильной нагрузке я бы не выполнял этот запрос, потому что он приводит к перекомпиляции запросов и это реально имеет смысл – ведь если статистика изменилась, то и план выполнения может измениться на более оптимальный. А если вы выполните обновление, но реально ничего не изменилось, перекомпиляция будет бессмысленной.

В принципе, выполнять обновление статистики можно, но не слишком часто. Работая над Sony сайтами, я никогда не выполнял полное обновление sp_updatestats, но регулярно выполнял UPDATE STATISTICS на отдельных таблицах.

У меня на канале есть видео, в котором я рассказываю, как я находил медленные запросы. На моем сайте есть и текстовая версия этого видео (Поиск медленных запросов). Если появлялись медленный запрос, который раньше выполнялся быстро, то первым делом я обновлял статистику всех таблиц, которые вовлечены в запрос и если не помогало, тогда уже переходил к исследованию, что произошло и как улучшить производительность.

Второй вопрос, который я хотел бы сегодня обсудить – это дефрагментация и перестроение индексов.

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

Чтобы определить фрагментацию индекса кликаем правой кнопкой по индексу и выбираем пункт Свойства. В появившемся окне выбираем слева Fragmentation и здесь можно увидеть наверху заполненность страниц и фрагментацию:

В моем случае заполненность почти 100 процентов, но фрагментации нет, потому что это такая база, куда не вставляется большого количества записей, это локальный тест.

Такая высокая заполненность получилась за счет того, что у меня Fill Factor установлен в 0. Фактор заполненности можно увидеть в разделе Options. Попробую его установить в 50 и если нажать OK, то индекс будет пересоздан и заполненность сократиться до 50.

Теперь в каждой странице индекса данные заполнены только на 50% и там достаточно пространства для вставки новых данных, и эта операция будет происходить очень быстро. В чем прикол? Почему не сделать 50% по умолчанию? Дело в том, это нужно далеко не всегда. Если это первичный ключ с авто увеличивающейся колонкой, в который данные всегда добавляются в конец, нет смысла выделять пустое пространство в каждой странице. Если это данные, где новая строка может вставляться в любое место, можно оставить 80 или даже 60 процентов пустого пространства. Все зависит от того, как часто вставляются данные и как часто читаются.

Если страницы индекса заполнять только на 50%, понадобиться больше страниц и серверу во время поиска придется читать больше данных с диска, что сократит немного производительность. Заполненность индекса может сократить скорость вставки, но замедлить чтение.

Чтобы определить текущую фрагментацию на базе данных с именем DevDB можно выполнить следующий запрос:

SELECT a.index_id, name, avg_fragmentation_in_percent  
FROM sys.dm_db_index_physical_stats (DB_ID(N'DevDB'), 
      NULL, NULL, NULL, NULL) AS a  
    JOIN sys.indexes AS b 
      ON a.object_id = b.object_id AND a.index_id = b.index_id;   

Теперь зная убитые индексы можно в SQL Server Management кликнуть на каждом из них и выбрать Reorganize или выполнить следующий запрос:

ALTER INDEX Имя_Индекса ON Имя_Таблицы REORGANIZE;

Можно реорганизовать индекс и сразу же поменять заполненность:

ALTER INDEX Имя_Индекса ON Имя_Таблицы
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);

Вместо имени индекса можно указать ALL, если вы хотите реорганизовать все индексы на таблице.

Для дефрагментации последнее время я использую скрипт https://github.com/MichelleUfford/sql-scripts/blob/master/indexes/dba_indexDefrag_sp.sql. Я создаю эту хранимую процедуру у себя в базе данных и потом ставлю следующий скрипт на выполнение каждый месяц в определенный день. Когда я отвечал за базу данных, то этот скрипт у меня бегал каждый понедельник в 3 часа ночи, когда самая минимальная нагрузка на базу данных:

EXECUTE dbo.dba_indexDefrag_sp
              @executeSQL           = 1
            , @printCommands        = 1
            , @debugMode            = 1
            , @printFragmentation   = 1
            , @forceRescan          = 1
            , @maxDopRestriction    = 1
            , @minPageCount         = 8
            , @maxPageCount         = NULL
            , @minFragmentation     = 5
            , @rebuildThreshold     = 30
            , @defragDelay          = '00:00:05'
            , @defragOrderColumn    = 'page_count'
            , @defragSortOrder      = 'DESC'
            , @excludeMaxPartition  = 1
            , @timeLimit            = NULL
            , @database             = 'mamberdatabase,pointsystem';

Внимание!!! Если ты копируешь эту статью себе на сайт, то оставляй ссылку непосредственно на эту страницу. Спасибо за понимание

29.12.16 — 14:34

1 — 29.12.16 — 14:39

2 — 29.12.16 — 14:39

реиндексация одной таблицы длится 3 часа?

3 — 29.12.16 — 14:41

Может там что-то пишет ночью.

4 — 29.12.16 — 15:28

(1) лог конечно не маленький, но раньше проблем не было

(2) Это не одна таблица. 2 УПП+2 ЗУП

(3) ночью только бекапы (заканчиваются за 15-30 минут до старта дефрагментации индекса)

5 — 29.12.16 — 15:31

(1) кроме того, перед запуском этого задания выполняется бекап лога — как я понимаю это уменьшает данные в файле лога.

BACKUP LOG [upp] TO  DISK = N’F:BACKUPupp_backup_2016_12_29_172952_5740145.trn’ WITH NOFORMAT, NOINIT,  NAME = N’upp_backup_2016_12_29_172952_5740145′, SKIP, REWIND, NOUNLOAD,  STATS = 10

6 — 29.12.16 — 15:31

модель восстановления полная.

7 — 29.12.16 — 18:56

8 — 30.12.16 — 08:48

ап

9 — 30.12.16 — 08:52

(7) Лог-файл сам не укорачивается. Просто в нём освобождается место.

Поэтому он просто не растёт (ну при нормальных нагрузках)

10 — 30.12.16 — 08:53

(9) вот и такого же мнения

11 — 30.12.16 — 08:54

(10) Попробуй руками.

Останови все задания.

Руками сделай фулбекап базы.

Сделай сжатие базы.

запусти дефраг

12 — 30.12.16 — 09:00

Было подобное помогло ТИИ со всеми галками, какая конкретно хз, возможно рестуктруктуризация. До этого базу таскали с одного SQL на другой с разыми версиями.

13 — 30.12.16 — 09:07

(12) ТИИ не вариант: производство не будет ждать больше 30 минут. А на копии ТИИ 6часов.

14 — 30.12.16 — 09:17

(12) yahoo.eu

чуть что — сразу ТиИ.

каким боком оно вообще к уровню SQL?

(13) см. (11)

15 — 30.12.16 — 09:52

(0) Для начала определитесь какая БД косячит.

Проблема с дефрагментацией явно указывает на проблемы с дисковой системой (или вообще 1м винтом если у вас не рейд)

16 — 30.12.16 — 10:02

17 — 30.12.16 — 10:11

18 — 30.12.16 — 10:20

19 — 30.12.16 — 12:05

(18) первые две ссылки читал в свое время. И ничего такого (про особенности/проблемы дефрагментации индексов) не припомню.

20 — 30.12.16 — 12:08

(19) ты уже (11) попробовал?

21 — 30.12.16 — 12:14

(20) он не пробует, он читает…

22 — 30.12.16 — 12:21

23 — 30.12.16 — 12:28

может банально диск подыхает? или блокировки какие-то по ночам?

24 — 30.12.16 — 12:29

25 — 30.12.16 — 12:34

(25) на утро когда наблюдал проблему — счетчики приемлемо выглядели. Постоянный сбор к сожалению отключен.

В данной статье рассматривается создание нового (ежедневного) субплана плана обслуживания базы данных на СУБД MS SQL Server, а также настройка выполнения задания реорганизации/дефрагментации индекса. Статья является продолжением статьи «Перечень необходимых задач регламентного обслуживания MS SQL Server».

Предисловие

Индексы таблиц базы данных являются важной составляющей для производительной работы системы. Во время работы с базой данных, при добавлении/изменении/удалении информации из таблиц базы данных, происходит автоматическое обновление индексов, что, в свою очередь, приводит к постепенной фрагментации последних. Фрагментированные индексы могут значительно снизить производительность системы, для их дефрагментации необходимо выполнять соответствующие регламентные операции. Настройку одного из таких мы сейчас и рассмотрим на примере создания ежедневного субплана плана обслуживания.

Настройка задания

В ранее созданный нами план обслуживания (статья «Резервное копирование транзакционного лога») добавим еще один субплан «EveryDayActivity» и назначим расписание его выполнения. Поскольку данный план обслуживания должен выполняться ежедневно (а точнее 6 раз в неделю, т.к. в один из дней недели мы будем выполнять другой, «Еженедельный» план), установим выполнение, например, с понедельника по субботу, в период времени когда пользовательская активность минимальна или ее нет (в моем случае 5:00 утра).

Далее, из панели инструментов Планов обслуживания перенесем задание «Реорганизация индекса» (Reorganize Index Task) в рабочую область субплана (т.е. добавим задание в наш субплан).

И, сразу после этого, двойным щелчком по заданию, откроем его свойства.

Данная задача имеет лишь небольшое количество настроек, тем не менее, рассмотрим их:

1. «Базы данных» (Databases): в данном свойстве можно выбрать одну/несколько/все базы данных. Здесь выбор зависит от вашего желания, если план обслуживания создается общий для нескольких баз, можно выбрать все необходимые.
2. «Объект» (Object): ограничивает набор данных в поле «Выбор» для отображения таблиц, представлений или обоих элементов. Для наших целей подходит значение «Таблица» (Table)
3. «Выбор» (Selection): в данном поле можно выбрать конкретные таблицы, индексы которых, необходимо реорганизовать. Такая возможность может быть полезной, например, если есть таблицы с редко изменяемыми данными, а значит и индексы у них фрагментируются медленно, тогда в целях экономии времени на выполнение задания, можно исключить такие таблицы из ежедневного задания, но включить в еженедельное, например.
4. «Сжатие больших объектов» (Compact large objects): сжимает большие объекты (LOB), по умолчанию установлено. Смысла отключать не имеет, разве что для сокращения времени выполнения задания.

Таким образом, сейчас у нас должно быть одно задание в новом субплане:

В следующих статьях будет рассмотрено добавление оставшихся заданий в ежедневный субплан плана обслуживания.