7

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?

3

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/

  • As this is for SQL 2008R2, the action sqlserver.tsql_frame is not available. I'll also need to extend what is being captured to include the client application name and client server. – Andrew Bickerton Jun 22 '17 at 9:19
  • You will need the tsql_frame only if you need to break it down at the statement level, but you are probably ok at the batch level. Application name and host name should be available actions in 2008R2, but I don't have an instance to test, sorry – spaghettidba Jun 22 '17 at 9:24
  • thanks, I'll edit your answer to incorporate those differences (once I've tested it) - thanks :-) – Andrew Bickerton Jun 22 '17 at 9:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.