Home Forums SQL Server 7,2000 Performance Tuning Any Catalog view to find objects referred in other database in dynamic query RE: Any Catalog view to find objects referred in other database in dynamic query

  • August 2, 2013 at 9:54 am

    #1638244

    AFAIK there's no view that would capture references in dynamic queries DIRECTLY;

    you could look at cached execution plans, and parse the xml of the plan for server/database/schema/table; but that's only going to get you items with cache-able plan, and that are still in the current cache since the last restart.

    the plans?

    SELECT decp.query_plan

    FROM sys.dm_exec_query_stats AS deqs

    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

    CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS decp

    WHERE decp.query_plan IS NOT NULL

    at that point, i'd stick the xml in a text file and use Regulus expressions on it instead. playing with xml is a little obscure for me sometimes.

    i have this saved in my snippets for example, where i'm getting missing index schema/table from the plans:

    WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)

    SELECT p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Database)[1]', 'NVARCHAR(256)') AS DatabaseName

    ,s.sql_handle

    ,s.total_elapsed_time

    ,s.last_execution_time

    ,s.execution_count

    ,s.total_logical_writes

    ,s.total_logical_reads

    ,s.min_elapsed_time

    ,s.max_elapsed_time

    ,p.query_plan

    ,p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Table)[1]', 'NVARCHAR(256)') AS TableName

    ,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Schema)[1]', 'NVARCHAR(256)') AS SchemaName

    ,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/@Impact)[1]', 'DECIMAL(6,4)') AS ProjectedImpact

    ,ColumnGroup.value('./@Usage', 'NVARCHAR(256)') AS ColumnGroupUsage

    ,ColumnGroupColumn.value('./@Name', 'NVARCHAR(256)') AS ColumnName

    FROM sys.dm_exec_query_stats s

    CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p

    CROSS APPLY p.query_plan.nodes('/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/sp:ColumnGroup') AS t1 (ColumnGroup)

    CROSS APPLY t1.ColumnGroup.nodes('./sp:Column') AS t2 (ColumnGroupColumn)

    WHERE p.query_plan.exist(N'/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan//sp:MissingIndexes') = 1

    ORDER BY s.total_elapsed_time DESC

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!