7 вещей, которые нужно знать о SQL Server.

Вообще исходная статья называется "7 things Developers Should Know About SQL Server", но я считаю, что изложенные в ней вещи полезно знать и тестировщикам. Поэтому и предлагаю вам ее перевод :) А у кого хорошо с английским - добро пожаловать в исходник, там еще и комментарии интересные.

Итак...

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



7. SQL функции редко выполняются хорошо.

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

Посмотрите на прекрасный пост Пола Уайта "Forcing a Parallel Query Plan" - в частности, на примеры инструкций, по которым sql оптимизатор строит последовательный план, вместо параллельного. Большинство функций будут выполняться в однопоточном режиме. Грустное зрелище.

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

6. "WITH (NOLOCK)" на самом деле не означает, что блокировки нет.

На каком-то этапе своей карьеры вы начинаете использовать WITH (NOLOCK) где попало, потому что это позволяет получить результаты запроса быстрее. Это не обязательно плохая идея, но это может привести к неожиданным побочным эффектам, описанных Кендрой в ее видео "There's Something About Nolock". Я собираюсь разобрать тут один из них.

Когда вы запрашиваете таблицу - даже с WITH (NOLOCK) - вы стабильно блокируете схему. Больше никто не может изменить эту таблицу или индексы до тех пор, пока не закончится ваш запрос. Это не большая проблема до тех пор, пока вам не понадобится удалить индекс, а вы этого сделать не можете, т.к. люди постоянно запрашивают таблицу, но при этом думают, что все в порядке, ведь они используют WITH(NOLOCK).

Здесь нет серебряной пули, но я прочитал об уровнях изоляции SQL сервера и теперь, бьюсь об заклад, READ COMMITED SNAPSHOT ISOLATION - лучший выбор для вашего приложения. Он дает вам достаточное количество информации при минимальных конфликтах блокировки.

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

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

- соединение для записи и чтения в реальном времени - этот дескриптор вы используете сейчас и все "горячие" данные должны приходить отсюда. Вы можете держать весь свой код неизменным, но когда вы пишите новый код или рефакторите имеющийся, подумайте, как изменить запрос с учетом новых соединений.
- Соединение для данных за последние 5-15 минут - для данных, которые слегка неактуальны, но все равно должны быть свежими.
- Соединение для вчерашних данных - для отчетов менеджеров и отслеживания тенденций. Если вы запускаете онлайн магазин, вы можете получать отсюда информацию, например, и отвечать пользователям, что их данные будут обработаны в течение суток.

Первый дескриптор соединения самый непростой для масштабирования; в SQL Server не очень много методов для горизонтального масштабирования нескольких серверов, которые обрабатывают записи (и все они вызывают головную боль при установке и поддержке). Два следующих дескриптора соединения гораздо, гораздо более простые и легкие в масштабировании. Для того, чтобы узнать больше об этой технике, прочтите мои 3 Favorite Connection String Tips.

4. Используйте staging/apptempdb базу данных.

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

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

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

3. Вчерашние статьи и книги зачастую неверны сегодня.

 SQL Server существует уже больше 10 лет и за это время многое изменилось. Но старый материал не обновляется, и не стремится соответствовать тому, что происходит сегодня. Даже сегодняшние статьи из авторитетных источников часто неверны - взять хотя бы критику инструкции Microsoft Performance Tuning SQL Server. Дипломированный специалист Microsoft Джонатан Кухайа дал множество действительно плохих советов, которые были основаны на документации Microsoft.

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


2. Избегайте ORDER BY; вместо этого сортируйте в самом приложении.

Чтобы отсортировать результат вашего запроса, SQL Server нагружает CPU. Стоимость SQL Server Enterprise Edition приближается к $7000 за ядро; не весь процессор, а именно ядро. Двух процессорный (в каждом по 6 ядер) сервер уже доходит до примерно $84 K -  и это только стоимость лицензий, а не железа. Вы можете купить чертову кучу серверов приложений (даже с 256GB памяти или даже больше) по $84К.

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

(Тут пользователи MySQL и PostgreSQL начинают пищать о том, как можно тратить столько на лицензии, если есть open source базы данных.)

1. SQL Server имеет встроенный zero-impact инструмент.

Представления динамического управления (DMVs) SQL Server'а могут рассказать вам о таких киллер фичах, как:
- какое SQL выражение вызывает самые большие нагрузки на сервер;
- какие индексы занимают пространство и замедляют вставку/обновление/удаление;
- как быстро хранилище отвечает на запросы на уровне база данных - база данных (и даже более мелком, чем этот);
- где узкие места у вашего сервера (например, процессор, диск, сеть, блокировки и пр.)


Прим. переводчика: если Вы заметили в тексте какую-то ошибку или опечатку, пожалуйста, выделите ее и нажмите [Alt]+[F4].