Is there any way we can be alerted for any sprocs that are being executed on
SQL Server that are running for say > x secs.
So for example if we know that on a good day, all sprocs run for less than a
sec but want to capture any sproc that may run for 5 secs or greater so we
can take a look into it and be proactive before it gets worse and cause
other issues as a result. I can only think of running profiler and then
running some queries against the data captured.
I was hoping if there was any DMVs we could query that may capture this info
or any other way to do so.
Please let me know..
The reason I ask for this capability is that while SQL Server seems to be up
and running, at times we may have a change in query plan and now these
sprocs run a bit slower but we dont get to know about it quickly unless the
customer talks about slowness... so i was hoping to trap it as soon as any
query hits the threshold.Hi Hassan
You may want to try profiling for a period and then analysing the results.
This can be automated if you use a server side trace.
John
"Hassan" wrote:
> Is there any way we can be alerted for any sprocs that are being executed on
> SQL Server that are running for say > x secs.
> So for example if we know that on a good day, all sprocs run for less than a
> sec but want to capture any sproc that may run for 5 secs or greater so we
> can take a look into it and be proactive before it gets worse and cause
> other issues as a result. I can only think of running profiler and then
> running some queries against the data captured.
> I was hoping if there was any DMVs we could query that may capture this info
> or any other way to do so.
> Please let me know..
> The reason I ask for this capability is that while SQL Server seems to be up
> and running, at times we may have a change in query plan and now these
> sprocs run a bit slower but we dont get to know about it quickly unless the
> customer talks about slowness... so i was hoping to trap it as soon as any
> query hits the threshold.
>
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment