Предисловие
В данной сжатой статье будет приведен обзор ошибок, последствия которых были весьма ощутимыми и с которыми мне приходилось сталкиваться.
Статья написана с целью неповторения данных ошибок. И, как говорится, отрицательный опыт — это тоже опыт и порой даже ценнее положительного.
Ошибки
- Процентное приращение файлов БД (базы данных)
Т к рост файла (будь то данные или журнал транзакций) БД-весьма ресурсоемкая операция, то благими намерениями может показаться выставление этого роста именно в процентных соотношениях. Соглашусь, во многих рекомендациях сказано, что лучше выставлять не процентный, а фиксированный прирост, выраженный в МБ. Однако, не раскрывается почему именно так. Исходя из практики, не рекомендуется устанавливать прирост файла БД выше 1 ГБ, т к MS SQL Server быстрее выделит 2 раза по 1 ГБ, чем сразу 2 ГБ. Также, если выделять меньше 32 МБ (исходя опять же из практики), то рано или поздно сама база данных начинает просто висеть. Отлично, определились, что приращивать файлы БД стоит фиксировано от 32 до 1024 МБ. Но вот почему еще нельзя в процентах указывать прирост файлов БД? Оказывается, что как только файл станет меньше 1 МБ, то СУБД округляет эту величину до 0 МБ и прекращает увеличивать этот файл. В результате возникает простой системы. Чтобы узнать на сколько увеличивать файл, достаточно сделать анализ за сутки-на сколько вырастает каждый из файлов в МБ, и выставить соответствующее число, но в диапазоне от 32 до 1024 МБ. Сбор статистики по росту файлов БД можно получить следующим образом. - Очень много внешних ключей на таблицу
Вы когда-нибудь пробовали смотреть план при удалении хотя бы одной строки из таблицы, на которую ссылаются чуть ли не сотни других таблиц? Вы удивитесь, сколько там вложенных циклов. И все они-это проверки по внешним ключам. Поэтому если таблицы большие (миллионники), то лучше выключить внешние ключи на таблицу, в которой будут удаляться данные, затем удалить все необходимые и связанные с ними данные, и после этого включить внешние ключи. Аналогичная ситуация и с каскадными обновлениями и удалениями. Если внешних связей очень много (сотни), то даже удаление 1 строки может привести к долгой и очень обширной блокировке. - Много лишних индексов
Часто можно встретить в рекомендациях, что при создании внешних ключей, необходимо для них строить индексы, особенно при использовании этих ключей для соединений. Необходимо проверить, что индексы используются, иначе эти лишние индексы будут только тормозить любые операции по модификации данных. Проверить использование индексов можно следующим запросом:
Кодselect DB_NAME(t.database_id) as [DBName] , SCHEMA_NAME(obj.schema_id) as [SchemaName] , OBJECT_NAME(t.object_id) as [ObjectName] , obj.Type as [ObjectType] , obj.Type_Desc as [ObjectTypeDesc] , ind.name as [IndexName] , ind.Type as IndexType , ind.Type_Desc as IndexTypeDesc , ind.Is_Unique as IndexIsUnique , ind.is_primary_key as IndexIsPK , ind.is_unique_constraint as IndexIsUniqueConstraint , t.[Database_ID] , t.[Object_ID] , t.[Index_ID] , t.Last_User_Seek , t.Last_User_Scan , t.Last_User_Lookup , t.Last_System_Seek , t.Last_System_Scan , t.Last_System_Lookup from sys.dm_db_index_usage_stats as t inner join sys.objects as obj on t.[object_id]=obj.[object_id] inner join sys.indexes as ind on t.[object_id]=ind.[object_id] and t.index_id=ind.index_id where (last_user_seek is null or last_user_seek <dateadd(year,-1,getdate())) and (last_user_scan is null or last_user_scan <dateadd(year,-1,getdate())) and (last_user_lookup is null or last_user_lookup <dateadd(year,-1,getdate())) and (last_system_seek is null or last_system_seek <dateadd(year,-1,getdate())) and (last_system_scan is null or last_system_scan <dateadd(year,-1,getdate())) and (last_system_lookup is null or last_system_lookup <dateadd(year,-1,getdate())) and t.database_id>4 and t.[object_id]>0 --исключаются системные БД
- Нерациональное использование ресурсов
Часто можно встретить в рекомендациях, что необходимо журнал транзакций и файл данных БД выносить на разные носители данных. Если использовать RAID 10 с 4-мя и более SSD-дисками, то нет смысла изоляции файлов друг от друга. Для еще большей скорости, при необходимости БД tempdb можно разместить на диске, который был сформирован из ОЗУ. Также слишком большие объемы ОЗУ, которые предоставляются СУБД, приведут к тому, что последний заполнит всю память неактуальными планами запросов. - Битые резервные копии
Как это не банально может звучать, но всегда нужно не просто проверять созданные резервные копии, а также перемещать их на тестовый стенд и восстанавливать. И все это необходимо автоматизировать с последующим уведомлением администраторам как о проблемных, так и об успешных восстановлениях. - Ложная отказоустойчивость
Прежде чем делать из двух и более серверов кластер, необходимо убедиться в том, что система хранения данных тоже отказоустойчива, т к при выходе из строя последнего, сведет к нулю всю отказоустойчивость. - Сложная диагностика без простых проверок
Если происходит простой системы, то необходимо сначала проверить журналы MS SQL Server, а уже потом копаться более детально, т к зачастую все проблемы записываются именно туда. Не проводить простых проверок-это то же самое, что не померить температуру пациента, а сразу проводить сложную диагностику. - Забытые таблицы
Таблицы могут распухнуть ненужными старыми данными, которые либо необходимо архивировать в отдельную БД, либо удалять. Также таблицы могут перестать использоваться. Необходимо об этом помнить.
Это все 8 отрицательных опытов, с которыми мне приходилось сталкиваться.
Не повторяйте приведенных выше ошибок.
Источники:
» Документация по SQL
» Автоматизация по сбору данных о росте таблиц и файлов всех баз данных
Комментарии (38)
Focushift
24.09.2017 21:47+1Что-то в роде "… нюансах работы..", указанные вещи неприянтные конечно, но я не знаю как у других БД обстоят дела.
smple
25.09.2017 01:32по поводу второго пункта это в любой бд это же проверка целостности и ее никак не сделать по другому.
jobgemws Автор
25.09.2017 06:03Совершенно верно. Но весьма неприятно, когда в один момент почему-то зависнет удаление всего одной строки
epee
26.09.2017 18:17вообще второй пункт очень спорный, а из цитируемого куска я подозреваю что аффтор еще и использует каскадное удаление
то лучше выключить внешние ключи на таблицу, в которой будут удаляться данные, затем удалить все необходимые и связанные с ними данные, и после этого включить внешние ключи.
приведу один пример когда отсутствие внешнего ключа или внешний ключ с каскадным удалением может привести к жопе.
Тбалица контрагентов, на нее ссылаются различные таблички с документами и проводками.
что будет если удалить контрагента по которому многогодовая история документооборота и проводок? у кого-то определенно прибавится седины :)))jobgemws Автор
26.09.2017 18:19В любом случае есть каскадное обновление или удаление или нет, план будет плохим с вложенными циклами, а при каскадном удалении или обновлении еще хуже. В п.2 я имел в виду просто множество внешних ключей на таблицу, и в конце п.2 отметил каскадное удаление и обновление.
epee
26.09.2017 18:45+1хорошо, вопрос поставлю по другому — в приведеном мною сценарии (если вы не используете внешние ключи) вы вообще не будите проверять не оставил ли контрагент следа у вас в документации перед удалением его из таблицы?
если будите — то да вы сможете ускорить его удаление без внешних ключей за счет того что будите проверять не по всем завязанным таблицам, а лишь только проверите условно критичные таблицы которых может быть в разы меньше чем общее число ссылающихся таблиц. но всегда есть вероятность что какую-то важную табличку вы забыли добавить в проверку и как результат можете получить проблемы которые я описал выше.
если не будите проверять
вообще чаще всего таблицы на которые ссылается множество (ну в моем понимании «множество» это от 50 и больше, но это субъективно) таблиц — это различного рода таблички-словари. смысла удалять из них что либо как правило нет — т.к. придется много чего из дргих таблиц паравозом удалить.
из моего опыта обычно в сценариях с подобными таблицами записи не удаляются из таких табличек, а помечаются как неактивные — таким образом приложение скрывает таких «удаленных» из различных форм поиска. у нас как-то была таблица на которую ссылалось более сотни других внешними ключами и ни у кого и в мыслях не было эти ключи удалять или отключать.
кароче, второй пункт слишком категоричный и потому фтопку.jobgemws Автор
26.09.2017 18:49Полностью с Вами согласен, но увы не все системы такие. Правильнее-пометить на удаление и ночью удалить, а не в режиме огромной нагрузки от пользователей.
lumini
25.09.2017 07:19Насчет четвертого пункта: я стараюсь на проектах, где такое возможно, переводить режим бэкапов в Simple, чтобы минимально задействовались файлы логов. Это очень сильно оптимизирует нагрузку на диски.
Еще обязательно нужно выделять самый быстрый SSD под tempdb, и только под нее (хотя это зависит от характера базы и числа создаваемых временных таблиц, конечно). Держать рейд-массив из SSD под основные базы круто, но в моем случае нереально по бюджету, поэтому — на хранилище медленное, на темпдб — быстрое. Не так давно увидел пример, когда вынесение темпдб на ссд убрало дедлоки на основной базе, с которыми безуспешно вели борьбу годами.jobgemws Автор
25.09.2017 07:51Вы не сможете использовать зеркалирование и AlwaysOn при простой модели восстановления. Для отказоустойчивости и минимизации потери данных нужна полная модель восстановления с резервным копированием журналов транзакций БД
lumini
25.09.2017 11:20+1Да, безусловно. Но полно примеров небольших баз условно до 1гб размером и без жестких требований к отказоустойчивости, где явно проще сделать полный бэкап раз в день, чем возиться с инкременталом и заодно сократить нагрузку за счет лога транзакций. Но это не делают, так как дефолтная настройка — Full.
jobgemws Автор
25.09.2017 08:42Да и ещё. Вы наверное заметили, что даже если БД находится в простой модели восстановления, то транзакцию можно откатить? Так вот, при простой модели восстановления большая часть записей идёт как раз в БД tempdb. Т е интенсивность нагрузки на диски при любой модели восстановления суммарно по всем БД будет примерно одинакова.
На счёт дифицита средств-да, это не ваша проблема, а владельца бизнеса. Однако, стоит провести полный аудит системы с графиками, числами и выводами что нужно и что будет без этого нужно. И отправить результаты руководству. Важно письмо зафиксировать, чтобы в случае чего не попортить себе репутацию, да и по ТК РФ будет невозможно Вас уволить по статьеlumini
25.09.2017 11:25> при простой модели восстановления большая часть записей идёт как раз в БД tempdb
Вот это, кстати, не знал. Любопытно. А верно ли, что так происходит только при явных указаниях создавать транзакцию (BEGIN TRAN) или всегда?jobgemws Автор
25.09.2017 14:14все явные и неявные транзакции, будет меньше протоколировать при merge-операциях и при массовых вставках, а так все, т к СУБД нужно же как-то откатываться. При полной все протоколируется и даже массовые вставки, но по последнему врать не буду-что конкретно пишет-не смотрел
speshuric
25.09.2017 21:50+1Да и ещё. Вы наверное заметили, что даже если БД находится в простой модели восстановления, то транзакцию можно откатить? Так вот, при простой модели восстановления большая часть записей идёт как раз в БД tempdb. Т е интенсивность нагрузки на диски при любой модели восстановления суммарно по всем БД будет примерно одинакова.
Хм. Тут 3 не связанных утверждения, причем вместе они могут быть неверны. Сейчас поясню.
Вы наверное заметили, что даже если БД находится в простой модели восстановления, то транзакцию можно откатить?
Конечно можно! Ведь MS SQL Server устроен так, что с мелкими-мелкими оговорками он все изменения данных (практически построчно) и события начала-отмены-фиксации транзакций последовательно пишет в журнал транзакций. Причем делает это до начала выполнения следующей команды (write-ahead logging). Типичная нагрузка на ЖТ — огромное число последовательных записей небольшого размера (0,5-60 КБ) и чтения при откатах транзакций, резервном копировании, репликации и т.п. Для минимально протоколируемых операций он, правда пишет не сами изменения, а только идентификаторы страниц. Чуть-чуть это нарушается для tempdb и при Delayed Transaction Durability, но не радикально.
Так вот, при простой модели восстановления большая часть записей идёт как раз в БД tempdb.
При простой модели восстановления, и при наличии минимально протоколируемых операций, процентное соотношение записи в tempdb вырастет относительно общего количества записей, но вырастет именно на отсутвующие в журнале обычной бызы минимально протоколируемые операции.
Т е интенсивность нагрузки на диски при любой модели восстановления суммарно по всем БД будет примерно одинакова.
Да, но это только потому что минимально протоколируемые операции для усредненной OLTP БД скорее исключение, чем правило.
speshuric
25.09.2017 21:29+1Между моделями восстановления simple и full по разница такая же как между bulk-logged и full и отличается только на минимально протоколируемые операции, т.е. массовую вставку в тех или иных проявлениях. Если у вас не ETL-база и не источник для отчетности, то никакой разницы на обычных операциях не будет, хотя может быть разница на регулярных обслуживаниях: alter index rebuild может быть минимально протоколируемой.
DonAlPAtino
26.09.2017 09:31+1>переводить режим бэкапов в Simple, чтобы минимально задействовались файлы логов. Это очень сильно оптимизирует нагрузку на диски
Я может сейчас глупость скажу (прошу сильно не пинать), но разве при включенной модели восстановления full мы не получаем постоянную последовательную запись на диск (лог- файлов), что есть очень хорошо? Особенно если пишем на sdd? Вместо постоянной рендомной записи в файл с данными или в tempdb в свободное место, которое еще и найти надо?jobgemws Автор
26.09.2017 11:52Однозначного ответа дать не могу, но может кто ответит более опытный)
epee
26.09.2017 18:19-2аффтор вообще не понимает как работает журнал транзакций, иначе такой ереси не написал бы.
запись в журнал транзакций всегда последовательная и от модели восстановления не зависит!
почитайте лучше оф доку по тому как устроен журнал транзакций, а не аффтора тынц на BOL
так как любая (почти) тразакция SQL Server соответствует ACID — это значит что сиквел использует механизм упреждающей записи в журнал транзакций. Этим он гарантирует свойство Durability — в случае сбоя в ходе процесса recovery сиквел из журнала транзакций накатит те транзакции которые успели закомитить, но которые из грязных страниц чекпоинт не успел сбросить на диск в файлы данных. поэтому нагрузка на журнал транзакции большая даже если режим восстановления Simple.
насчет моей оговорки «почти» — это относится к фиче 2014 delay durability. там така транзакция может не сразу записаться из logbuffer на диск — поэтому есть небольшой шанс что в случае сбоя мы потеряем данные по закомиченой транзакции
ну и это ересь и отсутствие понимания того как SQL Server управляет памятью
Также слишком большие объемы ОЗУ, которые предоставляются СУБД, приведут к тому, что последний заполнит всю память неактуальными планами запросов.
jobgemws Автор
26.09.2017 18:32Ваш комментарий больше похож из кусков текста, вырванных из разных книг и документаций, чем собственный опыт. BOL все читали, я же исхожу из собственного опыта или опыта, полученного от коллег.
На счет второго-Вы не правы-понаблюдайте как он использует память сами, а не исходя из книжек и документаций.
По журналу транзакций лишь отчасти верно.epee
26.09.2017 19:09-1BOL все читали, я же исхожу из собственного опыта или опыта, полученного от коллег.
По журналу транзакций лишь отчасти верно.
ну поделитесь своим опытом и тем что же неверного вы в моих объяснениях вы там нашли?
и если вы действительно читали БОЛ — почему же не ответили на вопрос DonAlPAtino, а съехали? в приведеной мной ссылке ответ на его вопрос есть
Однозначного ответа дать не могу, но может кто ответит более опытный)
jobgemws Автор
26.09.2017 19:48Скажу лишь одно-всегда нужно настраивать тестовый стенд, максимально похожий по характеристикам на боевую среду, и там проводить эксперименты. И именно эти результаты будут самыми объективными для Вашей системы.
Я сейчас Вас удивлю, но в msdn написано, что при уровне фрагментации 30% нужно индекс реорганизовать, а выше перестроить. И боже упаси если Вы до сих пор так делаете на больших таблицах высоко нагруженных системахepee
26.09.2017 19:58-1я просил про журнал транзакций указать в чем я неправ.
а в ответ — перевод темы на индексы и очевидные рассказы что все нужно проверять на тестовом стенде. ну-ну
вы не умеете вести дискуссии, излагать свои мысли и обсуждать с вами что-либо попросту бесполезно. «чукча не читатель — чукча писатель» это как раз про вас
medvedevia
25.09.2017 23:06Для 1. можно использовать: docs.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization
jobgemws Автор
26.09.2017 06:18Это немного не то
medvedevia
26.09.2017 20:16+1У Вас написано, что увеличение размера файла медленная операция, а эта рекомендация позволяет ускорить эту операцию.
Focushift
Прочитав заголовок, ожидал статью с содержимым «Почему MS SQL гофно».
jobgemws Автор
На самом деле не знал как правильнее назвать статью
А как бы Вы назвали?
AlexeevEugene
«Типичные ошибки администраторов СУБД MS SQL»
jobgemws Автор
Большое спасибо-отредактировал
smalpik
Например, «Советы по работе с MS SQL Server».
HaJIuBauKa
Ожидал увидеть еще 12 статей об отрицательном опыте в использовании MS SQL.
Ну, например так: «Статья 13. Отрицательный опыт использования MS SQL — тоже опыт!»