Анализ работы MS SQL Server, для тех кто видит его впервые

Опубликовано продолжение: часть 2

Недавно столкнулся с проблемой — занедужил SVN на ubuntu server. Сам я программирую под windows и с linux “на Вы”… Погуглил по ошибке — безрезультатно. Ошибка оказалась самая типовая (сервер неожиданно закрыл соединение) и ни о чем конкретном не говорящая. Следовательно, надо погружаться глубже и анализировать логи/настройки/права/и т.п., а с этим, как раз, я “на Вы”.

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

Писать я буду не про линукс — проблему хоть и решил, но профессионалом вряд ли стал. Напишу про более знакомый мне MS SQL. Благо, уже приходилось много раз отвечать на вопросы и список типовых уже готов.

Для кого пишу

Если вы админ в Сбере (или в Яндексе или <другая топ-100 компания>), вы можете сохранить статью в избранное. Да, пригодится! Когда к вам, в очередной раз, с одними и теми же вопросами придут новички — Вы дадите им ссылку на нее. Это сэкономит Ваше время.

Если без шуток, эта СУБД часто используется в небольших компаниях. Часто совместно с 1С либо другим ПО. Отдельного БД-админа таким компаниям держать затратно — надо будет выкручиваться обычному ИТ-шнику. Для таких и пишу.

Какие проблемы рассмотрим

Если сервер вам сообщает “закончилось место на диске Е” — глубокий анализ не нужен. Не будем рассматривать ошибки, решение которых очевидно из текста сообщения. Также не будем рассматривать ошибки по которым гугл сразу выдает ссылку на msdn с решением.
Рассмотрим проблемы по которым не очевидно что гуглить. Такие как, например, внезапное падение производительности или, например, отсутствие соединения. Рассмотрим основные инструменты для настройки. Рассмотрим средства анализа. Поищем где лежат логи и другая полезная информация. И в целом, попробую в одной статье собрать нужную информацию для быстрого старта.

Самое первое

Начнем с лидера списка частых вопросов, настолько он опережает всех, что рассмотрим его отдельно. Вдобавок, об этом пишут во всех статьях про работу MS SQL — и я не буду нарушать традицию.

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

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

Сокращения и приложения
  • SSMS — приложение “Microsoft SQL Server Management Studio”, находится в “Пуске”. Устанавливается отдельной галочкой (Client management tools) с дистрибутива сервера. Начиная с 2016 версии, доступно бесплатно на сайте MS в виде отдельного приложения. Старшие версии студии нормально работают с младшими версиями сервера. Наоборот — тоже иногда работают (основные функции).
    docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms “SSMS is free! It does not require a license to install and use.”
  • Profiler — приложение “SQL Server Profiler”, находится в “Пуске”, устанавливается вместе с SSMS.
  • Performance Monitor (Системный монитор) — оснастка панели управления. Позволяет мониторить счетчики производительности, журналировать и просматривать историю замеров.


Обновление статистики с помощью “плана обслуживания”:

  • запускаем SSMS;
  • подключаемся к нужному серверу;
  • разворачиваем в Object Inspector дерево: Management \ Maintenance Plans (Планы обслуживания)
  • правой кнопкой на узле, выбираем “Maintenance Plan Wizard”
  • в визарде мышкой отмечаем нужные нам задачи:
    • rebuild index (перестроить индекс)
    • update statistics (обновить статистику)
  • отметить можно обе задачи сразу, либо сделать два плана обслуживания по одной задаче в каждом (смотрим “важные замечания” ниже);
  • далее, отмечаем галочками нужную нам БД (или несколько). Делаем это для каждой задачи (если выбрали две задачи — будет два диалога с выбором БД).
  • Next, Next, Finish

После этих действий у вас создастся (а не выполнится) “план обслуживания”. Запуск можно выполнить вручную — правой кнопкой на нем, выбрать “Execute”. Либо настроить запуск через “SQL Agent”.

Важные замечания:

  • Обновление статистики — неблокирующая операция. Можно выполнять в рабочем режиме. Дополнительную нагрузку конечно создаст, но ведь у вас и так всё тормозит, будет чуть больше — незаметно.
  • Перестроение индекса — блокирующая операция. Запускать только в нерабочее время. Есть исключение — Enterprise редакция сервера допускает выполнение “онлайнового ребилда”. Эта опция включается галочкой в настройках задачи. Обратите внимание, галочка есть во всех редакциях, но работает только в Enterprise.
  • Конечно, эти задачи необходимо выполнять регулярно. Предлагаю простой способ определения, как часто это делать:
    • при первых проблемах выполняете план обслуживания;
    • если помогло — ждете пока не начнутся проблемы снова (как правило, до очередного закрытия месяца/расчета зп/ и т.п. массовых операций);
    • получившийся срок нормальной работы и будет вам ориентиром;
    • например, настройте выполнение плана обслуживания в два раза чаще.

Сервер работает медленно — что делать?


Используемые сервером ресурсы

Как и любой другой программе, серверу нужны: время процессора, данные на диске, объемы оперативной памяти и пропускная способность сети.

Оценить нехватку того либо иного ресурса в первом приближении можно с помощью Task Manager (Диспетчер задач), как бы по кэпски это не звучало.

Загрузка ЦП

Посмотреть загрузку в диспетчере сможет даже школьник. Здесь нам надо просто убедиться, что если процессор загружен, то именно процессом sqlserver.exe.

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

Загрузка диска

Многие смотрят только загрузку процессора, но не надо забывать что СУБД — это хранилище данных. Объемы данных растут, производительность процессоров растет, а скорость HDD практически не меняется. С SSD ситуация получше, но терабайты на них хранить затратно.

Получается так, что я чаще сталкиваюсь с ситуациями, когда узким местом становится именно дисковая система, а не ЦПУ.

Для дисков нам важны следующие показатели:

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

Серверная версия диспетчера задач, как правило (зависит от версии системы), показывает и то и другое. Если нет — запускаем оснастку панели управления “Performance Monitor” (Системный монитор). Нас интересуют счетчики:

  • Физический (логический) диск / Среднее время чтения (записи)
  • Физический (логический) диск / Средняя длина очереди диска
  • Физический (логический) диск / Скорость обмена с диском

Развернуто — можно почитать мануалы производителя, например тут social.technet.microsoft.com/wiki/contents/articles/3214.monitoring-disk-usage.aspx. Вкратце:

  • Очередь желательно чтобы не превышала 1. Допустимы кратковременные всплески, если они быстро спадают. Всплески могут быть разными в зависимости от вашей системы. Для простого рэйда-зеркала из двух HDD — очередь больше 10-20 проблема. Для крутой библиотеки с супер кешированием я видел всплески до 600-800 которые мгновенно рассасывались, не приводя к задержкам.
  • Нормальная скорость обмена тоже зависит от типа дисковой системы. Обычный (настольный) HDD “качает” по 50-100 Мб/с. Хорошая дисковая библиотека по 500 Мб/с и более. Для мелких случайных операций скорость меньше. Примерно так и ориентируйтесь.
  • Эти параметры надо смотреть в комплексе. Если ваша библиотека качает 50Мб/с и при этом выстраивается очередь в 50 операций — явно что-то не так с железом. Если очередь выстраивается при прокачке близкой к максимальной — то скорее всего диски не виноваты — они просто больше не могут — надо искать способ уменьшить нагрузку.
  • Нагрузку надо смотреть раздельно по дискам (если их несколько) и сопоставлять с размещением файлов сервера. Диспетчер задач может показать наиболее активно используемые файлы. Это удобно использовать, чтобы убедиться, что нагрузка идет именно от СУБД.

Чем могут быть вызваны проблемы с дисковой системой:

  • проблемы с железом
    • погорел кэш, резко упала производительность;
    • дисковая система используется чем-то еще;
  • Недостаток оперативной памяти. Свопинг. Ухудшилось кэширование, производительность упала (смотрим раздел про ОП ниже).
  • Увеличилась пользовательская нагрузка. Необходимо оценить работу пользователей (проблемный запрос / новый функционал / увеличение количества пользователей / увеличение объема данных / и т.п.).
  • Фрагментация данных БД (смотрим ребилд индексов выше), фрагментация файлов системы.
  • Дисковая система достигла своих максимальных возможностей.

Если у вас последний вариант — не спешите выкидывать оборудование. Иногда из системы можно выжать чуть больше если подойти к проблеме с умом. Проверьте размещение файлов системы на соответствие рекомендуемым требованиям:

  • не смешивайте файлы ОС с файлами данных БД. Размещайте их на физически разных носителях чтобы система не конкурировала с СУБД за ввод-вывод.
  • БД состоит из файлов двух видов: данные (*.mdf, *.ndf) и логи (*.ldf). Файлы данных, как правило, больше используются на чтение. Логи — больше на запись (причем запись — последовательная). Из понимания этого факта, следует рекомендация размещать логи и данные на физически разных носителях, чтобы запись в лог не прерывала чтение данных (как правило, операция записи имеет приоритет выше чем у чтения).
  • MS SQL для обработки запросов может использовать “временные таблицы”. Они хранятся в системной базе tempdb. Если у вас высокая нагрузка на файлы этой БД — то можно попробовать вынести ее на физически отдельные носители.

Резюмируя по размещению файлов, используйте принцип “разделяй и властвуй”. Оцените к каким файлам идут обращения и попробуйте их распределить на разные носители. Также, используйте особенности RAID систем. Например, RAID-5 читает быстрее чем пишет — что хорошо подходит для файлов данных.

В продолжении:

  • анализируем использование ОП и сети.
  • смотрим детально работу пользователей используя SSMS, profiler и прямые запросы к системным представлениям.
  • план и статистика запросов (рассмотрим несколько способов получения). live query statistics.
  • waits (ожидания). текущая информация и статистика.
  • проблемы с подключением к серверу. процессы/порты/протоколы
AdBlock has stolen the banner, but banners are not teeth — they will be back

More
Ads

Comments 28

    +1

    Меня (возможно и не только меня) уже на протяжении многих лет в разных проектах преследует спонтанно возникающий глюк у mssql: условно говоря раз в месяц/другой (но строгой периодичности нет) он беспричинно начинает грузить одно ядро процессора и почти перестает отвечать на запросы. Что я только ни делал… обрубал все сетевые коннекты, останавливал сайты, даже рестартовал инстанс- всё равно он сразу возвращался к тормозам. В логах и профайлере никаких признаков ддоса или циклических запросов выявить не могу. Чаще всего помогает ребут винды.

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

        спасибо, буду рад )) Правда в моем случае я не уверен, что эти проблемы в прямой зависимости от запросов пользователей (ну или учетки движка сайта), ибо тогда оно по-любому отлавливалось бы профайлером.

          0
          У нас такое было, когда мы на одном виндовом сервере поставили два Sql Server: Developer Edition (default instance) и Express Edition (named instance). Developer сервер начал безпричинно тормозить, хотя никто его не трогал. Все, что знал из мониторинговых средств, попробовал — ничего страшного не нашел. С выключением и последующим удалением Express сервера тормоза исчезли. Справедливости ради, спустя какое-то время снова попробовал поставить второй инстанс Sql Express, с тех пор они живут дружно уже почти полгода.
      +2

      Что касается нагрузки на диски — в первую очередь нужно выучить, что такое IOPS и как они связаны с пропускной способностью. И тогда внезапно может стать понятно, что, например, 50Мб/сек на 4K блоках со случайным доступом может быть совсем не мало, и высокая очередь — это никакие не проблемы с железом, а банально недостаточно производительная дисковая.


      не смешивайте файлы ОС с файлами данных БД. Размещайте их на физически разных носителях чтобы система не конкурировала с СУБД за ввод-вывод.

      А что, разве ОС сама по себе создает какую-то серьезную нагрузку на диски? Разве только при постоянном своппинге, но тогда не о разнесении нагрузки на разные диски думать надо, а совсем о другом. Предлагаю вам на досуге померять, что будет быстрее — ОС + СУБД на RAID-10 из 4-х дисков, или ОС и СУБД на отдельных зеркалах.


      И т.д, и т.п.


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

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

          Я не вижу, как это может произойти, если вы описываете волшебные пилюли вместо того, чтобы давать понимание. Для примера — ну нельзя понять, что в случае СУБД происходит с нагрузкой на диски, не оперируя понятием IOPS, а вы это даже мимоходом не упоминаете.

            –1
            исходил из предположения что мсскл пишет данные страницами. в этом случае зависимость между IOPS и Mb/s — линейная.
            просто для меня лично удобнее прокачку оценивать в Мб/с, но тут кому как.
              0

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


              Если понимать, что скорость случайного чтения ограничена средним временем поиска, то становится ясно, что, скажем, с 15К SAS HDD с паспортным средним временем поиска 3.3мс вы не снимете больше примерно 300 IOPS, что, например, на 4К блоках даст нам жалкие 1.2 Мбайт/сек. А для Enterprise SATA, которые сейчас весьма популярны, цифры уменьшаются еще вдвое. А, скажем, 50Мбайт/сек в таких условиях можно снять только с массива из минимум 40 15K SAS HDD, что в природе хоть и встречается, но отнюдь не у целевой аудитории вашей статьи.


              Поэтому для оценки, насколько адекватно работает система в режиме случайного доступа, в перфмоне смотреть надо вовсе не скорость чтения, а количество операций в секунду (ну или уметь пересчитывать одно в другое).

          +1
          Автор, скорее всего, имел ввиду не размещать на одном логическом диске файлы mdf и ldf. Т.к. mdf пишется параллельно, а ldf последовательно. Тут может быть гонка на ввод-вывод, если они рядом.
            0
            А что, разве ОС сама по себе создает какую-то серьезную нагрузку на диски?

            ОС может и не создает, но сам диск может оказаться совсем не производительным. Встречал случаи когда С: создан внутри виртуальной машины которая лежит на общей хранилке с кучей всего остального и производительность там совсем никакая.
            В таком случае, вполне разумно файлы БД убрать с С:.
            прошу не воспринимать раздел «советы» как руководство к действию, а лучше как «варианты на подумать».
            надо будет в следующий раз в статье предупреждение указать :)
              0
              У Вас сравнение нечестное. Надо сравнивать ОС+СУБД на RAID10 vs. ОС на RAID10 + СУБД на RAID10.
              Разумеется, RAID10 быстрее RAID1 примерно в два раза будет. А ОС не даст такую же нагрузку, как СУБД.
                0

                Все честно. Вот есть у нас сервер с N дисками. Тормозит. Мы решаем вынести ОС на отдельное зеркало. Но откуда диски под него взять? При работе в рамках имеющегося оборудования, без апгрейда — только изъять из имеющегося массива. Вот и получаем в реальной жизни сравнение "N дисков, но вместе с ОС" против "N-2, но без ОС".

                  0
                  Погодите. Вы решаете задачу «разделить ОС и данные имеющимися средствами» или приводите аргумент в пользу своего «ОС не даёт сильной нагрузки и пофиг, на одном носителе или на разных у нас данные и ОС»?
                  Если первое — Вам надо куда-то забэкапить данные, чтобы пересобрать один RAID10 на два RAID1.
                  Если второе — мы оперируем любым количеством дисков для соблюдения одинаковых условий — и там, и там RAID10.
                    0

                    Это не я решаю задачу. :) Это автор предлагает решать задачу тормозов дисковой подсистемы конкретного сервера путем разнесения ОС и СУБД на разные физические диски. И мои комментарии следует воспринимать исключительно в этом контексте, а не в контексте некоего абстрактного тестирования в лабораторных условиях.

              0
              А я вот помню, как я (как раз айтишник, а не ДБА) пытался разобраться с резким падением прозводительности на SaaS интсансе при больших операциях. Ну просто одна большая операция, в которой копируются миллионы строчек вдруг после бодрого начала начинала безбожно тормозить. Даже до того, что начало было бодрое, нужно было ещё докопаться, я даже график строил, зависимости времени от количества операций, пока не познакомился с разными типами дисков на AWS:
              docs.aws.amazon.com/AWSEC2/latest/UserGuide/EBSVolumeTypes.html

              А так всё перепробовал. и таймауты, и настройки различные…
                0
                Не совсем согласен с началом решения проблем. Перестраивать индексы и статистику надо только на таблицах, где они реально портятся, а так можно мышкой накликать задач, которые будут только еще больше замедлять работу сервера. Можно конечно перестраивать только сильно попорченные индексы, статистики и фрагментированные таблицы, но серебряной пули тут не существует, если что-то тормозит, надо садиться и разбираться, какие запросы тормозят, смотреть их планы, в реальности кроме индексов и статистики может быть еще тысяча причин торможения
                  0
                  Очередь желательно чтобы не превышала 1. Допустимы кратковременные всплески, если они быстро спадают. Всплески могут быть разным


                  Откуда такая информация? Даже для старых дисков говорили про 2N, где N — количество шпинделей.

                  Если интересно ознакомиться с темой глубже — вот неплохая статья (правда, трёхлетней давности): Управление глубиной очереди дисков для достижения лучшей производительности
                    0
                    Если очередь выше 1, то значит в ней кто-то ждёт завершения предыдущей операции. Если эти операции записи в mdf, то пусть будут. Если они на чтение, то, как и написано в вашей статье, задержки будут расти линейно. От количества шпинделей это никак не зависит (это странная байка откуда-то из 90-х или раньше, сам я это году в 97-98 читал, применительно в NT4 встроенным RAID).
                    Но. Если это write-ahead запись в LDF, то очередь 1 — это значит, что вы полностью упёрлись в производительность диска. Потому что тут: SQL кинул запись в журнал транзакций (очередь стала 1), подождал, получил ответ от ОС «я кончила» (очередь стала 0) и тут же кинул новую запись в журнал.
                    Так что если очередь 1, то уже нужно убедиться, что это не связано с однопоточным синхронным сценарием IO.
                    +1
                    Почти десять лет ежедневно работаю с mssql, ни разу не перестраивал индексы. Статистика да, иногда в особо запущенных случаях временно приходилось. Но избавится от этого можно более простым кодом. Парсер/оптимизатор запросов у mssql, увы, не шибко умный. Почти всегда перефразированный, упрощенный и разбитый на более мелкие части запрос решает проблемы. Вместо вложенных запросов делайте выборки во временные таблицы, например. Но индексы ребилдить — это лишнее, проблема у вас где-то в другом. Имхо.
                      0
                      как верно заметили выше, нужно понимать что при этом происходит…
                      например, выборка во временные таблицы приведет к дополнительному вводу-выводу в tempdb…
                        0
                        Думаю мы говорим про разные ситуации. Я про настроенный специалистами сервер, вы — про песочницу для начинающих.
                        Когда сервер выполняет неоптимальные запросы, то смотреть на надрывающееся железо равносильно лечению не причины, а последствий. То есть совершенно бесмысленно. Если сервер тупит на простых запросах, это совсем другая история.
                        Если у вас достаточно памяти и использовать переменные табличного типа, то выборка никаких i/o не добавит.
                      0
                      в первую очередь, обновите статистику и перестройте индексы

                      Статистика по-умолчанию обновляется сама,
                      а индексы перестраивать надо только те, которые имеют определенное количество страниц и уровень фрагментации. Перестройка индекса очищает кеш планов, что в некоторых случаях воспринимается как «волшебная помощь» именно от перестройки индекса, хотя дело-то в кеше планов.
                      Кроме того, так как ни слов «кеш планов», ни IOPS, в вашей статье нет, то рекомендовать ее кому-либо, в том числе новичкам, смысла я не вижу.
                        0
                        Пользуясь случаем, хотел спросить немного не по теме. Какая сейчас линуксовая ФС наиболее подходящая для sql-ных и 1с-ных баз? Btrfs хороша, но кажется не доросла ещё до времени, когда её можно пускать в продакшн, XFS хороша дефрагментацией на лету, но боится сбоев по питанию, ZFS хороша, но тогда BSD надо ставить наверно… А что тогда? Как всегда старая добрая ext4?
                          +1
                          " В кратце:"-> " Вкратце:"
                          «скорость варьирует»->«скорость изменяется(варьируется)»
                            0
                            Описанные вами админы обычно используют MS SQL для 1С. Для них в интернете уже много есть рекомендаций, среди которых вы упустили достаточно (для новичков) полезные:
                            1. Использовать протокол Shared Memory
                            2. Изменить «шаг» роста для рабочих баз со стандартного 1 МБ на 100 МБ
                            3. Убедиться, что план электропитания стоит «Высокая производительность». В случае виртуализованного сервера — и на гипервизоре

                            Кто-то скажет, что эти пункты о быстродействии, а не об анализе, но у меня и от статьи такое ощущение.
                              0
                              Shared Memory — только для локальных клиентов
                                0
                                Там же сервер приложений, который скорее всего вместе с сервером SQL стоит.

                            Only users with full accounts can post comments. Log in, please.