Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
LearningSQLKid
Yak Posting Veteran
51 Posts |
 Posted - 2012-12-24 : 03:37:16
|
| Hi DBA'sI 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.DB02The 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 Server02.DB02 to use same execution plan as of Server01.DB02Please helpThanks in advanceSelect Knowledge from LearningProcess |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-24 : 07:33:43
|
| Do they have the same hardware configuration?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. |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-24 : 11:26:12
|
| IS Memory configuration same and have you done rebuild/reorganize indexes and statistics as mentioned? |
|
|
|
james_wells
Yak Posting Veteran
55 Posts |
Posted - 2013-01-15 : 09:38:01
|
| 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