Stored Procedure performane hit after migrating to 2012

• chinna1990

  Ten Centuries

  Points: 1005

  More actions

  June 12, 2014 at 11:06 am

  #289971

  Hi All,

  Recently we have migrated sql server 2008R2 server to 2012, But after migration one of the stored procedure is taking 2mins execute in 2012 server. It took only 9 seconds to execute in 2008R2 environment.

  I have verified/followed these,

  1) Updated database level and table level statistics

  2) Rebuild indexes which has greater fragmentation level

  3) checked max memory settings

  Any suggestions here to resolve performance issue.

  Thanks,

  Sathvik.

• Eirikur Eiriksson

  SSC Guru

  Points: 182631

  More actions

  June 12, 2014 at 1:26 pm

  #1721219

  Sathvik (6/12/2014)

  ---

  Hi All,

  Recently we have migrated sql server 2008R2 server to 2012, But after migration one of the stored procedure is taking 2mins execute in 2012 server. It took only 9 seconds to execute in 2008R2 environment.

  I have verified/followed these,

  1) Updated database level and table level statistics

  2) Rebuild indexes which has greater fragmentation level

  3) checked max memory settings

  Any suggestions here to resolve performance issue.

  Thanks,

  Sathvik.

  Could you possibly share some more information? Execution plan, procedure code, server configuration, compatibility level etc.

  😎

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  June 12, 2014 at 3:41 pm

  #1721263

  Sathvik (6/12/2014)

  ---

  Hi All,

  Recently we have migrated sql server 2008R2 server to 2012, But after migration one of the stored procedure is taking 2mins execute in 2012 server. It took only 9 seconds to execute in 2008R2 environment.

  I have verified/followed these,

  1) Updated database level and table level statistics

  2) Rebuild indexes which has greater fragmentation level

  3) checked max memory settings

  Any suggestions here to resolve performance issue.

  Thanks,

  Sathvik.

  There are 328,927 different reasons this could be happening. You have tried to address just 3 of those. Unfortunately we can't help narrow down the field without quite a bit more information.

  1) Is the hardware/OS different?

  2) Is the new stuff virtualized?

  3) Did you patch up the new stuff?

  4) Did you run sp_whoisactive while the code was running?

  5) Did you do file IO stall and wait stats analysis while the code was running?

  4 and 5 together have a very high chance of pointing you in the right direction.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Grant Fritchey

  SSC Guru

  Points: 397721

  More actions

  June 13, 2014 at 4:17 am

  #1721393

  Adding to Kevin's list, what's the compatibility level of the database? Have you generated an execution plan on the 2008 box and the 2012 box to figure out what's different?

  There are edge cases where you can see a regression, but these are almost always with queries that are extremely complex and probably only barely functioned in the previous version of SQL Server. They're usually filled with tuning opportunities.

  ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software