3

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?

1

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);
| improve this answer | |
1

Using Profiler via the UI can cause serious loads on Production systems. What you should bear in mind is that you must always consider using a Server-Side Trace instead of using Profiler's User Interface.

You might think of it as of almost the same. There is very nice article by Gail Shaw that you should definitely read and understand.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.