SQL trace

Question

SQL trace

sunny.tjk

SSCertifiable

Points: 5041
More actions
September 23, 2010 at 9:47 am

#148706

What events and columns do I choose when sql server is running slow?
Thanks everyone.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think the events SQL:BatchCompleted , which catch ad hoc queries, and RPC:Completed, which catches code executes in a procedure or function(is that right) are what i typically use;

I think i've got an example SQL DML trace that i suggest a lot, you can take a look at that for creating a server side trace:

DML Trace example

the only thing mine does that is "special" is creates a view so i can see the results a little easier...select * from sp_MyTrace from any database.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

subbu_e SSCrazy Points: 2431 More actions · Answer 2

Could you please elaborate what events you want to set. are you looking at the Profiler or performance counter.

if you could give the bit more explanation so that every one can have better understanding on what you are looking for.

Regards,
Subbu
Click here to Get Speedy answer or solution

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 3

I meant which events and columns should I choose in Profiler when sql server is running slow?

Lowell SSC Guru Points: 323518 More actions · Answer 4

sunny.tjk (9/23/2010)
I meant which events and columns should I choose in Profiler when sql server is running slow?

well you need TextData and DatabaseID so you Know what command was sent, and then

Duration

StartTime

EndTime

Reads

Writes

CPU

will give you the columns needed to start diagnosing...looking for high Duration , or lot of time between StartTime and EndTime , or high # of Reads or Writes, and maybe how hi the CPU was for the query.

that's where i would start.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!