Performance Tuning
Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)
There's an important rule for tuning stored procedures that's easy to forget: when you're testing queries from procedures in SQL Server Management Studio, execute it as a stored procedure, a temporary stored procedure, or using literal values.
Don't re-write the statemet you're tuning as an individual TSQL statement using local variables!
Where it goes wrong
Stored procedures usually have multiple queries in them. When you're tuning, you usually pick out the most problematic statement, maybe from the query cache), and tune that.
