CODEWITHSUNDEEP
sql-serverextended-events
dan Kalio
dan Kalio

Reputation: 33

Extended event to capture which is running view in SQL Server

I have a requirement to capture which users are hitting a view in a database. My initial thought was to use extended events but for some reason when I test nothing is being captured. This is what I have so far. Any suggestion would be greatly appreciated.

-- Test 1

CREATE EVENT SESSION [Track_View] ON SERVER 
ADD EVENT sqlserver.module_start
(
    SET collect_statement=1
    ACTION
    (
        sqlserver.client_app_name,                
        sqlserver.database_name,                   
        sqlserver.session_server_principal_name,   
        sqlserver.username,                        
        sqlserver.sql_text,
        sqlserver.tsql_stack
    )
    WHERE 
    (
        [object_type]='V ' 
        AND [object_name]=N'MyView'
    )
)

ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.module_start',source=N'object_name',source_type=(0)),
ADD TARGET package0.event_file(SET filename=N'C:\Event_Trace\XE_Track_view.xel',max_rollover_files=(20))
WITH (MAX_MEMORY=1048576 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO

-- Test 2

CREATE EVENT SESSION [Track_View] ON SERVER 
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)
 
WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)) 
 
AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'myview') 
 
AND [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'myDB') 
 
 ))
 
ADD TARGET package0.event_file(SET filename=N'C:\Event_Trace\XE_Track_view.xel',max_rollover_files=(20))
WITH (MAX_MEMORY=1048576 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO

Upvotes: 0

Views: 1340

Answers (2)

IVNSTN
IVNSTN

Reputation: 9299

Object types in Extended Events have different spelling from dbo.objects field type. Here you can filter objects by values 'TRIGGER', 'PROC', 'FUNCTION' and so on. In your case it will be object_type = 'VIEW'.

I don't know if there is a docs page or a table containing list of these values but they can be seen at session configuration dialog in filter predicates tab: enter image description here

object_type as I understand is actually an integer attribute but SqlServer performs some magic under the hood so you can use string literals as you see them in shown dropdown.

You can practice with GUI Wizard in SSMS and then call Script as > CREATE to > New tab context menu item to see how it looks like in raw code.

Upvotes: 0

Ben Thul
Ben Thul

Reputation: 32687

First, let's break down why each of the XE sessions are not capturing access to the view and then let's see if we can make a small change so that one of them does.

The session that you've labeled as "test 1" is capturing the sqlserver.module_start event. Even though views are modules (which, before writing up this answer, I didn't believe that they were but the documentation for sys.sql_modules says that they are), they don't start and end in the same way as, say, a stored procedure or a function does. So the event isn't firing.

The session that you've labeled as "test 2" has a subtle error. Let's look at this predicate specifically:

[sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'myview')

Because it lacks any wildcards in the search criteria, this effectively says [sqlserver].[sql_text] = N'myview'. Changing the search criteria to N'%myview%' should suffice. And in my testing†, that was sufficient.

One last thing I will note is that XE may not be sufficient to capture all uses of a given object. Take, for instance, a situation where a base object is referenced indirectly through a synonym. I've had good luck using the SQL Audit feature (which, ironically, uses XE under the hood) to track object use. It's a bit more to set up, but you get most (if not all) of what you're looking for as far as execution context. For your use case you'd want to audit some or all of the CRUD operations against the view.

† here is the XE session that I used in my testing. I used my local copy of AdventureWorks (which is why it references vEmployee) and added a predicate for the session from which I was issuing the query (to avoid spamming the XE session). But otherwise the approach is identical.

CREATE EVENT SESSION [Track_View] ON SERVER 
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_name,
        sqlserver.nt_username,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE (
        [package0].[equal_boolean]([sqlserver].[is_system],(0)) 
        AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%vEmployee%') 
        AND [sqlserver].[session_id]=(70)
    )
)
ADD TARGET package0.event_counter,
ADD TARGET package0.ring_buffer(SET max_events_limit=(10))
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
);

Upvotes: 1

Related Questions