SET QUERY_GOVERNOR_COST_LIMIT

Question

Post reply

SET QUERY_GOVERNOR_COST_LIMIT

Farrell Keough

SSCoach

Points: 17414
More actions
October 5, 2005 at 8:03 am

#87961

I can find very little information about this setting. Other than trial and error for setting the "value", I do could not find anything for determining what an optimum 'general' value might be for using this. (is that a dangling participle?).
If anyone knows of a good technical definition I would very much appreciate having that reference. It is something we may use, (it was suggested via another thread about Dynamic SQL) but we need more information as to what settings to choose.
TIA.
I wasn't born stupid - I had to study.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

jhklink Mr or Mrs. 500 Points: 582 More actions · Answer 1

Farrell,

I found a good link which might help you.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_73u6.asp

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.

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

Thank you! That seems to be only one of the two references on MSDN.

I was hoping to learn more about how to determine a general value for the upper limit. Right now it seems to be trial and error, which is a horrible approach.

Hopefully someone else will know the inner workings or we may just happen to hit upon the optimum number.

I wasn't born stupid - I had to study.

noeld SSC Guru Points: 96590 More actions · Answer 3

Hi, its me again

Let me again try to persuade you from following that path because query costs are very relative and depend not only on the Hardware platform used but also on the datadistribution. The Actual Formulas are really not disclosed openly by MS (for a good reason ).

If I still didn't convinced you, here is an article that tries to come up with formulas for determining the query cost. Something you may be spared from doing in 2005 because you can get read the plan back from the engine before you execute the query

Cheers,

* Noel

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

Thanks Noel. I did not realize you were trying to persuade me not to use this method.

Unfortunately, that leaves me back at space 1 with no solution to preventing Infinite Loops from a Dynamic SQL statement spawn from a front-end ad hoc reporting page...

Any ideas on other paths to pursue?

Thanks

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

October 6, 2005 at 11:22 am

#595573

command timeout??

Farrell Keough SSCoach Points: 17414 More actions · Answer 6

Thanks Remi. We already thought of that option, but it is too restrictive, (not dis-similar to my KILL approach if the SPID runs too long).

On occassion there are legitimate reports, (over multiple years) that can take a while. And during certain fiscal seasons, more reporting will be run, hence more workload on the CPU's.

You are right that this may end up being our only option. But I was hoping for something that handled the specific situation of an infinite loop over something that just ran too long...

Maybe there is a way to recognize if a query is calling the same, (or an extremely similar) dataset over and over... I dunno. I am rather dumbfounded on this one - a mindset I seem to be falling into more and more as I get older.... (that and hair growing in new places that needs trimming... [ ooops, TMI ] ).

I must admit to being surprised that this has not arisen for someone else as ad hoc reporting is a common practice. Maybe people are not recognizing it or they have built amazing CASE statements for their ad hoc reports?

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Just a question... How would someone generate such an infinite loop?

Why not block that by forbidding the use of the while keyword in the query?

Farrell Keough SSCoach Points: 17414 More actions · Answer 8

I started a new thread on this for information on QUERY_GOVERNOR_COST_LIMIT.

My original thread indicated this is not my data, so I am not familiar with it. Basically, I was told that the 'potential' exists for someone entering two dates that could somehow conflict and start an infinite loop. The potential may be close to zero, but if that argument cannot be proven false, then we have to code for its possibility.

I think the long and short is, the DBA wants to make sure nothing could cause such a circumstance, (and rightly so) which could bring down the database. I believe WHILE statements are already not allowed.

So, basically, I cannot answer your question directly because this is not my stuff. But it is of great interest to me and I have been asked to look into it. Its just too bad my tag line is actually true!!!

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

How could a pair of date cause a loop in a query???. The condition is either true or false, how can this generate a recursing/looping algorithm of some kind??

Farrell Keough SSCoach Points: 17414 More actions · Answer 10

Damned if I know. This is one of those prove you did not kill Kennedy, (i.e., prove a negative). As I have stated in the past, our DBA is a little excessive... This was the "off of the top of the head" example. Wish I could be more specific, but like I said, it ain't my data...

(p.s., I like the moniker sushila got you!)

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

I like it too. As for Keneddy I wasn't born so that one's pretty easy for me. Who's gonna build the queries? How are they building them?

Farrell Keough SSCoach Points: 17414 More actions · Answer 12

Young punk.... (of course I was just a child..., but I 'may' have been able to pull a trigger...).

I think they are using .NET and will basically have check boxes, (which should cause no problem) and date fields. But I really have no true knowledge of the design.

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

I think that the answer will lie there. I doubt it's possible to cover 100% over the possible issues here but 99.99% may be possible . Let him do his job for the other 0.01%.