24.09.2017 16:49
jobgemws 38 7300 Источник

Предисловие


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

Статья написана с целью неповторения данных ошибок. И, как говорится, отрицательный опыт — это тоже опыт и порой даже ценнее положительного.

Ошибки


  1. Процентное приращение файлов БД (базы данных)

    Т к рост файла (будь то данные или журнал транзакций) БД-весьма ресурсоемкая операция, то благими намерениями может показаться выставление этого роста именно в процентных соотношениях. Соглашусь, во многих рекомендациях сказано, что лучше выставлять не процентный, а фиксированный прирост, выраженный в МБ. Однако, не раскрывается почему именно так. Исходя из практики, не рекомендуется устанавливать прирост файла БД выше 1 ГБ, т к MS SQL Server быстрее выделит 2 раза по 1 ГБ, чем сразу 2 ГБ. Также, если выделять меньше 32 МБ (исходя опять же из практики), то рано или поздно сама база данных начинает просто висеть. Отлично, определились, что приращивать файлы БД стоит фиксировано от 32 до 1024 МБ. Но вот почему еще нельзя в процентах указывать прирост файлов БД? Оказывается, что как только файл станет меньше 1 МБ, то СУБД округляет эту величину до 0 МБ и прекращает увеличивать этот файл. В результате возникает простой системы. Чтобы узнать на сколько увеличивать файл, достаточно сделать анализ за сутки-на сколько вырастает каждый из файлов в МБ, и выставить соответствующее число, но в диапазоне от 32 до 1024 МБ. Сбор статистики по росту файлов БД можно получить следующим образом.
  2. Очень много внешних ключей на таблицу

    Вы когда-нибудь пробовали смотреть план при удалении хотя бы одной строки из таблицы, на которую ссылаются чуть ли не сотни других таблиц? Вы удивитесь, сколько там вложенных циклов. И все они-это проверки по внешним ключам. Поэтому если таблицы большие (миллионники), то лучше выключить внешние ключи на таблицу, в которой будут удаляться данные, затем удалить все необходимые и связанные с ними данные, и после этого включить внешние ключи. Аналогичная ситуация и с каскадными обновлениями и удалениями. Если внешних связей очень много (сотни), то даже удаление 1 строки может привести к долгой и очень обширной блокировке.
  3. Много лишних индексов

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

    Код
    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 --исключаются системные БД


  4. Нерациональное использование ресурсов

    Часто можно встретить в рекомендациях, что необходимо журнал транзакций и файл данных БД выносить на разные носители данных. Если использовать RAID 10 с 4-мя и более SSD-дисками, то нет смысла изоляции файлов друг от друга. Для еще большей скорости, при необходимости БД tempdb можно разместить на диске, который был сформирован из ОЗУ. Также слишком большие объемы ОЗУ, которые предоставляются СУБД, приведут к тому, что последний заполнит всю память неактуальными планами запросов.
  5. Битые резервные копии

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

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

    Если происходит простой системы, то необходимо сначала проверить журналы MS SQL Server, а уже потом копаться более детально, т к зачастую все проблемы записываются именно туда. Не проводить простых проверок-это то же самое, что не померить температуру пациента, а сразу проводить сложную диагностику.
  8. Забытые таблицы

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

Это все 8 отрицательных опытов, с которыми мне приходилось сталкиваться.
Не повторяйте приведенных выше ошибок.

Источники:


» Документация по SQL
» Автоматизация по сбору данных о росте таблиц и файлов всех баз данных

Комментарии (38)


  1. Focushift
    24.09.2017 21:33
    #10434688
    +3

    Прочитав заголовок, ожидал статью с содержимым «Почему MS SQL гофно».


    1. jobgemws Автор
      24.09.2017 21:39
      #10434704

      На самом деле не знал как правильнее назвать статью
      А как бы Вы назвали?


      1. AlexeevEugene
        25.09.2017 10:14
        #10435360
        +1

        «Типичные ошибки администраторов СУБД MS SQL»


        1. jobgemws Автор
          25.09.2017 10:57
          #10435450

          Большое спасибо-отредактировал


      1. smalpik
        25.09.2017 10:57
        #10435448
        +1

        Например, «Советы по работе с MS SQL Server».


      1. HaJIuBauKa
        25.09.2017 11:09
        #10435486

        Ожидал увидеть еще 12 статей об отрицательном опыте в использовании MS SQL.
        Ну, например так: «Статья 13. Отрицательный опыт использования MS SQL — тоже опыт!»


  1. Focushift
    24.09.2017 21:47
    #10434724
    +1

    Что-то в роде "… нюансах работы..", указанные вещи неприянтные конечно, но я не знаю как у других БД обстоят дела.


  1. smple
    25.09.2017 01:32
    #10435026

    по поводу второго пункта это в любой бд это же проверка целостности и ее никак не сделать по другому.


    1. jobgemws Автор
      25.09.2017 06:03
      #10435146

      Совершенно верно. Но весьма неприятно, когда в один момент почему-то зависнет удаление всего одной строки


      1. epee
        26.09.2017 18:17
        #10438518

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

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


        приведу один пример когда отсутствие внешнего ключа или внешний ключ с каскадным удалением может привести к жопе.
        Тбалица контрагентов, на нее ссылаются различные таблички с документами и проводками.
        что будет если удалить контрагента по которому многогодовая история документооборота и проводок? у кого-то определенно прибавится седины :)))


        1. jobgemws Автор
          26.09.2017 18:19
          #10438520

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


          1. epee
            26.09.2017 18:45
            #10438586
            +1

            хорошо, вопрос поставлю по другому — в приведеном мною сценарии (если вы не используете внешние ключи) вы вообще не будите проверять не оставил ли контрагент следа у вас в документации перед удалением его из таблицы?
            если будите — то да вы сможете ускорить его удаление без внешних ключей за счет того что будите проверять не по всем завязанным таблицам, а лишь только проверите условно критичные таблицы которых может быть в разы меньше чем общее число ссылающихся таблиц. но всегда есть вероятность что какую-то важную табличку вы забыли добавить в проверку и как результат можете получить проблемы которые я описал выше.
            если не будите проверять

            вообще чаще всего таблицы на которые ссылается множество (ну в моем понимании «множество» это от 50 и больше, но это субъективно) таблиц — это различного рода таблички-словари. смысла удалять из них что либо как правило нет — т.к. придется много чего из дргих таблиц паравозом удалить.

            из моего опыта обычно в сценариях с подобными таблицами записи не удаляются из таких табличек, а помечаются как неактивные — таким образом приложение скрывает таких «удаленных» из различных форм поиска. у нас как-то была таблица на которую ссылалось более сотни других внешними ключами и ни у кого и в мыслях не было эти ключи удалять или отключать.

            кароче, второй пункт слишком категоричный и потому фтопку.


            1. jobgemws Автор
              26.09.2017 18:49
              #10438598

              Полностью с Вами согласен, но увы не все системы такие. Правильнее-пометить на удаление и ночью удалить, а не в режиме огромной нагрузки от пользователей.


  1. lumini
    25.09.2017 07:19
    #10435166

    Насчет четвертого пункта: я стараюсь на проектах, где такое возможно, переводить режим бэкапов в Simple, чтобы минимально задействовались файлы логов. Это очень сильно оптимизирует нагрузку на диски.

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


    1. jobgemws Автор
      25.09.2017 07:51
      #10435180

      Вы не сможете использовать зеркалирование и AlwaysOn при простой модели восстановления. Для отказоустойчивости и минимизации потери данных нужна полная модель восстановления с резервным копированием журналов транзакций БД


      1. lumini
        25.09.2017 11:20
        #10435528
        +1

        Да, безусловно. Но полно примеров небольших баз условно до 1гб размером и без жестких требований к отказоустойчивости, где явно проще сделать полный бэкап раз в день, чем возиться с инкременталом и заодно сократить нагрузку за счет лога транзакций. Но это не делают, так как дефолтная настройка — Full.


        1. jobgemws Автор
          27.09.2017 06:07
          #10439206

          Вы правы, есть такое


    1. jobgemws Автор
      25.09.2017 08:42
      #10435206

      Да и ещё. Вы наверное заметили, что даже если БД находится в простой модели восстановления, то транзакцию можно откатить? Так вот, при простой модели восстановления большая часть записей идёт как раз в БД tempdb. Т е интенсивность нагрузки на диски при любой модели восстановления суммарно по всем БД будет примерно одинакова.
      На счёт дифицита средств-да, это не ваша проблема, а владельца бизнеса. Однако, стоит провести полный аудит системы с графиками, числами и выводами что нужно и что будет без этого нужно. И отправить результаты руководству. Важно письмо зафиксировать, чтобы в случае чего не попортить себе репутацию, да и по ТК РФ будет невозможно Вас уволить по статье


      1. lumini
        25.09.2017 11:25
        #10435536

        > при простой модели восстановления большая часть записей идёт как раз в БД tempdb
        Вот это, кстати, не знал. Любопытно. А верно ли, что так происходит только при явных указаниях создавать транзакцию (BEGIN TRAN) или всегда?


        1. jobgemws Автор
          25.09.2017 14:14
          #10435960

          все явные и неявные транзакции, будет меньше протоколировать при merge-операциях и при массовых вставках, а так все, т к СУБД нужно же как-то откатываться. При полной все протоколируется и даже массовые вставки, но по последнему врать не буду-что конкретно пишет-не смотрел


      1. speshuric
        25.09.2017 21:50
        #10436806
        +1

        Да и ещё. Вы наверное заметили, что даже если БД находится в простой модели восстановления, то транзакцию можно откатить? Так вот, при простой модели восстановления большая часть записей идёт как раз в БД tempdb. Т е интенсивность нагрузки на диски при любой модели восстановления суммарно по всем БД будет примерно одинакова.

        Хм. Тут 3 не связанных утверждения, причем вместе они могут быть неверны. Сейчас поясню.


        Вы наверное заметили, что даже если БД находится в простой модели восстановления, то транзакцию можно откатить?

        Конечно можно! Ведь MS SQL Server устроен так, что с мелкими-мелкими оговорками он все изменения данных (практически построчно) и события начала-отмены-фиксации транзакций последовательно пишет в журнал транзакций. Причем делает это до начала выполнения следующей команды (write-ahead logging). Типичная нагрузка на ЖТ — огромное число последовательных записей небольшого размера (0,5-60 КБ) и чтения при откатах транзакций, резервном копировании, репликации и т.п. Для минимально протоколируемых операций он, правда пишет не сами изменения, а только идентификаторы страниц. Чуть-чуть это нарушается для tempdb и при Delayed Transaction Durability, но не радикально.


        Так вот, при простой модели восстановления большая часть записей идёт как раз в БД tempdb.

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


        Т е интенсивность нагрузки на диски при любой модели восстановления суммарно по всем БД будет примерно одинакова.

        Да, но это только потому что минимально протоколируемые операции для усредненной OLTP БД скорее исключение, чем правило.


        1. jobgemws Автор
          26.09.2017 11:50
          #10437476

          Благодарю за уточнение


    1. speshuric
      25.09.2017 21:29
      #10436792
      +1

      Между моделями восстановления simple и full по разница такая же как между bulk-logged и full и отличается только на минимально протоколируемые операции, т.е. массовую вставку в тех или иных проявлениях. Если у вас не ETL-база и не источник для отчетности, то никакой разницы на обычных операциях не будет, хотя может быть разница на регулярных обслуживаниях: alter index rebuild может быть минимально протоколируемой.


    1. DonAlPAtino
      26.09.2017 09:31
      #10437234
      +1

      >переводить режим бэкапов в Simple, чтобы минимально задействовались файлы логов. Это очень сильно оптимизирует нагрузку на диски
      Я может сейчас глупость скажу (прошу сильно не пинать), но разве при включенной модели восстановления full мы не получаем постоянную последовательную запись на диск (лог- файлов), что есть очень хорошо? Особенно если пишем на sdd? Вместо постоянной рендомной записи в файл с данными или в tempdb в свободное место, которое еще и найти надо?


      1. jobgemws Автор
        26.09.2017 11:52
        #10437478

        Однозначного ответа дать не могу, но может кто ответит более опытный)


      1. epee
        26.09.2017 18:19
        #10438528
        -2

        аффтор вообще не понимает как работает журнал транзакций, иначе такой ереси не написал бы.
        запись в журнал транзакций всегда последовательная и от модели восстановления не зависит!
        почитайте лучше оф доку по тому как устроен журнал транзакций, а не аффтора тынц на BOL
        так как любая (почти) тразакция SQL Server соответствует ACID — это значит что сиквел использует механизм упреждающей записи в журнал транзакций. Этим он гарантирует свойство Durability — в случае сбоя в ходе процесса recovery сиквел из журнала транзакций накатит те транзакции которые успели закомитить, но которые из грязных страниц чекпоинт не успел сбросить на диск в файлы данных. поэтому нагрузка на журнал транзакции большая даже если режим восстановления Simple.
        насчет моей оговорки «почти» — это относится к фиче 2014 delay durability. там така транзакция может не сразу записаться из logbuffer на диск — поэтому есть небольшой шанс что в случае сбоя мы потеряем данные по закомиченой транзакции

        ну и это ересь и отсутствие понимания того как SQL Server управляет памятью

        Также слишком большие объемы ОЗУ, которые предоставляются СУБД, приведут к тому, что последний заполнит всю память неактуальными планами запросов.


        1. jobgemws Автор
          26.09.2017 18:32
          #10438554

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


          1. epee
            26.09.2017 19:09
            #10438644
            -1

            BOL все читали, я же исхожу из собственного опыта или опыта, полученного от коллег.

            По журналу транзакций лишь отчасти верно.

            ну поделитесь своим опытом и тем что же неверного вы в моих объяснениях вы там нашли?

            и если вы действительно читали БОЛ — почему же не ответили на вопрос DonAlPAtino, а съехали? в приведеной мной ссылке ответ на его вопрос есть
            Однозначного ответа дать не могу, но может кто ответит более опытный)


            1. jobgemws Автор
              26.09.2017 19:48
              #10438714

              Скажу лишь одно-всегда нужно настраивать тестовый стенд, максимально похожий по характеристикам на боевую среду, и там проводить эксперименты. И именно эти результаты будут самыми объективными для Вашей системы.
              Я сейчас Вас удивлю, но в msdn написано, что при уровне фрагментации 30% нужно индекс реорганизовать, а выше перестроить. И боже упаси если Вы до сих пор так делаете на больших таблицах высоко нагруженных системах


              1. epee
                26.09.2017 19:58
                #10438726
                -1

                я просил про журнал транзакций указать в чем я неправ.
                а в ответ — перевод темы на индексы и очевидные рассказы что все нужно проверять на тестовом стенде. ну-ну
                вы не умеете вести дискуссии, излагать свои мысли и обсуждать с вами что-либо попросту бесполезно. «чукча не читатель — чукча писатель» это как раз про вас


  1. Rubinchik
    25.09.2017 12:10
    #10435652
    +1

    по checkdb можно еще многое добавить


    1. jobgemws Автор
      25.09.2017 12:11
      #10435654

      Согласен-п.5 это подразумевает


  1. medvedevia
    25.09.2017 23:06
    #10436908

    Для 1. можно использовать: docs.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization


    1. jobgemws Автор
      26.09.2017 06:18
      #10437118

      Это немного не то


      1. medvedevia
        26.09.2017 20:16
        #10438750
        +1

        У Вас написано, что увеличение размера файла медленная операция, а эта рекомендация позволяет ускорить эту операцию.


        1. jobgemws Автор
          26.09.2017 20:42
          #10438786

          Спасибо)
          Блин через мобильник случайно -1 поставил Вашему комменты, чет обратно нельзя
          Хотел плюсануть


          1. 71rmn
            27.09.2017 18:16
            #10440736
            +1

            +1
            компенсировал )


            1. jobgemws Автор
              27.09.2017 18:51
              #10440834

              Благодарю)