profiler Duration event - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

profiler Duration event

Author

Topic

miranwar
Posting Yak Master

125 Posts

Posted - 2003-08-06 : 09:12:49

Hi,

I am currently trying to detect any bottlenecks within our sales database. I am using profiler. One of the few events i am looking at is Reads,Wites and duration. Some of the durations goes into the 100's like 225. Am i right to belive that the sql being fired requires investigation ? basically i need to know what is the acceptable values for Reads,Writes and Duration when executing a SQL statement ? Thanks in Advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-06 : 13:18:02

Duration is in milliseconds. I typically only look at the queries that are over 5000 for Duration. 255 is pretty fast. If you save the trace into a table, you can then perform T-SQL on it:

SELECT TextData, hostname, loginname, Duration --etc...
FROM TraceTable
WHERE TextData IS NOT NULL AND Duration > 5000
ORDER BY Duration DESC

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-06 : 13:28:43

Did you look in to locking?

Brett

8-)

SELECT POST=NewId()

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-06 : 13:49:05

I don't think that there is even a reason to look into locking right now since the queries are completing rather quickly. 255 milliseconds is pretty fast. I think that miranwar is just trying to see if any bottlenecks exist but probably isn't experiencing any slowness. Locking gets quite ugly when looking into it in SQL Profiler.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-06 : 14:18:59

I meant sp_lock...don't think I've looked at it in Prof.

Why is it painful...have to take a look...

Brett

8-)

SELECT POST=NewId()

p2bl
Yak Posting Veteran

54 Posts

Posted - 2003-08-07 : 07:11:12

Should we do this while thers is no obvious bottleneck?

========================
look!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-07 : 13:00:27

You can run it whenever. Just because a bottleneck doesn't exist doesn't mean that you won't find anything that could be done better.

Tara