February 23, 2011 at 8:36 am
We're currently running a server side trace on a SQL 2008 Production instance. The trace has a filter to only capture jobs running longer than 5 seconds. We are discussing creating another instance of the application (on another web server) and create an additional trace that captures all SQL traffic coming only from the test web server. I'm wondering if the trace has a filter does that minimize the impact of the trace? In our case the filter should only capture a tiny percentage of the overall traffic.
Or does just running the trace have an impact regardless of what it's actually writing out?
Using the trace from the test version of the application would allow us to really troubleshoot any application issues we're having.
Any insights or advice would be greatly appreciated!
February 23, 2011 at 8:46 am
Running a trace does have an impact on the server, but it's microscopic. You'd have to run dozens or even hundreds of server-side traces before you're likely to even notice the impact.
Filters don't really limit the impact on the CPU, et al. They mainly limit the amount of data written to the trace files, which makes managing disk-use for them easier, and makes querying them for data much faster and easier.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 23, 2011 at 8:56 am
This is exactly the answer I was hoping for. I plan on trying this out during off hours this week. Thanks for your response!
February 23, 2011 at 8:59 am
You're welcome.
I have a couple of traces I add to every production server I administer, and a couple I add to every dev/test/QA server. I find them very, very valuable on a pretty routine basis.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 23, 2011 at 9:05 am
Can you suggest an article or blog that would recommend what to trace for different environments (prod, dev, etc.)?
February 23, 2011 at 9:15 am
K Foster (2/23/2011)
Can you suggest an article or blog that would recommend what to trace for different environments (prod, dev, etc.)?
I've actually been meaning to write an article on that.
To summarize, in all environments, I run traces on batch completion, including cost-associated data, so I can monitor for performance-affecting issues. I'm mainly interested in procs that are expensive on production, not internal details of them.
In dev/test/QA, I keep more details and keep a longer duration on the traces, especially for load-testing. I include statements inside procs here. More to log, but helps in finding what piece of a proc is an issue, not just what proc is an issue. Same kind of data, just a lot more granular/detailed.
Comparing cost-associated data from QA to production is helpful in finding weaknesses in the QA/testing procedure.
I used to keep a trace on DDL commands, in SQL 2000 days, but I use DDL logging triggers to do that these days. Very helpful in correlating performance changes in procs to DDL commands to either the proc or the underlying tables.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 23, 2011 at 9:16 am
Edit to remove double-posting. (Connection issue.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 23, 2011 at 9:21 am
Thanks for the summary - look forward to the article(s). I don't want to wait for the movie. 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply