Это тема о семи вещах, которые разработчик должен знать о SQL Server...
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, но поверьте мне, оно стоит того. Создайте три строки соединения, которые сейчас будут ссылаться только на один сервер, но потом, когда вы задумаетесь о масштабировании, у вас будет возможность использовать разные сервера "для обслуживания" каждой из этих строк.
- Строка соединения для записи и чтения "в реальном времени" - это та строка соединения, которую вы используете сейчас и думаете, что все данные должны приходить именно отсюда. Вы можете оставить весь свой код таким, какой он есть сейчас, но когда будете что-то дописывать, или изменять текущий, подумайте о том, чтобы изменить в запросах строку соединения на одну из представленных ниже.
- Строка соединения для получения "относительно свежих" данных, возрастом 5-15 минут - для данных которые могут быть слегка устаревшими, но всё равно сегодняшними.
- Строка соединения для "вчерашних" данных - для отчётов и построения трендов. Например, в онлайн-магазине, с этой строкой соединения вы можете вытягивать пользовательские обзоры к товарам, а самих пользователей предупреждать, что их обзоры будут опубликованы на следующий день.
Первую строку соединения "масштабировать" достаточно сложно, в 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, диск, сеть, блокировки и т.д.)?