July 25, 2012 at 12:33 pm
Hello,
I don't post a lot here but read from time-to-time. We have unfortunately run into a major server issue the day our dba has surgery...I'm usually more of a BI guy that knows enough to be dangerous...that said....
I'm trying to determine what is causing some major slowness on one of our main db servers. We have transactional replication going to it and that has been backing up as well. Whether that is a cause or another symption I don't know. I suspect blocking of some sort and I'm doing my best to check into that as well, but this is more looking for poor-performing queries (or an influx of many queries etc).
In any event I'm running a trace and see tons of the following:
eventclass: SQL:BatchCompleted
textdata: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
I understand what that is doing; what I'm having trouble with is finding the corresponding sql/proc call in the trace. I've tried looking by spid but the same spid will have a bunch of these in a row, so I suspect I just am not capturing the correct events. I started with the sql duration trace and added additional columns--are there additional events I need to capture?
Currently I have: RPC: Completed (stored procs) and SQL:BatchCompleted (TSql). I've tried a couple others but nothing pertinent so far.
I appreciate any help. And yes as the backup dba I need to get more training, perhaps this will convince some folks to make it happen 🙂 In any event, if I can get any help to get me through this it would be wonderful.
Rick
July 25, 2012 at 12:42 pm
Performance troubleshooting using waittype stats from DMVs (as a first step) will be less intrusive and will not add overhead to the server.
you can take a look at the following URL for related information:
July 25, 2012 at 12:46 pm
Vikrant S Patil (7/25/2012)
Performance troubleshooting using waittype stats from DMVs (as a first step) will be less intrusive and will not add overhead to the server.you can take a look at the following URL for related information:
Make it easier for others to check out the url's you post, put them inside of the [ url ]/[ /url ] IFCode shortcut (no spaces inside the square brackets).
July 25, 2012 at 1:11 pm
Thanks Lynn, point taken , will do.
July 25, 2012 at 1:21 pm
randerson-917627 (7/25/2012)
In any event I'm running a trace and see tons of the following:eventclass: SQL:BatchCompleted
textdata: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
I understand what that is doing; what I'm having trouble with is finding the corresponding sql/proc call in the trace.
It may not be a proc call (and you're capturing all the events that you need). It could just be a connection setting, some DALs set the isolation level automatically.
For analysing trace data, have a look at these:
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply