251
8.1 Lecture d’un plan d’exécution
Le plan d’exécution (colonne query_plan) étant de type XML, vous pouvez y
appliquer du XQuery. Exemple d’extraction du code SQL de l’intérieur du plan :
WITH XMLNAMESPACES (DEFAULT
'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT er.session_id, er.start_time, er.status, er.command,
qp.query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/
StmtSimple/@StatementText)[1]', 'varchar(8000)') as sql_text
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
(la ligne de requête XQuery est coupée dans l’exemple).
Ce qui a permis à Bob Beauchemin de créer une intéressante procédure de
recherche d’opérateur dans un plan, que nous reproduisons ici. Vous pouvez la
trouver dans cette entrée de blog : http://www.sqlskills.com/blogs/bobb/2006/03/
03/MoveOverDevelopersSQLServerXQueryIsActuallyADBATool.aspx.
CREATE PROCEDURE LookForPhysicalOps (@op VARCHAR(30))
AS
SELECT sql.text, qs.EXECUTION_COUNT, qs.*, p.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE query_plan.exist('
declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/
07/showplan";
/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/
@PhysicalOp[. = sql:variable("@op")]
') = 1
GO
EXECUTE LookForPhysicalOps 'Clustered Index Scan'
EXECUTE LookForPhysicalOps 'Hash Match'
EXECUTE LookForPhysicalOps 'Table Scan'
8.1.1 Principaux opérateurs
Les opérateurs de plan sont séparés en deux niveaux. L’opérateur logique représente
l’action logique entreprise, au niveau de l’algèbre relationnelle (par exemple, Full
Outer Join), ou du concept de recherche, tandis que l’opérateur physique est la
méthode algorithmique utilisée pour implémenter cette opération (par exemple,
nested loop, ou boucle imbriquée).
Dans notre exemple, il s’agit d’une recherche d’index (index seek), un opérateur à
la fois logique et physique. Les premiers opérateurs présents sont en général une
extraction de données (ou un calcul sur un scalaire).
Vous trouvez la description des opérateurs et leurs icônes dans les BOL, sous
« Graphical Execution Plan Icons (SQL Server Management Studio) », nous listons ici
les plus courants et les plus significatifs.
8.1 Lecture d’un plan d’exécution
Le plan d’exécution (colonne query_plan) étant de type XML, vous pouvez y
appliquer du XQuery. Exemple d’extraction du code SQL de l’intérieur du plan :
WITH XMLNAMESPACES (DEFAULT
'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT er.session_id, er.start_time, er.status, er.command,
qp.query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/
StmtSimple/@StatementText)[1]', 'varchar(8000)') as sql_text
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
(la ligne de requête XQuery est coupée dans l’exemple).
Ce qui a permis à Bob Beauchemin de créer une intéressante procédure de
recherche d’opérateur dans un plan, que nous reproduisons ici. Vous pouvez la
trouver dans cette entrée de blog : http://www.sqlskills.com/blogs/bobb/2006/03/
03/MoveOverDevelopersSQLServerXQueryIsActuallyADBATool.aspx.
CREATE PROCEDURE LookForPhysicalOps (@op VARCHAR(30))
AS
SELECT sql.text, qs.EXECUTION_COUNT, qs.*, p.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE query_plan.exist('
declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/
07/showplan";
/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/
@PhysicalOp[. = sql:variable("@op")]
') = 1
GO
EXECUTE LookForPhysicalOps 'Clustered Index Scan'
EXECUTE LookForPhysicalOps 'Hash Match'
EXECUTE LookForPhysicalOps 'Table Scan'
8.1.1 Principaux opérateurs
Les opérateurs de plan sont séparés en deux niveaux. L’opérateur logique représente
l’action logique entreprise, au niveau de l’algèbre relationnelle (par exemple, Full
Outer Join), ou du concept de recherche, tandis que l’opérateur physique est la
méthode algorithmique utilisée pour implémenter cette opération (par exemple,
nested loop, ou boucle imbriquée).
Dans notre exemple, il s’agit d’une recherche d’index (index seek), un opérateur à
la fois logique et physique. Les premiers opérateurs présents sont en général une
extraction de données (ou un calcul sur un scalaire).
Vous trouvez la description des opérateurs et leurs icônes dans les BOL, sous
« Graphical Execution Plan Icons (SQL Server Management Studio) », nous listons ici
les plus courants et les plus significatifs.