I'm developing the SQL Server version of an application in which I need
to be able to set the maximum amount of CPU time a query may use before
it will return an error.  With Oracle, I set the CPU_PER_CALL parameter
for the user to the maximum amount of CPU time a query can use before
the server will return an error.  I use this to queue long-running
queries to execute in a seperate process.

Any ideas?

Thanks in advance.
Paul Schofield

Per connection:
SET QUERY_GOVERNOR_COST_LIMIT

Or per server:
sp_configure 'query governor cost limit'

Many thanks for the quick response!

Paul Schofield
Consultant
Software Architects

Note that the query governor only allows or disallows a query to start
executing (based on estimates). Once the query execution has started,
the query governor will not stop it, regardless of the actual CPU use.

Gert-Jan


TheSak wrote:
Show quoteHide quote

And keep in mind it is a global setting not per individual user.

Show quoteHide quote

I need to be able to configure one user to be able to run queries
without a limitation on execution time, but I need to limit execution
time on another user.  Is there any way to accomplish this?

Regards,
Paul Schofield
Consultant
Software Architects

*** Sent via Developersdex http://www.developersdex.com ***

Each connection has it's own ability to set the Timeout for that connection.
So change the connection string, DSN etc. on that client to some amount
other than unlimited.

Show quoteHide quote

Does anyone know how to set this option in the querystring?  I tried
adding "QUERY_GOVERNOR_COST_LIMIT=1" to the querystring, but I got an
error:

System.ArgumentException: Keyword not supported:
'query_governor_cost_limit'.

I can't find any documentation for changing these sorts of settings from
the connection string.

Thanks in advance,
Paul Schofield
Consultant
Software Architects

*** Sent via Developersdex http://www.developersdex.com ***

Why did I say "querystring"?  I meant connection string, of course!

*** Sent via Developersdex http://www.developersdex.com ***

Paul,

It is not the query governor.  That is used for something else and will
affect everyone not just that one connection.  I am not at a place to look
up the answer at the moment but if you state which driver you are using to
connect I can look it up.

Show quoteHide quote

Hi Andrew,

We're using the default driver included with the System.Data.SqlClient
namespace in the .NET Framework v1.1.

Paul Schofield

*** Sent via Developersdex http://www.developersdex.com ***

You should look in the VS help for "ConnectionString" property.  These are
the ones in particular you are interested in:

      Connect Timeout
      -or-
      Connection Timeout
      The length of time (in seconds) to wait for a connection to the server
before terminating the attempt and generating an error.



Show quoteHide quote

Paul Schofield (pnschofi***@yahoo.com) writes:
How does the user submit his queries?

SQL Server as such does not have this concept. However, you can set up
a command timeout from all client APIs. In fact most APIs has a default
timeout of 30 seconds. Note that this is wallclock time and nothing else,
and it is measured until the first piece of data comes back from SQL Server.

How you set this up varies a little from API to API, but I can't recall
that any API exposes this in the connection string. Typically, you define
this timeout on some command object. So depending on how your applicaiton
looks like, it can be a lot of work for you to implement this.

Queries will initially be submitted through a web or mainframe
interface.  If it is determined that the time to execute the query will
exceed a configurable threshold, the query will terminate and the use
will have the option to execute the query in "batch mode".  Under the
batch mode scenario, the query information is queued in the database and
a dedicated Windows service will re-run the queries, persisting the
results into a table in the database.

When a query is performed through the web and mainframe interfaces, any
long-running queries need to time out and stop executing.  When the
Windows service runs the query, it needs to be able to execute a query
of any duration without failing.

Paul Schofield
Consultant
Software Architects

*** Sent via Developersdex http://www.developersdex.com ***

Paul Schofield (pnschofi***@yahoo.com) writes:
As I understand it, the client code for the user and for the Windows
service will be entirely different, so you will just set the command
timeout differently in the two applications.

That is, this problem is best solved outside SQL Server.

Note though that a query could run into a command timeout without executing
for a second, if gets blocked by some long-running transaction.

SImple code error
How Do I Avoid Update Deadlock
ordered insert - select is not inserting in order
Delete Without Writing To Transaction Log
clustered index on a identity field