Server Side Traces.......How often do you use them?

Question

Server Side Traces.......How often do you use them?

DominantDBA

SSC Eights!

Points: 862
More actions
September 7, 2012 at 2:52 am

#280829

Hi All
I would like to gauge how you all use server side traces in your environments
So Im a big believer in gathering perfmon stats all the time. I use typeperf to load all the data into a collection databaser and then use powervivot to analyse the data
This runs round the clock
Now what about server side trace? Is this something you guys run all the time? I would like to run this all the time as my understanding goes is that there isnt much of a performance overhead when doing this. But not everyone agrees with this.
Are extended events a better way to go now? From what Ive read maybe?
It would be good to hear some thoughts on this
Thanks
Chris

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sestell1 SSChampion Points: 10230 More actions · Answer 1

It may be minimal, but there's still an overhead, so I wouldn't run traces you don't absolutely need.

I generally only use them temporarily while tracking down an issue, however there are a some I run continuously from startup. I run a trace to audit login failures on databases that are applicatoin backends. I also use a trace flag to have information about deadlock events written to the log.

I've just started looking into SQL Server Audits to track information, but haven't made it very far with that yet.

By the way, there is a default trace that runs on SQL Server. You may want to poke at it a bit to see if whatever information you are looking for is in there.

Lowell SSC Guru Points: 323518 More actions · Answer 2

I'm a lot less worried about performance impacts of server side traces than most people i guess;

I have three traces that are created by procedures that have been marked as startup procs on my main servers;

one for DDL, that keeps a longer history than the default trace;

one for DML for those inevitable finger pointing questions where someone wants to know if someone is selecting from payroll, or who deleted thed ata and won't fess up;

finally i have a login trace mostly as a proof of concept; i haven't had to reference it for login questions, but I have it, if somone needs to know if someone logged in outside of business hours or whatever.

this blog post certainly affected my decision on wehter to worry about trace impacts:

this article about the near-zero impact of a server side trace where someone actually compared the throughput compared to no trace vs profiler trace vs server side trace.

take a look at how the graph from that article says the server side trace hardly affects the transactions per second: only when he starts emulating 300 users doing 1200 transactions per second do you see any deviance from no trace at all vs server side trace.

all performance issues we've ever tripped over to date have more to do with indexing, sargability and the like. never an IO issue due to massive log files or anything.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 3

You may find this link very interesting "http://blog.sqlauthority.com/2011/02/01/sql-server-introduction-to-wait-stats-and-wait-types-wait-type-day-1-of-28".

If you would like more info on the default trace, you can check this link "http://ignaciosalom.com/2012/02/08/the-sql-server-default-trace/".

My blog