Statistics Information For Performance Tuning

  • 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!

  • 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

  • Thank you, David!

    I understand that all of the categories are important, but is there some sort of "ranking" that I could use as a guideline? In your example, you said that I probably wouldn't want CPU time to go up which I agree. So, I guess in a way, you could rank CPU time as #1 in terms of importance?

    I completely understand that there will have to be some sort of give and take. I just want to be certain that my order of importance is correct. By the way, I know I have a LOT to read to improve myself in this area of query tuning.

  • Sincerely I would be hard pressed to rank them. In my present environment we place a high emphasis on reads due to the volume of activity that we have on disk and we can have some exceptions in the area of CPU since we have "room to spare" so to speak.

    Me personally, I would rank reads and cpu highest, in that order. I'm sure you could probably get a bunch of different answers though.

    David

    @SQLTentmaker

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

  • dajonx (1/14/2011)


    Thank you, David!

    I understand that all of the categories are important, but is there some sort of "ranking" that I could use as a guideline? In your example, you said that I probably wouldn't want CPU time to go up which I agree. So, I guess in a way, you could rank CPU time as #1 in terms of importance?

    I completely understand that there will have to be some sort of give and take. I just want to be certain that my order of importance is correct. By the way, I know I have a LOT to read to improve myself in this area of query tuning.

    Personally, execution time (from call to results) is almost always my priority. That can encapsulate a lot, so the rest of them are on a per-issue basis. They will get ranked according to your system's usage more than any 'set' list, as David mentioned above. If you've got a heavy transactional system, disk I/O will become important, where as if you're doing a ton of sorting CPU and memory may be more important.

    It's a matter of isolating your primary bottlenecks and working to make sure they don't get overwhelmed.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Craig Farrell (1/14/2011)


    It's a matter of isolating your primary bottlenecks and working to make sure they don't get overwhelmed.

    We need a "Like" button on here. 🙂

    David

    @SQLTentmaker

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

  • I agree. Execution time is one of the most important. Not on your list is one of my other favorites, frequency of call. Your longest running proc may be called once a year. You may have a very fast proc that is called a hundred times a second. If you can cut either proc's execution time in half, which would be the better target?

    But, that said, you have to take it all into account. Just tuning for reads may not solve slow performing queries. Just tuning for speed might not tune queries that are accessing too much data. While I do put execution time & frequency at the top of the list, the entire list has to be taken into account.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Ohh, ok. I understand. Thank you very much for shedding some light on this subject for me. If, by chance, you happen to have any literature (links, books, etc) that I can read, I'd greatly appreciate it!

    Thanks again! 😀

  • Hi I am in a similar situation to you in that I am just getting into performance tuning. I have found this a great article:

    http://sqlcat.com/whitepapers/archive/2007/11/19/sql-server-2005-waits-and-queues.aspx

  • dajonx (1/17/2011)


    Ohh, ok. I understand. Thank you very much for shedding some light on this subject for me. If, by chance, you happen to have any literature (links, books, etc) that I can read, I'd greatly appreciate it!

    Thanks again! 😀

    Yes, I can recommend a book on query performance tuning. Look down in my signature.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Kwisatz78 (1/17/2011)


    Hi I am in a similar situation to you in that I am just getting into performance tuning. I have found this a great article:

    http://sqlcat.com/whitepapers/archive/2007/11/19/sql-server-2005-waits-and-queues.aspx%5B/quote%5D

    That is an excellent article. You can also check out Gail Shaw's articles[/url] on Simple-Talk[/url].

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thank you very much! I will read those articles and hopefully be able to grab your book. 😀

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply