October 17, 2014 at 7:39 am
Is it possible to have database timeout because of running SQL Profiler.
Thanks
Rechana
October 17, 2014 at 7:44 am
Profiler can hurt performance of the monitored instance. Events are processed synchronously and the server may have to wait for Profiler to consume them all.
That said, what kind of timeout are you experiencing? Login timeout? Query timeout?
-- Gianluca Sartori
October 17, 2014 at 7:50 am
A server-side profiler trace is quite light but it depends upon how much data you're trying to save from the trace. It has to write the results to a file and a large amount of data can slow everything down.
Client-side is a bad thing to do - actually watching the trace in real-time through the Profiler slows the server down because the server waits until the profiler has got the current trace result before progressing to the next. Then you start seeing alerts because messages can also get lost.
October 17, 2014 at 7:55 am
Thanks Gianluca & BrainDonor,
Its 'The wait operation timed out' error looged in our exception table and i believe its timeout experienced for customer when accessing the site.
had a high CPU usage issue and because of that we had to run trace and we did it from client-side.
October 17, 2014 at 8:11 am
Rechana Rajan (10/17/2014)
Thanks Gianluca & BrainDonor,Its 'The wait operation timed out' error looged in our exception table and i believe its timeout experienced for customer when accessing the site.
had a high CPU usage issue and because of that we had to run trace and we did it from client-side.
Create your trace with profiler (specifying 'save to file' ) and then run it for a second. Stop the trace and select File/Export/Script Trace Definition/ and save the sql code it generates. This can be run on the server itself and will output to a file. These files can be imported back into Profiler afterwards and examined at your liesure.
It's also a good idea to specify a trace stop time, just in case.
October 17, 2014 at 8:41 am
Thanks a lot BrainDonor,
Can high CPU usage over 95% for hours will bring SQL Server down??
October 17, 2014 at 9:01 am
If you're experiencing pain from Profiler, trace is one option, but since you're in 2008, you can use extended events. These are even more lightweight than trace and filter much better. I would recommend that over anything else, especially for a system that's already under stress.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 17, 2014 at 9:14 am
Rechana Rajan (10/17/2014)
Thanks a lot BrainDonor,Can high CPU usage over 95% for hours will bring SQL Server down??
It depends on what is causing the high cpu. If you have profiler running and a couple people running activity monitor - sure that can bring down sql server to where it causes connection timeouts.
More than likely you have some queries running amuck. Queries that have triangular joins, high io, or are just poorly written in general can cause high cpu.
Think of the high CPU as more of a Symptom than a root cause.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply