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

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

USE AdventureWorks2012;
GO

SELECT qp.query_plan
FROM sys.dm_exec_procedure_stats ps
JOIN sys.objects o ON ps.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) qp
WHERE ps.database_id = DB_ID()
AND s.name = 'dbo'
AND o.name = 'usp_MonsterStoredProcedure';
GO

Этот запрос извлекает план запроса для хранимой процедуры “usp_MonsterStoredProcedure” в базе данных AdventureWorks2012. После получения плана запроса мы можем открыть его в Management Studio или Plan Explorer для анализа и оптимизации хранимой процедуры.

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

USE AdventureWorks2012;
GO

SELECT *
FROM sys.dm_exec_procedure_stats ps
JOIN sys.objects o ON ps.object_id = o.object_id
WHERE o.name = 'usp_MonsterStoredProcedure';
GO

Этот запрос подтверждает, что план запроса все еще находится в памяти SQL Server. Так почему наш первый запрос не вернул XML-план? Причина может быть в том, что план запроса слишком большой и сложный, достигая ограничения XML в SQL Server. Сообщение об ошибке “XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels” указывает на это ограничение.

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

SELECT *
FROM sys.dm_exec_text_query_plan(plan_handle, DEFAULT, DEFAULT);
GO

Однако и этот подход имеет свои ограничения. Management Studio имеет ограничение на количество символов в результатах сетки – 65535 и в текстовых результатах – 8192. В результате план запроса может быть усечен, что затрудняет анализ всего плана.

Чтобы преодолеть эти ограничения, мы можем использовать PowerShell для получения полного плана запроса. Создав простой сценарий, мы можем выполнить функцию sys.dm_exec_text_query_plan и вывести данные в файл. Вот пример такого сценария PowerShell:

param (
    [Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()]
    [string] $SqlInstance,

    [Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()]
    [string] $PlanHandle
)

$SqlCommand = "SELECT query_plan FROM sys.dm_exec_text_query_plan(" + $PlanHandle + ",DEFAULT,DEFAULT);"
$QueryPlanText = $cmd.ExecuteScalar()

$stream = New-Object System.IO.StreamWriter($FileName)
$stream.WriteLine($QueryPlanText)

Этот сценарий принимает две переменные в качестве входных данных: имя SQL Server, где план находится в кэше, и plan_handle. Он выполняет T-SQL-скрипт для получения плана запроса и сохраняет вывод в строковую переменную. Наконец, он использует класс System.IO.StreamWriter() для вывода данных в файл.

Запустив этот сценарий PowerShell, мы можем сохранить весь XML-план запроса в файл, что позволяет нам просмотреть полный план в нашем любимом инструменте и проанализировать сложность хранимой процедуры.

Этот пример демонстрирует важность изучения PowerShell для DBA. PowerShell предоставляет мощный и гибкий способ автоматизации задач и преодоления ограничений в инструментах, таких как Management Studio. Инвестируя время в изучение PowerShell, DBA могут расширить свои возможности по устранению неполадок и оптимизации в SQL Server.

Не стесняйтесь изменять предоставленный сценарий PowerShell под свою среду и требования. Вы можете найти полный сценарий и дополнительную информацию на моем блоге по адресу www.patrickkeisler.com.

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