Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Оператор Sort  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 902
Привет!
В плане запроса присутствует оператор Sort. Отрабатывает он с предупреждением:"Operator used tempdb to spill data during execution with spill level 1".
Может кто знает что такое spill level 1.

К сообщению приложен файл. Размер - 17Kb
20 ноя 15, 10:34    [18445561]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Yagrus2, Google знает.

Вот, например:

http://www.sqlskills.com/blogs/joe/spilltotempdb-warning-and-spilllevels-mapping-to-single-versus-multiple-pass/
20 ноя 15, 10:42    [18445633]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Yagrus2,

Добрый день. Если отвечать кратко, то SQL Server может использовать разные алгоритмы для сортировки. Самый распространенный из них, это MS вариация алгоритма «External Merge Sort».

Идея алгоритма в том, чтобы уметь сортировать данные, если размер памяти меньше размера данных. Каждый раз заполняя буферы в памяти и выполняя сортировку, вы делаете один проход. Если данные не отсортированы, надо делать еще одну итерацию и т.д. Можете посмотреть, как работает External Merge Sort, например поискав на ютюб ролик "External Merge Sort". Реальный алгоритм сложнее, но базовую идею понять можно.

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

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

Второй интересный факт связан с тем, что даже если оценки количества и размера строк полностью соответствуют действительности, сортировка все равно может иногда сливать данные на диск. Одно из возможных объяснений в том, что сервер пытается соблюдать баланс между количеством задействованной запросом памяти и диском. Это немного странное объяснение как по мне, и видимо в какой-то момент в MS тоже решили это учесть и выпустили cumulative update (CU) в котором ввели изменения алгоритма выделения памяти, включающееся TF 7470 (более подробно - FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct).

Резюмируя, если у вас проблемы с сортировкой, сливающей данные в tempdb:
1) убедитесь что estimated number of rows равно или близко к actual number of rows
2) если это не помогло – рассмотрите вариант установить CU и использовать TF.

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

В 2016 CTP3 сервере в плане для оператора Sort в сообщение о Spill также выводится информация о количестве записанных/прочитанных страниц во время слива и грантах памяти. Также там есть новая подсказка MIN_GRANT_PERCENT, которой можно влиять на количество выделенной памяти (только там в процентах, так что аккуратно, почитайте в документации как она работает). Ну и тот же флаг 7470 там тоже работает.
20 ноя 15, 12:19    [18446534]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SomewhereSomehow,

Спасибо. Не знал про такой фикс от MS.
21 ноя 15, 03:46    [18451612]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить