Just Code‎ > ‎

T-SQL - An attempt to extract filter-, join- and output columns from query plan XML

posted Nov 5, 2012, 1:42 AM by Peter Henell   [ updated Nov 5, 2012, 1:42 AM ]
I wanted to see which of our columns was being used for filtering, joining and output. I also needed to know which stored procedure that was using those columns.
What I did was to run all the stored procedures, capture their query plans in the aaPlans table. Then I was going to query those plans to get the information i needed.

Query the Query plan XML to get out the Output columns, Filter Columns and join Columns.

Ran into a dead end when i realized that aliased columns was aliased from other aliases.

DECLARE @xml XML;
SELECT @xml = queryplan FROM aaPlans WHERE id = 58;
--SELECT * FROM dbo.aaPlans where id = 171

    
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT DISTINCT
    t.c.value('@Column', 'varchar(128)') AS FilterColumn
    ,t.c.value('@Table', 'varchar(128)') AS FilterTable
    , scalarString.s AS Expression
    
FROM 
    @xml.nodes('//Predicate//Identifier/ColumnReference') AS T(c)
OUTER APPLY(
    -- take out the Expression if the filterTable is missing
    SELECT 
        scalarString.s.value('@ScalarString', 'varchar(max)') AS Expression,
        alias.col.value('@Column', 'varchar(128)') AS ColumnAlias,
        actual.col.value('@Table', 'varchar(128)') AS AliasTable
    FROM    
        @xml.nodes('//*[local-name() = "SequenceProject" or local-name() = "ComputeScalar"]/DefinedValues/DefinedValue')    level1(col)
        OUTER APPLY level1.col.nodes('ColumnReference') ALIAS(col)
        OUTER APPLY level1.col.nodes('.//Identifier/ColumnReference') actual(col)
        OUTER APPLY level1.col.nodes('.//ScalarOperator') scalarString(s)

    WHERE 
        t.c.value('@Table', 'varchar(128)') IS NULL 
        AND t.c.value('@Column', 'varchar(128)') = alias.col.value('@Column', 'varchar(128)')
        
) scalarString(s, ColumnAlias, AliasTable);

    
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT DISTINCT
    t.c.value('@Column', 'varchar(128)') AS JoinColumn
    ,t.c.value('@Table', 'varchar(128)') AS JoinTable
FROM 
    @xml.nodes('//IndexScan/SeekPredicates//ColumnReference') AS T(c);
    
    

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT DISTINCT
    outputs.col.value('@Column', 'varchar(128)') AS OutputColumn,
    outputs.col.value('@Table', 'varchar(128)') AS OutputTable
    ,scalarString.s AS Expression
FROM
    @xml.nodes('//StmtSimple/QueryPlan/RelOp/OutputList/ColumnReference')  outputs(col)
OUTER APPLY(
    -- take out the Expression if the filterTable is missing
    SELECT 
        scalarString.s.value('@ScalarString', 'varchar(max)') AS Expression,
        alias.col.value('@Column', 'varchar(128)') AS ColumnAlias,
        actual.col.value('@Table', 'varchar(128)') AS AliasTable
    FROM    
        @xml.nodes('//*[local-name() = "SequenceProject" or local-name() = "ComputeScalar"]/DefinedValues/DefinedValue')    level1(col)
        OUTER APPLY level1.col.nodes('ColumnReference') ALIAS(col)
        OUTER APPLY level1.col.nodes('.//Identifier/ColumnReference') actual(col)
        OUTER APPLY level1.col.nodes('.//ScalarOperator') scalarString(s)

    WHERE 
        outputs.col.value('@Table', 'varchar(128)') IS NULL 
        AND outputs.col.value('@Column', 'varchar(128)') = alias.col.value('@Column', 'varchar(128)')
        AND scalarString.s.value('@ScalarString', 'varchar(max)') IS NOT NULL
        
) scalarString(s, ColumnAlias, AliasTable)    ;
    
    
    

--WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
--select distinct
--    alias.col.value('@Column', 'varchar(128)') AS Alias,
--    actual.col.value('@Column', 'varchar(128)') AS AliasFromColumn,
--    actual.col.value('@Table', 'varchar(128)') AS AliasTable,
--    scalarString.s.value('@ScalarString', 'varchar(max)') AS Expression
--from    
--            @xml.nodes('//*[local-name() = "SequenceProject" or local-name() = "ComputeScalar"]/DefinedValues/DefinedValue')    level1(col)
--outer apply level1.col.nodes('ColumnReference') alias(col)
--outer apply level1.col.nodes('.//Identifier/ColumnReference') actual(col)
--outer apply level1.col.nodes('.//ScalarOperator') scalarString(s)

--where scalarString.s.value('@ScalarString', 'varchar(max)') is not null
Comments