How to estimate query governor cost setting?

To prevent a test (which can poorly written) query for running too long that
exhausts my desktop, how I can prevent it from happening? I am thinking of
setting a limit, which I can tolerate a 4 minutes window but not longer than
that.
If this helps: On the properties of "Use query governor to prevent queries
exceeding specified cost", I need to set the cost, but don't know how much.
Thanks.Hi
From BOL:
query governor cost limit Option
Use the query governor cost limit option to specify an upper limit for the
time in which a query can run. Query cost refers to the estimated elapsed
time, in seconds, required to execute a query on a specific hardware
configuration.
Regards
Mike
"C TO" wrote:
> To prevent a test (which can poorly written) query for running too long that
> exhausts my desktop, how I can prevent it from happening? I am thinking of
> setting a limit, which I can tolerate a 4 minutes window but not longer than
> that.
> If this helps: On the properties of "Use query governor to prevent queries
> exceeding specified cost", I need to set the cost, but don't know how much.
> Thanks.|||Dear Mike,
Thanks but sorry for my confusing question. I still don't know what the BOL
means by that. I do not know how to estimate the cost limitation I need to
set.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> From BOL:
> query governor cost limit Option
> Use the query governor cost limit option to specify an upper limit for the
> time in which a query can run. Query cost refers to the estimated elapsed
> time, in seconds, required to execute a query on a specific hardware
> configuration.
> Regards
> Mike
> "C TO" wrote:
> > To prevent a test (which can poorly written) query for running too long that
> > exhausts my desktop, how I can prevent it from happening? I am thinking of
> > setting a limit, which I can tolerate a 4 minutes window but not longer than
> > that.
> >
> > If this helps: On the properties of "Use query governor to prevent queries
> > exceeding specified cost", I need to set the cost, but don't know how much.
> >
> > Thanks.|||Hi
If you want to limit the query to 4 minutes, (4x60 = 240), so put 240 in the
setting box.
Instead of aborting the query, rather look at why it is not performaing well.
Look at http://www.sql-server-performance.com
Regards
Mike
"C TO" wrote:
> Dear Mike,
> Thanks but sorry for my confusing question. I still don't know what the BOL
> means by that. I do not know how to estimate the cost limitation I need to
> set.
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > From BOL:
> >
> > query governor cost limit Option
> > Use the query governor cost limit option to specify an upper limit for the
> > time in which a query can run. Query cost refers to the estimated elapsed
> > time, in seconds, required to execute a query on a specific hardware
> > configuration.
> >
> > Regards
> > Mike
> >
> > "C TO" wrote:
> >
> > > To prevent a test (which can poorly written) query for running too long that
> > > exhausts my desktop, how I can prevent it from happening? I am thinking of
> > > setting a limit, which I can tolerate a 4 minutes window but not longer than
> > > that.
> > >
> > > If this helps: On the properties of "Use query governor to prevent queries
> > > exceeding specified cost", I need to set the cost, but don't know how much.
> > >
> > > Thanks.|||C TO wrote:
> To prevent a test (which can poorly written) query for running too
> long that exhausts my desktop, how I can prevent it from happening? I
> am thinking of setting a limit, which I can tolerate a 4 minutes
> window but not longer than that.
> If this helps: On the properties of "Use query governor to prevent
> queries exceeding specified cost", I need to set the cost, but don't
> know how much.
> Thanks.
You could also set a query timeout from Query Analyzer or your
application for 4,000ms which forces the command to abort if it's not
completed within the allotted time.
--
David Gugick
Imceda Software
www.imceda.com