Index spool как избавиться

Основные операции плана выполнения SQL Server

Данная статья представляет собой описание основных операций, отображаемых в планах выполнения запросов СУБД MS SQL Server.

Index Seek

Поиск по некластеризованному индексу. В большинстве случаев является хорошим для производительности, так как представляет собой прямой доступ SQL Server к требуемым строкам данных. Однако это вовсе не означает, что он всегда работает быстро, например, если он возвращает большое число строк, то по производительности он будет практически равен Index Scan.

Index Scan

Сканирование некластеризованного индекса. Обычно наличие этой операции плохо отражается на производительности, поскольку она предполагает последовательное чтение индекса для извлечения большого числа строк, приводя к более медленной обработке. Но бывают исключения, например, применение директивы TOP, ограничивающей число возвращаемых записей; если возвращать всего несколько строк, то операция сканирования будет выполняться достаточно быстро, и вы не сможете получить лучшую производительность, чем ту, которую уже имеете, даже если вы попытаетесь перестроить запрос/индексы, чтобы добиться операции Index Seek.

RID Lookup

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

Key Lookup

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

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

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

Простой способ избежать оператора сортировки – иметь данные, хранящиеся в предварительно упорядоченном виде. Это может быть выполнено созданием индекса с ключевыми столбцами, перечисленными в том же самом порядке, который использует оператор сортировки.

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

Spool

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

SQL Server часто использует спул для обработки сложных запросов, преобразуя данные во временную таблицу в базе tempdb для использования её данных в последующих операциях. Побочным эффектом здесь является необходимость записи данных на диск.

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

Merge Join

Соединение слиянием. Редко встречаются в реальных запросах, как правило, являются наиболее эффективными из операторов логического соединения.

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

Операция неприменима, если входные данные не отсортированы.

Nested Loops Join

Соединение вложенными циклами. Встречаются очень часто. Выполняют довольно эффективное соединение относительно небольших наборов данных.

Соединение вложенными циклами не требует сортировки входных данных. Однако производительность можно улучшить при помощи сортировки источника входных данных; SQL Server сможет выбрать более эффективный оператор, если оба входа отсортированы.

Операция неприменима, если данные слишком велики для хранения в памяти.

Hash Match Join

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

1. Соединяемые наборы данных настолько велики, что они могут быть обработаны только с помощью Hash Match Join.
2. Наборы данных не упорядочены по столбцам соединения, и SQL Server думает, что вычисление хэшей и цикл по ним будет быстрей, чем сортировка данных.

При первом сценарии трудно оптимизировать выполнение запроса, если только не найти способа соединять меньшие объемы данных.

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

Операторы Hash Match Join достаточно эффективны тогда, когда не сбрасывают данные в tempdb.

Parallelism

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

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

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

Stream Aggregate

Статистическое выражение потока. Группирует строки в один или несколько столбцов и вычисляет одно или несколько агрегатных выражений (пример: COUNT, MIN, MAX, SUM и AVG), возвращенных запросом. Выход этого оператора может быть использован последующими операторами запроса, возвращен клиенту или то и другое. Оператору Stream Aggregate необходимы входные данные, упорядоченные по группируемым столбцам. Оптимизатор использует перед этим оператором оператор Sort, если данные не были ранее отсортированы оператором Sort или используется упорядоченный поиск или просмотр в индексе.

Compute Scalar

Оператор Compute Scalar вычисляет выражение и выдает вычисляемую скалярную величину. Затем эту величину можно вернуть пользователю или сослаться на нее в каком-либо запросе, а также выполнить эти действия одновременно. Примерами одновременного использования этих возможностей являются предикаты фильтра или соединения. Всегда возвращает одну строку. Часто применяется для того, чтобы конвертировать результат Stream Aggregate в ожидаемый на выходе тип int (когда Stream Aggregate возвращает bigint в случае с COUNT, AVG при типах столбцов int).

Concatenation

Оператор просматривает несколько входов, возвращая каждую просмотренную строку. Используется в запросах с UNION ALL. Копирует строки из первого входного потока в выходной поток и повторяет эту операцию для каждого дополнительного входного потока.

Filter

Оператор просматривает входные данные и возвращает только те строки, которые удовлетворяют критерию фильтрации (предикату).

Источник

Ways to avoid eager spool operations on SQL Server

I have an ETL process that involves a stored procedure that makes heavy use of SELECT INTO statements (minimally logged and therefore faster as they generate less log traffic). Of the batch of work that takes place in one particular stored the stored procedure several of the most expensive operations are eager spools that appear to just buffer the query results and then copy them into the table just being made.

The MSDN documentation on eager spools is quite sparse. Does anyone have a deeper insight into whether these are really necessary (and under what circumstances)? I have a few theories that may or may not make sense, but no success in eliminating these from the queries.

The .sqlplan files are quite large (160kb) so I guess it’s probably not reasonable to post them directly to a forum.

So, here are some theories that may be amenable to specific answers:

• The query uses some UDFs for data transformation, such as parsing formatted dates. Does this data transformation necessitate the use of eager spools to allocate sensible types (e.g. varchar lengths) to the table before it constructs it?
• As an extension of the question above, does anyone have a deeper view of what does or does not drive this operation in a query?

1 Answer 1

My understanding of spooling is that it’s a bit of a red herring on your execution plan. Yes, it accounts for a lot of your query cost, but it’s actually an optimization that SQL Server undertakes automatically so that it can avoid costly rescanning. If you were to avoid spooling, the cost of the execution tree it sits on will go up and almost certainly the cost of the whole query would increase. I don’t have any particular insight into what in particular might cause the database’s query optimizer to parse the execution that way, especially without seeing the SQL code, but you’re probably better off trusting its behavior.

However, that doesn’t mean your execution plan can’t be optimized, depending on exactly what you’re up to and how volatile your source data is. When you’re doing a SELECT INTO , you’ll often see spooling items on your execution plan, and it can be related to read isolation. If it’s appropriate for your particular situation, you might try just lowering the transaction isolation level to something less costly, and/or using the NOLOCK hint. I’ve found in complicated performance-critical queries that NOLOCK , if safe and appropriate for your data, can vastly increase the speed of query execution even when there doesn’t seem to be any reason it should.

In this situation, if you try READ UNCOMMITTED or the NOLOCK hint, you may be able to eliminate some of the Spools. (Obviously you don’t want to do this if it’s likely to land you in an inconsistent state, but everyone’s data isolation requirements are different). The TOP operator and the OR operator can occasionally cause spooling, but I doubt you’re doing any of those in an ETL process.

You’re right in saying that your UDFs could also be the culprit. If you’re only using each UDF once, it would be an interesting experiment to try putting them inline to see if you get a large performance benefit. (And if you can’t figure out a way to write them inline with the query, that’s probably why they might be causing spooling).

One last thing I would look at is that, if you’re doing any joins that can be re-ordered, try using a hint to force the join order to happen in what you know to be the most selective order. That’s a bit of a reach but it doesn’t hurt to try it if you’re already stuck optimizing.

Источник

Index spool как избавиться

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

Thanks for your time and expertise.

My question is about Table Spool operator

I have spent numerous hours reading all suggested topics, including topics when suggested during creation of this post.

The reason for Table Spool operator in query plan seems to be NOT an exact science but it’s in the realm of magic.

I have a simple reproduction code to demonstrate an issue.

if you add Performance: Show Plan XML in profiler, then you will see something like this

I have appropriate indexes and queries are really simple.

I do not understand why this operator is there and is there any way to rid of it? I really would benefit from 44% gain.

Answers

There is an answer to this by Itzik Ben-Gan in SQL Server Pro magazine.

Article name: Divide and Conquer Halloween Avoid the overhead of Halloween protection

It is possible to avoid it with two table solution. I tested it but in my case it does not gain me any speed in my real database processing.

Thank you for all your help.

All replies

Maybe this operator is there because you have to remember the information between iterations done in your function?

Maybe CTE would be better that manipulating the @table variable in the way you did it?

You may try with SCHEMABINDING in your funtion. This can reduce the Tablespool.

This is where table @ oriopr comes to play, to remember iteration results.

Recursive CTE on large data sets performs really poor compared to WHILE iteration. I tried it too. it also has spool operator, as it has implied temp table. With CTE I can at least understand why it’s needed. With my function, I have no idea as my temp storage is explicitly declared by me and it’s @ oriopr temp table variable.

I supplied full reproduction code. why don’t you try, see if you can remove spool operator?

That is why I am saying it voodoo science with this operator. Many suggestions but nothing works for me.

Also, I need temp @ oriopr table as I do further processing with it.

Can you try with ITVF(inline TVF)? You need to tweak your code a bit to make it Inline TVF.

For testing ourpose, you may try commenting your WHILE and confirm.

There are two queries involved. The first one is producing this plan, which is about what inline table valued function would be. And the first INSERT @ oriopr does not have spool in it.

the second statement INSERT @ oriopr produces table spool.

ITVF is not enough for me here. I need multi statement table valued function, as I have other code following this recursion.

the actual function is a lot more complex. I am just giving you narrowed down definition of this «spool» issue.

You can try my code that I posted above; see for yourself.

I don’t think there is any reasonable way you’re going to get rid of this eager spool.

The reason (as I understand it) that the optimizer is using an eager spool in this case is that you are updating a table and using data in that table as a source for the update. This can lead to the «Halloween problem» which essentially is an error where SQL updates or inserts a row and then (since the source of the update or insert is from the same table that is being updated or inserted) uses this new or updated row to do additional updates or inserts. That would lead to invalid results. The original Halloween problem was a query meant to give every employee who make less than $25,000 a 10% raise. Essentially UPDATE EMPLOYEES SET Salary = Salary * 1.1 WHERE Salary = 25,000.

SQL Server uses Eager Spools to prevent this problem when the optimizer determines it might exist. Essentially the Eager Spool forces everything in the query plan prior to the Eager Spool to be completely finished before going on to the next step. So in this case, the Eager Spool forces the read of the @oriopr table to be completed before any new rows are inserted into the @oriopr table.

You can get rid of the Eager Spool step by rewriting the function so the query so that it does not both read from and update the same table in the same query. For example you could do

So now you insert the new rows into a different table variable in one command and then use that table variable to insert into the @oriopr table. But if you sum the total costs of the two inserts in this function, you will find they total to essentially the same cost as the one insert in your original function which has the Eager Spool. Effectively, you would have written your own Eager Spool.

Источник