1

i have searched this problem but have had no luck find any improvement.

I have a complex application that used stored procedures in SQL server.

Some of the stored procedures use cursors due to the complex nature of the SQL in them.

A few months ago I purchased a new machine expecting an improvement in speed.

The new machine is Xeon E5-2690 single CPU with 32Gig of ram.

The same application runs faster on the new machine as expected. But the database is much slower. One of the stored procedures is more than 20x slower. What takes seconds on the old machine takes minutes on the Xeon machine.

The database and application are identical. I generate the DB from scripts and have done back to back tests with clean empty databases. Still many times slower.

I have searched for answers and tried what little I could find, no help.

I have looked at the settings of SQL server on both machines, many times, and cant find a difference.

I tried turning off HT on the Xeon because I found a post that it helped someone, didn't help me however.

I have started to move some of the DB processing into the applications to try and reduce the time penalty running on the new Xeon.

Any help would be appreciated. Cheers.

migrated from stackoverflow.com Apr 10 '13 at 21:33

This question came from our site for professional and enthusiast programmers.

  • 1
    You generate the database from scripts but that doesn't mean it's identical. One very easy thing to overlook is how that data is laid into tables. Data in your fast environment might have been added to the tables resulting in low fragmentation while the hotrod environment paved that data in there in a jacked up manner resulting in heavy fragmentation and/or out of date statistics. This results in SQL Server being told to look for data in one location on disk but having to search until it finds it in another time zone. How do you tell? Post some sample query plans and we can analyze the diffs. – billinkc Apr 10 '13 at 20:15
  • 3
    Also voting to punt this over to dba.stackexchange as you'll get better focus from the DBA crowd – billinkc Apr 10 '13 at 20:16
  • 2
    What kind of wait stats are you seeing? Review your wait stats using this query to get an idea on what your highest waits are on the server. You can also look at what waits the query specifically is waiting on using Sys.dm_os_wait_stats. – Mike Fal Apr 10 '13 at 21:43
  • 10 times out of 9 (sic) this is due to different plans in each environment. That in turn typically being due to differing statistics. As per @billinkc's comment, execution plans need to be added to your question. – Mark Storey-Smith Apr 10 '13 at 21:49
  • Thank you guys. How do i post the execution plans here, they are very large. – Jason Hay Apr 11 '13 at 0:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.