May 20, 2005 at 4:08 am
We discovered that if we were running a broad profiler session to log all access to the server, it had a massive performance hit on a VB application that we used. Processing time per run went from 2 seconds to 6 which caused massive application related operation problems for us.
Has any research been done as to the level of performance hit caused by Profiler? How this can be minimised, etc.
Any useful general links on Profiler would be helpful too!
Thanks.
Tom.
May 20, 2005 at 4:48 am
I think the best route is to make sure your box has sufficient horsepower to cope with intrusive monitoring. I use profile and other tools and the best methods seem to make sure you only grab those events and data you really want.
I'd usually capture to a table on another server, I have lots of network bandwidth to do this.
You can audit logons using the sql and windows logs - maybe that may help?
Failing that server side tracing is a better route than profiler in as much as it is less intrusive - I've got to say I've not ever found profiler having any adverse effect like you describe.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 20, 2005 at 5:29 am
Another thing is can you provide what type of information you are looking for so we could possibly help you tailor the profiler??
Good Hunting!
AJ Ahrens
webmaster@kritter.net
May 20, 2005 at 5:50 am
It was being use to log logins and access to various tables as a stopgap measure for SOX compliance until we get a proper tool. One is being investigated, but I was surprised at the performance hit by SQL Profiler and I'm trying to learn from this experience.
I work in a production environment and I am worried about using Profiler on a production server in future and was wondering if general research had been done on the type of performance hit it entails, what traces are more entensive than others, etc.
General stuff on to allow me to make better decisions in future.
Tom.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply