Long Running Queries in Procedure

derek@.programmer.net May 19, 12:57 pm show options
From: "d...@.programmer.net" <d...@.programmer.net> - Find messages by this
author
Date: Thu, 19 May 2005 09:57:29 -0700
Local: Thurs,May 19 2005 12:57 pm
Subject: Long Running Queries in Procedure
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse
Hi,
Has anyone ever seen a query take longer in a stored procedure than it
does when running in Query Analyzer? I have a set of about 10 queries
that run for 18 minutes when running in a schedule job (which runs a
stored procedure) but only for about 4 minutes when I run them manually
in Query Analyzer.
I'm trying to trim time out of a daily procedure that takes about 3
hours and I've already done some performance tuning - that's why I know
how long they take in Query Analyzer. However, when I time how long
the individual portions of the proc run - I get different results than
those I receive when I run the queries myself.
Any thoughts?
DerekUse SQL Server Profiler to monitor performance related events. Whatever, the
issue it will turn up there.
"Derek Fisher" <DerekFisher@.discussions.microsoft.com> wrote in message
news:E1976E3E-7CF3-4475-8600-6FEFA92370B8@.microsoft.com...
> derek@.programmer.net May 19, 12:57 pm show options
> From: "d...@.programmer.net" <d...@.programmer.net> - Find messages by this
> author
> Date: Thu, 19 May 2005 09:57:29 -0700
> Local: Thurs,May 19 2005 12:57 pm
> Subject: Long Running Queries in Procedure
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Remove | Report Abuse
> Hi,
>
> Has anyone ever seen a query take longer in a stored procedure than it
> does when running in Query Analyzer? I have a set of about 10 queries
> that run for 18 minutes when running in a schedule job (which runs a
> stored procedure) but only for about 4 minutes when I run them manually
> in Query Analyzer.
>
> I'm trying to trim time out of a daily procedure that takes about 3
> hours and I've already done some performance tuning - that's why I know
> how long they take in Query Analyzer. However, when I time how long
> the individual portions of the proc run - I get different results than
> those I receive when I run the queries myself.
>
> Any thoughts?
>
> Derek
>
>|||Did you looked for locks on tables ? Are your queries very "cost" intensive
or "lock" intensive ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Derek Fisher" <DerekFisher@.discussions.microsoft.com> schrieb im
Newsbeitrag news:E1976E3E-7CF3-4475-8600-6FEFA92370B8@.microsoft.com...
> derek@.programmer.net May 19, 12:57 pm show options
> From: "d...@.programmer.net" <d...@.programmer.net> - Find messages by this
> author
> Date: Thu, 19 May 2005 09:57:29 -0700
> Local: Thurs,May 19 2005 12:57 pm
> Subject: Long Running Queries in Procedure
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Remove | Report Abuse
> Hi,
>
> Has anyone ever seen a query take longer in a stored procedure than it
> does when running in Query Analyzer? I have a set of about 10 queries
> that run for 18 minutes when running in a schedule job (which runs a
> stored procedure) but only for about 4 minutes when I run them manually
> in Query Analyzer.
>
> I'm trying to trim time out of a daily procedure that takes about 3
> hours and I've already done some performance tuning - that's why I know
> how long they take in Query Analyzer. However, when I time how long
> the individual portions of the proc run - I get different results than
> those I receive when I run the queries myself.
>
> Any thoughts?
>
> Derek
>
>|||JT,
This will probably sound like a stupid question, but I'm unfamiliar with
using Profiler. Do you have any suggestions on how to setup the trace - wha
t
events to add, etc? If not, any suggestions on helpful articles about the
Profiler?
Derek
"JT" wrote:

> Use SQL Server Profiler to monitor performance related events. Whatever, t
he
> issue it will turn up there.
> "Derek Fisher" <DerekFisher@.discussions.microsoft.com> wrote in message
> news:E1976E3E-7CF3-4475-8600-6FEFA92370B8@.microsoft.com...
>
>|||Jens,
I'm not sure how to know. How would I tell?
Derek
"Jens Sü?meyer" wrote:

> Did you looked for locks on tables ? Are your queries very "cost" intensiv
e
> or "lock" intensive ?
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Derek Fisher" <DerekFisher@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:E1976E3E-7CF3-4475-8600-6FEFA92370B8@.microsoft.com...
>
>|||You can use the statemtent sp_lock and sp_who and sp_who2 to identify the
blockings issue (if you have one). ANother oprtion would be to monitor the
events in the profiler, just check on the lock properties to monitor. If you
are not familiar with the profiler you can look in the current activitie in
your EM, to see wheter a lock or perhaps deadlocks occured.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Derek Fisher" <DerekFisher@.discussions.microsoft.com> schrieb im
Newsbeitrag news:C80C8BFB-3227-4306-AB80-B7DB2F34B695@.microsoft.com...
> Jens,
> I'm not sure how to know. How would I tell?
> Derek
> "Jens Smeyer" wrote:
>