Friday, March 30, 2012

optimizing resource allocation

Hi All,
Some of the queries we are running are resource intensive which is impacting
other users running other processes on the same SQL server. Is there a way
to limit the time allocated to a query so it pauses after x seconds in order
to allow other processes to run? Please note that the query should not be
permanently interrupted but temporarily halted.
I have checked the "query governor" option but it seems that this parameter
would disallow running the query if it exceeds the cost limit.
RegardsHi
Perhaps it's time to start to optimize your queries.Compare an execution
plan , make sure that the optimizer is available to use indexes defined on
the tables.
"imad koussa" <imad.koussa@.aims-co.com> wrote in message
news:uu6kex0FGHA.2444@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Some of the queries we are running are resource intensive which is
> impacting other users running other processes on the same SQL server. Is
> there a way to limit the time allocated to a query so it pauses after x
> seconds in order to allow other processes to run? Please note that the
> query should not be permanently interrupted but temporarily halted.
> I have checked the "query governor" option but it seems that this
> parameter would disallow running the query if it exceeds the cost limit.
> Regards
>|||Basically no there is no easy or practical way to do that. But if the
resources are CPU you should consider using a MAXDOP hint in those queries
to limit the number of CPU's they can use in parallel.
--
Andrew J. Kelly SQL MVP
"imad koussa" <imad.koussa@.aims-co.com> wrote in message
news:uu6kex0FGHA.2444@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Some of the queries we are running are resource intensive which is
> impacting other users running other processes on the same SQL server. Is
> there a way to limit the time allocated to a query so it pauses after x
> seconds in order to allow other processes to run? Please note that the
> query should not be permanently interrupted but temporarily halted.
> I have checked the "query governor" option but it seems that this
> parameter would disallow running the query if it exceeds the cost limit.
> Regards
>

No comments:

Post a Comment