I run into a very strange problem this morning which is driving me crazy.
I've two databases with 100% identical schema. There is a stored procedure in Server01.DB01 which runs just in 2 sec and displays the result. However the same stored proc takes forever to on DB02 which is on Server02.DB02
The number of records in both the database are almost, no big difference. Indexes are same , infact everything is same.
I don;t know what to do.. Is there any way where i can ask Server 02.DB02 to use same execution plan as of Server01.DB02
If that is not the issue, check if statistics are updated on the slow server - see here for how to do that.
If statistics have been updated, compare the query plans to see if they are the same.
If they are not, recompile the stored procedure on the slow server and see if that helps.
You can get the query plan from the fast server and give that as a query hint (but there are limitations), but I would reserve that option as a last resort.
i had a similar issue where a query was running slower on the bigger better beast.
This was down to parameter sniffung and resolved by masking the passed parameters local to the stored procedire. Simply declare a local veriable of the same type and size of the passed parameter snd store the passed value into the local variable.
the local variable is then used in the TSQL contained in the stored procedure.
Stored Procedure Optimization
Posted - 12/24/2012 : 03:37:16
