sql_er (10/4/2010)
I think some of this was mentioned to some extent in the replies, but here are my two cents:1. Subtree cost: Not sure how often people use this, but I have been taught to look at this as a measuring stick when tuning a stored procedure. It is supposed to combine all the costs (io/cpu ...) into one, and provide an overall cost figure. Maybe it is not often used because there isn't a good documentation on it [or any at all?] into what the numbers actually mean. I always try to reduce the number, and in my experience an ideal stored procedure would have this number in the range of 0.1 to 0.2 and once it crosses 1.0 it indicates trouble. NOTE:This is for SELECT only stored procedures which are expected to return results in milliseconds.
While you can use these numbers as a measure, because they are just estimates based on the operators chosen by the optimizer and the statistics available to the optimizer, you have to remember that they're just estimates. The values were based, so the story goes, on a developers machine in Microsoft back in 1997-1998, not on any real measure of performance or cost on any modern system. Keep that in mind when you use these numbers. Also remember, these things can tell lies. For example, a multi-statement table valued function has no statistics. Because of this, it's cost, regardless of what it does, is extremely low in the estimates. But in fact it can be a very costly operation.
2. Memory tables: In my experience I try to avoid #temp tables and use @temp tables [i.e. variable tables in memory]. I do this only in cases where the the resulting data to be stored in the temp table is known before hand and is not too big [i.e. 100 rows and just a few small columns]. I found that using #temp tables in sps which are called thousands of times over a period of 1 hour is not a very good thing
Just remember that #temp tables are in memory, exactly the same as @temp tables, and @temp tables can be written out to disk exactly the same as #temp tables. The one difference between the two that is substantial is the lack of statistics in @temp tables. When dealing with very small data sets, say less than 100 rows, like you say, the missing stats are not a problem. As the data set grows, it becomes more and more of an issue.
3. sp_recompile: I use this command when tuning a stored procedure to only take off the execution plan of sp I am tuning from procedure cache. But of course, still better not to do it in production, although good to do in a production like server to have real tuning results [i.e. not to tune for the data size in test environment and then get in trouble once it is pushed to production].
Just so you know, if you alter a stored procedure, the next time you run it, it generates a new plan. You don't need to use sp_recompile to get it to compile a new plan.
4. If a stored procedure has IF/ELSE, where with IF you touch one set of tables and with ELSE another set of tables, I found that breaking it into 3 sps, one 1 main one, which based on IF/ELSE condition calls the appropriate one of the other two would speed things up. I think it is because it fixes the problem of the original sp where it would create one execution plan which would not work in the other case
I used to think this too, but in fact what happens is, you'll get multiple plans, regardless of how you work it. You'll get a plan for the wrapper, and then a plan for each individual SELECT statement. You can validate this using the DMOs for execution plans.
5. Index Rebuild: In a production server where data gets deleted/inserted a lot (like ours), indexes would need to be rebuild periodically [ideally, daily]. Otherwise, you might be tuning an sp which takes 10 seconds, which would be taking milliseconds if you simply rebuilt your indexes.
Thanks!
"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