Журнал транзакций SQL Server, Часть 1: Основы журналирования

Автор: Paul Randal, The SQL Server Transaction Log, Part 1: Logging Basics

(Эта статья впервые появилась на SQLperformance.com четыре года назад как часть серии публикаций, до того как этот сайт был законсервирован в конце 2022 года и серия была прервана. Переопубликована здесь с разрешения, с небольшими правками.)

За всю мою карьеру в сфере данных, как внутри Microsoft, так и в качестве консультанта, я обнаружил, что одна из самых непонятых частей SQL Server — это журнал транзакций. Недостаток знаний о том, как работает журнал транзакций и как им нужно управлять, или просто распространённые заблуждения, могут привести к различным проблемам в производственной среде, включая:

• Неуправляемый рост журнала транзакций и потенциальное исчерпание свободного места.
• Проблемы с производительностью из-за повторяющихся операций сжатия журнала транзакций.
• Проблемы с производительностью из-за явления, известного как фрагментация виртуальных файлов журнала (VLF), о котором я писал в этой статье.
• Невозможность восстановить базу данных до желаемой точки во времени с использованием резервных копий журнала транзакций.
• Невозможность выполнить резервное копирование заключительного фрагмента журнала (tail-log backup) во время аварийного восстановления (см. здесь объяснение таких копий).
• Различные проблемы, связанные с переключением при отказе (failover) и производительностью восстановления.

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

Структуры хранения #2 – Columnstore

Автор: Hugo Kornelis, Storage structures 2 – Columnstore;

В первой части этой серии я описал структуру хранения и шаблоны доступа для традиционной структуры хранения SQL Server: классических индексов хранения строк (on-disk rowstore) на диске (кучи и B-деревья).

Columnstore-индексы были представлены в SQL Server 2012. В той версии поддерживались только некластерные columnstore-индексы (то есть они хранили копию данных во включённых столбцах, в то время как фактические данные таблицы по-прежнему хранились в куче или кластерном индексе rowstore). И они делали таблицу доступной только для чтения! Это ограничение было снято в SQL Server 2014, когда также были добавлены кластерные columnstore-индексы. Затем в SQL Server 2016 добавили возможность создавать дополнительные некластерные индексы (rowstore) на кластерном columnstore-индексе. И, также начиная с SQL Server 2016, у нас теперь есть упорядоченные columnstore-индексы — на мой взгляд, несколько вводящее в заблуждение название.

Примечание: Columnstore-индексы, оптимизированные для памяти (memory-optimized), доступные с SQL Server 2016, будут описаны в отдельной записи.

Структуры хранения #1 – Классическое хранение строк на диске

Автор: Hugo Kornelis, Storage structures 1 – On-disk rowstore;

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

Итак, мы рассматриваем просмотры (scans), поиски (seeks) и обратные поиски (lookups). Мы знаем, что просмотры хороши, когда мы обращаемся к большей части данных. Или, в случае упорядоченного просмотра, чтобы избежать необходимости сортировки данных. Мы знаем, что поиск предпочтительнее, когда в запросе есть фильтр. И мы знаем, что обратный поиск представляет собой хороший компромисс между лучшей производительностью и слишком большим количеством индексов, но только если фильтр высокоизбирательный.

Всё вышесказанное верно. И всё это очень обобщённо. И, следовательно, часто недостаточно верно, чтобы быть действительно полезным.

SQL Server за годы своего развития научился поддерживать ошеломляющее количество различных структур хранения. Мы все знаем о кучах (heaps), а также о кластерных и некластерных B-деревьях (индексах). Но есть гораздо больше. Columnstore-индексы. Индексы, оптимизированные для памяти. Специализированные структуры индексов для поддержки определённых типов данных, таких как XML, JSON или векторы. И многое другое.

На высоком уровне общие описания просмотров, поисков и обратных поисков всегда применимы. Но вы по-настоящему поймёте влияние каждого из этих операторов на конкретный объект хранения, только если будете знать тип структуры хранения... и знать детали реализации этой структуры хранения.

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

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

Миф: Переход на зеркальный сервер при зеркалировании баз данных происходит мгновенно

Автор: Paul Randal, A SQL Server DBA myth a day: (11/30) database mirroring failover is instantaneous;

Переход на реплику при зеркальном отображении баз данных происходит мгновенно

ЛОЖЬ

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

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

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

Автоматическое обновление статистики не всегда аннулирует кешированные планы выполнения

Автор: Brent Ozar, Automatic Stats Updates Don’t Always Invalidate Cached Plans

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

Однако обновление статистики, созданной системой, не обязательно приводит к повторной компиляции планов.

Это действительно странный крайний случай, и вы, вероятно, никогда с ним не столкнётесь, но я сталкиваюсь с ним на каждом занятии, которое провожу. Я каждый раз мимоходом упоминаю об этом в классе и даже не обращаю на это особого внимания. Однако недавно студент спросил меня: «Это где-нибудь задокументировано?», и я подумал, э-э, может быть, но я не уверен, так что лучше задокументировать это здесь, в старом добром блоге.

Сериализация операций удаления из кластерных columnstore-индексов

Автор: Aaron Bertrand , Serializing Deletes From Clustered Columnstore Indexes

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

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

Автор: Brent Ozar, Maybe You Shouldn’t Even Be Using Clustering or AGs.

Сандра Делани написала хорошо продуманную запись в блоге под названием Должен ли SQL Server DBA разбираться в Windows Server Failover Clustering? У неё около 20 лет опыта работы администратором баз данных, и она работает консультантом в Straight Path (компании, которую я уважаю). Вы, вероятно, можете догадаться, исходя из её опыта, что да, она считает, что вы должны знать, как настроить, сконфигурировать и устранять неполадки в кластерах Windows. Это хорошая статья, и вам стоит её прочитать.

Но... я не согласен.

Вы задумывались о том, чтобы не использовать высокую доступность SQL Server?

Автор: Chrissy LeMaire, Have You Considered Not Using SQL Server High Availability?

Когда кто-то спрашивает об архитектуре SQL Server, рефлекторным ответом обычно становится «Высокая доступность» (High Availability, HA), будто это требование, а не выбор. Но после 20 лет управления средами SQL Server я обнаружила, что высокая доступность часто создаёт больше проблем, чем решает, особенно в организациях определённого типа.

Миф: Зеркалирование баз данных мгновенно обнаруживает сбои

Автор: Paul Randal, A SQL Server DBA myth a day: (10/30) database mirroring detects failures immediately

Database Mirroring мгновенно обнаруживает сбои

ЛОЖЬ

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

Это не так. Скорость обнаружения сбоя зависит, среди прочего, от того, что именно вышло из строя.

Миф: Сжатие файла данных не влияет на производительность

Автор: Paul Randal, A SQL Server DBA myth a day: (9/30) data file shrink does not affect performance

Сжатие файла данных не влияет на производительность.

Ха-ха-ха-ха-ха-ха-ха-ха-ха-ха-ха-ха-ха! <фыркает>

<вытирает слёзы с глаз, пытается сфокусироваться на экране ноутбука, убирает слюну с клавиатуры>

ЛОЖЬ

Единственный случай, когда сжатие файла данных не повлияет на производительность, — это если вы используете параметр WITH TRUNCATEONLY и в конце сжимаемого файла есть свободное пространство.

Единороги, радуги и операции с индексами в режиме ONLINE

Автор: Paul Randal, A SQL Server DBA myth a day: (8/30) unicorns, rainbows, and online index operations

Операции с индексами в режиме ONLINE не устанавливают блокировок

ЛОЖЬ

Операции с индексами в режиме ONLINE — это не сплошные единороги и радуги (об информации по единорогам и радугам см. http://whiteboardunicorns.com/ — безопасно для рабочего просмотра).

Миф: Несколько зеркал и задержка применения журналов при доставке журналов

Автор: Paul Randal, A SQL Server DBA myth a day: (7/30) multiple mirrors and log shipping load delays

У базы данных может быть несколько зеркал

ЛОЖЬ

Этот миф довольно однозначен — зеркальное отображение баз данных позволяет создавать только одно зеркало для каждой основной базы данных. Если вам нужно несколько копий основной базы данных, рассмотрите возможность использования доставки журналов. Вы можете иметь столько вторичных серверов доставки журналов, сколько вам нужно.

Ещё одна классная особенность доставки журналов заключается в том, что для одного из вторичных серверов можно установить задержку применения, скажем, в 8 часов. Это означает, что резервные копии журналов, сделанные на основном сервере (не правда ли забавно, что в разных технологиях используется разная терминология:

• зеркалирование баз данных: основной — зеркальный
• доставка журналов: основной — вторичный
• репликация: издатель — подписчик

Ладно… эта вводная часть как-то сама по себе развилась…) не будут восстановлены на вторичном сервере доставки журналов, пока не пройдёт 8 часов. Если кто-то удалит таблицу в рабочей среде, она почти сразу же исчезнет и в зеркале (с какой-то задержкой, зависящей от состояния очередей SEND и WAIT в тот момент — но вы не можете остановить этот процесс), а вот на вторичном сервере доставки журналов с задержкой применения таблица останется нетронутой.

Кстати, команда SQLCAT написала очень хорошую статью, разоблачающую миф (который происходит из документации Books Online) о том, что можно зеркалировать только 10 баз данных на один экземпляр — см. Mirroring a Large Number of Databases in a Single SQL Server Instance. Также взгляните на статью базы знаний, которую я написал для CSS, где обсуждается то же самое: KB 2001270 Предварительные условия, ограничения и рекомендации по зеркальному отображению базы данных.