SQL Server Performance

Estimated Query Cost

Discussion in 'T-SQL Performance Tuning for Developers' started by Chappy, Dec 1, 2002.

  1. Chappy New Member

    Hi.
    In an estimated execution plan, what is the subtree cost quoted in terms of? Given this, is there any way to roughly determine how long a query will take to run based on its estimated execution plan?

    I realise this would all be very approximate (server load, inaccurate estimated plan etc), but my application allows users to build complex queries which can take from 3 secs to 11 minutes to run. If I can determine that a given query is going to take longer than a minute or so, Id like to spawn it in a different thread and bring up some entertainment for the user in the meantime.

    Thanks
  2. bradmcgehee New Member

    The cost factor you see in an execution plan is an internal figure used by the Query Optimizer and has no real-world parallel, so it can't be used to estimate the amount of time it would take for a query to run. On the other hand, you can use this figure to compare against different veriations of the same query in order to see which one is more costly than another, but this is for comparison only.

    So, based on what I think you are asking, you want a way in order to find out before a query runs how long it will run, then based on the runnig time, do some other action. Based on what I know about SQL Server (and I still don't know a lot) I don't think this is easily possible. I don't know of any way to query the Query Optimizer about its decisions (which can change), and then take an action based on the results of the query. From the top of my head, the only way I can think you might be able to so this is to build some of your own intelligence into your application to make a rough estimate of the time a particular query will take to run, but this would be a lot of work and probably not all that accurate. Hopefully others will have some good ideas.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page