Sql-server – Performance of query against column level dependencies much faster when using deprecated system view

sql serversystem-tablest-sql

Consider these two queries that get the names of triggers dependant on the JV_VoyageFlight column in table dbo.JobVoyage.

the first uses the 'old' mechanism that is deprecated (sys.sql_dependencies).

SELECT
    depObj.name
FROM
    sys.sql_dependencies dep
    INNER JOIN sys.tables tab ON tab.object_id = dep.referenced_major_id
    INNER JOIN sys.schemas sch ON sch.schema_id = tab.schema_id
    INNER JOIN sys.columns col ON col.object_id = dep.referenced_major_id AND col.column_id = dep.referenced_minor_id
    INNER JOIN sys.objects depObj ON depObj.object_id = dep.object_id
WHERE
    sch.name = 'dbo'
    AND tab.name = 'JobVoyage'
    AND col.name = 'JV_VoyageFlight'
    AND depObj.type = 'TR';

The suggested replacement view is sys.sql_expression_dependencies but this does not contain column level dependencies for non schema bound objects and the documentation advises to use the sys.dm_sql_referenced_entities function instead for that.

The second query uses the new mechanism.

select distinct name from sys.triggers
cross apply sys.dm_sql_referenced_entities('dbo.' + object_name(object_id), 'object')
where 
    parent_Class = 1 
    and Is_Ms_Shipped = 0 
    and referenced_entity_Name = 'JobVoyage'
    and referenced_minor_name = 'JV_VoyageFlight'

One takes 3 milliseconds. One takes 3000 milliseconds.
Does anyone have a better version of this query to get dependencies out of triggers without using deprecated functionality?

Best Answer

I tried this rewrite that uses a temp table with the new DMF on AdventureWorks2014, and it runs around twice as fast as the version from your question.

SELECT t.[object_id], t.[name]
INTO #trigs
FROM sys.triggers t
WHERE 
    t.parent_class = 1 
    and t.is_ms_shipped = 0;

SELECT DISTINCT t.[name] 
FROM #trigs t
CROSS APPLY sys.dm_sql_referenced_entities
(
    'Person.' + object_name(t.[object_id]), 
    'object'
) deps
WHERE 
    deps.referenced_entity_Name = 'Person'
    and deps.referenced_minor_name = 'BusinessEntityID';

Here's a screenshot of the Plan Explorer summary after running both queries in the same batch (plans here):

screenshot of plan explorer summary

The original query (the first row) runs for about 8 ms, the temp table version (the second two rows) varies between 4 and 5 ms. Of course, your database might have vastly more triggers, objects, and dependencies than AW2014.

As Dan Guzman mentioned, you may also get some improvements by updating system table stats.

FYI - those two queries are not exactly equivalent. The second query will not show the same results if there is a trigger in a different (non-dbo) schema that references dbo.JobVoyage.JV_VoyageFlight.

In the example query from my test, there is a trigger in the Sales schema (iduSalesOrderDetail) that is returned by the deprecated view but not the DMF version, because the column I'm checking for is in the Person schema:

screenshot of SSMS results pane showing different results

If all your objects are in "dbo" then this won't matter, but I thought it was worth pointing out.

Related Question