Sql-server – Is this considered a “light load” trace to run on SQL Server Profiler

profilersql server

This is my first experience of using SQL Server (2012 Enterprise Edition) Profiler. I've set up a trace to log when views are being accessed on a particular database.

I'm running Profiler on my personal PC, connecting to our Live/Production Server and letting the trace run.

It's only monitoring a single database, and writes events when views are accessed (of which there are approx. 300 potential Views in total).

I have reservations that leaving Profiler to run for long periods of time may affect server performance (especially as I'm querying a Live/Production server).

Is what I've proposed considered a fairly "light load" to be running?

Best Answer

I suggest you use audit trace which behind the scene uses Extended event. I created a sample script for you. I am filtering by database name and schema name. You can also add object name, login etc. What I was hoping is to filter by objecttype=8278 which is VIEW but I could not figure it out.

Ref:

https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/objecttype-trace-event-column

USE [master]
GO

CREATE SERVER AUDIT [AccessView]
TO FILE 
(   FILEPATH = N'C:\AccessView\'
    ,MAXSIZE = 50 MB
    ,MAX_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE

)
WHERE ([database_name]='WideWorldImporters' AND [schema_name]='Website' )
ALTER SERVER AUDIT [AccessView] WITH (STATE = OFF)
GO


CREATE SERVER AUDIT SPECIFICATION [AccessView]
FOR SERVER AUDIT [AccessView]
ADD (SCHEMA_OBJECT_ACCESS_GROUP)
WITH (STATE = ON)
GO

USE [master]
GO
ALTER SERVER AUDIT [AccessView] WITH (STATE = ON);
Related Question