Huge performance issue with Stored Procedure instantly fixed when Database Engine Tuning Advisor run
I have a very interesting scenario. I have a stored procedure that when I first update it (using ALTER) it runs incredibly slow - I left it for 40 minutes with no results. However when I run the "Database Engine Tuning Advisor" against the query, it suddenly takes 10-15 seconds. However when I update the stored procedure again (using ALTER) it takes the huge amount of time.
Things too note:
- Each time I am not chaning the stored procedure in any way
- When I run the "Database Engine Tuning Advisor", i don't apply any of the index or statistic options they recommend. I just anlayse the query, see the recommendations and close the program.
Why is this? Obviously the database engine tuning advisor is doing something - but what? and can I automate it?
Also I have disabled parameter sniffing in my stored procedures by copying all parameters to local variables at the start.
Thanks for any input.
The index tuning wizard creates hypothetical indexes and stats which probably cause a recompile of the proc. This may give it a better plan. If you can post the show plan xml we can see what the problem is. Also there are problems with local variables as described in this whitepaper:
Avoid use of local variables in queries
If you use a local variable in a query predicate instead of a parameter or literal, the optimizer resorts to a reduced-quality estimate, or a guess for selectivity of the predicate. Use parameters or literals in the query instead of local variables, and the optimizer typically will be able to pick a better query plan. For example, consider this query that uses a local variable:
When you execute the stored proc, SQL Server generates an execution plan based on your indexes and statistics available. Now it is possible that the stored proc inserts a lot of rows into some table which is used in another statement in a join and the plan that was built is not efficient for the subsequent sql statement because the profile of the table has changed since the plan was generated. I would suggest splitting the stored proc into two and the split should be done such that the second proc deals with the updated table. When the second proc is executed, it will have a more realistic execution plan for the updated table.
When the proc is run from the Tuning Advisor, the mechanics are different and I suspect it executes line by line and it considers each sql statement dynamically, so it always working with the latest plan.
Thanks for both of the helpful replies.
Chiraj - in response to your message when I run the stored procedure again *after* I have used the Tuning Advisor it is still quick. i.e. the Tuning Advisor does something - not sure what tho? - that speads it up until I alter the actual procedure, or remove and recreate it. Quite bizarre...