Кому план? Cвежий план, берем из кэша не стесняемся)

Как всегда возник вопрос где взять план..

1.Метод, взять план из кэша

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

CROSS APPLY sys.dm_exec_query_plan(plan_handle)

where text like '%Текст%'

Сохранить XML план с расширением .sqlplan 

и открыть через SSMS

2.Метод, посмотреть через SSMS

Для начала включить опцию Актуальный план

Затем запустить выполнение инструкции, план появится в отдельной вкладке

3.Метод, используя опцию SHOWPLAN

SET SHOWPLAN_TEXT ON

SET SHOWPLAN_ALL ON

SET SHOWPLAN_XML ON

SET STATISTICS PROFILE ON

SET STATISTICS XML ON -- The is the recommended option to use

• SHOWPLAN_TEXT отображает приблизительный план без реального запуска инструкции
• SHOWPLAN_ALL – Отображает приблизительный план со стоимостными оценками без реального запуска инструкции
• SHOWPLAN_XML - Displays an XML based estimated execution plan with cost estimations, without executing the query. This is equivalent to the "Display Estimated Execution Plan..." option in SQL Server Management Studio.
• STATISTICS PROFILE – Выполняет запрос и отображает реальный текстовый план.
• STATISTICS XML – Выполняет запрос и отображает актуальный план. Эквивалент  опции "Include Actual Execution Plan" в SSMS.

4.Метод, используя профайлер

1. Открыть SQL Server Profiler и запустить новую трассировку для требуемой базы.
2. В опциях "Events Selection" выбрать "Show all events", отметить "Performance" -> "Showplan XML" и запустить трассировку.
3. При запущенной трассировке необходимо отловить проблемный запрос.
4. Подождать пока завершится запрос.
5. Нажать кнопку сохранить правым кликом мыши в xml для SQL Server Profiler и выбрать "Extract event data..."  чтобы сохранить план в XML формат.

P/s на добавку очень полезная опция влияющая на план и производительность запросов MAXDOP

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

для каждого из планов параллельного выполнения. Можно использовать параметр max degree of parallelism для ограничения числа процессоров, применяемых в планах параллельного выполнения.

Чтобы разрешить серверу определять максимальную степень параллелизма, установите 0 в качестве значения данного параметра, то есть значение по умолчанию.

Установите для параметра «Максимальная степень параллелизма» значение 0, чтобы использовать все доступные процессоры (до 64 процессоров).

Чтобы отключить создание параллельных планов, присвойте параметру max degree of parallelism значение 1. Установите значение, превышающее 1, чтобы ограничить максимальное количество процессоров,

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

Если указано значение, превышающее количество доступных процессоров, используется фактическое число доступных процессоров. Если у компьютера только один процессор, то значение параметра max degree of parallelism учитываться не будет.

USE AdventureWorks

GO

SELECT *

FROM Sales.SalesOrderDetail

ORDER BY ProductID

GO

Отключаем создание параллельных планов.

USE AdventureWorks

GO

SELECT *

FROM Sales.SalesOrderDetail

ORDER BY ProductID

OPTION (MAXDOP 1)

Установка параметра max degree of parallelism

Параметр max degree of parallelism является дополнительным параметром. Если для изменения настроек используется системная хранимая процедура sp_configure,

то изменить значение параметра max degree of parallelism можно только при условии, что параметр show advanced options равен 1. Новые настройки вступают в силу немедленно (без перезапуска службы MSSQLSERVER).

В следующем примере параметру max degree of parallelism присваивается значение 8.

-- проверить значения
select name, value, value_in_use from sys.configurations 
where name like '%max degree%' OR name like '%cost threshold for parallelism%'

sp_configure 'show advanced options', 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

sp_configure 'max degree of parallelism', 8;

GO

RECONFIGURE WITH OVERRIDE;

GO

Переопределяет параметр конфигурации max degree of parallelism хранимой процедуры sp_configure и регулятора ресурсов для запросов, в которых указывается этот параметр.

Подсказка в запросе MAXDOP может превысить значение, заданное с помощью процедуры sp_configure.

Если MAXDOP превышает значение, настроенное с помощью регулятора ресурсов, компонент Компонент Database Engine использует значение MAXDOP регулятора ресурсов, описанное в разделе

ALTER WORKLOAD GROUP (Transact-SQL). Все семантические правила, используемые параметром конфигурации max degree of parallelism, применимы при использовании подсказки в запросе MAXDOP.

Дополнительные сведения см. в разделе Настройка параметра конфигурации сервера max degree of parallelism.