Comments 38
Прочитав заголовок, ожидал статью с содержимым «Почему MS SQL гофно».
Что-то в роде "… нюансах работы..", указанные вещи неприянтные конечно, но я не знаю как у других БД обстоят дела.
по поводу второго пункта это в любой бд это же проверка целостности и ее никак не сделать по другому.
Совершенно верно. Но весьма неприятно, когда в один момент почему-то зависнет удаление всего одной строки
вообще второй пункт очень спорный, а из цитируемого куска я подозреваю что аффтор еще и использует каскадное удаление
приведу один пример когда отсутствие внешнего ключа или внешний ключ с каскадным удалением может привести к жопе.
Тбалица контрагентов, на нее ссылаются различные таблички с документами и проводками.
что будет если удалить контрагента по которому многогодовая история документооборота и проводок? у кого-то определенно прибавится седины :)))
то лучше выключить внешние ключи на таблицу, в которой будут удаляться данные, затем удалить все необходимые и связанные с ними данные, и после этого включить внешние ключи.
приведу один пример когда отсутствие внешнего ключа или внешний ключ с каскадным удалением может привести к жопе.
Тбалица контрагентов, на нее ссылаются различные таблички с документами и проводками.
что будет если удалить контрагента по которому многогодовая история документооборота и проводок? у кого-то определенно прибавится седины :)))
В любом случае есть каскадное обновление или удаление или нет, план будет плохим с вложенными циклами, а при каскадном удалении или обновлении еще хуже. В п.2 я имел в виду просто множество внешних ключей на таблицу, и в конце п.2 отметил каскадное удаление и обновление.
хорошо, вопрос поставлю по другому — в приведеном мною сценарии (если вы не используете внешние ключи) вы вообще не будите проверять не оставил ли контрагент следа у вас в документации перед удалением его из таблицы?
если будите — то да вы сможете ускорить его удаление без внешних ключей за счет того что будите проверять не по всем завязанным таблицам, а лишь только проверите условно критичные таблицы которых может быть в разы меньше чем общее число ссылающихся таблиц. но всегда есть вероятность что какую-то важную табличку вы забыли добавить в проверку и как результат можете получить проблемы которые я описал выше.
если не будите проверять
вообще чаще всего таблицы на которые ссылается множество (ну в моем понимании «множество» это от 50 и больше, но это субъективно) таблиц — это различного рода таблички-словари. смысла удалять из них что либо как правило нет — т.к. придется много чего из дргих таблиц паравозом удалить.
из моего опыта обычно в сценариях с подобными таблицами записи не удаляются из таких табличек, а помечаются как неактивные — таким образом приложение скрывает таких «удаленных» из различных форм поиска. у нас как-то была таблица на которую ссылалось более сотни других внешними ключами и ни у кого и в мыслях не было эти ключи удалять или отключать.
кароче, второй пункт слишком категоричный и потому фтопку.
если будите — то да вы сможете ускорить его удаление без внешних ключей за счет того что будите проверять не по всем завязанным таблицам, а лишь только проверите условно критичные таблицы которых может быть в разы меньше чем общее число ссылающихся таблиц. но всегда есть вероятность что какую-то важную табличку вы забыли добавить в проверку и как результат можете получить проблемы которые я описал выше.
если не будите проверять
вообще чаще всего таблицы на которые ссылается множество (ну в моем понимании «множество» это от 50 и больше, но это субъективно) таблиц — это различного рода таблички-словари. смысла удалять из них что либо как правило нет — т.к. придется много чего из дргих таблиц паравозом удалить.
из моего опыта обычно в сценариях с подобными таблицами записи не удаляются из таких табличек, а помечаются как неактивные — таким образом приложение скрывает таких «удаленных» из различных форм поиска. у нас как-то была таблица на которую ссылалось более сотни других внешними ключами и ни у кого и в мыслях не было эти ключи удалять или отключать.
кароче, второй пункт слишком категоричный и потому фтопку.
Насчет четвертого пункта: я стараюсь на проектах, где такое возможно, переводить режим бэкапов в Simple, чтобы минимально задействовались файлы логов. Это очень сильно оптимизирует нагрузку на диски.
Еще обязательно нужно выделять самый быстрый SSD под tempdb, и только под нее (хотя это зависит от характера базы и числа создаваемых временных таблиц, конечно). Держать рейд-массив из SSD под основные базы круто, но в моем случае нереально по бюджету, поэтому — на хранилище медленное, на темпдб — быстрое. Не так давно увидел пример, когда вынесение темпдб на ссд убрало дедлоки на основной базе, с которыми безуспешно вели борьбу годами.
Еще обязательно нужно выделять самый быстрый SSD под tempdb, и только под нее (хотя это зависит от характера базы и числа создаваемых временных таблиц, конечно). Держать рейд-массив из SSD под основные базы круто, но в моем случае нереально по бюджету, поэтому — на хранилище медленное, на темпдб — быстрое. Не так давно увидел пример, когда вынесение темпдб на ссд убрало дедлоки на основной базе, с которыми безуспешно вели борьбу годами.
Вы не сможете использовать зеркалирование и AlwaysOn при простой модели восстановления. Для отказоустойчивости и минимизации потери данных нужна полная модель восстановления с резервным копированием журналов транзакций БД
Да, безусловно. Но полно примеров небольших баз условно до 1гб размером и без жестких требований к отказоустойчивости, где явно проще сделать полный бэкап раз в день, чем возиться с инкременталом и заодно сократить нагрузку за счет лога транзакций. Но это не делают, так как дефолтная настройка — Full.
Да и ещё. Вы наверное заметили, что даже если БД находится в простой модели восстановления, то транзакцию можно откатить? Так вот, при простой модели восстановления большая часть записей идёт как раз в БД tempdb. Т е интенсивность нагрузки на диски при любой модели восстановления суммарно по всем БД будет примерно одинакова.
На счёт дифицита средств-да, это не ваша проблема, а владельца бизнеса. Однако, стоит провести полный аудит системы с графиками, числами и выводами что нужно и что будет без этого нужно. И отправить результаты руководству. Важно письмо зафиксировать, чтобы в случае чего не попортить себе репутацию, да и по ТК РФ будет невозможно Вас уволить по статье
На счёт дифицита средств-да, это не ваша проблема, а владельца бизнеса. Однако, стоит провести полный аудит системы с графиками, числами и выводами что нужно и что будет без этого нужно. И отправить результаты руководству. Важно письмо зафиксировать, чтобы в случае чего не попортить себе репутацию, да и по ТК РФ будет невозможно Вас уволить по статье
> при простой модели восстановления большая часть записей идёт как раз в БД tempdb
Вот это, кстати, не знал. Любопытно. А верно ли, что так происходит только при явных указаниях создавать транзакцию (BEGIN TRAN) или всегда?
Вот это, кстати, не знал. Любопытно. А верно ли, что так происходит только при явных указаниях создавать транзакцию (BEGIN TRAN) или всегда?
Да и ещё. Вы наверное заметили, что даже если БД находится в простой модели восстановления, то транзакцию можно откатить? Так вот, при простой модели восстановления большая часть записей идёт как раз в БД tempdb. Т е интенсивность нагрузки на диски при любой модели восстановления суммарно по всем БД будет примерно одинакова.
Хм. Тут 3 не связанных утверждения, причем вместе они могут быть неверны. Сейчас поясню.
Вы наверное заметили, что даже если БД находится в простой модели восстановления, то транзакцию можно откатить?
Конечно можно! Ведь MS SQL Server устроен так, что с мелкими-мелкими оговорками он все изменения данных (практически построчно) и события начала-отмены-фиксации транзакций последовательно пишет в журнал транзакций. Причем делает это до начала выполнения следующей команды (write-ahead logging). Типичная нагрузка на ЖТ — огромное число последовательных записей небольшого размера (0,5-60 КБ) и чтения при откатах транзакций, резервном копировании, репликации и т.п. Для минимально протоколируемых операций он, правда пишет не сами изменения, а только идентификаторы страниц. Чуть-чуть это нарушается для tempdb и при Delayed Transaction Durability, но не радикально.
Так вот, при простой модели восстановления большая часть записей идёт как раз в БД tempdb.
При простой модели восстановления, и при наличии минимально протоколируемых операций, процентное соотношение записи в tempdb вырастет относительно общего количества записей, но вырастет именно на отсутвующие в журнале обычной бызы минимально протоколируемые операции.
Т е интенсивность нагрузки на диски при любой модели восстановления суммарно по всем БД будет примерно одинакова.
Да, но это только потому что минимально протоколируемые операции для усредненной OLTP БД скорее исключение, чем правило.
Между моделями восстановления simple и full по разница такая же как между bulk-logged и full и отличается только на минимально протоколируемые операции, т.е. массовую вставку в тех или иных проявлениях. Если у вас не ETL-база и не источник для отчетности, то никакой разницы на обычных операциях не будет, хотя может быть разница на регулярных обслуживаниях: alter index rebuild может быть минимально протоколируемой.
>переводить режим бэкапов в Simple, чтобы минимально задействовались файлы логов. Это очень сильно оптимизирует нагрузку на диски
Я может сейчас глупость скажу (прошу сильно не пинать), но разве при включенной модели восстановления full мы не получаем постоянную последовательную запись на диск (лог- файлов), что есть очень хорошо? Особенно если пишем на sdd? Вместо постоянной рендомной записи в файл с данными или в tempdb в свободное место, которое еще и найти надо?
Я может сейчас глупость скажу (прошу сильно не пинать), но разве при включенной модели восстановления full мы не получаем постоянную последовательную запись на диск (лог- файлов), что есть очень хорошо? Особенно если пишем на sdd? Вместо постоянной рендомной записи в файл с данными или в tempdb в свободное место, которое еще и найти надо?
Однозначного ответа дать не могу, но может кто ответит более опытный)
аффтор вообще не понимает как работает журнал транзакций, иначе такой ереси не написал бы.
запись в журнал транзакций всегда последовательная и от модели восстановления не зависит!
почитайте лучше оф доку по тому как устроен журнал транзакций, а не аффтора тынц на BOL
так как любая (почти) тразакция SQL Server соответствует ACID — это значит что сиквел использует механизм упреждающей записи в журнал транзакций. Этим он гарантирует свойство Durability — в случае сбоя в ходе процесса recovery сиквел из журнала транзакций накатит те транзакции которые успели закомитить, но которые из грязных страниц чекпоинт не успел сбросить на диск в файлы данных. поэтому нагрузка на журнал транзакции большая даже если режим восстановления Simple.
насчет моей оговорки «почти» — это относится к фиче 2014 delay durability. там така транзакция может не сразу записаться из logbuffer на диск — поэтому есть небольшой шанс что в случае сбоя мы потеряем данные по закомиченой транзакции
ну и это ересь и отсутствие понимания того как SQL Server управляет памятью
запись в журнал транзакций всегда последовательная и от модели восстановления не зависит!
почитайте лучше оф доку по тому как устроен журнал транзакций, а не аффтора тынц на BOL
так как любая (почти) тразакция SQL Server соответствует ACID — это значит что сиквел использует механизм упреждающей записи в журнал транзакций. Этим он гарантирует свойство Durability — в случае сбоя в ходе процесса recovery сиквел из журнала транзакций накатит те транзакции которые успели закомитить, но которые из грязных страниц чекпоинт не успел сбросить на диск в файлы данных. поэтому нагрузка на журнал транзакции большая даже если режим восстановления Simple.
насчет моей оговорки «почти» — это относится к фиче 2014 delay durability. там така транзакция может не сразу записаться из logbuffer на диск — поэтому есть небольшой шанс что в случае сбоя мы потеряем данные по закомиченой транзакции
ну и это ересь и отсутствие понимания того как SQL Server управляет памятью
Также слишком большие объемы ОЗУ, которые предоставляются СУБД, приведут к тому, что последний заполнит всю память неактуальными планами запросов.
Ваш комментарий больше похож из кусков текста, вырванных из разных книг и документаций, чем собственный опыт. BOL все читали, я же исхожу из собственного опыта или опыта, полученного от коллег.
На счет второго-Вы не правы-понаблюдайте как он использует память сами, а не исходя из книжек и документаций.
По журналу транзакций лишь отчасти верно.
На счет второго-Вы не правы-понаблюдайте как он использует память сами, а не исходя из книжек и документаций.
По журналу транзакций лишь отчасти верно.
BOL все читали, я же исхожу из собственного опыта или опыта, полученного от коллег.
По журналу транзакций лишь отчасти верно.
ну поделитесь своим опытом и тем что же неверного вы в моих объяснениях вы там нашли?
и если вы действительно читали БОЛ — почему же не ответили на вопрос DonAlPAtino, а съехали? в приведеной мной ссылке ответ на его вопрос есть
Однозначного ответа дать не могу, но может кто ответит более опытный)
Скажу лишь одно-всегда нужно настраивать тестовый стенд, максимально похожий по характеристикам на боевую среду, и там проводить эксперименты. И именно эти результаты будут самыми объективными для Вашей системы.
Я сейчас Вас удивлю, но в msdn написано, что при уровне фрагментации 30% нужно индекс реорганизовать, а выше перестроить. И боже упаси если Вы до сих пор так делаете на больших таблицах высоко нагруженных системах
Я сейчас Вас удивлю, но в msdn написано, что при уровне фрагментации 30% нужно индекс реорганизовать, а выше перестроить. И боже упаси если Вы до сих пор так делаете на больших таблицах высоко нагруженных системах
я просил про журнал транзакций указать в чем я неправ.
а в ответ — перевод темы на индексы и очевидные рассказы что все нужно проверять на тестовом стенде. ну-ну
вы не умеете вести дискуссии, излагать свои мысли и обсуждать с вами что-либо попросту бесполезно. «чукча не читатель — чукча писатель» это как раз про вас
а в ответ — перевод темы на индексы и очевидные рассказы что все нужно проверять на тестовом стенде. ну-ну
вы не умеете вести дискуссии, излагать свои мысли и обсуждать с вами что-либо попросту бесполезно. «чукча не читатель — чукча писатель» это как раз про вас
по checkdb можно еще многое добавить
Для 1. можно использовать: docs.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization
Sign up to leave a comment.
13-я статья о типичных ошибках DBA MS SQL Server