План выполнения sql-запроса. интерпретация основных операций. Как получить план выполнения запросов? Вот небольшая вырезка из неё

SQL Server .

Планы запросов

Когда сервер SQL Server выполняет запрос , сначала требуется определить наилучший способ выполнения. Для этого нужно рассчитать, как и в каком порядке обращаться к данным и соединять их, как и когда выполнять вычисления и агрегации и т. д. За это отвечает подсистема, которая называется Query Optimizer ( Оптимизатор запроса ). Оптимизатор запроса использует статистические данные о распределении данных, метаданные , относящиеся к объектам в базе данных, информацию индекса и другие факторы для вычисления нескольких возможных планов выполнения запроса. Для каждого из этих планов Оптимизатор запроса предполагает его стоимость на основе статистики по этим данным и выбирает план с минимальными затратами ресурсов на выполнение. Конечно, SQL Server не вычисляет всех возможных планов для каждого запроса, поскольку для некоторых запросов сами эти вычисления могут отнять больше времени, чем выполнение наименее эффективного из всех планов. Следовательно, SQL Server использует сложные алгоритмы, чтобы найти план выполнения с разумной стоимостью, близкой к минимально возможной. После того, как план выполнения сгенерирован, он хранится в буферном кэше (на что SQL Server выделяет большую часть своей виртуальной памяти). Затем план выполняется тем способом, который Оптимизатор запроса сообщает ядру базы данных (компоненту database engine ).

Примечание . Планы выполнения в буферном кэше могут быть повторно использованы при выполнении такого же или аналогичного запроса. Следовательно, планы выполнения хранятся в кэше максимально возможное время. Дополнительную информацию о кэшировании планов выполнения см. в официальном документе под названием: "Batch Compilation, Recompilation , and Plan Caching Issues in SQL Server 2005" (Проблемы компиляции и рекомпиляции пакетов, а также кэширования планов в SQL Server 2005) на странице http://www.microsoft.com/ technet/prodtechnol/sql/2005/recomp.mspx .

Сможет ли Query Optimizer ( Оптимизатор запросов ) сгенерировать эффективный план для конкретного запроса, зависит от следующих аспектов:

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

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

Примечание . Конечно, возможно, что неэффективно выполненный запрос выполнялся в соответствии с хорошим планом. В этих случаях дело не в оптимизации запроса . Скорее всего, проблема кроется совсем в другом, например, в проекте запроса, конфликте доступа к данным, операций ввода/вывода, памяти, использования ЦПУ, сетевых ресурсов и т. п. Чтобы получить дополнительную информацию по этим проблемам, рекомендуем ознакомиться с официальным документом " Troubleshooting Performance Problems in SQL Server 2005" (Поиск и решение проблем с производительностью в SQL Server 2005), который доступен по следующей ссылке: http://www.microsoft.com/ technet/prodtechnol/sql/2005/tsprfprb.mspx .

Знакомимся с планами выполнения запросов

1. В меню Start (Пуск) выберите All Programs,. Microsoft SQL Server 2005, SQL Server Management Studio (Все программы, Microsoft SQL Server 2005, Среда SQL Server Management Studio). Нажмите кнопку New Query (Создать запрос), чтобы открыть окно нового запроса, и измените контекст выполнения на базу данных Adventure Works , выбрав ее из раскрывающегося списка Available Databases (Доступные базы данных).
2. Выполните следующую инструкцию SELECT . Код этого примера имеется в файлах примеров под именем Viewing Query Plans .sql .
3. Чтобы вывести на экран план выполнения для этого запроса, нажмите комбинацию клавиш (Ctrl+L) или выберите из меню Query (Запрос) команду Display Estimated Execution Plan (Показать предполагаемый план выполнения). План выполнения показан на следующем рисунке.

   При генерировании предполагаемого плана запроса запрос на самом деле не выполняется. Он только оптимизируется Оптимизатором запроса. Эта особенность Оптимизатора запросов является преимуществом, когда приходится иметь дело с запросами, которые имеют продолжительные рабочие циклы , ведь для того, чтобы увидеть план выполнения запроса, нет необходимости выполнять сам запрос. Графическое представление плана выполнения запроса читается справа налево и сверху вниз. Каждый значок в плане представляет один оператор, а данные, изменяемые между этими операторами, обозначены стрелками. Толщина стрелок соответствует объему данных, которые передаются между операторами. Мы не будем углубляться в подробности и объяснять значение каждого оператора; расскажем только о тех из них, которые показаны в данном плане выполнения запроса.

   • SQL Server обращается к данным при помощи операции Clustered Index Scan (Просмотр кластеризованного индекса ). Это сканирование представляет собой реальную операцию доступа к данным и подробно рассматривается далее.
   • Данные переходят к оператору Sort (Сортировка), который сортирует данные на основе предложения ORDER BY .
   • Данные пересылаются клиенту.

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

   Стоимость в процентах под пиктограммой каждого оператора показывает процент от общей стоимости запроса, представленного на графической схеме. Это число поможет вам понять, какая операция использует при выполнении больше всего ресурсов. В нашем случае самой дорогостоящей операцией является Clustered Index Scan (Просмотр, а также поиск ProductID 712 . Эта информация находится в секции Predicates (Предикаты). Кроме того, показаны предполагаемая стоимость и предполагаемое количество строк, а также размер строки. В то время, как количество строк оценивается на основе статистики, которую SQL Server хранит для этой таблицы, значения стоимости вычисляются на основе статистики и значений эталонной системы. Следовательно, значения стоимости не следует использовать для того, чтобы рассчитать, сколько времени запрос будет выполняться на компьютере. Эти цифры могут использоваться только для выявления более дешевой или более дорогостоящей операции.

4. Эту информацию об операторах можно увидеть также в окне Properties (Свойства) в SQL Server Management Studio. Чтобы открыть окно Properties (Свойства), щелкните правой кнопкой мыши на значке оператора и выберите из контекстного меню команду Properties (Свойства).
5. Планы запросов можно также сохранить. Чтобы сохранить план запроса, щелкните в панели плана правой кнопкой мыши и выберите из контекстного меню команду Save Execution Plan As (Сохранить план выполнения как). План сохраняется в формате XML с расширением .sqlplan . Его можно открыть через SQL Server Management Studio. выбрав из меню File (Файл) команды Open, File (Открыть, Файл).
6. То, что вы видели до сих пор - это предполагаемый план выполнения запроса, но можно просмотреть и действительный план выполнения. Действительный план выполнения аналогичен предполагаемому плану выполнения, но включает также действительные (не предполагаемые) значения количества строк, количества перемоток и т. д. Чтобы включить в запрос действительный план выполнения, нажмите (Ctrl+M) или выберите из меню Query (Запрос) команду Include Actual Execution Plan (Включить действительный план выполнения). Затем нажмите F5 и выполните запрос. Результаты запроса отображаются как обычно, но вы увидите также план выполнения, который показан на вкладке Execution Plan (План выполнения).

Всем привет! Столкнулся тут недавно я с проблемой долгого проведения документа.

Вводные данные: конфигурация «Управление производственным предприятием, редакция 1.3 (1.3.52.1)», документ «Платежное поручение входящее». Жалоба: проведение в рабочей базе длится 20-30 секунд, что интересно в копии базы этот же документ проводится 2-4 секунды. О расследованиях и причине такого поведения читайте ниже.

Итак, с помощью замера производительности , как его использовать думаю, все знают, было найдено место-виновник:

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

По данным замера производительности эта операция занимала 13 секунд, если посчитать среднее, то получается 0,5 секунды на регистр, вечность просто!

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

• Showplan Statistics Profile
• Showplan XML Statistics Profile
• RPC Completed
• SQL:BatchCompleted .

В настройках трассировки есть фильтр по SPID :

SPID — это идентификатор процесса сервера баз данных. В случае с 1С по сути соединение между сервером 1С и СУБД, посмотреть его можно в консоли администрирования серверов 1С в колонке «Соединение с СУБД».

Отображается в том случае, если в данный момент соединение с базой данных захвачено сеансом: либо выполняется вызов СУБД, либо открыта транзакция, либо удерживается объект «МенеджерВременныхТаблиц», в котором создана хотя бы одна временная таблица.

Напишем обработку для удержания SPID, в ней будет одна процедура:

Важно, чтобы удерживаемый объект соединения, в нашем случае менеджер временных таблиц, был определен как переменная обработки. Открываем обработку, запускаем процедуру и до тех пор, пока она открыта, SPID будет зафиксирован. Открываем консоль администрирования серверов 1С:

Итак, SPID получен, вводим его значение в фильтр и получаем трассировку из рабочей базы ток по своему сеансу. При анализе трассировки была найдена операция, которая выполнялась 11 секунд:

Также в глаза бросилось количество чтений — 1872578 , но я сразу не придал этому значения и начал разбирать, что и с какой таблицей тут делается.

exec sp_executesql <= @P2) AND (T1._Fld1466RRef = @P3)) OR ((T1._Period <= @P4) AND (T1._Fld1466RRef = @P5))) OR ((T1._Period <= @P6) AND (1=0)))’,N’@P1 varbinary(16),@P2 datetime2(3),@P3 varbinary(16),@P4 datetime2(3),@P5 varbinary(16),@P6 datetime2(3)’,0x8A2F00155DBF491211E87F56DD1A416E,’4018-05-31 23:59:59′,0x00000000000000000000000000000000,’4018-05-31 23:59:59′,0x9A95A0369F30F8DB11E46684B4F0A05F,’4018-05-31 23:59:59"

Как видно по запросу SQL обрабатывается таблица «AccRg1465» это таблица регистра бухгалтерии Хозрасчетный. Текстовое представление плана выполнение запроса:

Как видно по плану выполнения запросу SQL ничего страшного не происходит, обрабатывается таблица «AccRg1465 », везде используется поиск по кластерному индексу.

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

Как было сказано выше, в свежей копии базы проблема не воспроизводилась, копия была снята с рабочей базы после появления в ней проблемы, поэтому было решено проанализировать ее поведение в SQL Server Profiler на том же документе.
Вот результаты:

Текст запроса в SQL:

EXEC sp_executesql N"SELECT TOP 1 0x01 FROM dbo._AccRg1465 T1 WHERE (T1._RecorderTRef = 0x0000022D AND T1._RecorderRRef = @P1) AND ((((T1._Period <= @P2) AND (T1._Fld1466RRef = @P3)) OR ((T1._Period <= @P4) AND (T1._Fld1466RRef = @P5))) OR ((T1._Period <= @P6) AND (1=0)))" , N"@P1 varbinary(16),@P2 datetime2(3),@P3 varbinary(16),@P4 datetime2(3),@P5 varbinary(16),@P6 datetime2(3)" , 0x8A2F00155DBF491211E87F56DD1A416E, "4018-05-31 23:59:59" ,00, "4018-05-31 23:59:59" , 0x9A95A0369F30F8DB11E46684B4F0A05F, "4018-05-31 23:59:59"

Графическое представление плана запроса:

Тексты запроса совпадают, планы выполнения отличаются кардинально. В чем же может быть дело? Грешил на статистику в SQl, но она одинаковая между рабочей и копией базы, а статистика хранится в базе для каждой таблицы:

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

DBCC FLUSHPROCINDB(< database_id >)

где < database_id > — это идентификатор базы. Чтобы узнать идентификатор базы нужно выполнить скрипт

select name, database_id from sys . databases

он вернет нам список баз и их идентификаторы.

Получаем опять трассировку:

Текстовое представление плана запроса:

Графическое представление плана запроса:

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

Спасибо за внимание!

Помогла ли вам данная статья?

Оптимизация запросов в SQL Server 2005, статистика баз данных SQL Server 2005, CREATE STATISTICS, UPDATE STATISTICS, SET NOCOUNT ON, планы выполнения запросов, количество логических чтений (logical reads), хинты оптимизатора (optimizer hints), MAXDOP, OPTIMIZE FOR, руководства по планам выполнения (plan guides), sp_create_plan_guide

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

Отношение к оптимизации запросов у многих специалистов неоднозначное. С одной стороны, работа программного модуля Query Optimizer , который генерирует планы выполнения запросов, вызывает множество справедливых нареканий и в SQL Server 2000, и в SQL Server 2005. Query Optimizer часто выбирает не самые оптимальные планы выполнения запросов и в некоторых ситуациях проигрывает аналогичным модулям из Oracle и Informix . С другой стороны, ручная оптимизация запросов - процесс чрезвычайно трудоемкий. Вы можете потратить много времени на такую оптимизацию и, в конце концов, выяснить, что ничего оптимизировать не удалось: план, предложенный Query Optimizer изначально, оказался наиболее оптимальным (так бывает в большинстве случаев). Кроме того, может случиться так, что созданный вами вручную план выполнения запросов через какое-то время (после добавления новой информации в базу данных) окажется неоптимальным и будет снижать производительность при выполнении запросов.

Отметим также, что для выбора наилучших планов построения запросов Query Optimizer необходима правильная информация о статистике. Поскольку, по опыту автора, далеко не все администраторы знают, что это такое, расскажем о статистике подробнее.

Статистика - это специальная служебная информация о распределении данных в столбцах таблиц. Представим, например, что выполняется запрос, который должен вернуть всех Ивановых, проживающих в городе Санкт-Петербурге. Предположим, что у 90% записей в этой таблице одно и то же значение в столбце Город - "Санкт-Петербург" . Конечно, с точки зрения выполнения запроса вначале выгоднее выбрать в таблице всех Ивановых (их явно будет не 90%), а затем уже проверять значение столбца Город для каждой отобранной записи. Однако для того, чтобы узнать, как распределяются значения в столбце, нужно вначале выполнить запрос. Поэтому SQL Server самостоятельно инициирует выполнение таких запросов, а потом сохраняет информацию о распределении данных (которая и называется статистикой) в служебных таблицах базы данных.

Для баз данных SQL Server 2005 по умолчанию устанавливаются параметры AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS . При этом статистика для столбцов баз данных будет создаваться и обновляться автоматически. Для самых больших и важных баз данных может получиться так, что операции по созданию и обновлению статистики могут мешать текущей работе пользователей. Поэтому для таких баз данных иногда эти параметры отключают, а операции по созданию и обновлению статистики выполняют вручную в ночное время. Для этого используются команды CREATE STATISTICS и UPDATE STATISTICS .

Теперь поговорим об оптимизации запросов.

Первое, что необходимо сделать, - найти те запросы, которые в первую очередь подлежат оптимизации. Проще всего это сделать при помощи профилировщика, установив фильтр на время выполнения запроса (фильтр Duration в окне Edit Filter (Редактировать фильтр), которое можно открыть при помощи кнопки Column Filters на вкладке Events Selection окна свойств сеанса трассировки). Например, в число кандидатов на оптимизацию могут попасть запросы, время выполнения которых составило более 5секунд. Кроме того, можно использовать информацию о запросах, которая предоставляется Database Tuning Advisor .

Затем нужно проверить, устанавлен ли для ваших соединений, хранимых процедур и функций параметр NOCOUNT . Установить его можно при помощи команды SET NOCOUNT ON . При установке этого параметра, во-первых, отключается возврат с сервера и вывод информации о количестве строк в результатах запроса (т. е. не отображается строка "N row(s) affected" на вкладке Messages (C ообщения) окна работы с кодом при выполнении запроса в Management Studio ). Во-вторых, отключается передача специального серверного сообщения DONE_IN_PROC , которое по умолчанию возвращается для каждого этапа хранимой процедуры. При вызове большинства хранимых процедур нужен только результат их выполнения, а количество обработанных строк для каждого этапа никого не интересует. Поэтому установка параметра NOCOUNT для хранимых процедур может серьезно повысить их производительность. Повышается скорость выполнения и обычных запросов, но в меньшей степени (до 10%).

После этого можно приступать к работе с планами выполнения запросов.

План выполнения запроса проще всего просмотреть из SQL Server Management Studio . Для того чтобы получить информацию об ожидаемом плане выполнения запроса, можно в меню Query (Запрос) выбрать команду Display Estimated Execution Plan (Отобразить ожидаемый план выполнения). Если вы хотите узнать реальный план выполнения запроса, можно перед его выполнением установить в том же меню параметр Include Actual Execution Plan (Включить реальный план выполнения). В этом случае после выполнения запроса в окне результатов в SQL Server Management Studio появится еще одна вкладка Execution Plan (План выполнения), на которой будет представлен реальный план выполнения запроса. При наведении указателя мыши на любой из этапов можно получить о нем дополнительную информацию (рис. 11.15).

Рис. 11.15. План выполнения запроса в SQL Server Management Studio

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

q вначале в окне Management Studio выполните команду SET STATISTICS IO ON . В результате после каждого выполнения запроса будет выводиться дополнительная информация. В ней нас интересует значение только одного параметра - Logical Reads . Этот параметр означает количество логических чтений при выполнении запросов, т. е. сколько операций чтения пришлось провести при выполнении данного запроса без учета влияния кэша (количество чтений и из кэша, и с диска). Это наиболее важный параметр. Количество физических чтений (чтений только с диска) - информация не очень представительная, поскольку зависит от того, были ли перед этим обращения к данным таблицам или нет. Статистика по времени также является величиной переменной и зависит от других операций, которые выполняет в это время сервер. А вот количество логических чтений - наиболее объективный показатель, на который в наименьшей степени влияют дополнительные факторы;

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

Хинтов оптимизатора в SQL Server 2005 предусмотрено много. Прочитать информацию о них можно в Books Online (в списке на вкладке Index (Индекс) нужно выбрать Query Hints [ SQL Server ] (Хинты запросов ), Join Hints (Хинты джойнов) или Table Hints [ SQL Server ] (Табличные хинты )). Чаще всего используются следующие хинты:

q NOLOCK , ROWLOCK , PAGLOCK , TABLOCK , HOLDLOCK , READCOMMITTEDLOCK , UPDLOCK , XLOCK - эти хинты используются для управления блокировками (см. разд. 11.5.7) ;

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

q FORCE ORDER - объединение таблиц при выполнении запроса будет выполнено точно в том порядке, в котором эти таблицы перечислены в запросе;

q MAXDOP (от Maximum Degree of Parallelism - максимальная степень распараллеливания запроса) - при помощи этого хинта указывается максимальное количество процессоров, которые можно будет использовать для выполнения запроса. Обычно этот хинт используется в двух ситуациях:

· когда из-за переключения между процессорами (context switching ) скорость выполнения запроса сильно снижается. Такое поведение было характерно для SQL Server 2000 на многопроцессорных системах;

· когда вы хотите, чтобы какой-то тяжелый запрос оказал минимальное влияние на текущую работу пользователей;

q OPTIMIZE FOR - этот хинт позволяет указать, что запрос оптимизируется под конкретное значение передаваемого ему параметра (например, под значение фильтра для WHERE );

q USE PLAN - это самая мощная возможность. При помощи такого хинта можно явно определить план выполнения запроса, передав план в виде строкового значения в формате XML . Хинт USE PLAN появился только в SQL Server 2005 (в предыдущих версиях была возможность явно определять планы выполнения запросов, но для этого использовались другие средства). План в формате XML можно написать вручную, а можно сгенерировать автоматически (например, щелкнув правой кнопкой мыши по графическому экрану с планом выполнения, представленному на рис. 11.15, и выбрав в контекстном меню команду Save Execution Plan As (Сохранить план выполнения как)).

В SQL Server 2005 появилась новая важная возможность, которая позволяет вручную менять план выполнения запроса без необходимости вмешиваться в текст запроса. Очень часто бывает так, что код запроса нельзя изменить: он жестко "прошит" в коде откомпилированного приложения. Чтобы справиться с этой проблемой, в SQL Server 2005 появилась хранимая процедура sp_create_plan_guide . Она позволяет создавать так называемые руководства по планам выполнения (plan guides ), которые будут автоматически применяться к соответствующим запросам.

Если вы анализируете запросы, которые направляет к базе данных какое-то приложение, то имеет смысл в первую очередь обратить внимание на следующие моменты:

q насколько часто в планах выполнения запроса встречается операция Table Scan (Полное сканирование таблицы). Вполне может оказаться, что обращение к таблице при помощи индексов будет эффективнее;

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

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

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

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

q если приложение передает на сервер команды EXECUTE , то имеет смысл подумать о том, чтобы заменить их на вызов хранимой процедуры sp_executesql . Она обладает преимуществами в производительности по сравнению с обычной командой EXECUTE ;

q повышения производительности иногда можно добиться, устранив необходимость повторной компиляции хранимых процедур и построения новых планов выполнения запросов. Нужно обратить внимание на применение параметров, постараться не смешивать в коде хранимой процедуры команды DML и DDL и следить за тем, чтобы параметры подключения SET ANSI_DEFAULTS , SET ANSI_NULLS , SET ANSI_PADDING , SET ANSI_WARNINGS и SET CONCAT_NULL_YIELDS_NULL не изменялись между запросами (любое изменение таких параметров приводит к тому, что старые планы выполнения считаются недействительными). Обычно проблема может возникнуть тогда, когда эти параметры устанавливаются на уровне отдельного запроса или в коде хранимой процедуры.

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

История стара как мир. Две таблицы:

• Cities – 100 уникальных городов.
• People – 10 млн. людей. У некоторых людей город может быть не указан.

Распределение людей по городам – равномерное.
Индексы на поля Cites.Id, Cites.Name, People .CityId – в наличии.

Нужно выбрать первых 100 записей People, отсортированных по Cites.

Засучив рукава, бодро пишем:

Select top 100 p.Name, c.Name as City from People p
order by c.Name

При этом мы получим что-то вроде:

За… 6 секунд. (MS SQL 2008 R2, i5 / 4Gb)

Но как же так! Откуда 6 секунд?! Мы ведь знаем, что в первых 100 записях будет исключительно Алматы! Ведь записей – 10 миллионов, и значит на город приходится по 100 тыс. Даже если это и не так, мы ведь можем выбрать первый город в списке, и проверить, наберется ли у него хотя бы 100 жителей.

Почему SQL сервер, обладая статистикой, не делает так:

Select * from People p
left join Cities c on c.Id=p.CityId
where p.CityId
in (select top 1 id from Cities order by Name)
order by c.

Данный запрос возвращает примерно 100 тыс. записей менее чем за секунду! Убедились, что есть искомые 100 записей и отдали их очень-очень быстро.

Однако MSSQL делает все по плану. А план у него, «чистый термояд» (с).

Вопрос к знатокам:
каким образом необходимо исправить SQL запрос или сделать какие-то действия над сервером, чтобы получить по первому запросу результат в 10 раз быстрее?

P.S.
CREATE TABLE . (


uniqueidentifier
ON
GO

CREATE TABLE . (
uniqueidentifier NOT NULL,
nvarchar(50) NOT NULL,
ON
GO

P.P.S
Откуда растут ноги:
Задача вполне реальная. Есть таблица с основной сущностью, от нее по принципу «звезда» отходит множество измерений. Пользователю нужно ее отобразить в гриде, предоставив сортировку по полям.
Начиная с некоторого размера основной таблицы сортировка сводится к тому, что выбирается окно с одинаковыми (крайними) значениями, (вроде «Алматы») но при этом система начинает жутко тормозить.
Хочется иметь ОДИН параметризированный запрос, который будет эффективно работать как с малым размером таблицы People так и с большим.

P.P.P.S
Интересно, что если бы City были бы NotNull и использовался InnerJoin то запрос выполняется мгновенно.
Интересно, что ДАЖЕ ЕСЛИ поле City было бы NotNull но использовался LeftJoin – то запрос тормозит.

В комментах идея: Сперва выбрать все InnerJoin а потом Union по Null значениям. Завтра проверю эту и остальные безумные идеи)

P.P.P.P.S Попробовал. Сработало!

WITH Help AS
select top 100 p.Name, c.Name as City from People p
INNER join Cities c on c.Id=p.CityId
order by c.Name ASC
UNION
select top 100 p.Name, NULL as City from People p
WHERE p.CityId IS NULL
SELECT TOP 100 * FROM help

Дает 150 миллисекунд при тех же условиях! Спасибо

Александр Куклин написал отличную статью «Кэш планов и параметризация запросов. Часть 1. Анализ кэша планов «. Всем рекомендую к ознакомлению.

Вот небольшая вырезка из неё:

Процессор запросов (query processor), который и занимается выполнением SQL-запросов, поступивших на SQL-сервер, и выдачей их результатов клиенту, состоит из двух основных компонентов:

1. Оптимизатор запросов (Query Optimizer).
2. Исполнитель запросов (Relational Engine).

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

1. Синтаксический анализатор (Parser) просматривает инструкцию SELECT и разбивает ее на логические единицы, такие как ключевые слова, выражения, операторы и идентификаторы, а также производит нормализацию запроса.
2. Из синтаксического анализатора данные попадают на вход компонента Algebrizer, который выполняет семантический анализ текста. Algebrizer проверяет существование указанных в запросе объектов базы данных и их полей, корректность использования операторов и выражений запроса, и извлекает из кода запроса литералы, для обеспечения возможности использования автоматической параметризации.
   Например, именно поэтому запрос, имеющий в секции SELECT поля, не содержащиеся ни в агрегатных функциях, ни в секции GROUP BY, пройдёт в SQL Server Management Studio (SSMS) проверку по Ctrl+F5 (синтаксический анализ), но свалится с ошибкой при попытке запуска по F5 (не пройдёт семантический анализ).
3. Далее Algebrizer строит дерево разбора запроса с описанием логических шагов, необходимых для преобразования исходных данных к желаемому результату. Для дерева запроса извлекаются метаданные объектов запроса (типы данных, статистика индексов и т.д.), производятся неявные преобразования типов (при необходимости), удаляются избыточные операции (например, ненужные или избыточные соединения таблиц).
4. Затем оптимизатор запросов анализирует различные способы, с помощью которых можно обратиться к исходным таблицам. И выбирает ряд шагов, которые, по мнению оптимизатора, возвращают результаты быстрее всего и используют меньше ресурсов. В дерево запроса записывается последовательность этих полученных шагов и из конечной, оптимизированной версии дерева генерируется план выполнения запроса.

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