Στον Microsoft SQL Server πώς μπορώ να λάβω ένα σχέδιο εκτέλεσης ερωτήματος για ένα ερώτημα/μια αποθηκευμένη διαδικασία;
Υπάρχουν διάφορες μέθοδοι για την απόκτηση ενός σχεδίου εκτέλεσης, η επιλογή της οποίας θα εξαρτηθεί από τις περιστάσεις σας. Συνήθως μπορείτε να χρησιμοποιήσετε το SQL Server Management Studio για να λάβετε ένα σχέδιο, ωστόσο αν για κάποιο λόγο δεν μπορείτε να εκτελέσετε το ερώτημά σας στο SQL Server Management Studio, τότε μπορεί να σας φανεί χρήσιμο να μπορείτε να λάβετε ένα σχέδιο μέσω του SQL Server Profiler ή επιθεωρώντας την προσωρινή μνήμη σχεδίου.
exec p_Example 42
Όταν το ερώτημά σας ολοκληρωθεί, θα πρέπει να δείτε μια επιπλέον καρτέλα με τίτλο "Execution plan" να εμφανίζεται στο παράθυρο αποτελεσμάτων. Εάν εκτελέσατε πολλές εντολές, τότε μπορεί να δείτε πολλά σχέδια να εμφανίζονται σε αυτή την καρτέλα. ![Στιγμιότυπο ενός σχεδίου εκτέλεσης][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 για να ανοίξει το σχέδιο σε ένα νέο παράθυρο - κάντε δεξί κλικ και επιλέξτε "Save execution plan as..." για να αποθηκεύσετε το σχέδιο σε αρχείο σε μορφή 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.
Ακολουθεί ένα δείγμα στιγμιότυπου οθόνης για να έχετε μια ιδέα για τις λειτουργίες που προσφέρει το εργαλείο:
[]]2
Είναι μόνο μία από τις προβολές που είναι διαθέσιμες στο εργαλείο. Παρατηρήστε ένα σύνολο καρτελών στο κάτω μέρος του παραθύρου της εφαρμογής, το οποίο σας επιτρέπει να λαμβάνετε διαφορετικούς τύπους αναπαράστασης του σχεδίου εκτέλεσης και χρήσιμες πρόσθετες πληροφορίες επίσης.
Επιπλέον, δεν έχω'παρατηρήσει κάποιους περιορισμούς της δωρεάν έκδοσής του που να εμποδίζουν τη χρήση του σε καθημερινή βάση ή να σας αναγκάζουν να αγοράσετε τελικά την έκδοση Pro. Έτσι, αν προτιμάτε να μείνετε με τη δωρεάν έκδοση, τίποτα δεν σας απαγορεύει να το κάνετε.
ΝΕΑ ΕΝΗΜΕΡΩΣΗ: (Χάρη στον Martin Smith) Το Plan Explorer είναι τώρα δωρεάν! Δείτε http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view για λεπτομέρειες.