SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Stored Procedure Optimization
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LearningSQLKid
Starting Member

Hong Kong
45 Posts

Posted - 12/24/2012 :  03:37:16  Show Profile  Reply with Quote
Hi DBA's

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


Please help

Thanks in advance

Select Knowledge from LearningProcess

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/24/2012 :  07:33:43  Show Profile  Reply with Quote
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.

Edited by - sunitabeck on 12/24/2012 07:34:35
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/24/2012 :  11:26:12  Show Profile  Reply with Quote
IS Memory configuration same and have you done rebuild/reorganize indexes and statistics as mentioned?
Go to Top of Page

james_wells
Yak Posting Veteran

53 Posts

Posted - 01/15/2013 :  09:38:01  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000