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]

Re: Perfomance Question


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:BatchComple ted
(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.l.breeze@ope n.ac.uk> wrote in message
news:448ED88F.1 000104@open.ac. uk...[color=blue]
> 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
>[/color]

[Laurence Breeze]

Re: Perfomance Question

Thanks Jack,

I'll give that a go.

Laurence

Jack Vamvas wrote:[color=blue]
> 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:BatchComple ted
> (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.l.breeze@ope n.ac.uk> wrote in message
> news:448ED88F.1 000104@open.ac. uk...
>[color=green]
>>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
>>[/color]
>
>
>[/color]