Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    2

    Exclamation Unanswered: Query Analyzer -> subtree cost vs. execution time

    I am using a stored procedure that is behaving badly - the subtree cost is about 2000 and it takes between 3-4 seconds to run, and sometimes it takes over a minute to run. I have made some optimizations that cause the stored procedure to run in generally under 1 second (at most under 2 seconds), but the subtree cost of it jumps to 4000!! All of this while the server was experiencing similar load (the tests were done within minutes of each other).

    I know that the subtree cost is a way to gauge the performance of a query against other queries, but I have typically seen the cost go in the same direction as the execution time (they both go up or the both go down).

    How does SQL Server determine the cost (I know that is based on statistics, but I was wondering if anyone had more details)? Is it more important to have a lower subtree cost, or a lower execution time? Am I going to get into trouble later with this high subtree cost?

    I would appreciate any help on this matter.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2004
    Posts
    2
    Unforunately, the article does not help - it doesn't go into enough detail for me.

    Any other ideas?

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Taken directly from the article:


    Each component operation has an I/O cost and a CPU cost. The full cost of the component operation is the sum of the I/O and CPU cost only if the number of executes is one. When the number of executes is more than one, the cost is not simply the sum of the I/O and CPU costs multiplied by the number of executes. The subtree cost includes the cost of all operations that feed into to this specific component operation.

    The unit of measure for the SQL Server execution plan cost is not documented. It is known that the cost values are the same on a given platform for both SQL Server 7.0 and SQL Server 2000, including Standard and Enterprise editions, and all service packs. The I/O costs for the Index Seek and Bookmark Lookup operations depend on the whether the system has up to 1GB of memory or more than 1GB of memory. The CPU cost is the same for all platforms tested. Earlier versions of this document attributed the I/O cost variation to the number processors, which is now known not to be correct.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •