November 14, 2002 at 11:32 am
I've looked in BOL and the various posts and articles on this site, but cannot find any specific information regarding the mechanics of Profiler.
I'm specifically concerned with the performance hit the server or db takes with Profiler implementation. For instance, I have been charged with capturing all SELECT statements from a production server as part of our internal auditing scheme. The application DBA claims that this is blocking other processes and can't be run during office hours. Does the tracing of SELECTs place locks on records or tables? And if it does, how long would these locks be held?
If so, is there a way to capture this information while minimizing the effect on production, perhaps using a third-party utility?
I am running my traces on a remote server dedicated for this purpose, and the particular server I am having the issue with is running 7.0.
Thanks for the help,
Rob Haas
November 14, 2002 at 12:46 pm
Profiler does place a noticable measure of overhead on any server its run against. The more data and columns in the trace, the more of a load on the server.
The only way it could be "Blocking" other processes, is by the server being busy writing the audit data and not able to serve the other requests fast enough. It could cause waits to occur while sending the audit data to another server. It does not lock records itself.
In order to minimize the impact on your server, minimize the data being recorded to just whats actually needed, whether that is reducing the columns or the rows, do what you can. Another way of doing this is to run several smaller traces rather than a large one.
Also, you might compare the available IO slack time to the Network slack time. Which ever is greater would determine where to record the data. If you have more slack time in network IO than in Disk IO, I would say to go ahead and write the data across the network. However, this will be slower than writing the data locally. If you have enough slack disk time on the server, I would say to record your data to the local machine.
A combination of these two things will give you the best performance. Reducing the data to speed up the collection of the data, and determining the best place to store the data for the faster storage techniques.
I've also noticed on several occassions that writing to a table seems to perform much better than writing the data to a file.
One more thing. I have yet to see any third party utilities which place less of a load on SQL server when doing a profile than profiler. In fact, most of them are many times more expensive in terms of overhead.
November 14, 2002 at 2:35 pm
I notice that I am collecting a ridiculously large amount of data. That is the first thing I will trim down.
"Another way of doing this is to run several smaller traces rather than a large one."
Are you saying for instance, in the case of auditing users, that it would be more efficient to have more than one simultaneously running profile, each tracing a subset of all users? I would have thought this to create more overhead, not less.
"Also, you might compare the available IO slack time to the Network slack time."
This is great stuff and something I would never have thought of. However, for security's sake, I think the trace is better saved to a remote location with limited access. For troubleshooting traces this does seem like an excellent idea though.
"I've also noticed on several occassions that writing to a table seems to perform much better than writing the data to a file."
This is the way I prefer to do it, although that is contrary to other things I have read.
"I have yet to see any third party utilities which place less of a load on SQL server when doing a profile than profiler."
That should save us a few $$$!
Thanks for your quick and detailed response.
November 14, 2002 at 2:50 pm
In the case of a few small traces rather than a large one, it is true that overall the load on the server will be greater. However, the data collections by the threads will be smaller batches individually, and the writes, whether to network or local, would also be smaller. Broken up like that, they do not hold resources for so long, and therefore cause less blockages even though there is more activity.
Kind of the same idea as creating smaller batches to allow more processes use of resources, rather than a single large batch which holds its resources till completion.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply