June 2, 2008 at 8:20 pm
hi guys, I have sql server instance which we were having performance issues today. Users were complaining of slowness in the application (.net) side, some timeout errors, etc. I decided to run the sql profiler to see the stored procedures being run, any errors, deadlocks, etc. After i started running my profiler, the developers and dba's could'nt get in into management studio which they were able before, every in the server side started being very slow, i.e opening activity monitor, running a sp from the sql server, etc. . Could me running the sql profiler ( i run it from my pc connecting to the server) had created sql server to be almost frozen? :ermm:
June 2, 2008 at 8:25 pm
I doubt it was profiler only, but it certainly does add to the load. Have you checked for blocking\locking particularly in tempdb.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 2, 2008 at 8:29 pm
I was checking for locks and blocks in the actual database being used, i did'nt know to check also in the tempDb, i will do so. Thanks for the tip!.
Then it is not good to run the profiler when having performance issues?
Another question. i was reading http://support.microsoft.com/kb/328551 about the tempDb like you stated, how would i know if its the tempDb that is causing the SQL Server to be unresponsive?
June 2, 2008 at 8:49 pm
Some things to look for short of a profiler trace can be found using sp_who and sp_who2 which will show active processes and blocking, sp_lock which will show locks held, DBCC INPUTBUFFER([spid]) which will show the SQL executed.
There are other, much more qualified performance trouble shooters than I that I am sure will chime in in the morning.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 2, 2008 at 11:42 pm
I thought we could try this as well along with Jack's input
DBCC opentran Database_name
This gives info about the oldest active transactions.
M&M
June 3, 2008 at 12:14 am
Profiler's not usually that heavy on a server, but it is a small load. Perhaps you could use server-side traces instead (Profiler will generate the scripts for you. Stick into a job and start whenever you like)
If you go that way, make sure that the output file is not on the same drive as the data file, the log file or tempDB.
If you're using the profiler client, try to ensure that there's a good network connection between the server and the machine running profiler. save the trace to disk, not to a table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 3, 2008 at 11:43 am
Thank you guys, i apreciate it. 😀
June 3, 2008 at 12:10 pm
i have had sql profiler put the final nail in the coffin of a poorly performing server. I was on tech support call with Microsoft and they gave me a big list of items to capture. This flat lined the server to the point of rebooting.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply