Как мога да получа план за изпълнение на заявка в Microsoft SQL Server за заявка/съхранена процедура?
Съществуват няколко метода за получаване на план за изпълнение, като изборът на този метод зависи от обстоятелствата. Обикновено можете да използвате SQL Server Management Studio, за да получите план, но ако по някаква причина не можете да стартирате заявката си в SQL Server Management Studio, тогава може да ви е от полза да можете да получите план чрез SQL Server Profiler или чрез проверка на кеша на плана.
exec p_Example 42
Когато заявката завърши, трябва да видите, че в прозореца с резултатите се появява допълнителен раздел, озаглавен "План за изпълнение". Ако сте изпълнили много команди, може да видите много планове, показани в този раздел. ![Екранна снимка на план за изпълнение][3] Оттук можете да прегледате плана за изпълнение в SQL Server Management Studio или да щракнете с десния бутон на мишката върху плана и да изберете "Save Execution Plan As ...", за да запазите плана във файл във формат XML.
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
Това са опции за връзка и затова трябва да ги изпълнявате само веднъж за всяка връзка. От този момент нататък всички изпълнени изявления ще бъдат придружени от допълнителен набор от резултати, съдържащ плана за изпълнение в желания формат - просто изпълнете заявката си както обикновено, за да видите плана. След като приключите, можете да изключите тази опция със следната инструкция:
SET <<option>> OFF
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)
Изпълнете тази заявка и щракнете върху XML плана, за да се отвори планът в нов прозорец - щракнете с десния бутон на мишката и изберете "Запазване на плана за изпълнение като...", за да запазите плана във файл във формат XML.
В допълнение към вече публикувания изчерпателен отговор понякога е полезно да имате възможност за програмен достъп до плана за изпълнение, за да извлечете информация. Примерен код за това е даден по-долу.
DECLARE @TraceID INT
EXEC StartCapture @@SPID, @TraceID OUTPUT
EXEC sp_help 'sys.objects' /*<-- Call your stored proc of interest here.*/
EXEC StopCapture @TraceID
StartCapture
CREATE PROCEDURE StartCapture
@Spid INT,
@TraceID INT OUTPUT
AS
DECLARE @maxfilesize BIGINT = 5
DECLARE @filepath NVARCHAR(200) = N'C:\trace_' + LEFT(NEWID(),36)
EXEC sp_trace_create @TraceID OUTPUT, 0, @filepath, @maxfilesize, NULL
exec sp_trace_setevent @TraceID, 122, 1, 1
exec sp_trace_setevent @TraceID, 122, 22, 1
exec sp_trace_setevent @TraceID, 122, 34, 1
exec sp_trace_setevent @TraceID, 122, 51, 1
exec sp_trace_setevent @TraceID, 122, 12, 1
-- filter for spid
EXEC sp_trace_setfilter @TraceID, 12, 0, 0, @Spid
-- start the trace
EXEC sp_trace_setstatus @TraceID, 1
StopCapture
ОпределениеCREATE PROCEDURE StopCapture
@TraceID INT
AS
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql),
CTE
as (SELECT CAST(TextData AS VARCHAR(MAX)) AS TextData,
ObjectID,
ObjectName,
EventSequence,
/*costs accumulate up the tree so the MAX should be the root*/
MAX(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost
FROM fn_trace_getinfo(@TraceID) fn
CROSS APPLY fn_trace_gettable(CAST(value AS NVARCHAR(200)), 1)
CROSS APPLY (SELECT CAST(TextData AS XML) AS xPlan) x
CROSS APPLY (SELECT T.relop.value('@EstimatedTotalSubtreeCost',
'float') AS EstimatedTotalSubtreeCost
FROM xPlan.nodes('//sql:RelOp') T(relop)) ca
WHERE property = 2
AND TextData IS NOT NULL
AND ObjectName not in ( 'StopCapture', 'fn_trace_getinfo' )
GROUP BY CAST(TextData AS VARCHAR(MAX)),
ObjectID,
ObjectName,
EventSequence)
SELECT ObjectName,
SUM(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost
FROM CTE
GROUP BY ObjectID,
ObjectName
-- Stop the trace
EXEC sp_trace_setstatus @TraceID, 0
-- Close and delete the trace
EXEC sp_trace_setstatus @TraceID, 2
GO
Любимият ми инструмент за получаване и задълбочен анализ на плановете за изпълнение на заявките е SQL Sentry Plan Explorer. Той е много по-удобен, лесен за използване и изчерпателен за подробен анализ и визуализация на плановете за изпълнение от SSMS.
Ето една примерна снимка на екрана, за да добиете представа каква функционалност предлага инструментът:
Това е само един от изгледите, налични в инструмента. Обърнете внимание на набора от раздели в долната част на прозореца на приложението, който ви позволява да получите различни видове представяне на плана за изпълнение, както и полезна допълнителна информация.
Освен това не съм забелязал никакви ограничения на безплатната му версия, които да пречат на ежедневното му използване или да ви принуждават евентуално да закупите Pro версията. Така че, ако предпочитате да се придържате към безплатното издание, нищо не ви забранява да го направите.
ДОПЪЛНИТЕЛ: (Благодарение на Мартин Смит) Plan Explorer вече е безплатен! Вижте http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view за подробности.