SQL Server Performance Forum – Threads Archive

Tuning a stored proc

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

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".

See these are any help:
http://sqlserver-qa.net/blogs/perft…owcount-affects-the-performance-sql-2005.aspx
http://sqlserver-qa.net/blogs/perft…ver-2005-how-to-find-a-rarely-used-index.aspx 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.

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

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

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.

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=’

‘ /> 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!"

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

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/

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=’

‘ /> 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!"

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

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/

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

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.

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

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

]]>