Optimizing I/O Performance by Sorting – Part 2

Автор: Craig Freedman Optimizing I/O Performance by Sorting – Part 2

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

Компьютер, который использовался для запуска теста, имеет 8 ГБ памяти. Чтобы сделать эффект более заметным с точки зрения производительности из-за того, что размер таблицы был бы достаточно большим, чтобы не поместится в оперативную память, предварительно был настроен параметр SP_CONFIGURE 'MAX SERVER MEMORY', который разрешал использовать только 1 ГБ памяти. Команда CHECKPOINT выполнялась для того, чтобы гарантировать, что страницы созданной базы данных полностью сброшена на диск перед запуском тестов. Также, запуск DBCC DROPCLEANBUFFERS перед каждым тестом призван гарантировать что никакие данные не закэшировались в буферном пуле перед началом теста.

CHECKPOINT
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', '1'
RECONFIGURE
EXEC SP_CONFIGURE 'MAX SERVER MEMORY', '1024'
RECONFIGURE
DBCC DROPCLEANBUFFERS
 

Обратите внимание: НЕ СТОИТ запускать этот набор операторов на промышленном сервере!

Как уже отмечалось в первой части, SQL Server может использовать один из трех возможных планов показанного ниже запроса в зависимости от значения константы:

SELECT SUM(Data)
FROM T
WHERE RandKey < constant

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

Чтобы продемонстрировать преимущества сортировки, мне нужно иметь возможность запускать один и тот же запрос с сортировкой и без нее. Простой способ заставить SQL Server удалить сортировку — использовать оператор UPDATE STATISTICS с нужными параметрами, которые заставят SQL Server поверить, что таблица в тесте якобы очень маленькая. А чтобы гарантировать, что план будет с поиском по некластерному индексу и поиском закладок, нужно добавить хинт INDEX. Также добавим подсказку оптимизатору RECOMPILE, которая будет гарантировать, что SQL Server создаст новый план после того, как будет изменена статистика.

UPDATE STATISTICS T WITH ROWCOUNT = 1, PAGECOUNT = 1
 
SELECT SUM(Data)
FROM T WITH (INDEX (IRandKey))
WHERE RandKey < constant
OPTION (RECOMPILE)

После этого изменим статистику, используя следующий оператор:

UPDATE STATISTICS T WITH ROWCOUNT = 25600000, PAGECOUNT = 389323

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

  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
       |--Stream Aggregate(DEFINE:([Expr1010]=COUNT_BIG([T].[Data]), [Expr1011]=SUM([T].[Data])))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([T].[PK], [Expr1009]) WITH UNORDERED PREFETCH)
                 |--Sort(ORDER BY:([T].[PK] ASC))
                 |    |--Index Seek(OBJECT:([T].[IRandKey]), SEEK:([T].[RandKey] < (2000000)) ORDERED FORWARD)
                 |--Clustered Index Seek(OBJECT:([T].[PK__T__...]), SEEK:([T].[PK]=[T].[PK]) LOOKUP ORDERED FORWARD)

Вот пример плана после запуска UPDATE STATISTICS и без сортировки:

  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END))
       |--Stream Aggregate(DEFINE:([Expr1009]=COUNT_BIG([T].[Data]), [Expr1010]=SUM([T].[Data])))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([T].[PK]))
                 |--Index Seek(OBJECT:([T].[IRandKey]), SEEK:([T].[RandKey] < (2000000)) ORDERED FORWARD)
                 |--Clustered Index Seek(OBJECT:([T].[PK__T__...]), SEEK:([T].[PK]=[T].[PK]) LOOKUP ORDERED FORWARD)

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

		Execution Time		% Increase
		with Sort	without Sort
Constant	2,000,000(0.2% of rows)	91 seconds	352 seconds	286%
	4,000,000(0.4% of rows)	97 seconds	654 seconds	574%
% Increase	100%	6%	86%

Анализируя эти результаты стоит отметить два момента. Во-первых, план с сортировкой значительно быстрее (в ~6 раз), чем план без сортировки. Этот результат наглядно демонстрирует преимущество последовательного ввода-вывода по сравнению со случайным. Во-вторых, удвоение количества затронутых строк практически не повлияло на время выполнения плана с сортировкой, но почти удвоило время выполнения плана без сортировки. Увеличение операций ввода-вывода в плане с сортировкой незначительно увеличивает затраты, поскольку операции ввода-вывода последовательны. Увеличение операций ввода-вывода в плане без сортировки увеличивает время выполнения пропорционально увеличению количества строк. Если продолжить удваивать значение константы, время выполнения плана с сортировкой будет постепенно увеличиваться, а время выполнения плана без сортировки будет увеличиваться намного сильнее.