Perfomance Question

[Laurence Breeze]

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

[Jack Vamvas]

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

[Laurence Breeze]

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