Capturing object dependencies at execution time in SQL Server

Question

Is there any sane way to capture execution-time object dependencies in SQL Server?

For instance, take this dynamic SQL scenario:

DECLARE @table SYSNAME = 'SomeTable'
DECLARE @column SYSNAME = 'SomeColumn'
DECLARE @proc SYSNAME
DECLARE @command NVARCHAR(MAX) = 'SELECT TOP 1 @proc = '+@column+' FROM '+@table
EXEC sp_executesql @command, N'@proc SYSNAME OUTPUT', @proc OUTPUT
EXEC @proc

The execution-time dependencies would be SomeTable, sp_executesql, the value of @proc, and whatever objects are referenced at run-time by the @proc procedure.

Methods I have considered thus far:

• grabbing the xml query plan from sys.dm_exec_query_plan from within each batch, and passing it off to another process via service broker for processing. pros: I think it might actually work. cons: potentially expensive, and intrusive: each batch and execution level must be retrofitted to capture the query plan.

• extended events. pros: if it could be made to work, then great! cons: I don't think there is a suitable event class for "object access", eg scan and/or seek and/or exec etc etc.

Ideally, the capture would work something like this:

DECLARE @guid UNIQUEIDENTIFIER
EXEC usp_begin_object_capture @guid OUTPUT

DECLARE @table SYSNAME = 'SomeTable'
DECLARE @column SYSNAME = 'SomeColumn'
DECLARE @proc SYSNAME
DECLARE @command NVARCHAR(MAX) = 'SELECT TOP 1 @proc = '+@column+' FROM '+@table
EXEC sp_executesql @command, N'@proc SYSNAME OUTPUT', @proc OUTPUT
EXEC @proc

EXEC usp_stop_object_capture @guid

SELECT object_name FROM object_capture_table WHERE guid = @guid

------------------------------
object_name 
------------------------------
SomeTable
sp_executesql
<proc_named_by_@proc>
<object1_referenced_by_@proc>
<object2_referenced_by_@proc>
<object3_referenced_by_@proc>
<objectn_referenced_by_@proc>

Context:

I am trying to cache/memoize the deterministic result sets of long-running procedures. The underlying data is fairly static. If I could gather actual dependencies at execution-time, I could automatically associate an entry in the cache with a set of objects. If any of those objects changed, I would know which entries to invalidate.

This may be a poor caching strategy, I don't know. But the dependency technique would still be useful in other contexts.

Any thoughts? Many thanks.

Answer 1

How about leveraging the SQL Server built-in mechanism for cache invalidation, namely Query Notifications? You cache/memoize your query result, and let SQL Server itself notify you when a result has changed.

Answer 2

[Answering my own question....]

See the sp_trace% system procs, beginning w/ sp_trace_create.

Use event 114, "Audit Schema Object Access Event".

Include columns DatabaseName, ParentName and ObjectName, and perhaps ServerName.

Filter on column 12, SPID.

Extended events don't expose the schema object access event (yet), otherwise it might be preferable.