none
Capture Table usage - using Xevents RRS feed

  • Question

  • i need to capture particular table usage (hits - select ,insert,update,delete..who used,hostname..prog_name.)  metrics from a database.

    how can i implement this via xevent

    i used below xevent , but this one not capture data,


    CREATE EVENT SESSION [Xevent_DBA_Capture_Specific_Object] ON SERVER 
    ADD EVENT sqlserver.module_end(SET collect_statement=(1)
        WHERE ([sqlserver].[database_name]=N'DATABASENAME' AND [object_name]=N'TABLENAME')),
    ADD EVENT sqlserver.rpc_completed(
        WHERE ([sqlserver].[database_name]=N'DATABASENAME' AND [object_name]=N'TABLENAME')),
    ADD EVENT sqlserver.sp_statement_completed(
        WHERE ([sqlserver].[database_name]=N'DATABASENAME' AND [object_name]=N'TABLENAME'))
    ADD TARGET package0.event_file(SET filename=N'X:\Backups\Xevent\Xevent_DBA_Capture_Specific_Object.xel')
    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=ON,STARTUP_STATE=OFF)
    GO


    Tuesday, August 13, 2019 3:30 PM

All replies

  • [object_name] is not a property of rpc_completed. 
    Tuesday, August 13, 2019 4:50 PM
  • rpc_completed.  have Object_name,

    Wednesday, August 14, 2019 1:54 AM
  • this one capture query which one hit particular table ...but i want to capture host,user ,prog_name  metrics also..

    CREATE EVENT SESSION [DBATRACK] ON SERVER 
    ADD EVENT sqlserver.sql_statement_completed(
        ACTION(sqlserver.sql_text)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TABLENAMEHERE%')))
    ADD TARGET package0.event_file(SET filename=N'D:\Backups\DBATRACK_Target.xel',max_file_size=(2),max_rollover_files=(2))
    WITH (MAX_MEMORY=2048 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=3 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
    GO

    Wednesday, August 14, 2019 1:56 AM
  • Hi KIRUBAKARAN J A,

     

    I would recommend going with SQLAudit functionality. This will give you some great granualar information about who is touching your tables, and the commands that are being executed.

     

    This script should get you there (test in your dev environment, and replace the relevant pieces for the table(s) that you want to monitor):

     

    USE [master]
    
    GO
    
    CREATE SERVER AUDIT [Audit-TblChanges] 
    TO FILE  
    (   FILEPATH = N'C:\SQLAudit' 
        ,MAXSIZE = 0 MB 
        ,MAX_ROLLOVER_FILES = 2147483647 
        ,RESERVE_DISK_SPACE = OFF 
    ) 
    WITH 
    (   QUEUE_DELAY = 1000 
        ,ON_FAILURE = CONTINUE 
    )
    
    GO
    
    USE [YourDatabase] 
    GO
    
    CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-MyTable] 
    FOR SERVER AUDIT [Audit-TblChanges] 
    ADD (UPDATE ON OBJECT::[YourTable] BY [public]), 
    ADD (INSERT ON OBJECT::[YourTable] BY [public])
    
    GO
    
    USE [master] 
    GO 
    ALTER SERVER AUDIT [Audit-TblChanges] WITH (STATE = ON);
    
    USE [YourDatabase] 
    GO 
    ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-MyTable] WITH
    (STATE = ON); 
    GO

     

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, August 14, 2019 8:00 AM
  • yes dedmon,

    above audit forum created by me. http://answers.flyppdevportal.com/MVC/Post/Thread/694a2ce6-dabc-44d8-9b1a-68207351f424?category=sqldatabaseengine

    i just want to know is there any Xevent  available for this scenario. 

    Wednesday, August 14, 2019 8:32 AM
  • Hi Kirubakaran,

    it will work with the following code (see the higlighted action items!)

    CREATE EVENT SESSION [DBATRACK]
        ON SERVER
        ADD EVENT sqlserver.sql_statement_completed
            (ACTION
                 (
                     sqlserver.sql_text,
                     sqlserver.client_app_name,
                     sqlserver.client_hostname,
                     sqlserver.nt_username
                 )
             WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text], N'%TABLENAMEHERE%'))
            )
        ADD TARGET package0.event_file
            (SET filename = N'F:\TraceFiles\DBATRACK_Target.xel', max_file_size = (2), max_rollover_files = (2))
        WITH
            (
                MAX_MEMORY = 2048KB,
                EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
                MAX_DISPATCH_LATENCY = 3 SECONDS,
                MAX_EVENT_SIZE = 0KB,
                MEMORY_PARTITION_MODE = NONE,
                TRACK_CAUSALITY = OFF,
                STARTUP_STATE = OFF
            )
    GO

    BTW: The solution from Dedmon will work, too! An audit will capture all desired information the same way:

    See information about sys.fn_get_audit_file here:

    https://docs.microsoft.com/de-de/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql?view=sql-server-2017


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)


    Wednesday, August 14, 2019 10:55 AM