471,073 Members | 1,404 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,073 software developers and data experts.

Perfomance Question

I look after a database which is part of a third party CRM product. The
users of the product complain of intermittant poor performance, the
suspicion is that some more senior users are running their own queries
(the product allows users to do this). I've been asked by the
development team to try to capture the details of long running queries.

I've looked at the events listed in profiler and can't see one that
would be useful. Ideally I want to know who is running which query that
is taking longer than x seconds.

Any suggestions

TIA

Laurence

Jun 13 '06 #1
2 961

Personally, I would say 4-6 seconds is to long for a query. You may have a
different expectation.
Run the Profiler for a period of time _ i aim for at least 3 hrs - depnds on
server traffic etc

Events to capture : Stored Procedures--RPC:Completed &&
TSQL--SQL:BatchCompleted
(all sps and t-sql statement)

The critical columns to capture are: Duration and textdata . Others as well
for whatever other analysis you may need

Use the "duration" filter , do it by db id.

Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________

"Laurence Breeze" <i.********@open.ac.uk> wrote in message
news:44**************@open.ac.uk...
I look after a database which is part of a third party CRM product. The
users of the product complain of intermittant poor performance, the
suspicion is that some more senior users are running their own queries
(the product allows users to do this). I've been asked by the
development team to try to capture the details of long running queries.

I've looked at the events listed in profiler and can't see one that
would be useful. Ideally I want to know who is running which query that
is taking longer than x seconds.

Any suggestions

TIA

Laurence

Jun 13 '06 #2
Thanks Jack,

I'll give that a go.

Laurence

Jack Vamvas wrote:
Personally, I would say 4-6 seconds is to long for a query. You may have a
different expectation.
Run the Profiler for a period of time _ i aim for at least 3 hrs - depnds on
server traffic etc

Events to capture : Stored Procedures--RPC:Completed &&
TSQL--SQL:BatchCompleted
(all sps and t-sql statement)

The critical columns to capture are: Duration and textdata . Others as well
for whatever other analysis you may need

Use the "duration" filter , do it by db id.

Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________

"Laurence Breeze" <i.********@open.ac.uk> wrote in message
news:44**************@open.ac.uk...
I look after a database which is part of a third party CRM product. The
users of the product complain of intermittant poor performance, the
suspicion is that some more senior users are running their own queries
(the product allows users to do this). I've been asked by the
development team to try to capture the details of long running queries.

I've looked at the events listed in profiler and can't see one that
would be useful. Ideally I want to know who is running which query that
is taking longer than x seconds.

Any suggestions

TIA

Laurence



Jun 14 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by TGF | last post: by
2 posts views Thread by news.onet.pl | last post: by
5 posts views Thread by Scott Reynolds | last post: by
2 posts views Thread by James T. | last post: by
4 posts views Thread by =?Utf-8?B?VmVlcmFiaGFkcmFpYWggTCBN?= | last post: by
4 posts views Thread by Alexander Adam | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.