November 5, 2002 at 1:37 pm
BOL: SQL Server 2000 Profiler
How to save trace results to a file (SQL Profiler)
To save trace results to a file.....
Select the Server processes SQL Server trace data check box.
- To avoid missing events, select this option.
Does everyone select this?
How much affect on the server?
November 5, 2002 at 2:37 pm
Same as any write to a HD would have. Just don't send to the same drive as your data or log files if possible. Also, it will print the information to the screen to save memory try using the sp_trace... or if you have to use the Profiler GUI turn the scrolling of the window off.
November 5, 2002 at 2:52 pm
I am wondering about the extra processing needed due to selecting "Sever processes sql server trace data".
(CPU mainly).
It seems that running a Stress Test against a box that must also "Process sql server trace data" may put extra load on the server.
November 5, 2002 at 2:55 pm
If you are going to save trace results, saving to a trace file is considered fastest. At some later point you can take a trace file and convert to a trace table if you need to do analysis.
The Server processes SQL Server trace data does exactly what it says it does. You shouldn't end up missing any events, but it could result in a performance hit if you've got a lot of events firing. If, when you run a trace from the client side you don't see anything saying something to the effect of "Some events may not have been captured" your client can keep up. We use the server processes only when we have to make sure we grab everything.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
November 5, 2002 at 2:58 pm
I will be selecting this option during my stress tests since any profiler data loss will affect my reports.
Just wondering on a higher level how much this option will affect a SQL Server box.
November 5, 2002 at 2:58 pm
It does, but it varies based on condition of processes running and IO to the drive the fiel is saved to. I use the black box trace frequently when try to find root issues and have never had any trouble even on the servers with my highest load. The best thing to do to know for sure is start it and be ready to turn off at the first sign of trouble. You should however not run during a stress test to be sure you are getting godd results.
November 5, 2002 at 3:03 pm
Unfortunately, there is no finite answer and I have looked at a rough one under various loads.
November 5, 2002 at 5:13 pm
From bkelly:
"If, when you run a trace from the client side you don't see anything saying something to the effect of "Some events may not have been captured" your client can keep up.""
- if this happens, will the above text be in the trace file?
or is it just a pop-up one time warning message for example?
November 5, 2002 at 5:35 pm
If I remember correctly that is the message when using some Performance Monitor counters, I have not seen in Profiler. However I would think it was a popup and not recorded in the trace as you can group and reorganize so how would you know where to associate.
November 5, 2002 at 8:38 pm
It shows up in the trace itself but I'll be honest in saying that it requires a LOT of activity. The case I can cite was a trace that dumped 50 MB of data in 20 minutes as there was that much activity going on.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply