Extended event to capture which is running view in SQL Server

Question

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

Answer 1

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
);