[Microsoft SQL Server, Администрирование баз данных] Ваш скрипт, обслуживающий индексы, измеряет не то, что надо (перевод)
Автор
Сообщение
news_bot ®
Стаж: 6 лет 3 месяца
Сообщений: 27286
Мой Дорогой Друг Шон недавно написал пост, рассказывающий о том, как люди неправильно обслуживают индексы. Я собираюсь пойти немного дальше и поговорить о том, что сам метод, которым ваш скрипт, обслуживающий индексы, оценивает фрагментацию индексов - неправильный.Если вы посмотрите на то, как ваш скрипт решает нужно или нет перестраивать индексы, и тоже самое касается планов обслуживания (я запускал ПРОФАЙЛЕР ДА ЗДРАВСТВУЕТ ПРОФАЙЛЕР ВПЕРЁД ПРОФАЙЛЕР чтобы проверить), вы увидите, что они выполняют запрос к sys.dm_db_index_physical_stats.Все эти запросы используют столбец avg_fragmentation_in_percent, чтобы понять - нужно ли перестроить индекс. Документация (по ссылке выше) про этот столбец имеет сказать следующее:
хныкЭто мера логической фрагментации. Логическая фрагментация - это когда страницы "перемешаны" на диске.Если вы используете приличные диски, даже на SAN, или у вас нормальный объём ОЗУ, вы можете понять из Великого Поста Шона, что фрагментация - это не самая худшая участь, которая может выпасть на долю ваших индексов. Если вы обслуживаете статистику, всё будет в порядке. Кэши рулятЕсли вы человек, который заботится о различных кэшах на сервере, вроде буферного пула или кэша планов выполнения, тогда вы бы захотели измерить что-от совершенно иное. Вы бы захотели измерить сколько свободного пространства у вас есть на каждой странице данных, потому что с кучей свободного пространства на каждой странице, ваши данные будут занимать больше места в памяти, когда вы будете считывать их с диска.Вы могли бы сделать это с помощью столбца avg_page_space_used_in_percent.НО...
упсВаше любимое решение по обслуживанию индексов позаботится о вас и запустит, по умолчанию, dm_db_index_physical_stats в режиме LIMITED. Это всё потому что более подробные измерения могут быть очень тяжёлыми на сервере, где хранится множество данных, и, блин, даже LIMITED может выполняться очень долго.Но, если бы я собирался принять решение о том нужно ли перестраивать индекс, это именно та метрика, которую я бы хотел использовать. Потому что этого неиспользуемого пространства может быть очень много.Штука в том, что между avg_fragmentation_in_percent и avg_page_space_used_in_percent, нет особой корреляции.Локальная БДПосмотрим на фрагментацию в моей локальной БД Stack Overflow 2013:
отстойОбе таблицы достаточно фрагментированы, чтобы привлечь внимание обслуживающего скрипта, но перестройка индекса, на самом деле, помогает только таблице Posts, несмотря на то, что мы перестроили оба.В таблице Comments, avg_page_space_used_in_percent слегка уменьшается, а в Posts становится лучше примерно на 10%.Количество страниц для Comments не изменяется, но уменьшается примерно на 500 тысяч для Posts.Вот это то, что мне нравится. Я был бы рад читать на 500 тысяч меньше страниц при сканировании таблицы целиком.Но при этом, я вообще-то не хочу сканировать целиком таблицу, если говорить не об отчётах или хранилищах данных.Если мы говорим об OLTP, мы обычно избегаем сканирования больших таблиц и чтобы этого добиться, мы создаём некластерные индексы, которые помогают искать данные эффективно, и пишем запросы с чёткими условиями, которые обеспечивают эффективное использование этих индексов.Правильно?
ПравильноПодумайте о настройках обслуживания индексовВероятно они на стандартных 5% и 30% для реорганизации и перестроения. Дело не только в том, что они абсурдно низкие, но и в том, что они даже измеряют не тот тип фрагментации. Даже при 84% "фрагментации" мы видели страницы, заполненные на 75%.Это не идеально, но едва ли это катастрофа.Да вы возможно размышляли о том, чтобы установить fill factor ещё меньше, чтобы избежать фрагментации.Что ещё хуже, вы, вероятно, смотрите все таблицы > 1000 страниц, т.е. примерно 8МБ. Но если у вас проблемы с тем, чтобы прочитать и удержать в памяти 8 мегабайт - может пора сгонять в магазин?Спасибо, что прочитали!Примечание переводчикаТема достаточно холиварная. Erik Darling и Brent Ozar достаточно давно относятся к той группе, которая топит за то, что, в общем случае, индексам не нужно обслуживание. В противовес им можно поискать посты Paul S. Randal и Paul White, которые наоборот считают, что индексы нужно регулярно обслуживать. На Хабре не нашёл постов/переводов, представляющих такую точку зрения, поэтому решил сделать сам. Ну и интересно, как обслуживают индексы dba на Хабре - принимайте участие в опросе.
===========
Источник:
habr.com
===========
===========
Автор оригинала: Erik Darling
===========Похожие новости:
- [Nginx, Серверная оптимизация, Серверное администрирование, Администрирование баз данных, Хранение данных] Как ускорить сайт в 4 раза, просто перенастроив сервер
- [Oracle, Microsoft SQL Server, Администрирование баз данных] Управление базами данных в Toad — анонс вебинара
- [Администрирование баз данных] Самые популярные базы данных — 2006–2021 гг (перевод)
- [Oracle, PostgreSQL, Администрирование баз данных] «Росатом» переносит огромную финансовую ИТ-систему с Oracle на Postgres Pro
- [Системное администрирование, PostgreSQL, Администрирование баз данных] Что нового в плане мониторинга в PostgreSQL 14
- [Oracle, SQL, Администрирование баз данных] Oracle diagnostic events — Cheat sheet
- [Open source, NoSQL, Администрирование баз данных] Как ухаживать за грядкой Redis'а и как сохранить урожай?
- [SQL, Microsoft SQL Server, Администрирование баз данных] Управление и оптимизация баз данных в ApexSQL — анонс вебинара
- [Анализ и проектирование систем, Администрирование баз данных, Хранение данных] Как из одной базы данных сделать 10 разных, храня только инкременты: обзор решения
- [Системное администрирование, MySQL, Серверное администрирование, Администрирование баз данных] Создаем базу данных MySQL с Ansible (перевод)
Теги для поиска: #_microsoft_sql_server, #_administrirovanie_baz_dannyh (Администрирование баз данных), #_sql_server, #_index, #_fragmentation, #_microsoft_sql_server, #_administrirovanie_baz_dannyh (
Администрирование баз данных
)
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 20-Май 21:01
Часовой пояс: UTC + 5
| Автор | Сообщение |
|---|---|
|
news_bot ®
Стаж: 6 лет 3 месяца |
|
|
Мой Дорогой Друг Шон недавно написал пост, рассказывающий о том, как люди неправильно обслуживают индексы. Я собираюсь пойти немного дальше и поговорить о том, что сам метод, которым ваш скрипт, обслуживающий индексы, оценивает фрагментацию индексов - неправильный.Если вы посмотрите на то, как ваш скрипт решает нужно или нет перестраивать индексы, и тоже самое касается планов обслуживания (я запускал ПРОФАЙЛЕР ДА ЗДРАВСТВУЕТ ПРОФАЙЛЕР ВПЕРЁД ПРОФАЙЛЕР чтобы проверить), вы увидите, что они выполняют запрос к sys.dm_db_index_physical_stats.Все эти запросы используют столбец avg_fragmentation_in_percent, чтобы понять - нужно ли перестроить индекс. Документация (по ссылке выше) про этот столбец имеет сказать следующее:  хныкЭто мера логической фрагментации. Логическая фрагментация - это когда страницы "перемешаны" на диске.Если вы используете приличные диски, даже на SAN, или у вас нормальный объём ОЗУ, вы можете понять из Великого Поста Шона, что фрагментация - это не самая худшая участь, которая может выпасть на долю ваших индексов. Если вы обслуживаете статистику, всё будет в порядке. Кэши рулятЕсли вы человек, который заботится о различных кэшах на сервере, вроде буферного пула или кэша планов выполнения, тогда вы бы захотели измерить что-от совершенно иное. Вы бы захотели измерить сколько свободного пространства у вас есть на каждой странице данных, потому что с кучей свободного пространства на каждой странице, ваши данные будут занимать больше места в памяти, когда вы будете считывать их с диска.Вы могли бы сделать это с помощью столбца avg_page_space_used_in_percent.НО...  упсВаше любимое решение по обслуживанию индексов позаботится о вас и запустит, по умолчанию, dm_db_index_physical_stats в режиме LIMITED. Это всё потому что более подробные измерения могут быть очень тяжёлыми на сервере, где хранится множество данных, и, блин, даже LIMITED может выполняться очень долго.Но, если бы я собирался принять решение о том нужно ли перестраивать индекс, это именно та метрика, которую я бы хотел использовать. Потому что этого неиспользуемого пространства может быть очень много.Штука в том, что между avg_fragmentation_in_percent и avg_page_space_used_in_percent, нет особой корреляции.Локальная БДПосмотрим на фрагментацию в моей локальной БД Stack Overflow 2013:  отстойОбе таблицы достаточно фрагментированы, чтобы привлечь внимание обслуживающего скрипта, но перестройка индекса, на самом деле, помогает только таблице Posts, несмотря на то, что мы перестроили оба.В таблице Comments, avg_page_space_used_in_percent слегка уменьшается, а в Posts становится лучше примерно на 10%.Количество страниц для Comments не изменяется, но уменьшается примерно на 500 тысяч для Posts.Вот это то, что мне нравится. Я был бы рад читать на 500 тысяч меньше страниц при сканировании таблицы целиком.Но при этом, я вообще-то не хочу сканировать целиком таблицу, если говорить не об отчётах или хранилищах данных.Если мы говорим об OLTP, мы обычно избегаем сканирования больших таблиц и чтобы этого добиться, мы создаём некластерные индексы, которые помогают искать данные эффективно, и пишем запросы с чёткими условиями, которые обеспечивают эффективное использование этих индексов.Правильно? ПравильноПодумайте о настройках обслуживания индексовВероятно они на стандартных 5% и 30% для реорганизации и перестроения. Дело не только в том, что они абсурдно низкие, но и в том, что они даже измеряют не тот тип фрагментации. Даже при 84% "фрагментации" мы видели страницы, заполненные на 75%.Это не идеально, но едва ли это катастрофа.Да вы возможно размышляли о том, чтобы установить fill factor ещё меньше, чтобы избежать фрагментации.Что ещё хуже, вы, вероятно, смотрите все таблицы > 1000 страниц, т.е. примерно 8МБ. Но если у вас проблемы с тем, чтобы прочитать и удержать в памяти 8 мегабайт - может пора сгонять в магазин?Спасибо, что прочитали!Примечание переводчикаТема достаточно холиварная. Erik Darling и Brent Ozar достаточно давно относятся к той группе, которая топит за то, что, в общем случае, индексам не нужно обслуживание. В противовес им можно поискать посты Paul S. Randal и Paul White, которые наоборот считают, что индексы нужно регулярно обслуживать. На Хабре не нашёл постов/переводов, представляющих такую точку зрения, поэтому решил сделать сам. Ну и интересно, как обслуживают индексы dba на Хабре - принимайте участие в опросе. =========== Источник: habr.com =========== =========== Автор оригинала: Erik Darling ===========Похожие новости:
Администрирование баз данных ) |
|
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 20-Май 21:01
Часовой пояс: UTC + 5