SQL Server Performance Forum – Threads Archive
Tuning Stored Procs, subtree cost, cpu time, reads
Does anyone know how exactly the sub-tree cost is calculated and whether it is a sufficient metric for measuring query/stored procedure performance? I used to think (although I could never find a single definitive source to state it clearly and precisely) that a sub-tree cost is some function of (reads + cpu time). However, my recent experience appears to challenge this belief. Namely, I have a few versions of a stored procedure, where each version does the same thing, but differently. Ver 1: # reads = 2 million and cpu_time = 19,707 and sub-tree cost = 0.1171Ver 2: # reads = 1.5 million and cpu_time = 4,371 and sub-tree cost = 0.13
Ver 3: # reads = 154,000 and cpu_time = 935 and sub-tree cost = 0.25
Ver 4: # reads = 165,000 and cpu_time = 230 and sub-tree cost = 0.228 If I assume the sub-tree cost to be the benchmark, then I should go with Version 1. However, looking at other parameters, it it obviously the worst choice. So, the question is, what is the best benchmark for judging performance ?
Thanks a lot
cpu
Joe, Could you elaborate a bit more as to what makes you believe that "cpu" is the best benchmark for judging performance? Thanks a lot
cpu is not the only thing you need to consider. I/O is equally important I would say in many cases you can be constrained by I/O, not cpu. SqlSpec – a fast, cheap, and comprehensive data dictionary generator
for SQL Server 2000 and 2005 and Analysis Server 2005 – www.elsasoft.org
]]>