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 |
|
jthg
Starting Member
5 Posts |
 Posted - 2007-08-17 : 11:00:23
|
| I know that the query governor works off of estimated query plans, but the estimation is usually so far off from reality that it might as be generating a random number to determine whether a query should run.Right now I have query governor set at 300. I've been able to run queries that take more than 10 minutes without any complaint from query governor. What's really annoying though, is that it has also blocked queries that take less than a second to run. It blocked a query for having an estimated executing time of 338 seconds. When I set the limit up to 400, the query ran virtually instantly.Is this just how query governor is suppose to work? Is there any way to make it work better? |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-08-17 : 12:19:05
|
| In my opinion "300" is not "300" seconds....it's a "Effort Score" which the SQL engine calculate the Query will cost....1 < 300 < 50000...On this basis the 3rd query (50000) is guessed by the engine to take more "resources" than the 1st two, and possibly in the same ratio....but "resources" may not = "time".What does MSDN or BOL say on this front? |
 |
|
|
jthg
Starting Member
5 Posts |
Posted - 2007-08-17 : 13:18:37
|
| According to BOL, it's seconds."Use the query governor cost limit option to specify an upper limit on the time period in which a query can run. Query cost refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration." |
|
|
|
askben
Starting Member
8 Posts |
Posted - 2007-08-17 : 23:16:42
|
| What this should mean to you is that your statistics are out of date and that reorganizing your databases in a maintenance plan should be done.Just because your governor is not stopping queries does not mean that the estimator is flawed or the governer is not working, it indicates that your data is telling a different story.Update your statistics on your tables and do a reorganize on your database and then see what the query governer does.Ben.------------Ben Miller |
|
|
|
|
|
|
Query governor pretty useless?