MS SQL Server дело тонкое...: Тащим "багаж" через планы запросов: почему широкие запросы становятся неподъёмными

Автор: Kendra Little, Carrying Baggage Through Query Plans: Why Wide Queries Get Heavy

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

SQL Server часто минимизирует доступ к данным, захватывая все столбцы, которые ему понадобятся, на раннем этапе выполнения запроса, а затем выполняя соединения и фильтрацию. Это означает, что презентационные столбцы (presentation columns) подхватываются рано.

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

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

енот любит мусор,
пингвин любит хорошо прошлёпать,
администратор баз данных любит говорить «нет»,
инструкция MERGE любит хаос,
я люблю свободный проход в магазине.

Краткое содержание

Проблема: Презентационные столбцы, которые не нужны для фильтрации или сортировки, проходят через дорогостоящие операции, такие как сортировки и хэш-соединения. Это часто приводит к большим распределениям памяти (memory grants), сбросам на диск (spills) в TempDB и медленным запросам. Иногда это нормально. Особенно для широких запросов — часто нет.

Решение: Отделите основные данные, необходимые для фильтрации и сортировки, от презентационных столбцов. Два шаблона хорошо работают, когда вам нужно обработать много «багажа» в запросе SQL Server:

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

🤔 Но почему? Я подозреваю, что основная причина, по которой SQL Server делает такой выбор, — это боязнь случайного ввода-вывода (random I/O), которая преследует его ещё со времён вращающихся жёстких дисков. Хотела бы я иметь что-то вроде настройки стоимости случайной страницы в Postgres, чтобы поэкспериментировать, — мне интересно, как бы это повлияло. Но у нас есть другие способы настройки.

Почему это часто неэффективно

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

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

Таким образом, чем больше столбцов вы выбираете в своём списке SELECT, тем тяжелее и медленнее становятся эти операции. SQL Server оценивает распределение памяти (memory grant) на основе ожидаемого количества строк для обработки и ширины всех столбцов в этих строках. Большие распределения памяти «воруют» память у буферного пула (ей нужно откуда-то взяться). Конкурентные запросы с большими распределениями памяти могут приводить к ожиданиям типа RESOURCE_SEMAPHORE, из-за чего другие запросы могут вставать в очередь ещё до того, как они начнутся.

Разберём на примере

Спойлер: краткое изложение результатов ниже.

Подход	Время выполнения	Распределение памяти	Макс. использовано памяти	Лучшее применение
Исходный запрос	13 секунд	15 ГБ	10,5 ГБ	Предоставить оптимизатору запросов свободу выбора.
Временная таблица	3 секунды	6,8 ГБ (первый запрос), 6,5 МБ (второй запрос)	776 МБ (первый запрос), 0 (второй запрос)	Сложная логика, нужна статистика, отладка.
Производная таблица	3 секунды	7,8 ГБ	784 МБ	Вы хотите оставить всё в одном запросе и не беспокоитесь о необходимости статистики между шагами.

Вот уровень совместимости и индексы, которые я использую. Этот запрос выполнялся на SQL Server 2025 на моём ноутбуке.

USE StackOverflow2013;
GO

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 170;
GO

EXECUTE dbo.DropIndexes;
GO

CREATE NONCLUSTERED INDEX
    ix_Posts_OwnerUserId_Score_PostTypeId
ON dbo.Posts
(
    OwnerUserId,
    PostTypeId,
    Score
)
WITH
(
    DATA_COMPRESSION = PAGE,
    SORT_IN_TEMPDB = ON
);
GO

CREATE NONCLUSTERED INDEX
    ix_Comments_UserId_Score
ON dbo.Comments
(
    UserId,
    Score
)
WITH
(
    DATA_COMPRESSION = PAGE,
    SORT_IN_TEMPDB = ON
);
GO

CREATE NONCLUSTERED INDEX
    ix_Comments_PostId
ON dbo.Comments
(
    PostId
)
WITH
(
    DATA_COMPRESSION = PAGE,
    SORT_IN_TEMPDB = ON
);
GO

Широкий запрос входит в чат

Я назвала этот запрос «StackOverflow Sick Burns and Top Pithyness».

Запрос находит комментарии с высокими оценками, где:

Оценка комментария (Score) > 200 (обязательно)
Комментарий находится НЕ на посте, созданном самим пользователем
Если у пользователя есть вопросы, оценка комментария должна быть выше, чем оценка его самого высокого вопроса
Возвращает топ-100 комментариев (по оценке) с деталями пользователя, деталями комментария, деталями поста и (если есть) самым высоким вопросом пользователя
Обеспечивает один вопрос на пользователя (наивысшая оценка, наибольший Id как разрешение ничьей), чтобы избежать дублирования строк

Запрос выполняется 13 секунд на моём ноутбуке.

SELECT TOP 100
    /* Основные столбцы, используемые для фильтрации/сортировки */
    userId = u.Id,
    commentScore = c.Score,
    questionScore = userHighestQuestion.questionScore,
    scoreDifference = c.Score - ISNULL(userHighestQuestion.questionScore, 0),

    /* Только презентационные: детали комментария */
    commentId = c.Id,
    commentCreationDate = c.CreationDate,
    commentText = c.Text, /* большой varchar — только презентация */
    
    /* Только презентационные: детали пользователя */
    userDisplayName = u.DisplayName,
    userReputation = u.Reputation,
    userLocation = u.Location,
    userCreationDate = u.CreationDate,
    userUpvotes = u.UpVotes,
    userDownvotes = u.DownVotes,
    userViews = u.Views,
    userAboutMe = u.AboutMe, /* LOB-столбец — только презентация */
   
    /* Только презентационные: сообщение, к которому оставлен комментарий */
    commentPostTitle = p.Title,
    commentPostBody = p.Body, /* LOB-столбец — только презентация */
    commentPostViewCount = p.ViewCount,
    commentPostScore = p.Score,
    commentPostTags = p.Tags,
    commentPostOwnerDisplayName = up.DisplayName,
    commentPostOwnerReputation = up.Reputation,
    
    /* Только презентационные: самый популярный вопрос пользователя (если есть) */
    questionId = q.Id,
    questionTitle = q.Title,
    questionCreationDate = q.CreationDate,
    questionViewCount = q.ViewCount,
    questionTags = q.Tags,
    questionBody = q.Body, /* LOB-столбец — только презентация */
    
    /* Только презентационные: вычисляемые столбцы */
    daysSinceComment = 
        DATEDIFF
        (
            DAY,
            c.CreationDate,
            GETDATE()
        ),
    userReputationCategory = 
        CASE
            WHEN u.Reputation < 100
            THEN N'New User'
            WHEN u.Reputation < 1000
            THEN N'Regular User'
            WHEN u.Reputation < 10000
            THEN N'Established User'
            ELSE N'Expert User'
        END
FROM dbo.Comments AS c
JOIN dbo.Users AS u
  ON u.Id = c.UserId /* исключает комментарии с NULL UserId (удалённые пользователи) */
JOIN dbo.Posts AS p
  ON p.Id = c.PostId
JOIN dbo.Users AS up
  ON up.Id = p.OwnerUserId /* исключает комментарии к сообщениям вики-сообщества (NULL OwnerUserId) */
LEFT JOIN
(
    /* Получаем один вопрос на пользователя: наивысшая оценка, наибольший Id как разрешение ничьей */
    SELECT
        userId = q.OwnerUserId,
        questionId = q.Id,
        questionScore = q.Score
    FROM dbo.Posts AS q
    WHERE q.PostTypeId = 1
    /* Находим вопросы с максимальной оценкой для этого пользователя, наибольший Id как разрешение ничьей */
    AND   q.Id =
    (
        SELECT
            MAX(qMaxScore.Id)
        FROM dbo.Posts AS qMaxScore
        WHERE qMaxScore.OwnerUserId = q.OwnerUserId
        AND   qMaxScore.PostTypeId = 1
        AND   qMaxScore.Score =
        (
            SELECT
                MAX(qUser.Score)
            FROM dbo.Posts AS qUser
            WHERE qUser.OwnerUserId = q.OwnerUserId
            AND   qUser.PostTypeId = 1
        )
    )
) AS userHighestQuestion
  ON userHighestQuestion.userId = c.UserId
LEFT JOIN dbo.Posts AS q
  ON q.Id = userHighestQuestion.questionId
WHERE 
    CASE
        WHEN c.Score > 200
        AND  c.UserId <> p.OwnerUserId
        AND  (userHighestQuestion.questionScore IS NULL OR c.Score > userHighestQuestion.questionScore)
        THEN 1
        ELSE 0
    END = 1
ORDER BY
    c.Score DESC;
GO

Вот общая форма фактического плана запроса (описание).

План показывает несколько интересных вещей, но одно решение, которое идёт не так, заключается в том, что он подхватывает презентационные столбцы Body, Tags, Title и другие из таблицы Posts для псевдонима q, а затем передаёт все эти данные в оператор Filter.

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

Столбец Body содержит весь текст вопросов — это большой объём данных. Запрос имеет распределение памяти 15 ГБ и использует 10,5 ГБ из них.

Вариант настройки: временная таблица

Давайте разобьём запрос на два шага.

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

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

Этот вариант возвращает результат за 3 секунды на моём ноутбуке. Распределение памяти для запроса, который заполняет временную таблицу, составляет 6,8 ГБ (использовано 776 МБ) и 6,5 МБ для запроса, который читает из временной таблицы и выполняет соединения для получения детальных данных (использовано 0).

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

DROP TABLE IF EXISTS #core;

SELECT TOP 100 p.Id as PostId, u.Id as UserId, c.Id as CommentId, q.Id as questionId, userHighestQuestion.questionScore, up.DisplayName, up.Reputation
INTO #core
FROM dbo.Comments AS c
JOIN dbo.Users AS u
    ON u.Id = c.UserId /* исключает комментарии с NULL UserId (удалённые пользователи) */
JOIN dbo.Posts AS p
    ON p.Id = c.PostId
JOIN dbo.Users AS up
    ON up.Id = p.OwnerUserId /* исключает комментарии к сообщениям вики-сообщества (NULL OwnerUserId) */
LEFT JOIN
(
    /* Получаем один вопрос на пользователя: наивысшая оценка, наибольший Id как разрешение ничьей */
    SELECT
        userId = q.OwnerUserId,
        questionId = q.Id,
        questionScore = q.Score
    FROM dbo.Posts AS q
    WHERE q.PostTypeId = 1
    AND   q.Id =
    (
        SELECT
            MAX(qMaxScore.Id)
        FROM dbo.Posts AS qMaxScore
        WHERE qMaxScore.OwnerUserId = q.OwnerUserId
        AND   qMaxScore.PostTypeId = 1
        AND   qMaxScore.Score =
        (
            SELECT
                MAX(qUser.Score)
            FROM dbo.Posts AS qUser
            WHERE qUser.OwnerUserId = q.OwnerUserId
            AND   qUser.PostTypeId = 1
        )
    )
) AS userHighestQuestion
    ON userHighestQuestion.userId = c.UserId
LEFT JOIN dbo.Posts AS q
    ON q.Id = userHighestQuestion.questionId
WHERE 
    CASE
        WHEN c.Score > 200
        AND  c.UserId <> p.OwnerUserId
        AND  (userHighestQuestion.questionScore IS NULL OR c.Score > userHighestQuestion.questionScore)
        THEN 1
        ELSE 0
    END = 1
ORDER BY
    c.Score DESC;

SELECT 
    /* Основные столбцы, используемые для фильтрации/сортировки */
    userId = u.Id,
    commentScore = c.Score,
    questionScore = core.questionScore,
    scoreDifference = c.Score - ISNULL(core.questionScore, 0),

    /* Только презентационные: детали комментария */
    commentId = c.Id,
    commentCreationDate = c.CreationDate,
    commentText = c.Text,
    
    /* Только презентационные: детали пользователя */
    userDisplayName = u.DisplayName,
    userReputation = u.Reputation,
    userLocation = u.Location,
    userCreationDate = u.CreationDate,
    userUpvotes = u.UpVotes,
    userDownvotes = u.DownVotes,
    userViews = u.Views,
    userAboutMe = u.AboutMe,
   
    /* Только презентационные: пост, к которому оставлен комментарий */
    commentPostTitle = p.Title,
    commentPostBody = p.Body,
    commentPostViewCount = p.ViewCount,
    commentPostScore = p.Score,
    commentPostTags = p.Tags,
    commentPostOwnerDisplayName = core.DisplayName,
    commentPostOwnerReputation = core.Reputation,
    
    /* Только презентационные: самый высокий вопрос пользователя (если есть) */
    questionId = q.Id,
    questionTitle = q.Title,
    questionCreationDate = q.CreationDate,
    questionViewCount = q.ViewCount,
    questionTags = q.Tags,
    questionBody = q.Body,
    
    /* Только презентационные: вычисляемые столбцы */
    daysSinceComment = 
        DATEDIFF(DAY, c.CreationDate, GETDATE()),
    userReputationCategory = 
        CASE
            WHEN u.Reputation < 100 THEN N'New User'
            WHEN u.Reputation < 1000 THEN N'Regular User'
            WHEN u.Reputation < 10000 THEN N'Established User'
            ELSE N'Expert User'
        END
FROM #core as core
JOIN dbo.Posts as p on core.PostId=p.Id
JOIN dbo.Comments as c on core.CommentId=c.Id
JOIN dbo.Users as u on core.UserId=u.Id
JOIN dbo.Posts as q on q.Id = core.questionId;
GO

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

Вариант настройки: производная таблица

Действительно ли нам нужна временная таблица? Давайте посмотрим, как далеко мы можем продвинуться, оставаясь в одном запросе. Этот вариант настройки фокусируется на определении основного набора данных в производных таблицах или CROSS APPLY, а затем выполняет обратное соединение для получения более широких презентационных столбцов. В этом примере мы используем TOP, поэтому мы помещаем его внутрь производной таблицы и соединяемся с ней. Тот же шаблон может работать для запросов без TOP: используйте производную таблицу, чтобы отделить узкий набор данных для фильтрации/сортировки от презентационных столбцов.

Этот запрос завершается на моём ноутбуке за 3 секунды. Распределение памяти составляет 7,8 ГБ, и он использовал 784 МБ из этого. Если бы это был реальный производственный запрос, я бы выяснила, как немного уменьшить это распределение и донастроить запрос. Но для целей относительно быстрой статьи это передаёт общую идею.

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

SELECT 
    /* Основные столбцы, используемые для фильтрации/сортировки */
    userId = u.Id,
    commentScore = c.Score,
    questionScore = core.questionScore,
    scoreDifference = c.Score - ISNULL(core.questionScore, 0),

    /* Только презентационные: детали комментария */
    commentId = c.Id,
    commentCreationDate = c.CreationDate,
    commentText = c.Text,
    
    /* Только презентационные: детали пользователя */
    userDisplayName = u.DisplayName,
    userReputation = u.Reputation,
    userLocation = u.Location,
    userCreationDate = u.CreationDate,
    userUpvotes = u.UpVotes,
    userDownvotes = u.DownVotes,
    userViews = u.Views,
    userAboutMe = u.AboutMe,
   
    /* Только презентационные: пост, к которому оставлен комментарий */
    commentPostTitle = p.Title,
    commentPostBody = p.Body,
    commentPostViewCount = p.ViewCount,
    commentPostScore = p.Score,
    commentPostTags = p.Tags,
    commentPostOwnerDisplayName = core.DisplayName,
    commentPostOwnerReputation = core.Reputation,
    
    /* Только презентационные: самый высокий вопрос пользователя (если есть) */
    questionId = q.Id,
    questionTitle = q.Title,
    questionCreationDate = q.CreationDate,
    questionViewCount = q.ViewCount,
    questionTags = q.Tags,
    questionBody = q.Body,
    
    /* Только презентационные: вычисляемые столбцы */
    daysSinceComment = DATEDIFF(DAY, c.CreationDate, GETDATE()),
    userReputationCategory = 
        CASE
            WHEN u.Reputation < 100 THEN N'New User'
            WHEN u.Reputation < 1000 THEN N'Regular User'
            WHEN u.Reputation < 10000 THEN N'Established User'
            ELSE N'Expert User'
        END
FROM
(
    SELECT TOP 100 p.Id as PostId, u.Id as UserId, c.Id as CommentId, q.Id as questionId, userHighestQuestion.questionScore, up.DisplayName, up.Reputation
    FROM dbo.Comments AS c
    JOIN dbo.Users AS u
      ON u.Id = c.UserId
    JOIN dbo.Posts AS p
      ON p.Id = c.PostId
    JOIN dbo.Users AS up
      ON up.Id = p.OwnerUserId
    LEFT JOIN
    (
        SELECT
            userId = q.OwnerUserId,
            questionId = q.Id,
            questionScore = q.Score
        FROM dbo.Posts AS q
        WHERE q.PostTypeId = 1
        AND   q.Id =
        (
            SELECT
                MAX(qMaxScore.Id)
            FROM dbo.Posts AS qMaxScore
            WHERE qMaxScore.OwnerUserId = q.OwnerUserId
            AND   qMaxScore.PostTypeId = 1
            AND   qMaxScore.Score =
            (
                SELECT
                    MAX(qUser.Score)
                FROM dbo.Posts AS qUser
                WHERE qUser.OwnerUserId = q.OwnerUserId
                AND   qUser.PostTypeId = 1
            )
        )
    ) AS userHighestQuestion
      ON userHighestQuestion.userId = c.UserId
    LEFT JOIN dbo.Posts AS q
      ON q.Id = userHighestQuestion.questionId
    WHERE 
        CASE
            WHEN c.Score > 200
            AND  c.UserId <> p.OwnerUserId
            AND  (userHighestQuestion.questionScore IS NULL OR c.Score > userHighestQuestion.questionScore)
            THEN 1
            ELSE 0
        END = 1
    ORDER BY
        c.Score DESC
 ) as core
 
JOIN dbo.Posts as p on core.PostId=p.Id
JOIN dbo.Comments as c on core.CommentId=c.Id
JOIN dbo.Users as u on core.UserId=u.Id
JOIN dbo.Posts as q on q.Id = core.questionId;

Вот общая схема плана запроса:

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

Распространённые ошибки, на которые следует обратить внимание

При разделении запросов для уменьшения количества «багажа» следите за следующими проблемами:

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

Облегчение нагрузки

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

В следующий раз, когда вы увидите медленный запрос с широким результирующим набором, проверьте план выполнения. Посмотрите на операторы, такие как Sort или Hash Match, и изучите их свойства Output List или Defined Values. Если вы видите там гораздо больше столбцов, чем необходимо для самой операции, вы нашли «багаж». Попробуйте один из двух описанных подходов, чтобы облегчить нагрузку.

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

MS SQL Server дело тонкое...

20.5.26

Тащим "багаж" через планы запросов: почему широкие запросы становятся неподъёмными

Краткое содержание

Почему это часто неэффективно

Разберём на примере

Широкий запрос входит в чат

Вариант настройки: временная таблица

Вариант настройки: производная таблица

Распространённые ошибки, на которые следует обратить внимание

Облегчение нагрузки

Комментариев нет:

Отправить комментарий

Архив блога

Ярлыки

www.sql-ex.ru