• dajonx (1/14/2011)


    Hi,

    I was wondering what information is important for you in regards to tuning a query. Is my ranking in terms of importance accurate?

    1) Elapsed Time - SQL Server Execution Time

    2) CPU Time - SQL Server Execution Time

    3) Physical Reads (Potential I/O Contention)

    4) Logical Reads (Data from Cache)

    5) Read-Ahead Reads (I'm not sure...)

    Thank you!

    Pretty hard to give an exact on that. 😉 First though I would add that parse and compile time are important as well. An overly complex plan can be problematic to the optimizer, especially if that plan is being removed from cache.

    I personally go through the list when I look at the output of statistics time and IO. I start with the parse and compile, look at the reads and then CPU and Elapsed time.

    Ultimately they are all important and all are necessary for proper tuning. You want to make sure you have your query as low as it can go in all areas if this is a frequently run query. Example, you could have a query that is generating a bunch of logical reads due to a loop join. You force a hash join and see the reads go down but now your CPU time has doubled. Guess what, you probably don't want that and more than likely you can get the reads down further and keep the CPU down by reworking the query a bit.

    So, use them all. Take you time and get the query to perform in all categories.

    Just my thoughts.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot