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

Откуда:
Сообщений: 233
Здравствуйте!
Есть запрос с group by. План показывает, что самая дорогая операция - sort в конце.
Скажите, пожалуйста, каким образом можно ускорить выполнение?
Надо строить отсортированные индексы в каждой таблице запроса по колонкам из group by?
Если из таблицы в выводе несколько столбцов, то индексы раздельные или составные?

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

К сообщению приложен файл (sort_plan.sqlplan - 117Kb) cкачать
29 янв 14, 12:18    [15487285]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация sort  [new]
Glory
Member

Откуда:
Сообщений: 104751
А такой большой group by потому, что типа "дубликаты" надо убрать ?
29 янв 14, 12:26    [15487359]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация sort  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
Glory, это "честный" group by с целью получить сумму. Дубликатов быть не может, запрос абсолютно корректный.
29 янв 14, 14:08    [15488042]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация sort  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
MyNiGoo
Glory, это "честный" group by с целью получить сумму. Дубликатов быть не может, запрос абсолютно корректный.
Где запрос-то?
29 янв 14, 14:09    [15488049]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация sort  [new]
Glory
Member

Откуда:
Сообщений: 104751
MyNiGoo
Glory, это "честный" group by с целью получить сумму. Дубликатов быть не может, запрос абсолютно корректный.

У вас там в group by 13 полей. А агрегаты считаются из одной таблицы только.
Вы хотите сказать, что нельзя заранее посчитать эти агрегаты, а потом тольок присоединить другие таблицы ?
29 янв 14, 14:15    [15488087]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация sort  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
Во-первых, запрос генерируется приложением.
Во-вторых, я проверил - вынесение подсчета агрегатов в подзапрос плана не меняет.
Понятно, что если засунуть их в темповую таблицу, то эффект будет, но это не выход (см. п.1)
Индексами как-то можно облегчить sort?

запрос во вложении

К сообщению приложен файл (sort_plan.sql - 2Kb) cкачать
29 янв 14, 14:50    [15488355]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация sort  [new]
SomewhereSomehow
Member

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

Запрос действительно работает медленно? Если да, то необходимо установить причину.

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

Обратите внимание на оценки.

Картинка с другого сайта.

Оптимизатор сильно недооценил число строк. Оператор Sort требует выделения определенного количества памяти, для сортировки, это количество зависит в том числе и от предполагаемого числа строк. Если предполагаемое число строк меньше чем реальное, то выделенной памяти может не хватить, в таком случае сервер выполняет слив данных в tempdb (spill to tempdb). Это может очень сильно замедлять запрос. данном случае, было выделено 74 МБ памяти, при реальном объеме 238 МБ.

Картинка с другого сайта.

Вполне возможно, что запрос выполняется медленно как раз из-за того, что выполняется слив данных на диск. Кроме того, в плане присутствует 5 hash соединений. Оператор Hash Match также требует памяти как и оператор Sort, и также умеет сливать данные в tempdb. И поскольку оптимизатор ошибается в оценке в самом нижнем операторе - все последующие Hash соединения также могут страдать от нехватки памяти, как и сортировка.Нужно это проверить.

Для этого, открываете Profiler, выбираете пустой шаблон, выбираете события "Errors and Warnings : Hash Warning", "Errors and Warnings : Sort Warnings". Ставите фильтр по SPID, указывая SPID того окна в котором будете выполнять запрос (select @@spid в SSMS). Стартуете сессию трассировки в профайлере, выполняете запро, смотрите, есть ли предупреждения и какие.

Если они есть, то нужно постараться исправить оценки.

Возможные решения

В первую очередь, необходимо обновить всю статистику на таблицах dbo.OneC_D_ChartOfAccountsHierarchy и dbo.OneC_F_AccountingEntries.

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

1. Создать многоколоночную статистику (create statistics . У вас проверяется на равенство, в этом случае, многоколоночная статистика может помочь.
create statistics s_OrganizationActive on dbo.OneC_F_AccountingEntrie(Organization, Active);

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

3. Использовать TF 4137 http://support.microsoft.com/kb/2658214. Который исправляет ситуацию с коррелированными предикатами.

4. Разбитьзапрос на части, через промежуточные таблицы SQL Server Customer Advisory Team - When To Break Down Complex Queries. http://blogs.msdn.com/b/sqlcat/archive/2013/09/09/when-to-break-down-complex-queries.aspx

Сортировка

Что касательно самой сортировки, то она нужна здесь для оператора агрегации Stream Aggregate. Можете попробовать указать хинт option(hash group). Тогда сортировка должна исчезнуть, но это возможно не поможет, поскольку, как я сказал выше операция Hash Match также требует памяти, а хинт не исправляет оценки. Может быть spill в сортировке, заменится на spill в хэше, но алгоритмы все-таки разные, так что там где сортировка сливает данные на диск, хэш может и не сливать и наоборот. Короче, попробовать можно, но корень зла, скорее всего, в плохих оценках, а не в сортировке как таковой.

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

П.С.
Не имеет отношения к скорости, просто совет.
Если запрос пишете вы, а не генерирует приложение, то ради бога, используйте альясы и не ставьте лишних скобок =)
29 янв 14, 14:52    [15488381]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация sort  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
большое спасибо всем за советы, добился нужного результата. Пересмотрел where и изыскал возможность сократить количество условий, избавившись в том числе от коррелированных предикатов
30 янв 14, 11:37    [15493306]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить