| Author |
Topic |
|
MuadDBA
628 Posts |
 Posted - 2004-01-28 : 14:46:34
|
| My query governor doesn't seem to be governing. In a previous post I detailed how certain people were usng an application (soon to be replaced) improperly and doing a complete tablescan on my 65 million row database. This invariably takes in excess of 2,000 seconds to complete.So I set my query governor at 300, which is supposed to be 300 seconds, and then for fun I executed a select * from my table. It ran for 8 minutes (or 480 seconds) before I killed it, and I am wondering why it didn't get killed by the governor?I verified with SP_CONFIGURE that 300 is the running value for the governor. I am using SQL 7.0 SP3. |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2004-01-28 : 14:48:30
|
quote:
So I set my query governor at 300, which is supposed to be 300 seconds
Not quite - the governor restricts queries based on cost, not duration. The restriction occurs at compilation. A cost of 300 is quite high and lets virtually anything run. Try 5.JonathanGaming will never be the same |
 |
|
|
MuadDBA
628 Posts |
Posted - 2004-01-28 : 14:51:39
|
Directly from books online:quote:
The term query cost refers to the estimated elapsed time, in seconds, required to execute a query on a specific hardware configuration. Use the query governor cost limit option to specify an upper limit for the time in which a query can run
Also when I do an execution plan, it says the estimated cost is 2687, much higer than 300. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2004-01-28 : 14:56:08
|
quote:
The term query cost refers to the estimated elapsed time, in seconds, required to execute a query on a specific hardware configuration.
My understanding is that while cost and duration are related, they are not the same. Again, the restriction occurs at compilation - the governor will not "kill" a SPID when its duration exceed your cost threshold, it will simply not allow the SPID to execute the given buffer in the first place."Estimated" means just that - it's a guess. The duration of a query depends on external factors such as load on the machine at time of execution, network bandwidth available, etc.JonathanGaming will never be the same |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-01-28 : 14:59:49
|
| Wouldn't it be great if they'd just tell you what the hell it looks at to determine what the governor will kill and what it won't? I only paid 20 grand or so for the server lisence plus CALs....Since this is my production server, I don't think I'll set it to 5 just yet. I'll gradually reduce it and see what happens.I guarantee you that my server is completely incapable of delivering 65 million rows in less than 5 minutes. Even the execution plan knows that. Why the governor doesn't know it, well, maybe it's the governor of California :) |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2004-01-28 : 15:02:33
|
| This is one of the many reasons you should consider replication - move those users off the production box and let them happily slam another resource.JonathanGaming will never be the same |
|
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2004-01-28 : 15:54:56
|
| BOL: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. Sounds like it is supposed to be in seconds to me. There is nothing I hate more than when documentation says one thing and the software does something else.Is there some type of index you could create to aliviate the table scan? Can you post the query and the table structure?- Eric |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-01-28 : 15:56:58
|
| when someone does select * from the table, it pretty much does a table scan :)Also when someone uses wildcards inappropriately, ie "LIKE %1234' it does a tablescan. Nothing I can do tablewise to avoid this, just try to hunt down users and kill them. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2004-01-28 : 15:57:15
|
| That's not the right approach, IMO, you could run around forever optimizing adhoc queries against your production box. Replication fixes the issue in one pass.JonathanGaming will never be the same |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-01-29 : 08:29:14
|
| Actually, replication only moves the problem from one server to the next. I may isolate the problem from my workflow users, but the users who are loking up information (which is just as important as the workflow) now get hosed just the same as they used to.Beyond that, the application combines the functionality of workflow and querying, so to seperate it out would be ugly. Fixing the application is the first step, and that's coming in a month or so, but until then I had *thought* that the query governor might be what I needed. Unfortunately it doesn't seem to work as advertised. Not surprising, I suppose. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-29 : 13:04:45
|
quote:
Originally posted by crazyjoe
just try to hunt down users and kill them.
LOL....Good way to clear up any resource issues....Brett8-) |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2004-01-29 : 14:24:26
|
quote:
Actually, replication only moves the problem from one server to the next
I think I misread the intent of your post. The idea of replication vis-a-vis reporting is the elimination of locking/blocking against the production system. I assumed the long query durations were adversely affecting production. If that's not the case then yes, replication will not change the fact that expensive queries take lots of resources to execute.JonathanGaming will never be the same |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-01-29 : 15:33:35
|
| Somethign else interesting....even setting the query governor to "1" doesn't prevent you from doing a select * from the table. Apparently our governor has some pretty big blind spots. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2004-01-29 : 15:52:44
|
| Another question I have for you is what you would do if the query governor worked. Let's say it does everything it's supposed to and it kills queries after they run too long. How would your application react?JonathanGaming will never be the same |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-01-29 : 15:56:24
|
| Well since I can't get it to terminate the queries, I can't tell you how the application would react, because I didn't write it. However, I imagine it would deliver some type of error, a user would call in, and I could go hit them in the kneecaps with a bat. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-01-30 : 16:28:57
|
| As if I needed it to get any better.....apparently if you connect through ODBC, it must somehow set the query governor for that session, because I have now tested it against my aspplication and with Microsoft Access using linked tables and it allows the queries to run even though if I execute them in QA it kills the query. I didn't specify anythign special in the ODBC settings, either, just left them as the default. |
|
|
|
|
What's up with my query governor?