Мои правила создания индексов и работы с ними (ни на что не претендую, просто делюсь):
Если таблица используется только на чтение, например это справочник - допустимо любое количество любых индексов.
Если это справочник, то кластерный индекс должен совпадать с полем, являющимся ключом справочника.
Если таблицы связаны через FK декларативной ссылочной целостностью, индекс по этому полю должен быть (но с учетом п.2)
Не стоит слепо доверять оптимизатору, который рисует зеленым подсказку про недостающий индекс. Во-первых, он может предложить индекс по слабоселективному полю, и индекс будет бесполезен, во-вторых, он может предложить синоним.
Перед тем, как создать индекс, обязательно нужно изучить все имеющиеся. Вдруг ты пытаешься создать синоним?
Обязательно нужно пользоваться административными представлениями, предоставляющими информацию о статистике использования индексов и статистике недостающих индексов. Но слепо верить им нельзя, т.к. и первое и второе дает адекватную информацию только на долго проработавшем сервере. Если ваш сервер каждую ночь перезагружается и не очень интенсивно используется - толку от ваших инструментов чуть.
Много индексов - зло. Мало индексов - зло втройне.
Если система, в любой ипостаси, предлагает сделать индекс по какому то полю, а в инклюд - все остальные поля это повод: а) посмотреть, а не стоит ли по этому полю сделать кластерный индекс или б) игнорировать эти вопли.
Не стоит стесняться использовать датабэйз энжин тюннинг адвизор. Особенно следующим способом: записать профайлером трек активности сервера за длительное время и скормить его адвизору. Но подходить к рекомендациям надо критически.
10 индексов на таблицу - это нормально. И 20. И 30. Зависит от того, что это за таблица и что это за система. Если это какая то витрина, olap (не куб, а просто база с данными для отчетов) - в создании индексов не ограничивает ничего. Ну будут данные заливаться не сутки, а трое, и что с того?
Кстати, отключить все индексы при заливке в хранилище, а потом включить их - позволяет сильно экономить время. Даже десятикратно иногда.
Бывают ситуации, когда даже один индекс - это чересчур много. Даже кластерный. И даже особенно кластерный.
Если вы делаете некластерные индексы на куче - это как минимум странно.
На быстро меняющихся таблицах, если у вас классическое oltp - индексы зло. И из зла надо выбирать меньшее по количеству, весу и большее по селективности.
Индексы стоит создавать даже в базах 1С. Не 1Совские, а sqlные, если вы понимаете о чем я. Но следить за ними надо вручную.
Индексы не нужно регулярно "обслуживать". Т.е. все эти ребилды и реорганайзы каждые сутки, пристальное слежение за фрагментацией - чушь собачья (чуть менее, чем всегда). Вот статистику нужно регулярно пересчитывать, и пристально следить за ее актуальностью, порогом обновления, если пересчет автоматический и т.д. Эффективность ребилда (не реорганайза) индексов происходит из того, что в процессе пересоздания индекса пересчитывается статистика.
Индексы нужно иногда обслуживать, потому что очень сильно фрагментированные большие индексы тупо не очень эффективно расходуют ОЗУ.
Покрывающие индексы - манна небесная.
Колоночные индексы - манна небесная вдвойне. Но использовать их, не поставив в известность разработчиков - не получится. Система, увидев, что на таблице есть (некластерный) колоночный индекс - будет стремиться использовать его, и скорость выполнения запросов волшебным образом сильно упадет. Чтобы этого не произошло, нужно прибить это стремление хинтом в запросах (которым поплохело).
Разработчиков, вообще, крайне желательно ставить в известность по поводу того, чего вы там накреативили с индексами. Ибо!
Не все проблемы с производительностью можно исправить индексами. И даже более того - только некоторые проблемы производительности можно исправить с помощью индексов. Разработчиков нужно приучать писать саргабельные запросы.
Если ваш запрос очень хорошо работает, и тут же - очень плохо работает, 99% дело не в индексе, и не стоит пытаться этот индекс "обслужить". Посмотрите, может дело в блокировках или банальном параметр-сниффинге. Вообще, активность разработчиков в стиле "а давайте перестроим индексы и всё заработает" - нужно пресекать на корню. Если к вам пришли с такой сентенцией - 100 пудов у них в коде говно.
Индексы - если не главный, то очень заметный инструмент по борьбе с дедлоками.
Индексы, наряду с декларативной ссылочной целостностью - главный источник дедлоков.
Поменьше фанатизма. Это к каждому пункту относится.
Мультфильм? Там игры, ДИНАМИЧЕСКИЕ (!!!) можно было программировать.
Абсолютно не шутка. Именно игры, типа "Посадки на Луну", и именно динамические.
На экране мелькали цифры (Показатель скорости), положение в пространстве, в виде ---.---- вот такой картинки на индикаторах, а управление двигателем производилось переключением рычажка градусы-грады-радианы
Добротнее в математическом смысле (т.е. выдает более случайную последовательность). Была статья в англоязычном интернете, слету не нагуглилось, но как то так.
Плюс она немного побыстрее, что актуально на миллионных генерациях.
Я например исходный пример уже полчаса дождаться не могу.
Если нужен целочисленный генератор случайных чисел, с равномерным распределением (а обычно нужен как раз целочисленный), лучше (для диапазона 1-10, например):
Знаю, что можно было использовать для формирования последовательности натуральных чисел рекурсию, но в данном случае без нее получается немного быстрее, да и код понятней.
Select ROW_NUMBER() over (order by 1/0) N From string_split(Replicate(Cast(' ' as varchar(max)), 1000000), ' ') t
Не надо рекурсий. С 2016 (или 2017, когда там string_split появился?) - можно так. С 2022 используйте GENERATE_SERIES
Но-но-но! Для DWH - 500-800 полей - это, скорее, норма, чем извращение. Хотя, да, повод вопросить в сторону коллег: "Господа, вы в своем уме?"... ну, в переложении на арго, разумеется.
Думаю, что книгу я найду на пиратских сайтах, но мне хотелось бы, чтобы автор получил бонус. Очень жаль, что не получается оплатить. Буду искать способ.
Может есть вариант выложить ее на Литрес? Там с оплатой все нормально...
А чем это принципиально отличается от горизонтального секционирования (с помощью представлений), которое появилось, если мне память не изменяет, в MSSQLSERVER версии 6.5, т.е. лет 30 назад?
ИМХО, в контексте подобных статей хорошо бы смотрелась статья по "старобрядческому" секционированию. С помощью Union all.
Оно, на мой взгляд, незаслуженно забыто, и многие не знают, что "так тоже можно", и, главное, не знают как это "можно" сделать так, чтобы правильно было.
А у него, между прочим, есть куча плюсов, и куча возможностей, которые недоступны обычному секционированию.
Причем их столько, что Майкрософт сподобилось секционирование сделать только к 2008 серверу (если память мне не изменяет), т.е. через 15 лет, т.с.
У меня нагруженная с точки зрения OLTP база , поэтому сжатие я не рассматривал - поскольку бесплатным оно не бывает.
На самом деле, если у вас дисковая полка - шпиндельная, или иопсы как-то лимитируются, или сервер в виртуальной среде - скорость обращения к данным даже вырастет после включения компрессии PAGE. Т.к. данных будет к диску и обратно будет путешествовать на 10-60% меньше. Нагрузка на процессор при этом растет не сильно, на единицы процентов. С тех пор, как у 2019 стандарт появилось постраничное сжатие - нет причины его не включить.
Если речь зашла о партиционировании, и в случае, если это партиционирование - правильно используется (т.е., например, запросы написаны так, что в них происходит partition eliminations) , то, очень часто, данные можно вообще не архивировать, а оставить в той же базе, или даже в той же таблице. Нужно просто преобразовать партицию в columnstore или columnstore archive.
Кстати, если у вас есть большие (больше нескольких миллионов записей) таблицы, которые по большей части всегда просматриваются целиком - смело преобразуйте их в clustered columnstore. Это сжимает данные в 8-10 раз и ускоряет всяческое агрегирование в тысячи раз. Обратная сторона медали - таблица или партиция всегда сканируется целиком, и плохо работает во всяческих join'ах
В следующем кейсе - явная ошибка, причем не технического плана, а именно ошибкареализации:
with a as (
select
to_char(calendar_dt, 'MM') as mon,
count(distinct id) as cnt
from clients
group by mon
)
select avg(cnt) as mau
from a
Дело в том, что в таблице clients могут быть данные ЗА РАЗНЫЕ ГОДЫ.
И тогда ваш запрос вернет хрень (простите мой французский).
Запрос должен выглядеть как:
with a as (
select
month(calendar_dt) as mon,
count(distinct id) as cnt
from clients
group by year(calendar_dt), month(calendar_dt)
)
select avg(cnt) as mau
from a
Ну, и не забываем, что среднее от int - будет int. А то вдруг вы там десятые ждете? Тогда avg(cnt * 1.0), ну, или явное преобразование к какому либо плавающему или фиксированному типу.
Мои правила создания индексов и работы с ними (ни на что не претендую, просто делюсь):
Если таблица используется только на чтение, например это справочник - допустимо любое количество любых индексов.
Если это справочник, то кластерный индекс должен совпадать с полем, являющимся ключом справочника.
Если таблицы связаны через FK декларативной ссылочной целостностью, индекс по этому полю должен быть (но с учетом п.2)
Не стоит слепо доверять оптимизатору, который рисует зеленым подсказку про недостающий индекс. Во-первых, он может предложить индекс по слабоселективному полю, и индекс будет бесполезен, во-вторых, он может предложить синоним.
Перед тем, как создать индекс, обязательно нужно изучить все имеющиеся. Вдруг ты пытаешься создать синоним?
Обязательно нужно пользоваться административными представлениями, предоставляющими информацию о статистике использования индексов и статистике недостающих индексов. Но слепо верить им нельзя, т.к. и первое и второе дает адекватную информацию только на долго проработавшем сервере. Если ваш сервер каждую ночь перезагружается и не очень интенсивно используется - толку от ваших инструментов чуть.
Много индексов - зло. Мало индексов - зло втройне.
Если система, в любой ипостаси, предлагает сделать индекс по какому то полю, а в инклюд - все остальные поля это повод: а) посмотреть, а не стоит ли по этому полю сделать кластерный индекс или б) игнорировать эти вопли.
Не стоит стесняться использовать датабэйз энжин тюннинг адвизор. Особенно следующим способом: записать профайлером трек активности сервера за длительное время и скормить его адвизору. Но подходить к рекомендациям надо критически.
10 индексов на таблицу - это нормально. И 20. И 30. Зависит от того, что это за таблица и что это за система. Если это какая то витрина, olap (не куб, а просто база с данными для отчетов) - в создании индексов не ограничивает ничего. Ну будут данные заливаться не сутки, а трое, и что с того?
Кстати, отключить все индексы при заливке в хранилище, а потом включить их - позволяет сильно экономить время. Даже десятикратно иногда.
Бывают ситуации, когда даже один индекс - это чересчур много. Даже кластерный. И даже особенно кластерный.
Если вы делаете некластерные индексы на куче - это как минимум странно.
На быстро меняющихся таблицах, если у вас классическое oltp - индексы зло. И из зла надо выбирать меньшее по количеству, весу и большее по селективности.
Индексы стоит создавать даже в базах 1С. Не 1Совские, а sqlные, если вы понимаете о чем я. Но следить за ними надо вручную.
Индексы не нужно регулярно "обслуживать". Т.е. все эти ребилды и реорганайзы каждые сутки, пристальное слежение за фрагментацией - чушь собачья (чуть менее, чем всегда). Вот статистику нужно регулярно пересчитывать, и пристально следить за ее актуальностью, порогом обновления, если пересчет автоматический и т.д. Эффективность ребилда (не реорганайза) индексов происходит из того, что в процессе пересоздания индекса пересчитывается статистика.
Индексы нужно иногда обслуживать, потому что очень сильно фрагментированные большие индексы тупо не очень эффективно расходуют ОЗУ.
Покрывающие индексы - манна небесная.
Колоночные индексы - манна небесная вдвойне. Но использовать их, не поставив в известность разработчиков - не получится. Система, увидев, что на таблице есть (некластерный) колоночный индекс - будет стремиться использовать его, и скорость выполнения запросов волшебным образом сильно упадет. Чтобы этого не произошло, нужно прибить это стремление хинтом в запросах (которым поплохело).
Разработчиков, вообще, крайне желательно ставить в известность по поводу того, чего вы там накреативили с индексами. Ибо!
Не все проблемы с производительностью можно исправить индексами. И даже более того - только некоторые проблемы производительности можно исправить с помощью индексов. Разработчиков нужно приучать писать саргабельные запросы.
Если ваш запрос очень хорошо работает, и тут же - очень плохо работает, 99% дело не в индексе, и не стоит пытаться этот индекс "обслужить". Посмотрите, может дело в блокировках или банальном параметр-сниффинге. Вообще, активность разработчиков в стиле "а давайте перестроим индексы и всё заработает" - нужно пресекать на корню. Если к вам пришли с такой сентенцией - 100 пудов у них в коде говно.
Индексы - если не главный, то очень заметный инструмент по борьбе с дедлоками.
Индексы, наряду с декларативной ссылочной целостностью - главный источник дедлоков.
Поменьше фанатизма. Это к каждому пункту относится.
:-)
Брент, кстати, об этом пишет. "Если пользователи стали замечать блокировки и дедлоки - индексов слишком много".
Мультфильм? Там игры, ДИНАМИЧЕСКИЕ (!!!) можно было программировать.
Абсолютно не шутка. Именно игры, типа "Посадки на Луну", и именно динамические.
На экране мелькали цифры (Показатель скорости), положение в пространстве, в виде ---.---- вот такой картинки на индикаторах, а управление двигателем производилось переключением рычажка градусы-грады-радианы
Еще раз: не пошагово, а ДИНАМИЧЕСКИ.
:-)))
Вообще, обалденная статья. Спасибо!
См: Select ISNUMERIC(',,')
Так что не на 100, но на пару страничек, особенно как обходить нечто подобное, вполне себе.
Напишите?
Никак. Используйте динамический sql, или разворачивайте на клиенте.
Ага. И этот генератор инициируется при каждом запуске. CHECKSUM же - это просто побитовая операция.
Добротнее в математическом смысле (т.е. выдает более случайную последовательность).
Была статья в англоязычном интернете, слету не нагуглилось, но как то так.
Плюс она немного побыстрее, что актуально на миллионных генерациях.
Я например исходный пример уже полчаса дождаться не могу.
Если нужен целочисленный генератор случайных чисел, с равномерным распределением (а обычно нужен как раз целочисленный), лучше (для диапазона 1-10, например):
Не надо рекурсий. С 2016 (или 2017, когда там string_split появился?) - можно так.
С 2022 используйте GENERATE_SERIES
Но-но-но!
Для DWH - 500-800 полей - это, скорее, норма, чем извращение.
Хотя, да, повод вопросить в сторону коллег: "Господа, вы в своем уме?"... ну, в переложении на арго, разумеется.
Кстати, первая задача - не пишите так, это не саргабельно в общем случае.
Пишите :
SELECT CustomerName, City FROM Customers WHERE City LIKE 'S%[^n]';
Аналогичная проблема.
Думаю, что книгу я найду на пиратских сайтах, но мне хотелось бы, чтобы автор получил бонус. Очень жаль, что не получается оплатить. Буду искать способ.
Может есть вариант выложить ее на Литрес? Там с оплатой все нормально...
А чем это принципиально отличается от горизонтального секционирования (с помощью представлений), которое появилось, если мне память не изменяет, в MSSQLSERVER версии 6.5, т.е. лет 30 назад?
ИМХО, в контексте подобных статей хорошо бы смотрелась статья по "старобрядческому" секционированию. С помощью Union all.
Оно, на мой взгляд, незаслуженно забыто, и многие не знают, что "так тоже можно", и, главное, не знают как это "можно" сделать так, чтобы правильно было.
А у него, между прочим, есть куча плюсов, и куча возможностей, которые недоступны обычному секционированию.
Причем их столько, что Майкрософт сподобилось секционирование сделать только к 2008 серверу (если память мне не изменяет), т.е. через 15 лет, т.с.
После включения компрессии не забудьте сделать Alter table ... rebuild
На самом деле, если у вас дисковая полка - шпиндельная, или иопсы как-то лимитируются, или сервер в виртуальной среде - скорость обращения к данным даже вырастет после включения компрессии PAGE. Т.к. данных будет к диску и обратно будет путешествовать на 10-60% меньше.
Нагрузка на процессор при этом растет не сильно, на единицы процентов.
С тех пор, как у 2019 стандарт появилось постраничное сжатие - нет причины его не включить.
Если речь зашла о партиционировании, и в случае, если это партиционирование - правильно используется (т.е., например, запросы написаны так, что в них происходит partition eliminations) , то, очень часто, данные можно вообще не архивировать, а оставить в той же базе, или даже в той же таблице. Нужно просто преобразовать партицию в columnstore или columnstore archive.
Кстати, если у вас есть большие (больше нескольких миллионов записей) таблицы, которые по большей части всегда просматриваются целиком - смело преобразуйте их в clustered columnstore.
Это сжимает данные в 8-10 раз и ускоряет всяческое агрегирование в тысячи раз.
Обратная сторона медали - таблица или партиция всегда сканируется целиком, и плохо работает во всяческих join'ах
... хотя это, по большей части не про 1С
Вот так, конечно же start_dttm < Convert(date, Dateadd(day, 1, Current_timestamp)). Не вычитал, прошу прощения.
В следующем кейсе - явная ошибка, причем не технического плана, а именно ошибка реализации:
Дело в том, что в таблице clients могут быть данные ЗА РАЗНЫЕ ГОДЫ.
И тогда ваш запрос вернет хрень (простите мой французский).
Запрос должен выглядеть как:
Ну, и не забываем, что среднее от int - будет int. А то вдруг вы там десятые ждете? Тогда avg(cnt * 1.0), ну, или явное преобразование к какому либо плавающему или фиксированному типу.