Query Governor Cost Limit relationship with Time

JoseTeixeira 09/11/2014. 1 answers, 544 views
sql-server resource-governor

Based on Technet, Query Governor Cost Limit refers to the estimated elapsed time, in seconds. This meets this excerpt from the Microsoft SQL Server 2008 Administrator's Pocket Consultant book.

Walking to the book Microsoft SQL Server 2012 Unleashed, pages 30 and 31, the cost limit is proportional to the estimated time, and one should apply a factor (K), to take into account the system's specifics, so it would be K x time(s).

Now, if I take this (somehow older) article, it feels like establishing a relationship between cost limit and time is not doable.

Can I say that in the real world, despite some more rare exceptions, the 2nd approach could be taken as a rule of thumb?

1 Answers

Kin 09/11/2014.

Based on my notes from SQL Server internals (by Kalen Delaney).... below is my understanding

Query Governor Cost limit is the max no. of seconds that a query can run.

So, essentially :

  • a non-zero or a negative value will disallow execution of any query that has an estimated cost exceeding that value.
  • Zero which is default - turns off the query governor and all the queries are allowed to run without any time limit.

Note that the value set for seconds isn’t clock-based. It corresponds to seconds on a specific hardware configuration used during product development, and the actual limit might be higher or lower on your machine.

Also, it should be noted that changing the Query Governor Cost limit will flush the entire plan cache resulting in generating a new plan afterwards.

JoseTeixeira 09/11/2014
+1 important note about flushing the plan cache! An important curiosity; though this article states that issuing a RECONFIGURE will clear the cache plan, it becomes clear in here that only some of them (in which the QG Cost limit is included) actually cause it.
Aaron Bertrand♦ 09/11/2014
@user16484 Yes, that 5-year old article could probably have a better qualifier on that bullet point. I've passed that info along to the author - whether he has any power to see a change through at this point is probably unlikely.

HighResolutionMusic.com - Download Hi-Res Songs

1 Alan Walker

Diamond Heart flac

Alan Walker. 2018. Writer: Alan Walker;Sophia Somajo;Mood Melodies;James Njie;Thomas Troelsen;Kristoffer Haugan;Edvard Normann;Anders Froen;Gunnar Greve;Yann Bargain;Victor Verpillat;Fredrik Borch Olsen.
2 Sia

I'm Still Here flac

Sia. 2018. Writer: Sia.
3 Cardi B

Taki Taki flac

Cardi B. 2018. Writer: Bava;Juan Vasquez;Vicente Saavedra;Jordan Thorpe;DJ Snake;Ozuna;Cardi B;Selena Gomez.
4 Little Mix

Woman Like Me flac

Little Mix. 2018. Writer: Nicki Minaj;Steve Mac;Ed Sheeran;Jess Glynne.
5 Halsey

Without Me flac

Halsey. 2018. Writer: Halsey;Delacey;Louis Bell;Amy Allen;Justin Timberlake;Timbaland;Scott Storch.
6 Lady Gaga

I'll Never Love Again flac

Lady Gaga. 2018. Writer: Benjamin Rice;Lady Gaga.
7 Bradley Cooper

Shallow flac

Bradley Cooper. 2018. Writer: Andrew Wyatt;Anthony Rossomando;Mark Ronson;Lady Gaga.
8 Bradley Cooper

Always Remember Us This Way flac

Bradley Cooper. 2018. Writer: Lady Gaga;Dave Cobb.
9 Kelsea Ballerini

This Feeling flac

Kelsea Ballerini. 2018. Writer: Andrew Taggart;Alex Pall;Emily Warren.
10 Mako

Rise flac

Mako. 2018. Writer: Riot Music Team;Mako;Justin Tranter.
11 Dewain Whitmore

Burn Out flac

Dewain Whitmore. 2018. Writer: Dewain Whitmore;Ilsey Juber;Emilio Behr;Martijn Garritsen.
12 Avril Lavigne

Head Above Water flac

Avril Lavigne. 2018. Writer: Stephan Moccio;Travis Clark;Avril Lavigne.
13 Khalid

Better flac

Khalid. 2018. Writer: Charlie Handsome;Jamil Chammas;Denis Kosiak;Tor Erik Hermansen;Mikkel Stoleer Eriksen;Khalid.
14 Lady Gaga

Look What I Found flac

Lady Gaga. 2018. Writer: DJ White Shadow;Nick Monson;Mark Nilan Jr;Lady Gaga.
15 Deep Chills

Run Free flac

Deep Chills. 2018.
16 Dynoro

In My Mind flac

Dynoro. 2018. Writer: Georgi Kay;Feenixpawl;Ivan Gough.
17 Charli XCX

1999 flac

Charli XCX. 2018. Writer: Charli XCX;Troye Sivan;Leland;Oscar Holter;Noonie Bao.
18 NCT 127

Regular (English Version) flac

NCT 127. 2018.
19 Lukas Graham

Love Someone flac

Lukas Graham. 2018. Writer: Don Stefano;Morten "Rissi" Ristorp;Morten "Pilo" Pilegaard;Jaramye Daniels;James Alan;David LaBrel;Lukas Forchhammer Graham.
20 Rita Ora

Let You Love Me flac

Rita Ora. 2018. Writer: Rita Ora.

Related questions

Hot questions


Popular Tags