Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-31 : 09:13:06
|
| Dale Smith writes "We have a web based dynamic query builder. Problem with this is that if the user selects options that build : SELECT COUNT(*) FROM TABLE1, TABLE2, TABLE3; this returns 2.8 billion record count due to data duplication and permutations. This also causing a server lockup for 30 mins while this command takes place. Is there any possible way to limit the time spent on a query to 1 min, or at least prevent it from running?" |
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2002-05-31 : 09:18:40
|
| Check Books on Line for the "remote query timeout Option". By default it is set to 0 which indicates it process a query until the Chicago Cubs win a world series. You can set it to X number of seconds.hth,Justin |
 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-05-31 : 09:57:08
|
| Another 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. If you specify a nonzero, nonnegative value, the query governor disallows execution of any query that has an estimated cost exceeding that value. Specifying 0 (the default) for this option turns off the query governor. In this case, all queries are allowed to run.If you use sp_configure to change the value of query governor cost limit, the changed value is server-wide. To change the value on a per connection basis, use the SET QUERY_GOVERNOR_COST_LIMIT statement.query governor cost limit is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change query governor cost limit only when show advanced options is set to 1. The setting takes effect immediately (without a server stop and restart).HTHJasper Smith |
|
|
|
Dale Smith
Starting Member
1 Post |
Posted - 2002-06-03 : 13:25:28
|
| Thanks. Much appreciated. This will help a great deal. Starting tests with it now. |
|
|
|
|
|
|
|
|
How can you prevent SQL 7.0 from crashing without using limitations( JOIN, WHERE)?