Contents

1 Объяснение конструкций ANSI_NULLS / QUOTED_IDENTIFIER / NOCOUNT в Microsoft SQL Server
2 CROSS APPLY vs OUTER APPLY
- 2.1 Как работает OUTER APPLY в Microsoft SQL Server?
  - 2.1.1 Пример использования OUTER APPLY
  - 2.1.2 В чем отличие в работе между OUTER APPLY и LEFT OUTER JOIN?
- 2.2 Как работает CROSS APPLY в SQL Server?
  - 2.2.1 Пример использования CROSS APPLY
  - 2.2.2 CROSS APPLY vs INNER JOIN
3 Сравнение CAST, TRY_CAST, и COALESCE в SQL Server
4 OPTION CLAUSE
5 Индексы
6 Вопросы производительности запросов
- 6.1 Основные операции плана выполнения SQL Server
- 6.2 Несколько слов о производительности
7 Типы ожиданий MS SQL Server
8 Типы хранимых данных Microsoft SQL Server
9 Советы по T-SQL и Microsoft SQL Server (из книги Путь программиста T-SQL)
10 Литература, использованная при подготовки статьи

Объяснение конструкций ANSI_NULLS / QUOTED_IDENTIFIER / NOCOUNT в Microsoft SQL Server

Настройка `SET ANSI_NULLS ON;`

Включает поведение ANSI SQL стандартов для работы с NULL.

Как работает:

Если SET ANSI_NULLS ON, то сравнения с NULL (например, = NULL или <> NULL) всегда возвращают UNKNOWN, а не TRUE или FALSE. Для проверки на NULL необходимо использовать IS NULL или IS NOT NULL.
Если SET ANSI_NULLS OFF, то можно использовать = NULL или <> NULL, что нарушает стандарт SQL.

Это настройка обязательна для создания объектов (процедур, функций и т.д.), чтобы обеспечить стандартизированное поведение с NULL.
Всегда включается по умолчанию, начиная с определённых версий SQL Server.

Пример:

Подписывайся на мой канал "Data Engineering" в телеграме

SET ANSI_NULLS ON;
SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END; -- Результат: 0 (UNKNOWN)

1 2	SET ANSI_NULLS ON; SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END; -- Результат: 0 (UNKNOWN)

Настройка `SET QUOTED_IDENTIFIER ON;`

Определяет, как SQL Server интерпретирует двойные кавычки («).

Как работает:

Если SET QUOTED_IDENTIFIER ON, текст в двойных кавычках («) интерпретируется как имя объекта (например, название таблицы или столбца), а не как строковый литерал.
Если SET QUOTED_IDENTIFIER OFF, текст в двойных кавычках интерпретируется как строковый литерал, аналогично одинарным кавычкам (‘).

Применение:

Обычно включается для совместимости с ANSI SQL, где двойные кавычки используются только для идентификаторов.
Необходимо для создания объектов, таких как индексы, процедуры и функции.

Пример:

SET QUOTED_IDENTIFIER ON;

CREATE TABLE "MyTable" ("Column1" INT);
-- "MyTable" и "Column1" — имена объектов

SET QUOTED_IDENTIFIER ON;

CREATE TABLE "MyTable" ("Column1" INT);

-- "MyTable" и "Column1" — имена объектов

Настройка `SET NOCOUNT ON;`

Отключает возвращение информации о количестве строк, затронутых запросом.

Как работает:

Если SET NOCOUNT ON, SQL Server не отображает сообщение о количестве строк, обработанных INSERT, UPDATE, DELETE, SELECT.
Если SET NOCOUNT OFF, сервер возвращает такое сообщение (например, (3 rows affected)).

Применение:

Используется для уменьшения сетевого трафика, особенно в хранимых процедурах или скриптах, где возвращаемая информация о строках не нужна (для снижения объема ненужных сообщений).
Повышает производительность при массовых операциях.

Пример:

SET NOCOUNT ON;

INSERT INTO MyTable (Column1) VALUES (1), (2), (3);
-- Не отображается "3 rows affected"

SET NOCOUNT ON;

INSERT INTO MyTable (Column1) VALUES (1), (2), (3);

-- Не отображается "3 rows affected"

CROSS APPLY vs OUTER APPLY

Как работает OUTER APPLY в Microsoft SQL Server?

OUTER APPLY — это оператор, который позволяет выполнять строко-ориентированное соединение таблицы с табличной функцией или подзапросом, где каждая строка основной таблицы используется в качестве входного параметра. Это расширение стандартного SQL Server.

OUTER APPLY возвращает все строки из левой таблицы, включая те, для которых табличная функция или подзапрос возвращает пустой результат.
Если табличная функция или подзапрос для строки не возвращает данных, соответствующие столбцы результирующего набора будут заполнены NULL.

Это поведение аналогично LEFT OUTER JOIN, но используется для табличных функций или коррелированных подзапросов.

Синтаксис:

SELECT *
FROM LeftTable lt
OUTER APPLY (
    -- Табличная функция или подзапрос
    SELECT ...
    FROM ...
    WHERE ...
) AS alias;

SELECT *

FROM LeftTable lt

OUTER APPLY (

-- Табличная функция или подзапрос

SELECT ...

FROM ...

WHERE ...

) AS alias;

Пример использования OUTER APPLY

Предположим, есть две таблицы: Employees и Orders. Нужно для каждого сотрудника получить последние три заказа, которые он обработал, даже если у некоторых сотрудников заказов нет.

Employees:

Orders:

Запрос с OUTER APPLY:

SELECT e.EmployeeID, e.Name, o.OrderID, o.OrderDate
FROM Employees e
OUTER APPLY (
    SELECT TOP(3) OrderID, OrderDate
    FROM Orders o
    WHERE o.EmployeeID = e.EmployeeID
    ORDER BY o.OrderDate DESC
) AS o;

SELECT e.EmployeeID, e.Name, o.OrderID, o.OrderDate

FROM Employees e

OUTER APPLY (

SELECT TOP(3) OrderID, OrderDate

FROM Orders o

WHERE o.EmployeeID = e.EmployeeID

ORDER BY o.OrderDate DESC

) AS o;

Результат:

Запрос возвращает всех сотрудников и их последние три заказа (если есть), упорядоченные по дате, или NULL для сотрудников без заказов.

В чем отличие в работе между OUTER APPLY и LEFT OUTER JOIN?

Основное отличие между OUTER APPLY и LEFT OUTER JOIN заключается в том, как они работают с динамическими наборами данных и коррелированными подзапросами:

Особенность	OUTER APPLY	LEFT OUTER JOIN
Работа с табличными функциями	Может использовать табличные функции или коррелированные подзапросы, зависящие от текущей строки левой таблицы.	Работает только с фиксированными наборами данных, независимыми от текущей строки.
Гибкость условий	Позволяет динамически изменять результат подзапроса для каждой строки из левой таблицы.	Условия соединения определяются статично, без возможности строко-ориентированных вычислений.
Производительность	Может быть менее эффективным для больших таблиц из-за строки-за-строкой выполнения подзапроса.	Обычно быстрее на больших таблицах благодаря оптимизации индексов и использованию предсказуемых наборов.
Возврат данных, если правый источник пуст	Возвращает строки из левой таблицы с NULL для правых столбцов (как в LEFT OUTER JOIN).	Возвращает строки из левой таблицы с NULL для правых столбцов, если нет соответствий.

Подзапрос в OUTER APPLY в предыдущем примере динамически находит только 3 последних заказа для каждого сотрудника. Такое поведение невозможно реализовать с помощью LEFT OUTER JOIN!

Как работает CROSS APPLY в SQL Server?

CROSS APPLY в SQL Server выполняет строко-ориентированное соединение между таблицей и табличной функцией или коррелированным подзапросом, где каждая строка из левой таблицы используется как вход для правого источника. Возвращаются только те строки левой таблицы, для которых правый источник данных возвращает результат (аналогично INNER JOIN).

Другими словами, CROSS APPLY соединяет строки левой таблицы с результатами табличной функции или подзапроса, выполняемого для каждой строки левой таблицы.

Пример использования CROSS APPLY

Рассмотрим те же самые таблицы.

Запрос:

Для каждого сотрудника получить три последних заказа, но включить в результат только сотрудников, у которых есть заказы.

SELECT e.EmployeeID, e.Name, o.OrderID, o.OrderDate
FROM Employees e
CROSS APPLY (
    SELECT TOP 3 OrderID, OrderDate
    FROM Orders o
    WHERE o.EmployeeID = e.EmployeeID
    ORDER BY o.OrderDate DESC
) AS o;

SELECT e.EmployeeID, e.Name, o.OrderID, o.OrderDate

FROM Employees e

CROSS APPLY (

SELECT TOP 3 OrderID, OrderDate

FROM Orders o

WHERE o.EmployeeID = e.EmployeeID

ORDER BY o.OrderDate DESC

) AS o;

Результат:

Алгоритм работы следующий:

Для каждой строки из таблицы Employees выполняется подзапрос.
Подзапрос возвращает до трех последних заказов (TOP 3) сотрудника.
Если у сотрудника нет заказов, он не включается в результат (как при INNER JOIN).

CROSS APPLY vs INNER JOIN

Особенность	CROSS APPLY	INNER JOIN
Работа с табличными функциями	Может использовать табличные функции или коррелированные подзапросы, зависящие от текущей строки левой таблицы.	Работает только с фиксированными наборами данных, независимыми от текущей строки.
Гибкость условий	Позволяет динамически изменять результат подзапроса для каждой строки из левой таблицы.	Условия соединения определяются статично, без возможности строко-ориентированных вычислений.
Результаты соединения	Возвращает только строки из левой таблицы, для которых правый источник данных вернул результат (аналог INNER JOIN).	Возвращает строки, соответствующие условию соединения, из обеих таблиц.
Производительность	Может быть менее эффективным для больших таблиц из-за строки-за-строкой выполнения подзапроса.	Обычно быстрее на больших таблицах благодаря оптимизации индексов и фиксированным наборам данных.

Сравнение CAST, TRY_CAST, и COALESCE в SQL Server

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

Подписывайся на мой канал "Data Engineering" в телеграме

CAST

Функция CAST преобразует значение из одного типа данных в другой.

Особенности:

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

Пример:

SELECT CAST('123' AS INT) AS Result;   -- Результат: 123
SELECT CAST('abc' AS INT) AS Result;  -- Ошибка: Conversion failed

1 2	SELECT CAST('123' AS INT) AS Result; -- Результат: 123 SELECT CAST('abc' AS INT) AS Result; -- Ошибка: Conversion failed

TRY_CAST

Функция TRY_CAST — безопасная версия CAST, которая возвращает NULL вместо ошибки, если преобразование невозможно.

Особенности:

Используется для работы с потенциально некорректными данными.
Помогает избежать ошибок выполнения.

Пример:

SELECT TRY_CAST('123' AS INT) AS Result;   -- Результат: 123
SELECT TRY_CAST('abc' AS INT) AS Result;  -- Результат: NULL

1 2	SELECT TRY_CAST('123' AS INT) AS Result; -- Результат: 123 SELECT TRY_CAST('abc' AS INT) AS Result; -- Результат: NULL

COALESCE

Функция COALESCE возвращает первое ненулевое значение из списка аргументов.

Особенности:

Используется для обработки NULL значений.
Не выполняет преобразование типов, но может быть объединен с CAST или TRY_CAST для этого.

Пример:

SELECT COALESCE(NULL, 'Default') AS Result;   -- Результат: 'Default'
SELECT COALESCE(NULL, NULL, 123) AS Result;  -- Результат: 123

1 2	SELECT COALESCE(NULL, 'Default') AS Result; -- Результат: 'Default' SELECT COALESCE(NULL, NULL, 123) AS Result; -- Результат: 123

Комбинированный пример

Преобразовать строку в число и заменить NULL значением по умолчанию, если преобразование невозможно:

SELECT COALESCE(TRY_CAST('abc' AS INT), 0) AS Result; -- Результат: 0
SELECT COALESCE(TRY_CAST('123' AS INT), 0) AS Result; -- Результат: 123

1 2	SELECT COALESCE(TRY_CAST('abc' AS INT), 0) AS Result; -- Результат: 0 SELECT COALESCE(TRY_CAST('123' AS INT), 0) AS Result; -- Результат: 123

Вывод

Используйте CAST, когда данные гарантированно корректны.
Выбирайте TRY_CAST, чтобы безопасно обработать некорректные данные без ошибок.
Применяйте COALESCE для подстановки значений вместо NULL.

OPTION CLAUSE

OPTION (MAXDOP <integer_value>)

Ограничивает максимальное количество потоков (ядер процессора), которые SQL Server может использовать для выполнения запроса.

Используется для управления параллелизмом и минимизации влияния больших запросов на производительность других операций.
Например, OPTION (MAXDOP 1) заставляет запрос выполняться в одном потоке, что полезно для OLTP-систем, где параллелизм может вызывать блокировки.

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

OPTION (FORCE ORDER)

Заставляет SQL Server выполнять соединения таблиц в порядке, определённом в запросе.

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

Рекомендация: Применяйте только в случае, если стандартное поведение оптимизатора приводит к низкой производительности. Перед использованием рекомендуется тестирование.

OPTION (MAX_GRANT_PERCENT = <integer_value>)

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

Используется для предотвращения ситуаций, когда крупные запросы захватывают чрезмерное количество памяти, что может повлиять на другие запросы.
Например, OPTION (MAX_GRANT_PERCENT = 10) ограничивает использование памяти запросом до 10% от доступной для сервера.

Рекомендация: Установите это значение для крупных запросов в системах с интенсивным совместным использованием ресурсов.

OPTION (MAXRECURSION <integer_value>)

Устанавливает максимальное количество итераций для рекурсивных запросов (например, CTE).

Полезно для предотвращения бесконечной рекурсии и неконтролируемого роста потребления ресурсов.
Например, OPTION (MAXRECURSION 100) ограничит выполнение рекурсии до 100 итераций.

Рекомендация: Настройте значение на основе глубины рекурсии в ваших данных. Значение 0 снимает ограничение.

OPTION (RECOMPILE)

Заставляет SQL Server пересчитывать план выполнения каждый раз, когда запрос выполняется.

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

Рекомендация: Используйте для одноразовых запросов или тех, которые зависят от специфичных входных параметров.

OPTION (MERGE JOIN)

Принудительно использует алгоритм merge join для выполнения соединений.

Merge Join эффективен для соединений с отсортированными данными и минимальным числом операций по сравнению с Nested Loop или Hash Join.

Рекомендация: Используйте, если вы уверены, что данные предварительно отсортированы или соответствуют требованиям для Merge Join.

OPTION (MERGE UNION)

Заставляет SQL Server использовать алгоритм Merge для объединения данных из нескольких запросов.

Полезно для оптимизации операций UNION ALL, если входные данные упорядочены, что может снизить затраты на объединение.

Рекомендация: Редко используется, но может быть полезным при объединении больших объемов данных.

OPTION (HASH GROUP, FAST 10)

HASH GROUP: Принудительно использует алгоритм хэширования для группировки данных, что может быть эффективнее при обработке больших наборов данных.
FAST 10: Сообщает серверу, что нужно минимизировать задержку при возвращении первых 10 строк, даже если полный результат обрабатывается дольше.

HASH GROUP используется для оптимизации группировки, когда данные не отсортированы.
FAST <N> полезен в интерактивных системах, где важна скорость получения первых результатов.

Рекомендация: Используйте, когда это соответствует бизнес-требованиям: например, ускорьте показ первых строк в UI, сохраняя производительность.

Индексы

Что такое индексы в SQL Server

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

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

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

Индексы встроены в таблицы и являются важными инструментами SQL Server, которые оптимизируют работу с данными, делая их доступ более быстрым и эффективным.

Heap table (таблица-куча)

После создания таблицы, когда в ней еще отсутствуют индексы, она представляет собой неструктурированную совокупность данных, называемую «кучей» (Heap). Все записи в такой таблице хранятся в произвольном порядке, что и обусловило название.

Если требуется найти конкретные данные в такой таблице, SQL Server выполнит ее полное сканирование (Table scan). До тех пор, пока в таблице не определены индексы, обеспечивающие ограничения (например, UNIQUE CONSTRAINT, UNIQUE INDEX или PRIMARY KEY), сервер будет последовательно просматривать все записи — от первой до последней — и выбирать только те, которые соответствуют заданным условиям поиска.

Подписывайся на мой канал "Data Engineering" в телеграме

Это наглядно демонстрирует ключевые задачи индексов:

Ускорение поиска данных и улучшение производительности запросов.
Обеспечение целостности данных за счет гарантии уникальности строк в таблице.

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

У индексов есть и свои минусы:

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

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

Структура индекса

Индекс состоит из набора страниц (узлов индекса), организованных в структуру B-дерева. Эта структура имеет иерархическую природу, с корневым узлом наверху иерархии и конечными узлами внизу.

Структура B-дерева индекса SQL Server

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

Например, если вы ищете значение 123 в индексированном столбце, механизм запросов сначала будет искать на корневом уровне, чтобы определить, на какую страницу ссылаться на верхнем промежуточном уровне. В этом примере первая страница указывает на значения 1-100, а вторая страница — на значения 101-200, поэтому механизм запросов перейдет на вторую страницу на этом уровне. Затем механизм запросов определит, что он должен перейти на третью страницу на следующем промежуточном уровне. Оттуда механизм запросов перейдет к конечному узлу для значения 123.

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

Типы индексов в MSSQL

Характеристики индекса включают следующий список:

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

Ниже описаны все доступные типы индексов в MSSQL.

Hash Index (Хэш-индекс)

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

Memory-Optimized Nonclustered Index (Некластеризованный индекс, оптимизированный для памяти)

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

Clustered Index (Кластеризованный индекс)

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

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

Примеры создания:

--Create clustered index syntax
CREATE CLUSTERED INDEX &lt;index_name&gt;
ON [SCHEMA].[TABLE_NAME] (COLUMN_NAME)

--Create clustered index syntax

CREATE CLUSTERED INDEX <index_name>

ON [SCHEMA].[TABLE_NAME] (COLUMN_NAME)

на двух колонках

--Clustered index on 2 columns
CREATE CLUSTERED &lt;Index name&gt;
ON Humanresources.emp (Gender DESC, Salary ASC);

--Clustered index on 2 columns

CREATE CLUSTERED <Index name>

ON Humanresources.emp (Gender DESC, Salary ASC);

Краткий обзор кластеризованного индекса SQL Server:

Он физически сортирует данные в соответствии с ключом кластеризованного индекса.
У нас может быть только один кластеризованный индекс на таблицу.
Таблица без кластеризованного индекса представляет собой кучу, и это может привести к проблемам с производительностью.
SQL Server автоматически создает кластеризованный индекс для столбца первичного ключа.
Кластеризованный индекс хранится в формате b-дерева и содержит страницы данных в конечном узле.

Nonclustered Index (Некластеризованный индекс)

Некластеризованный индекс содержит отдельную структуру от данных таблицы. Он указывает на расположение строк в таблице с помощью указателя на кластеризованный индекс (или RID для таблиц без кластеризованного индекса).

Некластеризованные индексы не имеют индексных и информационных строк. Вместо этого есть ключевые значения и локаторы строк.

Один столбец или комбинация столбцов может иметь несколько некластеризованных индексов.

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

В некластеризованном индексе конечный узел не содержит фактических данных. Он состоит из указателя на фактические данные.

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

На изображении ниже мы можем увидеть конечный уровень некластеризованного индекса, указывающий на страницу данных в кластеризованном индексе:

Пример создания:

--nonclustered index syntax
CREATE NONCLUSTERED INDEX NC_EMP_NAME
ON HUMANRESOURCES.EMP(NAME);

--nonclustered index syntax

CREATE NONCLUSTERED INDEX NC_EMP_NAME

ON HUMANRESOURCES.EMP(NAME);

Unique Index (Уникальный индекс)

Уникальный индекс обеспечивает уникальность значений в одном или нескольких столбцах таблицы. Этот индекс автоматически создается для столбцов с ограничением уникальности (UNIQUE). Помогает предотвращать дублирование данных и повышает производительность запросов. Как кластеризованные, так и некластеризованные индексы могут быть уникальными.

Columnstore Index (Поколоночный индекс)

Поколоночный индекс хранит данные столбцами, а не строками. Это особенно эффективно для аналитических и агрегатных запросов, так как уменьшает объем операций ввода-вывода и оптимизирует использование памяти. Бывает кластеризованным и некластеризованным.

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

Index with Included Columns (Индекс с включенными столбцами)

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

Index on Computed Columns (Индекс на вычисляемых столбцах)

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

Filtered Index (Отфильтрованный индекс)

Отфильтрованный индекс создается для подмножества данных таблицы, что уменьшает объем хранимых данных и ускоряет выполнение запросов с конкретными фильтрами (WHERE). Этот тип индекса полезен для работы с данными с редкими значениями или часто используемыми подвыборками.

Spatial Index (Пространственный индекс)

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

XML Index (XML Индекс)

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

Подписывайся на мой канал "Data Engineering" в телеграме

Full-Text Index (Полнотекстовый индекс)

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

Индексы с колоночным хранением (Columnstore Indexes)

Что такое индексы Columnstore?

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

Такой подход имеет ряд преимуществ для аналитических запросов, таких как:

Более быстрая обработка запросов: Поскольку большинство аналитических запросов обращаются только к подмножеству столбцов таблицы, сканирование columnstore индекса может быть значительно быстрее, чем сканирование rowstore индекса или «кучи», где необходимо считывать все столбцы таблицы, даже если они не нужны.
Ускорение агрегаций: Так как значения из одного столбца хранятся вместе, операции вроде SUM, COUNT, AVG, MIN, MAX и т.д. выполняются проще. Индексы columnstore также используют пакетный режим обработки (batch mode), который может обрабатывать несколько строк одновременно, а также использовать SIMD-инструкции современных процессоров.
Ускорение соединений (joins): Columnstore индексы могут использовать пакетные методы соединений, такие как hash join или merge join, которые зачастую более эффективны, чем вложенные циклы (nested loop joins) или соединения с сортировкой-слиянием (sort-merge joins) на rowstore данных. Они также могут использовать исключение сегментов и групп строк, что позволяет пропускать большие блоки данных, не подходящие под условия соединения.
Высокая степень сжатия: Columnstore индексы применяют различные методы сжатия данных, такие как кодирование длины серии (run-length encoding), словарное кодирование (dictionary encoding) и дельта-кодирование (delta encoding), что позволяет уменьшить размер данных. Это может обеспечить до 10-кратного сжатия по сравнению с несжатыми rowstore данными. Сжатие не только экономит место на диске, но и улучшает производительность запросов за счет сокращения операций ввода-вывода и использования памяти.

Типы индексов Columnstore в SQL Server

В SQL Server существует два типа индексов columnstore: кластеризованные и некластеризованные.

Кластеризованный индекс columnstore (CCI)

Является основным хранилищем данных таблицы.
Заменяет rowstore кучу или кластеризованный индекс, которые обычно хранят данные таблицы.
Таблица может иметь только один CCI, и он должен включать все столбцы таблицы.
Подходит для больших фактологических (основных) таблиц, которые в основном только добавляются (append-only) и используются для аналитических нагрузок.

Некластеризованный индекс columnstore (NCCI)

Это дополнительный индекс, построенный поверх rowstore кучи или кластеризованного индекса, хранящего данные таблицы.
Может включать подмножество столбцов таблицы и иметь фильтрующее условие для ограничения индексируемых строк.
Таблица может иметь несколько NCCIs, но их столбцы не должны пересекаться.
Подходит для больших таблиц измерений (вторичных), где часто выполняются обновления и удаления, или для смешанных нагрузок, требующих как оперативных, так и аналитических запросов к одной таблице.

Когда следует использовать индексы Columnstore?

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

Примеры таких сценариев:

Традиционная рабочая нагрузка хранилища данных со схемой «звезда» или «снежинка»:

В этом сценарии имеются одна или несколько крупных фактологических таблиц, хранящих метрики или показатели, и несколько меньших таблиц измерений, содержащих атрибуты или категории, связанные с фактологическими таблицами.
Таблицы фактов обычно загружаются пакетами с использованием ETL-процессов, а таблицы измерений обновляются методами обработки медленно меняющихся измерений (SCD).
Запросы, как правило, являются OLAP-запросами, выполняющими агрегации и соединения между несколькими таблицами.
Для таких сценариев следует использовать CCI для каждой таблицы фактов, чтобы повысить производительность запросов и сжатие, а также NCCI для каждой таблицы измерений для включения пакетной обработки и исключения сегментов при соединениях.

Рабочие нагрузки Интернета вещей (IoT):

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

Когда не следует использовать индексы Columnstore?

Индексы columnstore подходят не для всех сценариев. Вот случаи, когда их лучше избегать:

OLTP-нагрузки с частыми обновлениями и удалениями небольших наборов строк:

В таких сценариях одна или несколько таблиц хранят транзакционные данные, которые постоянно изменяются конкурентными пользователями или приложениями.
Запросы в основном OLTP-типа, выполняющие точечные выборки или диапазонные сканирования отдельных строк или небольших групп строк.
Для таких сценариев использование CCI не рекомендуется, так как это значительно ухудшит производительность операций обновления и удаления из-за затрат на поддержание delta store и карты удаленных строк.
Можно использовать NCCI для некоторых таблиц для реализации оперативной аналитики (HTAP), но нужно тщательно протестировать влияние на рабочую нагрузку.

Таблицы с низкой кардинальностью столбцов или сильно коррелированными столбцами:

В таких таблицах есть столбцы с небольшим числом уникальных значений (например, пол или статус) или столбцы, сильно зависящие друг от друга (например, почтовый индекс и штат).
Эти столбцы не являются хорошими кандидатами для сжатия columnstore, так как это приведет к низким коэффициентам сжатия и сильной неоднородности сегментов.
В таких случаях не включайте эти столбцы в CCI или NCCI, если это возможно, или используйте другие методы сжатия, такие как сжатие на уровне страницы (page compression) или строки (row compression).

Заключение по Columnstore Index

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

Архитектура и преимущества

Колоннохранилищные индексы хранят данные по столбцам, что делает их идеальными для аналитических запросов. Они обеспечивают:

Быстрый доступ к данным за счет обработки только нужных столбцов.
Высокую степень сжатия данных с использованием методов, таких как RLE, dictionary encoding.
Эффективные агрегаты и аналитические операции благодаря пакетной обработке и оптимизации CPU (например, SIMD).

Два типа индексов:

Clustered Columnstore Index (CCI): используется для фактического хранения больших объемов данных и идеально подходит для исторической аналитики.
Nonclustered Columnstore Index (NCCI): подходит для частичного индексирования, часто на измерительных таблицах или смешанных нагрузках.

Лучшие практики

Рекомендуется использовать CCI для таблиц-фактов с редко изменяемыми данными и NCCI для измерительных таблиц.
Планируйте индексацию с учетом схемы данных и частоты обновлений. Например, при высокой активности обновлений нужно ограничить использование CCI.
Периодическое сжатие и реорганизация индексов помогают поддерживать их производительность и эффективность.

Обслуживание и реорганизация

Реорганизация индекса с параметром COMPRESS_ALL_ROW_GROUPS = ON может принудительно сжимать дельта-строки, повышая плотность данных.
В SQL Server 2019 появилась возможность онлайн-перестройки индексов, что сокращает простои.
Регулярно оценивайте состояние индексов, особенно долю удаленных строк. Высокий процент удаленных строк — сигнал к перестройке индекса.

Шаблоны запросов и оптимизация

Использование сегментных метаданных значительно ускоряет агрегаты (SUM, MIN, MAX) и выборки по диапазонам.
Сложные аналитические запросы, такие как временная группировка и соединение с измерительными таблицами, эффективны при условии минимального декомпрессирования сегментов.
Оптимизируйте фильтры и соединения, чтобы избежать чтения ненужных данных.

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

Дополнительные статьи и видео по Columnstore Index в MSSQL

Индексы с колоночным хранением (Columnstore Indexes) в SQL Server 2016

Clustered Columnstore Index — теория и реальность | Антон Белокрылов

Дмитрий Короткевич. Колоночные индексы (30.05.2018)

И три статьи от Microsoft

Оптимизация индексов

Гайд Optimize index maintenance to improve query performance and reduce resource consumption

Основы хранения данных в SQL Server

Подробнее можно почитать Pages and extents architecture guide

Страницы и экстенты:

Страница – это минимальная единица хранения данных в SQL Server. Она имеет фиксированный размер 8 КБ.
Группы из 8 страниц образуют экстент, который составляет 64 КБ. Экстенты используются для организации и выделения памяти для таблиц и индексов.

Еще немного про кучи и сбалансированные деревья

Страницы — это физические структуры. SQL Server организует данные на страницах в логические структуры.

SQL Server организует таблицы в кучи или сбалансированные деревья. Таблица, организованная в виде сбалансированного дерева, также известна как кластеризованная таблица или кластеризованный индекс (эти два термина являются взаимозаменяемыми).
Индексы всегда организованы как сбалансированные деревья. Другие индексы, которые не содержат всех данных и служат в качестве указателей на строки в таблицах для быстрого поиска, называются некластеризованными индексами.

Куча — это довольно простая структура. Данные в куче не имеют никакой логической организации. Куча — это просто набор страниц и экстентов.

SQL Server отслеживает, какие страницы и экстенты принадлежат объекту, с помощью специальных системных страниц, называемых страницами карты распределения индекса (Index Allocation Map, IAM). Каждая таблица или индекс имеет по крайней мере одну страницу IAM, называемую первой страницей IAM. Одна страница IAM может указывать примерно на 4 Гбайт пространства. Большие объекты могут иметь более одной страницы IAM. Страницы IAM объекта организованы в двунаправленный список; у каждой страницы есть указатель на ее потомка и предшественника. SQL Server хранит указатели на первые страницы.

Как заполняются страницы

При создании нового индекса или таблицы все данные записываются упорядоченно, а страницы заполняются плотно (то есть почти нет пустого пространства).

Фрагментация: Со временем данные изменяются: добавляются новые строки, удаляются старые, обновляются записи.

Это приводит к фрагментации:

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

Как возникает фрагментация?

Внешняя фрагментация:

Когда вы вставляете новые данные в таблицу, SQL Server может вставить новую страницу не рядом с предыдущими. Это нарушает порядок страниц в индексе.

Внутренняя фрагментация:

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

Мифы о фрагментации

Разделение страниц (page split) часто неправильно интерпретируется как главная причина низкой производительности базы данных.
Page split происходит, когда данные добавляются в середину упорядоченного индекса. Если текущая страница заполнена, SQL Server разделяет её на две, чтобы освободить место для новых записей.

Однако это не всегда плохо и не всегда существенно замедляет базу.

Почему внешняя фрагментация не так критична?

Есть мнение, что внешняя фрагментация не имеет большого влияния на производительность, потому что:

Современные диски и оперативная память:
SSD-диски имеют низкое время поиска данных, поэтому порядок страниц на диске почти не влияет на скорость чтения.
Если данные помещаются в оперативную память, порядок страниц вообще не имеет значения.

Задачи SQL Server:

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

Перестройка индексов может навредить:

Частая реорганизация или перестройка индексов увеличивает нагрузку на сервер и время резервного копирования. Это может привести к снижению общей производительности.

Выводы и рекомендации

Не зацикливайтесь на внешней фрагментации: Не всегда нужно исправлять её, особенно если вы работаете с современными системами хранения данных.
Фокусируйтесь на внутренних метриках: Например, плотности страниц (fill factor) и времени выполнения запросов.
Перестройка индексов: Используйте эту процедуру только тогда, когда это действительно необходимо, например, если запросы стали медленнее из-за низкой плотности данных или значительного роста размера индекса.

Вопросы производительности запросов

Настройка производительности СУБД — многоуровневый процесс, и оптимизация запросов является лишь одним из его аспектов.

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

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

По поводу плана запросов хорошая статья Причины скачков производительности запроса в SQL Server

Основные операции плана выполнения SQL Server

Index Seek

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

Однако это вовсе не означает, что он всегда работает быстро, например, если он возвращает большое число строк, то по производительности он будет практически равен Index Scan.

Index Scan

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

Но бывают исключения, например, применение директивы TOP, ограничивающей число возвращаемых записей; если возвращать всего несколько строк, то операция сканирования будет выполняться достаточно быстро, и вы не сможете получить лучшую производительность, чем ту, которую уже имеете, даже если вы попытаетесь перестроить запрос/индексы, чтобы добиться операции Index Seek.

RID Lookup

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

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

Key Lookup

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

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

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

Sort

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

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

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

Spool

Спулы бывают разных типов, но большинство из них можно сформулировать как операторы, которые сохраняют промежуточную таблицу результатов в tempdb.

SQL Server часто использует спул для обработки сложных запросов, преобразуя данные во временную таблицу в базе tempdb для использования её данных в последующих операциях. Побочным эффектом здесь является необходимость записи данных на диск.
Для ускорения выполнения запроса можно попытаться найти способ его перезаписи таким образом, чтобы избежать спула. Если это не получается, использую метод «разделяй и властвуй» для временных таблиц, который может также заменить спул, обеспечивая больший контроль по сравнению с тем, как SQL Server записывает и индексирует данные в tempdb.

Merge Join

Соединение слиянием. Редко встречаются в реальных запросах, как правило, являются наиболее эффективными из операторов логического соединения.

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

Операция неприменима, если входные данные не отсортированы.

Nested Loops Join

Соединение вложенными циклами. Встречаются очень часто. Выполняют довольно эффективное соединение относительно небольших наборов данных.
Соединение вложенными циклами не требует сортировки входных данных. Однако производительность можно улучшить при помощи сортировки источника входных данных; SQL Server сможет выбрать более эффективный оператор, если оба входа отсортированы.

Операция неприменима, если данные слишком велики для хранения в памяти.

Hash Match Join

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

Она выбираются оптимизатором запросов по одной из двух причин:

Соединяемые наборы данных настолько велики, что они могут быть обработаны только с помощью Hash Match Join.
Наборы данных не упорядочены по столбцам соединения, и SQL Server думает, что вычисление хэшей и цикл по ним будет быстрей, чем сортировка данных.

При первом сценарии трудно оптимизировать выполнение запроса, если только не найти способа соединять меньшие объемы данных.

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

Операторы Hash Match Join достаточно эффективны тогда, когда не сбрасывают данные в tempdb.

Parallelism

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

Stream Aggregate

Статистическое выражение потока. Группирует строки в один или несколько столбцов и вычисляет одно или несколько агрегатных выражений (пример: COUNT, MIN, MAX, SUM и AVG), возвращенных запросом.
Выход этого оператора может быть использован последующими операторами запроса, возвращен клиенту или то и другое. Оператору Stream Aggregate необходимы входные данные, упорядоченные по группируемым столбцам. Оптимизатор использует перед этим оператором оператор Sort, если данные не были ранее отсортированы оператором Sort или используется упорядоченный поиск или просмотр в индексе.

Compute Scalar

Оператор Compute Scalar вычисляет выражение и выдает вычисляемую скалярную величину. Затем эту величину можно вернуть пользователю или сослаться на нее в каком-либо запросе, а также выполнить эти действия одновременно.

Примерами одновременного использования этих возможностей являются предикаты фильтра или соединения. Всегда возвращает одну строку. Часто применяется для того, чтобы конвертировать результат Stream Aggregate в ожидаемый на выходе тип int (когда Stream Aggregate возвращает bigint в случае с COUNT, AVG при типах столбцов int).

Concatenation

Оператор просматривает несколько входов, возвращая каждую просмотренную строку. Используется в запросах с UNION ALL. Копирует строки из первого входного потока в выходной поток и повторяет эту операцию для каждого дополнительного входного потока.

Filter

Оператор просматривает входные данные и возвращает только те строки, которые удовлетворяют критерию фильтрации (предикату).

Несколько слов о производительности

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

Типы ожиданий MS SQL Server

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

Рекомендую статью для понимания Анализ ожиданий на СУБД.

Когда в MS SQL поступает запрос от пользователя, ему назначается один или несколько потоков для исполнения ядром процессора. Несколько потоков может быть в том случае, если запрос «тяжелый» и можно ускорить его выполнение параллельным выполнением на нескольких ядрах.

Поток может находиться в одном из трех состояний:

RUNNING – выполняется на процессоре. Каждому потоку дается квант времени в 4 мс. для выполнения.
SUSPENDED – ожидает какой-то ресурс. Если потоку понадобился какой-то ресурс, то его выполнение прекращается. При этом фиксируется, чего именно ожидал поток (WaitType), и включается счетчик ресурса (ResourceS), увеличивающего время ожидания. Например, поток может ждать, пока будет прочитана страница данных из памяти, или ждать освобождения заблокированных данных и т.д.
RUNNABLE – ожидает выполнения на процессоре. Как только ресурс получен и поток снова готов к исполнению на процессоре, он помещается в очередь, переходит в статус RUNNABLE и ждет от процессора сигнала, что можно начать выполнение. Время, которое готовый к выполнению поток проводит в ожидании процессора, и есть время сигнала (SignalS). Даже если поток не ожидал ресурс, а просто не успел выполниться за 4 мс, он все равно переходит в статус RUNNABLE и ждет своей очереди с типом ожидания SOS_SCHEDULER_YIELD.

Схематично состояния потока можно представить следующим образом:

Дополнительная терминология раздела

Флаги трассировки в SQL Server

Флаги трассировки в SQL Server позволяют активировать некоторые функции или изменить их поведение. В новых версиях SQL Server появляется все больше параметров конфигурации базы данных и сервера, но флаги трассировки по-прежнему широко используются. Вам нужно будет изучить, какие флаги есть в системе, и, возможно, включить некоторые из них.
Чтобы получить список включенных флагов трассировки, выполните команду DBCC TRACESTATUS. Флаги можно включить в диспетчере конфигурации SQL Server и/или с помощью параметра -T при запуске SQL Server.

Типы блокировок и их поведение

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

В области СУБД есть важное понятие — транзакция.

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

У транзакции есть четыре определяющих свойства: атомарность, согласованность, изоляция и устойчивость (вместе их называют ACID — atomicity, consistency, isolation и durability). Рассмотрим каждое из этих свойств.

Атомарность

Атомарность гарантирует, что каждая транзакция выполняется как отдельная единица работы по принципу «все или ничего»: изменения, сделанные в рамках транзакции, либо фиксируются все целиком, либо полностью откатываются.

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

Согласованность

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

Изоляция

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

Устойчивость

Устойчивость гарантирует, что после того, как транзакция зафиксирована, все внесенные ею изменения сохранятся и не пропадут в случае сбоя системы. SQL Server обеспечивает устойчивость путем ведения журнала транзакций с опережающей записью (WAL, write-ahead logging). Транзакция не считается зафиксированной до тех пор, пока все созданные ею записи журнала не закреплены в файле журнала.

Чтобы изолировать транзакции, в SQL Server используются блокировки (locks).

Блокировки устанавливаются и удерживаются на ресурсах, таких как строки данных, страницы, разделы, таблицы (объекты) и базы данных. На внутреннем уровне блокировки представляют собой структуры в памяти, которыми управляет соответствующий компонент SQL Server — диспетчер блокировок (Lock Manager).

Основные типы блокировок

Монопольные блокировки (X) используются операциями записи: инструкциями INSERT, UPDATE, DELETE и MERGE, которые изменяют данные.

Совмещаемые блокировки (S) используются операциями чтения, то есть запросами SELECT. Опять же, как понятно из названия, несколько сеансов могут параллельно устанавливать и удерживать блокировку (S) на одном и том же ресурсе.

Блокировки с намерением (Intent locks):
Их назначение — информировать транзакцию о намерении запросить блокировку. Это имеет место, когда транзакция требует разделяемую или эксклюзивную блокировку на ресурсы ниже в иерархии.
Транзакция не позволяет другим транзакциям получить эксклюзивную блокировку на таблицу, использующую блокировку с намерением.

Разделяемая блокировка с намерением (IS): указывает, что SQL Server намерен прочитать ресурсы ниже в иерархии, запрашивая разделяемую блокировку индивидуально на эти ниже находящиеся в иерархии ресурсы.
Эксклюзивная блокировка с намерением (IX): указывает, что SQL Server намерен модифицировать ресурсы ниже в иерархии, получая эксклюзивную блокировку на эти ниже находящиеся в иерархии ресурсы.
Блокировка обновления с намерением (IU): может запрашиваться только на уровне страницы для ресурсов ниже в иерархии, и по завершению обновления преобразуется в IX-блокировку.

При модификации данных SQL Server использует блокировки обновления (U). Он устанавливает их во время просмотра обновлений на строки, которые нужно обновить или удалить.

Или кратко:

Shared (S) — для чтения данных.
Exclusive (X) — для записи данных.
Update (U) — для предотвращения появления условий гонки при обновлении.
Intent locks (IX, IS) — для обозначения намерений на блокировку ресурса на разных уровнях (например, на уровне таблицы).
Schema locks (Sch-S, Sch-M) — для защиты схемы объекта.

Типы изоляции транзакций в MSSQL

READ UNCOMMITTED

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

READ COMMITTED

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

REPEATABLE READ

Ядро СУБД сохраняет блокировки чтения и записи, полученные для выбранных данных до конца транзакции. Однако из-за того, что блокировки диапазона не являются управляемыми, может возникнуть фантомное чтение.

SERIALIZABLE

Самый высокий уровень, при котором транзакции полностью изолированы друг от друга. Ядро СУБД сохраняет блокировки чтения и записи, приобретенные для выбранных данных до конца транзакции. Блокировки диапазона получаются, когда операция SELECT использует предложение RANGE WHERE, чтобы избежать фантомных операций чтения.

Примечание. Операции И транзакции DDL в реплицированных таблицах могут завершиться ошибкой при запросе SERIALIZABLE уровня изоляции. Это связано с тем, что запросы репликации используют указания, которые могут быть несовместимы с SERIALIZABLE уровнем изоляции.

Уровень изоляции управления версиями строк

Read Committed Snapshot (RCSI)

READ_COMMITTED_SNAPSHOT Если установлен ON параметр базы данных , который является параметром по умолчанию в База данных SQL Azure, READ COMMITTED уровень изоляции использует управление версиями строк для обеспечения согласованности чтения на уровне инструкций. Для операций чтения требуются только блокировки уровня таблицы (Sch-S стабильности схемы) и нет блокировок страниц или строк. То есть компонент Database Engine использует управление версиями строк для представления каждой инструкции согласованного на уровне транзакций моментального снимка данных в том виде, который они имели на момент начала выполнения инструкции. Для защиты данных от обновления другими транзакциями блокировки не используются. Определяемая пользователем функция может вернуть данные, зафиксированные после начала выполнения инструкции, содержащей эту функцию.

READ_COMMITTED_SNAPSHOT Если установлен OFF параметр базы данных , который является параметром по умолчанию в SQL Server и Управляемый экземпляр SQL Azure, изоляция использует общие блокировки, READ COMMITTED чтобы предотвратить изменение других транзакций, пока текущая транзакция выполняет операцию чтения. Совмещаемые блокировки также блокируют инструкции от считывания строк, измененных другими транзакциями, пока не завершится другая транзакция. Обе реализации соответствуют определению изоляции READ COMMITTED ISO.

SNAPSHOT

Уровень изоляции моментальных снимков использует управление версиями строк для обеспечения согласованности чтения на уровне транзакций. Операции чтения не получают блокировки страниц или строк; получаются только блокировки стабильности схемы (Sch-S) таблицы. При чтении строк, измененных другой транзакцией, операции чтения извлекают версию строки, которая существовала при запуске транзакции. Изоляцию можно использовать SNAPSHOT только в том случае, ALLOW_SNAPSHOT_ISOLATION если для параметра базы данных задано ONзначение . По умолчанию этот параметр установлен OFF для пользовательских баз данных в SQL Server и Управляемый экземпляр SQL Azure и имеет значение ON для баз данных в База данных SQL Azure.

Примечание. Ядро СУБД не поддерживает управление версиями метаданных. Поэтому, не все операции DDL могут выполняться в явной транзакции, работающей с уровнем изоляции моментального снимка. Следующие инструкции DDL не допускаются при изоляции моментальных снимков после BEGIN TRANSACTION инструкции: ALTER TABLE, CREATE INDEX, ALTER INDEXALTER PARTITION SCHEMECREATE XML INDEXDROP INDEXDBCC REINDEXALTER PARTITION FUNCTION или любой инструкции clR (CLR) DDL. Эти инструкции разрешены при использовании изоляции моментального снимка в неявных транзакциях. Неявная транзакция, по определению, это единственная инструкция, для которой возможно выполнение семантики изоляции моментального снимка, даже для инструкций DDL. Нарушение этого принципа может вызвать сообщение об ошибке 3961: Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.

Уровни изоляции транзакций и поведение совмещаемых блокировок (S)

ASYNC_IO_COMPLETION

Ожидание ASYNC_IO_COMPLETION происходит во время асинхронных операций ввода/вывода, не связанных с буферным пулом.

Вот его распространенные причины:

Обычные контрольные точки.
Внутренние контрольные точки, которые возникают, когда вы запускаете резервное копирование базы данных или DBCC CHECKDB.
Чтение страниц глобальной карты распределения (GAM) из файлов данных.
Чтение страниц данных из базы данных во время ее резервного копирования (к сожалению, информация о среднем времени ожидания при этом искажается, что затрудняет анализ).

Заметный процент ожиданий ASYNC_IO_COMPLETION может быть признаком перегруженной подсистемы ввода/вывода, особенно если он проявляется вместе с другими ожиданиями, связанными с вводом/выводом. Устраните неисправности системы ввода/вывода (см. ожидания PAGEIOLATCH далее в этом приложении).

ASYNC_NETWORK_IO

Ожидание ASYNC_NETWORK_IO возникает, когда SQL Server ждет, пока клиент примет данные.

Три самые распространенные причины этого ожидания таковы:

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

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

Этапы устранения неполадок:

Проверьте производительность сети на сервере, просматривая счетчики производительности сети и другие доступные ключевые индикаторы производительности (KPI).
Найдите клиентские приложения, которые генерируют ожидания, с помощью представлений:
- sys.dm_os_waiting_tasks,
- sys.dm_exec_requests,
- sys.dm_exec_sessions и
- sys.dm_exec_connections.
Для краткосрочного профилирования используйте расширенное событие wait_completed с захватом действий sqlserver.client_host_name и sqlserver.client_app_name.
Проанализируйте код приложения и производительность серверов приложений.

BACKUPIO и BACKUPBUFFER

Ожидания BACKUPIO и BACKUPBUFFER указывают на недостаточную пропускную способность резервного копирования и/или восстановления. Они могут возникать из-за медленной или перегруженной сетевой и/или дисковой подсистем.

В последнем случае они часто появляются вместе с другими ожиданиями, связанными с вводом/выводом.

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

BTREE_INSERT_FLOW_CONTROL

Ожидание BTREE_INSERT_FLOW_CONTROL указывает на существование индексов с постоянно увеличивающимися ключами, которые создают «горячие точки».

Это ожидание заменяет некоторые ожидания PAGELATCH, когда вы включаете параметр индекса OPTIMIZE_FOR_SEQUENTIAL_KEY.

CXPACKET, CXCONSUMER и EXCHANGE

Ожидания CXPACKET, CXCONSUMER и EXCHANGE возникают во время выполнения запросов с параллельными планами выполнения. Эти ожидания совершенно нормальны и должны присутствовать, особенно в системах с рабочими нагрузками хранения данных и/или отчетов. Однако в системах OLTP чрезмерное количество ожиданий параллелизма может создавать неприятности.

Ожидания параллелизма сами по себе не являются проблемой: они всего лишь признак ресурсоемких запросов, которые используют планы параллельного выполнения. В системах OLTP такие запросы нужно обнаружить и оптимизировать. Настройте параметры параллелизма.
Не устанавливайте параметр MAXDOP=1, потому что это просто замаскирует проблему.

DIRTY_PAGE_TABLE_LOCK

Ожидания DIRTY_PAGE_TABLE_LOCK, DPT_ENTRY_LOCK, PARALLEL_REDO_FLOW_CONTROL и PARALLEL_REDO_TRAN_TURN могут возникать на доступных для чтения вторичных узлах в группах доступности AlwaysOn.

Большое количество таких ожиданий может сигнализировать о проблемах с процессом параллельного повтора. Когда это происходит, ожидания наблюдаются в представлениях sys.dm_os_waiting_tasks и sys.dm_exec_requests и сопровождаются повышенной нагрузкой ЦП и ростом очереди повтора. Единственный способ борьбы с этой проблемой, известный на данный момент, — применить исправления SQL Server и/или отключить параллельный повтор с помощью флага трассировки T3459.

HADR_GROUP_COMMIT

Ожидание HADR_GROUP_COMMIT происходит, когда первичный узел группы доступности AlwaysOn пытается оптимизировать производительность репликации, группируя вместе несколько фиксационных записей журнала перед тем, как отправить их на вторичные узлы.

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

Можно также отключить групповую фиксацию с помощью флага трассировки T9546.

HADR_SYNC_COMMIT

Ожидание HADR_SYNC_COMMIT происходит, когда первичный узел группы доступности AlwaysOn ждет, пока синхронные вторичные узлы закрепят записи журнала транзакций.

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

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

HTBUILD

Ожидания HTBUILD, HTDELETE, HTMEMO, HTREINIT и HTREPARTITION возникают во время управления внутренними хеш-таблицами в пакетном режиме выполнения. В средах с индексами columnstore эти ожидания могут говорить о плохом обслуживании индексов columnstore, когда образуются большие разностные хранилища и/или группы строк неравномерного размера. Проанализируйте состояние групп строк с помощью представления sys.column_store_row_group и при необходимости перестройте индексы.

IO_COMPLETION

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

Вот несколько примеров:

Чтение страниц карты распределения из базы данных.
Чтение журнала транзакций во время восстановления базы данных.
Запись данных в tempdb во время переноса при сортировке.

Заметный процент ожиданий IO_COMPLETION может указывать на перегруженную подсистему ввода/вывода, особенно если при этом наблюдаются и другие ожидания, связанные с вводом/выводом. Устраните неисправности системы ввода/вывода (см. ожидание PAGEIOLATCH). Обратите особое внимание на задержку и пропускную способность tempdb. Плохая производительность tempdb — одна из наиболее частых причин этого ожидания.

LATCH_*

Ожидания, имена которых начинаются с LATCH_, вызываются кратковременными блокировками, не связанными с буферным пулом. SQL Server генерирует разные ожидания LATCH в зависимости от типа кратковременных блокировок (совмещаемые, монопольные и т. д.).

Чтобы получить статистику и подробности о кратковременных блокировках, а также анализировать узкие места, можно использовать представление sys.dm_os_latch_stats.

LCK_M_*

Ожидания, имена которых начинаются с LCK_M_, происходят во время блокирования. Каждому типу блокировки в SQL Server соответствует тип ожидания.

Проанализируйте, когда используется тот или иной тип блокировки, и определите и устраните основную причину блокирования.

LCK_M_I*

SQL Server устанавливает интентные блокировки на уровне объекта (таблицы) и страницы.

На уровне таблицы ожидания LCK_M_I* обычно происходят в двух случаях:

Несовместимость с блокировками модификации схемы. В этом случае обычно также наблюдаются ожидания блокировки схемы (LCK_M_SCH_S и LCK_M_SCH_M). Подробнее см. ожидание LCK_M_SCH_M.
Полная несовместимая блокировка на уровне таблицы, удерживаемая другим сеансом. Обычно это происходит из-за укрупнения блокировки или из-за кода, использующего указания TABLOCK и TABLOCKX. Для устранения неполадок с укрупнением блокировки можно использовать Blocking Monitoring Framework и представление sys.dm_index_operational_stats.

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

LCK_M_R*

Типы ожидания LCK_M_R* указывают на ожидание блокировки диапазона. SQL Server устанавливает такие блокировки на уровне изоляции SERIALIZABLE. Еще одна возможная причина ожиданий — некластеризованные индексы, для которых установлен параметр IGNORE_DUP_KEY=ON.

Увидев эти ожидания, определите и устраните основную причину блокирования. Избегайте параметра IGNORE_DUP_KEY=ON в некластеризованных индексах. Не используйте уровень изоляции SERIALIZABLE без крайней необходимости.

LCK_M_S

Тип ожидания LCK_M_S указывает на ожидание совмещаемых блокировок (S).
Этот тип блокировки устанавливается запросами SELECT на уровнях изоляции READ COMMITTED, REPEATABLE READ и SERIALIZABLE.

Неоптимизированные запросы SELECT — самый распространенный источник этих ожиданий.

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

(Правда, это лишь скроет проблему с неоптимизированными запросами, а не решит ее.)

LCK_M_SCH_M

Тип ожидания LCK_M_SCH_M указывает на ожидание блокировок модификации схемы (Sch-М), когда сеансы не могут получить монопольные блокировки для объектов.

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

Посмотрите, как часто возникают эти ожидания. Они могут быть вызваны однократными событиями и ошибками во время развертывания.

LCK_M_SCH_S

Тип ожидания LCK_M_SCH_S указывает на ожидание блокировки стабильности схемы (Sch-S). Этот тип блокирования возникает во время модификации схемы, когда другие сеансы удерживают блокировки модификации схемы (Sch-M) на объектах.

LCK_M_U

Тип ожидания LCK_M_U указывает на ожидание блокировок обновления (U). SQL Server устанавливает их во время просмотра обновлений, обычно вызванного неоптимизированными запросами записи (UPDATE, DELETE, MERGE). Во многих случаях это сопровождается типами ожидания PAGEIOLATCH* и CXPACKET.

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

LCK_M_X

Тип ожидания LCK_M_X указывает на ожидание монопольных блокировок (X).
Распространенные причины таких ожиданий — искусственные точки сериализации (таблицы счетчиков), злоупотребление уровнями изоляции REPEATABLE READ и SERIALIZABLE, неэффективное управление транзакциями и длительные транзакции, а также указания блокировки на уровне таблицы, такие как (TABLOCKX).

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

LOGBUFFER

Ожидание LOGBUFFER возникает, когда SQL Server ожидает доступный буфер журнала, чтобы внести записи журнала. Обычно это ожидание идет в паре с ожиданием WRITELOG и указывает на недостаточную пропускную способность журнала транзакций. См. ожидание WRITELOG.

OLEDB

Ожидание OLEDB происходит, когда SQL Server ожидает данных от поставщика OLEDB.

Чаще всего оно наблюдается в таких случаях:

Вызовы на связанные серверы.
Выполнение некоторых пакетов SQL Server Integration Services (SSIS).
Операции во время выполнения DBCC CHECKDB.
Запросы к динамическим административным представлениям.

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

PAGEIOLATCH*

Ожидания PAGEIOLATCH* возникают, когда SQL Server считывает страницы данных с диска. Эти ожидания очень распространены и наблюдаются в большинстве систем. Существует шесть типов ожидания PAGEIOLATCH, которые относятся к разным видам операций со страницами буферного пула. Анализируйте ожидания всех типов PAGEIOLATCH совместно, чтобы оценить их совокупное влияние.

Чрезмерное количество ожиданий PAGEIOLATCH показывает, что SQL Server постоянно считывает данные с диска.

Обычно это происходит в двух случаях:

недостаточное аппаратное обеспечение SQL Server, когда активные данные
не помещаются в памяти;
неоптимизированные запросы, которые просматривают ненужные данные,
сбрасывая содержимое буферного пула на диск.

Можно выполнить перекрестную проверку данных, изучив счетчик производительности Page Life Expectancy, который показывает, как долго страницы данных остаются в буферном пуле.

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

Как устранить проблему:

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

PAGELATCH

Ожидания PAGELATCH указывают на блокировки, связанные с буферным пулом, которые происходят, когда потокам необходимо одновременно получить доступ к данным (или страницам карт распределения) или модифицировать их.

Две основные причины таких ожиданий — состязания на системных страницах tempdb и «горячие точки» в постоянно растущих индексах.

Когда наблюдаются эти ожидания, разберитесь, что их вызывает. Чтобы проверить, связаны ли они с tempdb, проанализируйте столбец wait_resource в представлении sys.dm_os_waiting_tasks. Также можно перехватить расширенное событие sqlserver.latch_suspend_end. Устраняя проблемы с кратковременными блокировками в tempdb, убедитесь, что tempdb настроена правильно, уменьшите нагрузку на нее и попробуйте включить метаданные tempdb, оптимизированные для памяти, если такая функция поддерживается.

Индексы с «горячими точками» можно найти с помощью функции sys.dm_db_index_operational_stats. В SQL Server 2019 и более поздних версиях можно уменьшить состязания, включив настройку индекса OPTIMIZE_FOR_SEQUENTIAL_KEY.

Также можно провести рефакторинг схемы, внедрить секционирование хешей или использовать OLTP в памяти.

PREEMPTIVE_OS_AUTH*

Ожидания с именами, начинающимися с PREEMPTIVE_OS_AUTH, PREEMPTIVE_OS_LOOKUPACCOUNTSID и PREEMPTIVE_OS_ACCEPTSCURITYCONTEXT, происходят во время аутентификации пользователей.

Если наблюдается значительный процент таких ожиданий, проверьте производительность контроллеров Active Directory. Убедитесь, что SQL Server не выполняет аутентификацию на контроллерах Active Directory в удаленных дата-центрах.

Также проверьте, не используется ли в коде контекст EXECUTE AS OWNER или EXECUTE AS USER, который инициирует вызовы аутентификации.

PREEMPTIVE_OS_WRITEFILE

Ожидание PREEMPTIVE_OS_WRITEFILE может быть признаком узкого места во время синхронной записи в файлы. Обнаружив эти ожидания, проверьте, не выполняет ли сервер несколько трассировок или аудитов SQL, используя файлы как целевые объекты для сохранения данных.

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

PREEMPTIVE_OS_WRITEFILEGATHER

Ожидание PREEMPTIVE_OS_WRITEFILEGATHER происходит во время процесса инициализации нулями. Когда в системе возникает это ожидание, проверьте и включите мгновенную инициализацию файла, предоставив учетной записи SQL Server разрешение Perform Volume Management Tasks (SE_MANAGE_VOLUME_NAME).

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

QDS*

Семейство типов ожидания, имена которых начинаются с QDS, связано с хранилищем запросов. Они могут быть признаком накладных расходов из-за сбора данных хранилища запросов. Ожидания QDS_PERSIST_TASK_MAIN_LOOP_SLEEP и QDS_ASYNC_QUEUE можно игнорировать — они безвредные.

Если наблюдаются другие ожидания QDS, проверьте настройки хранилища запросов. Не используйте режим сбора данных QUERY_CAPTURE_MODE=ALL. Уменьшите размер хранилища запросов, если оно слишком велико.

Также включите флаги трассировки T7745 и T7752.

RESOURCE_SEMAPHORE

Ожидание RESOURCE_SEMAPHORE происходит, когда запросы ожидают предоставления памяти. Этот тип ожидания всегда стоит изучать, если он становится заметен в системе.

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

Ожидания RESOURCE_SEMAPHORE также могут вызываться отдельными запросами. Проанализируйте объемы предоставленной памяти с помощью представления sys.dm_exec_query_memory_grants и при необходимости оптимизируйте запросы.

RESOURCE_SEMAPHORE_QUERY_COMPILE

Ожидание RESOURCE_SEMAPHORE_QUERY_COMPILE происходит, когда SQL Server не хватает памяти для компиляции запросов. Как и в случае с RESOURCE_SEMAPHORE, необходимо изучить проблему.
Проверьте, не выполняет ли SQL Server слишком много компиляций. Для этого просмотрите счетчики производительности SQL Compilations/sec и SQL Recompilations/sec. Сократите количество компиляций за счет параметризации запросов. Выполните общее устранение неполадок, связанных с памятью, как описано в статье об ожидании RESOURCE_SEMAPHORE.

Я также замечал, что ожидание RESOURCE_SEMAPHORE_QUERY_COMPILE возникает, когда очень активная таблица удерживает блокировку (Sch-M) во время длительного процесса автономного перестроения индекса. Одновременно с этим SQL Server пытается перекомпилировать большое количество запросов, обращающихся к этой таблице. Компиляции блокируются и в конце концов потребляют огромное количество памяти, из-за чего остальным заявкам на компиляцию приходится ждать с этим типом ожидания.

THREADPOOL

Ожидание THREADPOOL возникает, когда у SQL Server нет доступных рабочих процессов для обслуживания запросов пользователей. Это опасное ожидание, которое необходимо исследовать.

Чаще всего оно возникает в следующих случаях:

неправильная настройка параметра max worker threads;
недостаточный объем памяти SQL Server (проверьте конфигурацию ОС и SQL Server);
длинные цепочки блокирования;
чрезмерная нехватка памяти;
большое количество подключенных клиентов;
нагрузка с чрезмерным количеством запросов с параллельными планами выполнения.

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

WRITE_COMPLETION

Ожидание WRITE_COMPLETION возникает во время операций синхронной записи в файлы данных и журналов. По моему опыту, чаще всего это происходит при создании снимков базы данных.
Когда в системе возникает это ожидание, проверьте, не создаются ли снимки базы. Помните, что некоторые внутренние процессы, такие как DBCC CHECKDB, создают внутренние снимки базы.
Когда ожидание WRITE_COMPLETION наблюдается вместе с другими ожиданиями, связанными с вводом/выводом, следует устранить неполадки системы ввода/вывода (см. ожидания PAGEIOLATCH).

WRITELOG

Ожидание WRITELOG возникает, когда SQL Server вносит записи журнала в журнал транзакций. Это ожидание нормально в любой системе, но его значительный процент и/или большое среднее время ожидания могут быть признаком узкого места в журнале транзакций. Ожидание WRITELOG часто наблюдается в паре с ожиданием LOGBUFFER, а оно тоже является признаком узкого места.
Чтобы устранить проблему, проанализируйте среднее время ожидания и задержку записи журнала транзакций с помощью представления sys.dm_io_virtual_file_stats. Высокие значения этих метрик могут повлиять на пропускную способность системы. Устраните неполадки с производительностью журнала транзакций и системы ввода/вывода (см. ожидания PAGEIOLATCH).

Типы хранимых данных Microsoft SQL Server

Советы по T-SQL и Microsoft SQL Server (из книги Путь программиста T-SQL)

Всегда визуализируйте информацию в базе данных в виде таблиц, это поможет Вам лучше выстроить связь между таблицами, и написать необходимый SQL запрос.
Если Вам нужно хранить в столбце таблицы логический тип данных (только TRUE или FALSE), т.е. как Boolean в других языках программирования, то используйте тип данных BIT, не нужно использовать SMALLINT или INT.
Не используйте столбцы с типами float и real в условии SQL запросов, так как данные типы не хранят точных значений. Также не используйте float и real в финансовых приложениях, в операциях, связанных с округлением. Для этого лучше использовать decimal, money или smallmoney.
Разработку и отладку инструкций на модификацию данных лучше всего осуществлять на тестовых данных, чтобы в случае логической ошибки в запросе ничего страшного не произошло, а на «боевых» данных выполнять только готовые, проверенные, отлаженные инструкции.
При добавлении данных в таблицу всегда указывайте столбцы, в которые необходимо добавить данные. Это избавит Вас от путаницы и возможных проблем с результатом данной операции. Чем четче Вы будете давать инструкции SQL серверу, тем меньше у Вас будет ошибок!
Перед запуском инструкции UPDATE всегда проверяйте наличие и корректность условия, которое предварительно должно быть проверено на SELECT.
Запросы на изменение данных лучше всего проектировать так, чтобы они вставляли или изменяли как можно больше строк одной инструкцией, другими словами, если есть возможность заменить несколько инструкций обновления или добавления данных одной инструкцией, заменяйте, так эффективней по производительности.
При удалении данных Вы так же, как и при обновлении данных, должны четко понимать и протестировать условие на тестовых данных или с помощью инструкции SELECT, иными словами, Вы должны четко видеть, какие данные Вы сейчас удалите.
Всегда перед запуском массовой операции, которая требует серьезных расчетов на большом объеме данных, необходимо заранее провести обслуживание индексов, т.е. запустить процесс реорганизации и перестроения.
При создании таблицы всегда явно указывайте ограничение «NOT NULL» у всех столбцов, и по возможности столбцы не должны принимать значения NULL, иными словами, указывайте NOT NULL, только в исключительных случаях разрешайте хранения NULL значений.
В процессе планирования таблицы всегда думайте о том, какие именно данные Вам нужны в данной таблице, и на основе этого анализа создайте соответствующие ограничения, это избавит Вас от многочисленных проблем при добавлении данных и тем более, когда Вы будете анализировать эти данные, т.е. делать аналитические выборки.
При планировании структуры базы данных всегда выстраивайте связь между таблицами, которые логически связаны между собой, и создавайте соответствующие ограничения.
Всегда комментируйте свой код, свои SQL инструкции, там, где у Вас или у другого программиста, который будет читать это код, могут возникнуть вопросы.
Всегда перед запуском новой SQL инструкции, в которой есть цикл, проверяйте условие, при котором цикл должен завершиться, для того чтобы избежать попадания в бесконечный цикл.
Если в SQL инструкции используются сложные и важные алгоритмы, всегда используйте конструкцию обработки ошибок, тем самым Вы избежите непредвиденных результатов.
Любой код, который полностью повторяется 2 и более раз в одной или нескольких SQL инструкциях, выносите в функцию, т.е. напишите функцию, и везде, где нужно использовать этот код, вызывайте эту функцию.
При внесении изменений в алгоритм работы функции помните о том, где и для каких целей эта функция используется, так как внесенные изменения отразятся во всех инструкциях, в которых задействована эта функция. Иными словами, не допускайте ситуаций, когда изменение алгоритма работы функции вносит корректные поправки в одни инструкции, для которых Вы и внесли эти изменения, а для других инструкций изменение алгоритма влечет некорректную работу этих SQL инструкций.
Если параметров в хранимой процедуре много, и некоторые из них являются необязательными, рекомендую при вызове таких процедур перечислять название параметров. Если параметров всего 1 или 2, то можно название параметров и не указывать, а использовать последовательность передачи значений.
При разработке триггера, который реализует некие бизнес правила, всегда четко планируйте и тестируйте все действия триггера, в противном случае в определенных условиях, которые Вы не учтете, триггер может скрытно, т.е. незаметно для Вас, вносить некорректные изменения в базу данных, а когда Вы обнаружите эти некорректные данные или нарушение целостности данных, Вам нужно будет еще отследить причины этих изменений, ведь с первого взгляда явных причин Вы не увидите. Последствия таких ошибок в работе триггера могут быть очень серьезные.
Если есть возможность выполнить задачу или реализовать алгоритм без использования курсоров, то делайте это без курсоров. Курсоры применяйте только в тех случаях, когда другого решения у Вас нет. Так как если в алгоритме работы приложения будет задействовано слишком много курсоров, это заметно замедлит работу приложения. Иными словами, используйте курсоры только в самых крайних случаях.
Если в одном SQL запросе SELECT Вы прибегаете к использованию нескольких подзапросов в секциях FROM или JOIN, выносите данные подзапросы в обобщенное табличное выражение (конструкция WITH) – впоследствии это Вам значительно упростит понимание логики запроса.
В целях безопасности и сохранности данных, рекомендуется четко планировать права доступа для пользователей. Например, не назначать всем пользователям роль db_owner или серверную роль sysadmin, так как пользователи, сами того не зная, могут выполнить действия, которые повлекут за собой неправомерное или некорректное изменение данных или вовсе потерю данных. Последствия в таких случаях могут быть очень серьезные. Поэтому назначайте пользователям только те права, которые им необходимы для выполнения их непосредственной работы.
Если у Вас на одном экземпляре SQL Server несколько баз данных, всегда проверяйте контекст подключения к базе данных перед запуском SQL инструкций. Также рекомендуется в инструкциях для администратора сервера принудительно использовать команду USE для точного указания базы данных, для которой предназначена эта инструкция.
Всегда создавайте архивы баз данных. Уделите время и разработайте план и схему резервирования базы данных, у инструкции BACKUP DATABASE много параметров, которые в этом Вам помогут. В итоге у Вас всегда должен быть актуальный архив базы данных на случай непредвиденных ситуаций, данный архив должен храниться в отдельном хранилище (сервере), а это хранилище или сервер должен располагаться в помещении, отличном от помещения, в котором расположен сервер с экземпляром SQL Server.
Операцию сжатия базы данных не рекомендуется выполнять слишком часто, так как она может вызвать фрагментацию индексов и замедлить работу базы данных. Поэтому выполняйте операцию сжатия базы данных только в самых крайних случаях.

Литература, использованная при подготовки статьи

Путь программиста T-SQL. Самоучитель по языку Transact-SQL.
Виталий Трунин

Объяснение конструкций ANSI_NULLS / QUOTED_IDENTIFIER / NOCOUNT в Microsoft SQL Server

Настройка SET ANSI_NULLS ON;

Настройка SET QUOTED_IDENTIFIER ON;

Настройка SET NOCOUNT ON;

CROSS APPLY vs OUTER APPLY

Как работает OUTER APPLY в Microsoft SQL Server?

Пример использования OUTER APPLY

В чем отличие в работе между OUTER APPLY и LEFT OUTER JOIN?

Как работает CROSS APPLY в SQL Server?

Пример использования CROSS APPLY

CROSS APPLY vs INNER JOIN

Сравнение CAST, TRY_CAST, и COALESCE в SQL Server

CAST

TRY_CAST

COALESCE

Комбинированный пример

Вывод

OPTION CLAUSE

OPTION (MAXDOP <integer_value>)

OPTION (FORCE ORDER)

OPTION (MAX_GRANT_PERCENT = <integer_value>)

OPTION (MAXRECURSION <integer_value>)

OPTION (RECOMPILE)

OPTION (MERGE JOIN)

OPTION (MERGE UNION)

OPTION (HASH GROUP, FAST 10)

Индексы

Что такое индексы в SQL Server

Heap table (таблица-куча)

Структура индекса

Типы индексов в MSSQL

Hash Index (Хэш-индекс)

Memory-Optimized Nonclustered Index (Некластеризованный индекс, оптимизированный для памяти)

Clustered Index (Кластеризованный индекс)

Nonclustered Index (Некластеризованный индекс)

Unique Index (Уникальный индекс)

Columnstore Index (Поколоночный индекс)

Index with Included Columns (Индекс с включенными столбцами)

Index on Computed Columns (Индекс на вычисляемых столбцах)

Filtered Index (Отфильтрованный индекс)

Spatial Index (Пространственный индекс)

XML Index (XML Индекс)

Full-Text Index (Полнотекстовый индекс)

Индексы с колоночным хранением (Columnstore Indexes)

Что такое индексы Columnstore?

Типы индексов Columnstore в SQL Server

Кластеризованный индекс columnstore (CCI)

Некластеризованный индекс columnstore (NCCI)

Когда следует использовать индексы Columnstore?

Когда не следует использовать индексы Columnstore?

Заключение по Columnstore Index

Архитектура и преимущества

Лучшие практики

Обслуживание и реорганизация

Шаблоны запросов и оптимизация

Дополнительные статьи и видео по Columnstore Index в MSSQL

Индексы с колоночным хранением (Columnstore Indexes) в SQL Server 2016

Clustered Columnstore Index — теория и реальность | Антон Белокрылов

Дмитрий Короткевич. Колоночные индексы (30.05.2018)

И три статьи от Microsoft

Оптимизация индексов

Основы хранения данных в SQL Server

Еще немного про кучи и сбалансированные деревья

Как заполняются страницы

Как возникает фрагментация?

Мифы о фрагментации

Выводы и рекомендации

Вопросы производительности запросов

Основные операции плана выполнения SQL Server

Index Seek

Index Scan

RID Lookup

Key Lookup

Sort

Spool

Merge Join

Nested Loops Join

Hash Match Join

Parallelism

Stream Aggregate

Настройка `SET ANSI_NULLS ON;`

Настройка `SET QUOTED_IDENTIFIER ON;`

Настройка `SET NOCOUNT ON;`