none
Query estimated time

    Question

  • I found there's an server option, query governor cost limit , specifying an upper limit on the estimated time in which a query can run.
    My doubt is about the 'estimated time'. How sql server determines the estimated time of a sql statement? Any run-time factors does it consider, e.g. CPU & memory usage, locks, rowcounts? How often does it makes estimation for a cached query? If a query is analyzed by sql server that its estimated time is above the query governor cost limit, doesn't mean it never be executed?
    Monday, April 06, 2009 8:47 AM

Answers

  • Hi Nono,

    Interesting question.

    It should certainly be possible, if for example, a new actual execution plan is created for the given query that results in a cost value that is below your threshold.



    John Sansom | SQL Server Consultant for Santech Solutions
    • Marked as answer by nonno Tuesday, April 07, 2009 9:07 AM
    Tuesday, April 07, 2009 8:52 AM

All replies

  • Hi Nono,

    The Query_Governor_Cost_Limit does not accept time as a parameter. It accepts a cost threshold value.

    The estimated cost for a given query is determined as a result of calculating the estimated execution plan for a query.

    This is a complex subject area, I suggest you consult the excellent freely available White Paper on Dissecting SQL Server Execution Plans. The first few chapters will provide details of concepts that you need to become familiar with in order to understand your problem.

    A reference to the document is available on my site, 'All About Execution Plans'.

    http://johnsansom.com/index.php/sql-server-resources/sql-server-performance/


    Hope this helps.

    Cheers,
    John Sansom | SQL Server Consultant for Santech Solutions
    Monday, April 06, 2009 3:10 PM
  • Thanks for your reply, John. That white paper is excellent!
    By the way, the SQL Server 2008 BOL says 'Query cost refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration'. Did I misunderstand something?
    Tuesday, April 07, 2009 2:13 AM
  • Hi Nono,

    Just ignore me, I am unecessarilly confusing the issue and you are correct in this case.

    If you're interested in learning more about the Query Optimizer's interpretation of time, some of the discussion from the following blog makes for interesting reading.

    http://sqlblog.com/blogs/joe_chang/archive/2008/03/29/execution-plan-costs.aspx
    John Sansom | SQL Server Consultant for Santech Solutions
    Tuesday, April 07, 2009 7:44 AM
  • Thanks again, John.
    Actually I just want to know if a long-running query was rejected to run by the query governor due to the cost limit, is it possible that the same query can run next time, let's say after system workload reduced?
    Tuesday, April 07, 2009 8:43 AM
  • Hi Nono,

    Interesting question.

    It should certainly be possible, if for example, a new actual execution plan is created for the given query that results in a cost value that is below your threshold.



    John Sansom | SQL Server Consultant for Santech Solutions
    • Marked as answer by nonno Tuesday, April 07, 2009 9:07 AM
    Tuesday, April 07, 2009 8:52 AM