Kako lahko v strežniku Microsoft SQL Server pridobim načrt izvajanja poizvedbe za poizvedbo / shranjeno proceduro?
Obstaja več načinov za pridobitev načrta izvršbe, izbira načina pa je odvisna od vaših okoliščin. Običajno lahko za pridobitev načrta uporabite SQL Server Management Studio, če pa iz kakšnega razloga ne morete zagnati poizvedbe v SQL Server Management Studiu, vam bo morda v pomoč, če boste lahko načrt pridobili s programom SQL Server Profiler ali s pregledom predpomnilnika načrta.
exec p_Example 42
Ko se poizvedba zaključi, se v podoknu z rezultati pojavi dodaten zavihek z naslovom "Načrt izvedbe". Če ste izvedli veliko ukazov, se lahko v tem zavihku prikaže veliko načrtov. ![Zaslonska slika izvedbenega načrta][3] Od tu lahko načrt izvedbe pregledate v programu SQL Server Management Studio ali desno kliknete na načrt in izberete "Save Execution Plan As ...", da shranite načrt v datoteko v obliki 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
To so možnosti povezave, zato jih morate za vsako povezavo izvesti le enkrat. Od tega trenutka naprej bo vse izvedene izjave spremljal dodatni niz rezultatov, ki bo vseboval načrt izvedbe v želeni obliki - za prikaz načrta preprosto izvedite poizvedbo kot običajno. Ko končate, lahko to možnost izklopite z naslednjim stavkom:
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)
Izvedite to poizvedbo in kliknite na načrt XML, da se načrt odpre v novem oknu - kliknite desno in izberite "Save execution plan as...", da shranite načrt v datoteko v obliki XML.
Poleg že objavljenega izčrpnega odgovora je včasih koristno, da lahko programsko dostopate do izvedbenega načrta in pridobite informacije. Primer kode za to je spodaj.
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
DefinicijaCREATE 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
Moje najljubše orodje za pridobivanje in poglobljeno analizo načrtov izvajanja poizvedb je SQL Sentry Plan Explorer. Za podrobno analizo in vizualizacijo izvedbenih načrtov je veliko bolj uporabniku prijazno, priročno in celovito kot SSMS.
Tukaj je vzorčni posnetek zaslona, da si boste lahko predstavljali, kakšne funkcionalnosti ponuja orodje:
To je le eden od pogledov, ki so na voljo v orodju. Opazite niz zavihkov na dnu okna aplikacije, ki vam omogoča, da pridobite različne vrste prikaza načrta izvajanja in tudi koristne dodatne informacije.
Poleg tega pri brezplačni izdaji nisem opazil nobenih omejitev, ki bi preprečevale vsakodnevno uporabo ali vas sčasoma prisilile v nakup različice Pro. Če torej raje ostanete pri brezplačni izdaji, vam tega nič ne prepoveduje.
DOPOLNITEV: (Zahvaljujoč Martinu Smithu) Raziskovalec načrtov je zdaj brezplačen! Za podrobnosti glejte http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view.