Tracing select statements on specific objects in SQL Server without using Profiler

A developer of an application asked me an interesting question the other day. He wanted to know (for reasons not worth going into here) whether his application issued select statements against a specific table in his database. This database was in production and under heavy load, so although we could run a server side SQL Profiler here, and then read through the results, this could be a time consuming process which could generate an extremely large amount of data (and also a quite heavy load on the server). We also wanted to run this monitoring for a number of days if possible, so we needed something more lightweight if possible.

I thought about this for a while and realised that the best way to achieve this (assuming you are running SQL 2008 or later) would be through the new functionality SQL Audit. This uses the extended events framework as the basis for its tracing and therefore falls into the lightweight category.

Here’s an example of what I wrote, converted into simple test objects which you can try yourself. This example requires a table called dbo.audit_test to be present in a database named audit_db for you to test against.


USE master ;
GO
-- Create the server audit.
CREATE SERVER AUDIT test_server_audit
TO FILE ( FILEPATH =
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA' ) ;
GO

— Enable the server audit.
ALTER SERVER AUDIT test_server_audit
WITH (STATE = ON) ;
GO
— Move to the target database.
USE audit_db ;
GO

— Create the database audit specification.
CREATE DATABASE AUDIT SPECIFICATION audit_test_table
FOR SERVER AUDIT test_server_audit
ADD (SELECT , INSERT, UPDATE
ON dbo.audit_test BY dbo,guest, public, db_datareader)
WITH (STATE = ON) ;
GO

/*
do some work here
which will trigger the audit to record something
*/

/* uncomment these statements to turn off the audit at either DB or server level

–turn off the database audit
use audit_db
go
ALTER DATABASE AUDIT SPECIFICATION audit_test_table
WITH (STATE = OFF)

use master
go
–turn off the server audit
ALTER SERVER AUDIT test_server_audit
WITH (STATE = OFF) ;
GO

*/

Here’s the key things to note about the above example:

1. This one actually traces 3 type of table access SELECT, INSERT and UPDATE
2. It traces specific users and groups – you can change these as relevant to your example
3. It writes the output to the default DATA directory of a default 2008 R2 install – change as you see fit
4. You need to watch the potential file space this will take up as it can be very verbose in big systems
5. Watching the file space used in real time will not work, as it holds most of the data in memory and flushes when you stop the trace

Once you have the output you need (and you have turned off the audit – don’t forget!) you simply run something like this to view the data (you’ll need to locate the exact file name created each time you turn the audit on or off).


SELECT COUNT(*), statement
FROM sys.fn_get_audit_file ('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test_server_audit_7E707DDD-03F3-4FFA-B24B-BB0DDBF4D5F3_0_129714455341990000.sqlaudit',default,default)
group by statement
GO

As you can see the above does a simple count and aggregate of the results, but there are many columns in the output which you can write TSQL against (although since it’s backed by a file the access might be slow if you have large files or slow disks!).

I found this to be a most effective technique and it didn’t impact the performance of the server whatsoever.

You can read more about SQL Server Audit at MSDN.

About Graham Kent

English born, ex-Swedish resident, walker, skier, lover of rainy days. I've been working full time with SQL Server and Windows since 1997, including 5 years in the SQL Server team at Microsoft.
This entry was posted in Troubleshooting and tagged . Bookmark the permalink.

2 Responses to Tracing select statements on specific objects in SQL Server without using Profiler

  1. David BM says:

    Hello, I want to add a master sql database extracted from the forensic image of a sql server under investigation to determine if the audit was enabled or not. If the audit was enabled I also want to see the SQL statements that were receieved by the server and if possible the dates and times as well as the client IP addresses.

    When I attach the master database of the server under investigation, it conflicts with the existing master database of my sql server. I tried renaming both the mdf and the ldf files, but this didn’t work, because it seems that the original database names are contained in the database files. Is there anyway to attach the master database of the server under investigation, determine if there was any audit enabled and determine the audit file path?

    Thanks in advance.

  2. toto says:

    Not available on Standard edition 🙁