Sql-server – How to identify cross-database queries in SQL Server

sql serversql-server-2008-r2

In preparation for a migration project we are trying to identify any queries that select data across databases.

This can be of the form:

SELECT fieldA, fieldB
FROM dbo.TableA a
JOIN DatabaseB.dbo.TableB b ON b.ID = a.ID

or 

DECLARE @resultFromDatabaseB VARCHAR(50)

SELECT @resultFromDatabaseB = b.ID
FROM DatabaseB.dbo.TableB

SELECT fieldA, fieldB
FROM dbo.TableA a
WHERE a.ID = @resultFromDatabaseB

or any other variation (including INSERTS/UPDATES/etc…)

Unfortunately the majority of queries are not stored procedures so I cannot do static analysis of the SQL text, my current plan calls for recording the cached query text and doing static analysis on them.

Are there any alternatives to this approach? ie: use of extended events/trace/audit?

Best Answer

I would set up an Extended Events session to capture lock acquired events and stream the events with the API.

First of all the session:

CREATE EVENT SESSION crossdb ON SERVER 
ADD EVENT sqlserver.lock_acquired(
    SET collect_database_name=(1),collect_resource_description=(1)
    ACTION(
        sqlserver.request_id,sqlserver.session_id,
        sqlserver.sql_text,sqlserver.tsql_frame
    )
    WHERE (
            [package0].[equal_boolean]([sqlserver].[is_system], (0)) -- user SPID
        AND [package0].[equal_uint64]([resource_type], (5)) -- OBJECT
        AND [package0].[not_equal_uint64]([database_id], (32767))  -- resourcedb
        AND [package0].[greater_than_uint64]([database_id], (4)) -- user database
        AND [package0].[greater_than_equal_int64]([object_id], (245575913)) -- user object
        AND (
               [mode] = (1) -- SCH-S
            OR [mode] = (6) -- IS
            OR [mode] = (8) -- IX
            OR [mode] = (3) -- S
            OR [mode] = (5) -- X
        )
        AND [database_name] <> N'distribution'
    )
)
WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF
)
GO

Then, you could group the events on transaction_id and tsql_frame. The groups that touch more than one database_id are the ones that issue cross-database statements. You can then extract the statement from the sql_text action, using the offsetStart and offsetEnd from tsql_frame.

enter image description here

I have a similar example on my blog here: https://spaghettidba.com/2015/04/20/tracking-table-usage-and-identifying-unused-objects/

Related Question