SQL Server installed on identical Hardwares and Database, but vary in working time

Hi Experts,

I am seeing a strange behavior with 2 SQL servers, but couldn't figure out the reason behind it, so I wonder if anyone has faced the same problem and can give me some advices...

I am monitoring job performances on some servers, and one sql server gets the job done in about 1hr45mins, while another server takes almost 2hrs45mins to complete.  These servers have the same hardware configurations, and they hold the same database and structure (redundant db servers for serving a load-balanced website).

I tried to pin-point the exact location where they have major processing time difference, and it came to a loop where the jobs are doing data manipulation.  (sorry, I am not a sql developers, and hence I know little of what the job does).  This job could be done on the quick server in about 7 minutes, but on the slow server, it takes over an hour for it to get through. Below is the SQL part of the job, I couldn't understand why there can be so much time difference between them... and since there are dependencies on it, it's late completion has other impact on other jobs as well...

****
open see_also_class_cursor_branch_c
fetch next from see_also_class_cursor_branch_c into @branch_id,@class_count
while @@fetch_status=0
Begin
         declare see_also_class_cursor_class_c cursor  for
         select distinct class_id from searchresult_basket_class_companyorder_business_working_c where branch_id=@branch_id
         open  see_also_class_cursor_class_c
         fetch next from see_also_class_cursor_class_c into @class_id
         while @@fetch_status=0
                 Begin
                         set @see_also_class_desc=''
                         declare see_also_class_cursor_see_also_class_desc_c cursor  for  
                         select distinct ltrim(rtrim(class_desc)) as class_desc from searchresult_basket_class_companyorder_business_working_c where branch_id=@branch_id  and class_id<>@class_id
                         open see_also_class_cursor_see_also_class_desc_c
                         fetch next from see_also_class_cursor_see_also_class_desc_c into @class_desc
                         while @@fetch_status=0
                         Begin
                                  set @see_also_class_desc=@see_also_class_desc + '  ' +  @class_desc

                         fetch next from see_also_class_cursor_see_also_class_desc_c into @class_desc
                         End
                         close see_also_class_cursor_see_also_class_desc_c
                        deallocate       see_also_class_cursor_see_also_class_desc_c
                         set @see_also_class_desc=ltrim(rtrim(@see_also_class_desc))
                        update searchresult_basket_class_companyorder_business_working_c set see_also_class=@see_also_class_desc
                        where branch_id=@branch_id and class_id=@class_id

                        fetch next from see_also_class_cursor_class_c into @class_id
                  End
          close  see_also_class_cursor_class_c
          deallocate  see_also_class_cursor_class_c

fetch next from see_also_class_cursor_branch_c into @branch_id,@class_count
End
close see_also_class_cursor_branch_c
deallocate see_also_class_cursor_branch_c
****

Does anyone have faced the same issue too?  Thanks very much in advance.
Start Free Trial