7 вещей, которые разработчик должен знать о SQL Server

Original author: Brent Ozar
  • Translation
Привет. Я бывший разработчик, ставший администратором баз данных, и ниже написал о том, что, в своё время, хотел бы услышать сам.

7. Производительность скалярных UDF оставляет желать лучшего

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

Посмотрите этот пост о принудительном использовании параллелизма – в частности, список того, что приводит к генерации «однопоточного» плана выполнения запроса. Скорее всего, использование скалярных UDF (прим. переводчика: а для серверов младше 2008 R2 и не только скалярных) приведёт к тому, что ваш запрос будет выполняться в одном потоке (*грустно вздыхает*).

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

6. «WITH (NOLOCK)» не означает, что блокировок не будет вообще

На одном из этапов своей карьеры разработчика вы можете начать использовать хинт WITH (NOLOCK) повсеместно, поскольку с ним ваши запросы выполняются быстрее. Это не всегда плохо, но может сопровождаться неожиданными побочными эффектами, про которые Kendra Little рассказывала вот в этом видео. Я же сфокусируюсь только на одном из них.

Когда ваш запрос обращается к какой-либо таблице, даже с хинтом NOLOCK, вы накладываете блокировку стабилизации схемы (schema stability lock, Sch-S). Никто не сможет изменить эту таблицу или её индексы до тех пор, пока ваш запрос не завершится. Это не кажется серьёзной проблемой до тех пор, пока вам не понадобится удалить индекс, но вы не сможете этого сделать, поскольку люди постоянно работают с этой таблицей, находясь в полной уверенности, что не создают никаких проблем, поскольку они используют хинт WITH (NOLOCK).

Здесь нет «серебряной пули», но начните читать об уровнях изоляции SQL Server — я полагаю, что уровень изоляции READ COMMITTED SNAPSHOT будет наилучшим выбором для вашего приложения. Вы будете получать целостные данные с меньшим количеством проблем с блокировками.

5. Используйте три строки соединения в своём приложении

Я знаю, что сейчас у вас только один SQL Server, но поверьте мне, оно стоит того. Создайте три строки соединения, которые сейчас будут ссылаться только на один сервер, но потом, когда вы задумаетесь о масштабировании, у вас будет возможность использовать разные сервера «для обслуживания» каждой из этих строк.

  1. Строка соединения для записи и чтения «в реальном времени» — это та строка соединения, которую вы используете сейчас и думаете, что все данные должны приходить именно отсюда. Вы можете оставить весь свой код таким, какой он есть сейчас, но когда будете что-то дописывать, или изменять текущий, подумайте о том, чтобы изменить в запросах строку соединения на одну из представленных ниже.
  2. Строка соединения для получения «относительно свежих» данных, возрастом 5-15 минут – для данных которые могут быть слегка устаревшими, но всё равно сегодняшними.
  3. Строка соединения для «вчерашних» данных – для отчётов и построения трендов. Например, в онлайн-магазине, с этой строкой соединения вы можете вытягивать пользовательские обзоры к товарам, а самих пользователей предупреждать, что их обзоры будут опубликованы на следующий день.

Первую строку соединения «масштабировать» достаточно сложно, в SQL Server не очень-то много вариантов для «масштабирования операций записи» (такие варианты есть, но их очень тяжело применять и управлять ими). Вторую и третью строки соединения «масштабировать» значительно легче и дешевле. Чтобы получить больше информации об использовании разных строк соединения, вы можете прочитать вот этот мой пост.

4. Используйте промежуточную БД

Вероятно, вы используете БД для выполнения каких-то второстепенных задач – вычисления, сортировка, загрузка и т.д. Если вдруг эти данные пропадут, вы вряд ли сильно расстроитесь, но вот структура таблиц – это, конечно, другое дело. Сейчас вы делаете всё в «основной базе данных» вашего приложения.

Создайте отдельную базу данных, назовите её MyAppTemp, и делайте всё в ней! Поставьте ей простую модель восстановления и просто создавайте резервную копию раз в день. Не заморачивайтесь с высокой доступностью или аварийным восстановлением этой БД.

Использование такой техники имеет кучу плюсов. Она минимизирует количество изменений в основной БД, а значит резервные копии журнала транзакций и дифференциальные бэкапы будут делаться быстрее. Если вы используете log shipping, по-настоящему важные данные будут копироваться быстрее. Вы даже можете хранить эту БД отдельно от других баз, например на недорогом, но шустром SSD-диске, оставив основную систему хранения данных для критически важных в продакшене данных.

3. «Вчерашние» статьи и книги могут перестать быть актуальными сегодня.

SQL Server вышел уже больше десяти лет назад и за эти годы в нём произошло множество изменений. К сожалению, старые материалы не всегда обновляются, чтобы описать «сегодняшние» изменения. Даже свежие материалы из проверенных источников могут быть неправильными – вот, например, критика методики Microsoft по повышению производительности SQL Server. Microsoft Certified Master Jonathan Kehayias нашёл множество по-настоящему плохих советов в документе Microsoft.

Когда вы слышите что-то, что звучит как хороший совет, я предлагаю вам использовать стратегию, обратную стратегии доктора Фила. Доктор Фил говорит, что вы должны «проникнуться» любой идеей на протяжении 15 минут. Вместо этого, попробуйте возненавидеть её – постарайтесь опровергнуть то, что вы прочитали перед тем как применять это в продакшене. Даже если совет чертовски хорош, он может быть не очень-то и полезным на вашей системе. (Да, это относится и к моим советам).

2. Избегайте использования ORDER BY; сортируйте данные в приложении

На сортировку результатов вашего запроса, SQL Server тратит процессорное время. SQL Server Enterprise Edition стоит порядка 7000$ за одно ядро – не за процессор, а за само ядро. Двухсокетный, шестиядерный сервер обойдётся примерно в 84000$ — и это только цена лицензий, не считая железа. Вы можете купить чертовски много серверов приложений (даже с 256 ГБ оперативки на каждом) за $84k.

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

UPD. Я получил множество комментариев о том, что приложение нуждается, например, только в десяти строках, вместо десяти миллионов строк, возвращаемых запросом. Да, конечно, если вы пишете TOP 10, вам нужна сортировка, но как на счёт того, чтобы переписать запрос так, чтобы он не возвращал кучу ненужных данных? Если же данных так много, что серверу приложений приходится тратить слишком много ресурсов на сортировку – так ведь и SQL Server выполняет ту же самую работу. Мы поговорим о том как находить такие запросы на вебинаре, ссылка на который есть в конце поста. Кроме того, помните, что я сказал «Избегайте использования ORDER BY», а не «Никогда не используйте ORDER BY». Я точно так же использую эту инструкцию – но, если я могу избежать этого на очень дорогом уровне баз данных, я стараюсь это сделать. Вот что означает «избегать».

(А это часть, в которой фанаты MySQL и PostgreSQL рассказывают о том как снизить стоимость лицензий, используя СУБД с открытым исходным кодом). (А в этой части вы ждёте, что я им остроумно отвечу, но я не буду этого делать. Если вы разрабатываете новое приложение и задумались о выборе БД, прочтите мой ответ на StackOverflow о том какая БД выдержит наибольшую нагрузку.)

1. У SQL Server есть встроенные инструменты для поиска узких мест, не влияющие на производительность

Динамические административные представления SQL Server (DMV) могут показать вам все места, пагубно влияющие на производительность, т.е.:

  • какие запросы генерируют наибольшую нагрузку на вашем сервере
  • какие индексы просто занимают место и замедляют операции вставки/удаления/обновления
  • какие узкие места есть на вашем сервере (CPU, диск, сеть, блокировки и т.д.)?

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

Примечание переводчика: любые предложения и замечания по переводу и стилистике, как обычно, приветствуются.
Share post

Comments 11

    +9
    6. «WITH (NOLOCK)» не означает, что блокировок не будет вообще
    2. Избегайте использования ORDER BY; сортируйте данные в приложении


    Эти два пункта поразили до глубины души
      +6
      Расшифрую:

      по поводу WITH NOLOCK. Поразило не то что будут блокировки, а то, что кто-то этой анафемой не только всерьез пользуется, но еще и рассчитывает, что при этом не будет блокировок

      по поводу сортировок — следующий шаг известен: видимо джойнить данные также следует в приложении
        0
        А что вас удивляет в сортировках? Ведь если что-то можно выполнить на клиенте, то зачем выполнять это на сервере? Это как если бы в многопользовательской игре графика вычислялась бы на серверах, а клиентам посылались просто картинки.
          0
          >графика вычислялась бы на серверах, а клиентам посылались просто картинки.
          OnLive же :)
          0
          Я всерьез пользуюсь. Да и не только я. В некоторых случаях падение TTFB с десятков секунд до миллисекунд. Есть вполне конкретные блокировки и конкретные кейсы, в которых этот хинт замечательно помогает.
            0
            На одном из этапов своей карьеры разработчика вы можете начать использовать хинт WITH (NOLOCK) повсеместно, поскольку с ним ваши запросы выполняются быстрее.


            Я не считаю подобную практику разумной, только и всего (особенно «повсеместно»)
            В исключительных случаях использовать можно (предварительно тщательно продумав все последствия)
            0
            По поводу WITH (NOLOCK) — многие пользуются. На счёт того, что думают, что блокировок никаких не накладывает — тоже есть пример (и не один).

            По поводу ORDER BY — никто не говорит, что им нельзя пользоваться. ИМХО, Брент, в своём UPD, достаточно чётко высказал свою позицию — «Если без него можно обойтись, лучше обойтись». Я с ним согласен. Серверу баз данных и так есть чем себя занять, а порядок строк, в ряде случаев важен не с точки зрения логики приложения/запроса, а только для пользователя — вот пусть у этого конкретного пользователя, в этих случаях, и сортируется.
          0
          7. Производительность UDF оставляет желать лучшего

          Следует различать скалярные, табличные-встроенные и табличные-multi-statement пользовательские функции. Всё что вы описали относится к скалярным функциям, но не к встроенным.
            0
            Я при переводе ориентировался на ту статью, на которую ссылался автор поста, а тот, в свою очередь, вот на эту. А в этой последней статье, прилеплена pdf-ка, в которой есть вот такой лист:

            Ну и, соответственно, написал про UDF (сорри за длинное объяснение, хотел всю цепочку показать).
            Если вы располагаете другой информацией, помогите, пожалуйста, правильно переформулировать 7-й пункт :).
            0
            Следим за руками. По вашим ссылкам имеем:
            Статья 1: Parallel Query Execution Presentation за авторством Craig Freedman от 17 Apr 2007
            Статья 2: Forcing a Parallel Query Execution Plan за авторством Paul White от 23 Dec 2011

            Разница в датах наводит на мысль, что статья 1 устарела (6 лет всё-таки и 2005 SQL). Если немного вчитаться в статью 2, то в разделе «Parallelism-Inhibiting Components» находим строчку «The information presented above is based on the original list published by Craig Freedman, and updated for 2008 R2.» И набор блокирующих паралельный план запроса компонентов выглядит так:
            That list changes from version to version, but for example these things make the whole plan serial on SQL Server 2008 R2 SP1:
            — Modifying the contents of a table variable (reading is fine)
            — Any T-SQL scalar function (which are evil anyway)
            — CLR scalar functions marked as performing data access (normal ones are fine)
            — Random intrinsic functions including OBJECT_NAME, ENCYPTBYCERT, and IDENT_CURRENT
            — System table access (e.g. sys.tables)

            И там я не вижу всех UDF, а только scalar (sql и CLR) Т.е. за 1.5 версии список немного уменьшился. Возможно для 2012 SQL список ещё меньше, но это нужно проверять или дождаться окончания этого цикла статей.

            Это был ответ к комментарию, сорри.
              0
              Окей. Поправлю на «скалярные UDF». Относительно 2012-го сервера — нашёл вот такую штуку и там же в комментах перечислена часть (а может быть и все возможные) причин, из-за которых план может не быть параллельным.

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