Автор: Sarjen Haque, Performance issues from ORDER BY/GROUP BY - spills in tempdb
Совершенно обычно и ожидаемо видеть запрос, содержащий предложение ORDER BY или GROUP BY для целей отображения или группировки. Также часто разработчики используют предложение ORDER BY по привычке, не задумываясь о его необходимости. В результате запросы со временем замедляются по мере увеличения количества записей.
Когда операция сортировки не может получить достаточный грант памяти и не может быть выполнена в памяти, она должна использовать промежуточную материализацию tempdb. Более высокая нагрузка на tempdb значительно ухудшает общую производительность SQL Server. Эта ситуация обычно известна как «spill to tempdb» или «spills in tempdb». Крайне важно выявлять такие предупреждения о сортировке и по возможности избегать их.
По моему опыту, я видел, как ORDER BY/GROUP BY используется для столбца VARCHAR (8000) при извлечении данных; и даже неразумно используется в предложении JOIN! Настройка таких запросов немного сложна, и в большинстве случаев это невозможно, поскольку внешнее приложение или бизнес-логика уже построены на этом критерии. Создание индекса на этом столбце невозможно из-за ограничения в 900 байт для столбца ключа индекса. Таким образом, кроме как скрестить пальцы, мало что можно сделать для немедленного решения проблемы с производительностью.
Общие проблемы
Ниже приведены некоторые распространённые проблемы, возникающие из-за неправильного использования предложений ORDER BY/GROUP BY:
- Быстрый рост файлов данных tempdb.
- Увеличение активности дискового ввода-вывода на tempdb и диске tempdb.
- Появление конкуренции за блокировки и эскалации блокировок.
- Увеличение гранта памяти для операции сортировки/хэширования.
- Появление параллельного плана запроса.
Обнаружение проблемы
Обнаружение проблем с производительностью, возникающих из-за операции сортировки, довольно простое и понятное. Ниже приведены некоторые советы по выявлению проблем:
- Просмотрите запрос и определите столбцы, используемые в предложениях ORDER/GROUP.
- Просмотрите план выполнения и найдите операторы «sort».
- Определите операторы параллелизма, которые выполняют «распределение потоков», «сбор потоков» и «перераспределение потоков» в параллельном плане выполнения.
- Используйте событие трассировки SQL Profiler «sort warnings».
- Расширенное событие (Extended Event) — «sort_warning».
- Используйте PerfMon или sys.dm_os_performance для отслеживания «worktables created/sec» и «workfiles created/sec».
Решение проблемы с производительностью
Для решения проблем с производительностью, возникающих из-за операции сортировки, можно предпринять несколько действий:
- Проверьте необходимость операции сортировки в запросе.
- Попробуйте выполнять сортировку на стороне клиентского приложения.
- Нормализуйте схему базы данных.
- Создайте один или несколько индексов.
- Примените фильтры к индексам.
- Используйте TOP (n), когда есть «ORDER BY», если это возможно.
- Добавьте больше фильтров в запрос, чтобы обрабатывать меньше данных.
- Обновите статистику распределения.
Наблюдение за поведением
Чтобы наблюдать за типичными проблемами с операциями ORDER BY/GROUP BY, давайте создадим базу данных, таблицу и выполним простой запрос SELECT на 500 000 записей.
CREATE DATABASE testDB
GO
USE testDB
GO
SET NOCOUNT ON
IF OBJECT_ID('tblLarge') IS NOT NULL
DROP TABLE tblLarge
GO
CREATE TABLE tblLarge
(
xID INT IDENTITY(1, 1) ,
sName1 VARCHAR(100) ,
sName2 VARCHAR(1000) ,
sName3 VARCHAR(400) ,
sIdentifier CHAR(100) ,
dDOB DATETIME NULL ,
nWage NUMERIC(20, 2) ,
sLicense VARCHAR(25)
)
GO
/*********************************
Add 500000 records
**********************************/
SET NOCOUNT ON
INSERT INTO tblLarge
( sName1 ,
sName2 ,
sName3 ,
sIdentifier ,
dDOB ,
nWage ,
sLicense
)
VALUES ( LEFT(CAST(NEWID() AS VARCHAR(36)), RAND() * 50) , -- sName1
LEFT(CAST(NEWID() AS VARCHAR(36)), RAND() * 60) , -- sName2
LEFT(CAST(NEWID() AS VARCHAR(36)), RAND() * 70) , -- sName2
LEFT(CAST(NEWID() AS VARCHAR(36)), 2) , -- sIdentifier
DATEADD(dd, -RAND() * 20000, GETDATE()) , -- dDOB
( RAND() * 1000 ) , -- nWage
SUBSTRING(CAST(NEWID() AS VARCHAR(36)), 6, 7) -- sLicense
)
GO 500000
/******************************************************
** Create a clustered index
******************************************************/
ALTER TABLE [tblLarge]
ADD CONSTRAINT [PK_tblLarge]
PRIMARY KEY CLUSTERED ([xID] ASC)
/***************************************************************
** To resolve the sort warning, create a non-clustered index
***************************************************************/
CREATE NONCLUSTERED INDEX [IX_sName1]
ON [tblLarge] ([sName1] ASC)
Простые операторы SELECT
Ниже приведены несколько простых операторов SELECT для воспроизведения поведения.
/******************************************************
** Simple select statement
******************************************************/
--First query
SELECT xID ,
sName1
FROM tblLarge
-- Second query with - ORDER BY
SELECT xID ,
sName1
FROM tblLarge
ORDER BY sName1
-- Third query - GROUP BY/ORDER BY
SELECT sName1 ,
COUNT(sName1) AS nCount
FROM tblLarge a
GROUP BY sName1
ORDER BY sName1
Используя расширенные события (SQL 2012), трассировку SQL Profiler и план выполнения, «sort warning» легко обнаруживаются. Ниже приведены некоторые выходные данные.
Рисунок №1: Предупреждение о сортировке с использованием расширенных событий в SQL 2012
Рисунок №2A: Обнаружение предупреждения о сортировке с использованием плана выполнения
Рисунок №2B: Обнаружение предупреждения о сортировке с использованием плана выполнения
Рисунок №3: Обнаружение предупреждения о сортировке с использованием трассировки SQL Profiler
Подробнее
- Что происходит с данными и журналом при сбросах в tempdb — Paul Randal
- Query Memory Spills — SQL Passion
- Identifying and Solving Sort Warnings Problems in SQL Server — SolidQ
Комментариев нет:
Отправить комментарий