martini60123
asked on
using query governer to limit long running queries in MS SQL 2005
We have a MS Access aplication that uses MS SQL 2005 as back end. We have a client that locks up when a particluar query is run. It does not happen all the time but when it does it appears that this query is the culprit. We are rewriting the query for the long term fix. For the short term i wanted to know if using the query governer to kill the query if runs too long (over 10000 ms) will do the trick? or am i just barking up the wrong tree?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
mastoo
Unless it's a pass-through query in Access, the sql you see may not bear any resemblence to what actually runs on Sql Server. Access may decide to run a couple of quick queries to get the data it needs and then process the results locally, instead of letting the server do it. This locks up the client with little load on the server, so the governor won't kick in. You might run sql profiler to watch what sql is actually sent to sql server when executing the access query.