Способы избежать нежелательных операций с буферами на SQL Server

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

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

Файлы .sqlplan довольно большие (160kb), поэтому я думаю, что, вероятно, нецелесообразно публиковать их непосредственно на форуме.

Итак, вот несколько теорий, которые могут поддаваться конкретным ответам:

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

Ответ 1

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

Однако это не означает, что ваш план выполнения не может быть оптимизирован, в зависимости от того, что вы делаете и насколько волатильны ваши исходные данные. Когда вы делаете SELECT INTO, вы часто увидите элементы буферизации в вашем плане выполнения, и это может быть связано с чтением изоляции. Если это соответствует вашей конкретной ситуации, вы можете попытаться просто снизить уровень изоляции транзакций на что-то менее дорогостоящее и/или использовать подсказку NOLOCK. Я нашел в сложных критически важных критически важных запросах, которые NOLOCK, если они безопасны и подходят для ваших данных, могут значительно увеличить скорость выполнения запроса, даже если нет причин, по которым это должно быть.

В этой ситуации, если вы попробуете READ UNCOMMITTED или подсказку NOLOCK, вы можете устранить некоторые из катушек. (Очевидно, вы не хотите этого делать, если это может привести к посадке вас в непоследовательном состоянии, но все требования к изоляции данных разные). Оператор TOP и оператор OR могут иногда вызывать буферизацию, но я сомневаюсь, что вы делаете какие-либо из процессов ETL...

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

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