SQL Server Performance

Tuning a stored proc

Discussion in 'SQL Server 2005 General DBA Questions' started by satya.sqldba, May 18, 2007.

  1. satya.sqldba New Member

    Hello fellow DBA's

    I am trying to tune a stored proc thats is running for 15 seconds (the first time when it runs).

    When I look at each and every individual query in the stored proc, one particular clustered index scan is taking:

    An I/O cost of 137 , operator cost is 142 and estimated CPU USage is 5. I am just trying to get a perception of good query and bad query. DO these figures mean that this is taking a huge toll on the CPU?

    That particular index scan has a join condition on a view that has 9 million rows. (there is no index created on the on the view).

    When I look at all the other queries in the stored proc, their I/O cost is relatively less compared to this particular one. Is it possible that this particular index scan is causing the stored proc to execute for so long?

    What can be a solution to this? (something like creating an index on the view or moving that particular table to a diff filegrp?)

    Thanks
    Satya
  2. chetanjain04 Member

    Satya,

    There are no thresholds for the cost estimates shown in the exec plan. These are just for comparing the costs vis-a-vis other operators in the query. Generally, you have got to look at which operator that is contributing high percentage to the overall query cost and see if that can be brought down by various tuning techniques like adding indexes, reducing table / index scans, etc.

    Like in your case, I believe one of the costly operation would be the scan on 9 million rows. Are you experiencing upsurge in CPU utilization during the execution of this query? Note that scans will use more CPU resources.

    Regards,

    Chetan


    Best Regards,

    Chetan
    "Calm seas can never make skillful sailors".
  3. satya Moderator

  4. MichaelB Member

    if you are wondering which part of the proc is causing it, I like to do the old "lets see it run". The reason is that I have found the estimates to be misleading. The number of rows is a very good indicator and so would be using SET STATISTICS IO ON and SET STATISTICS TIME ON so you can try those. By "see it run" I want to define:

    on a NON-PRODUCTION test box, run

    DBCC FREEPROCCACHE --(to clear plans from cache)
    DBCC DROPCLEANBUFFERS --(to clear data from cache)
    GO

    before you run anything and use your mouse cursor to highight the proc and F5 it one statement at a time. keep increasing the amount of text (code) you select (with any proc vars set at the begining)and keep track of the time each statement takes. as you pass a part of code that takes longer your time will jump up and you will know the offending statement.

    I would also suggest that if you are using SQL 2005 then you could use the SET STATISTICS XML ON
    statment which is awsome! You need to make sure that you turn OFF the other statistics statements mentioned earlier and make sure you do not use a query plan. What SET STATISTICS XML ON will do is create an eml link you can click on to give you missing indexes for your query the optimizer says it needs. Just find the word "Missing" in the xml that opens up. Go to the following link to find out more. Its like having SQL tell you how to set the indexes. Just make sure you regression test to avoid slowing other queries down my adding those indexes. see the following:

    http://www.sqlservercentral.com/columnists/aingold/2770.asp

    or Satya's blog
    http://sqlserver-qa.net/blogs/perft...ver-2005-how-to-find-a-rarely-used-index.aspx



    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  5. satya.sqldba New Member

    Thanks for the suggestions

    I brought it down from 15 seconds to 3 seconds. I am tyring to tune it further.

    Here is what I have done:

    1. The stored procedure was using a view that is doing a scan on 9 million rows. I tried creating an index on the view, but it wouldn't allow me to do so because the view was created using an outer join and for a view to have index it should not use outer joins.

    2. So I looked into what rows the view was referring to and created non-clustered indexes on them.

    And now it is improved.

    Thanks
    Satya
  6. satya Moderator

    Well done and appreciate your findings in this case, a best example to show how you can knock down the performance issue with the suitable investigation.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  7. MichaelB Member

    One other thought. for maintence reasons, I would recommend using the "include" option on the index. what this allows you to do is store actual data at the leaf level of the index and could keep you from having any bookmark lookups. Include is great for covering indexes. Just a thought<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><br /><br />Michael <br />MCDBA<br /><br />"The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  8. satya.sqldba New Member

    Is 'include' an alternative way of creating covering indexes?

    Consider my case having an index created on 3 columns at a time. Can the same performance be acheived by creating index on one of those colums and 'include' the rest of them (as an alternative to the first way of creating a clustered index)?

    Thanks
    Satya
  9. ndinakar Member

    That is something you have to decide based on the data you have. INCLUDE is NOT exactly an alternative to covering indexes. If you think your column needs to be part of an index it becomes part of covering index. If the data in the column is not worthy of having an index on (not distinctive enough) you could put it in the INCLUDE column.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  10. MichaelB Member

    True, but when it comes to bookmark lookups it is better to use the INCLUDE. The reason is since the data is stored at the leaf level then it doesnt have to even go to look up any table data. sounds like a good covering index to me<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Michael <br />MCDBA<br /><br />"The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  11. satya.sqldba New Member

    The concept of difference between covering index and using 'include' clause in creating an index are still unclear to me.

    When would each be ideal?

    Consider a table t1 with col1, col2, col3 and t2 with col4,col5,col6

    select t1.col1, t1.col2
    from t1,t2
    where t1.col3=t2.col6

    Now, on what columns should the index be created on t1,t2 for the performance to be maximized?

    When would a covering index or index with include clause come into play? Hope my perception towards understanding indexes would get beter by this clarification.

    Thanks
    Satya



  12. ndinakar Member

    Based on the info provided, index on col3 and including columns col1 and col2 in the INCLUDE would help. Index on col3 will help with an index seek and covering the col1 and col2 in include will avoid bookmark lookup so SQL Server doesnt have to go to the data pages for any other info. All the info it needs is in the index pages itself.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  13. satya.sqldba New Member

    Thanks for the info Dinakar

    So we need not worry about the table t2? Or does it make a difference if we create index on t2 too?

    Satya
  14. satya Moderator

    Do you have any covering index on t2?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  15. satya.sqldba New Member

    Nothing as of now, index on what columns on t2 would help in boosting the performance?

    Thanks
    Satya
  16. MichaelB Member

    using SET STATISTICS XML ON should tell you what columns to index on and what columns to just include if the optimizer thinks there is a need.

    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"

Share This Page