0

We are seeing some terrible performance degradation having just upgraded our MVC4 application from SQL 2008 to SQL 2012.

Queries run directly in management studio on the database server run somewhat slower than they did prior to the upgrade (up to twice as slow).

Queries run from the web server (a separate machine) run hugely slower, some more than four times slower. We pointed the finger at the network between the two machines, but our hosting company have suggested that the connection string should have changed for SQL 2012. I'm not aware that SQL 2012 connection strings are any different to SQL 2008 connection strings - am I right?

Thanks!

Our connection string has been unchanged for years and looks like this: Server=111.111.111.111;Database=MyDbName;User ID=MyDbUser;Password=MyDbPassword;

UPDATE: This eventually turned out to be a faulty physical port on the firewall!

  • 1
    The connection string wouldn't degrade performance. Its only going to connect, or it isn't. – DanBig May 29 '14 at 15:07
1

No. Connction stringhs have not changed for some time unless you use one of the new connectio nbased features and while there are some in the version jump you have (availability groups) they are not relevant when not used.

So, in your case - definitely nothing has changed.

You will have to run a profiler and find out why. This is something you should have done before upgrading production - standard approach.

  • Thanks. We did test the upgrade of course but didn't experience the same problems. – Edmond Bramhall May 29 '14 at 14:46
  • Agreed, most likely not. @Edmond If you're connecting successfully at all then you shouldn't need to change connection strings. There is a new SQL Native Client with SQL2012 so OLE DB connections should change their provider. Is this a single-instance SQL server? Can you post the connection string you're using? Also, did you change the compatability level of any of your databases during the upgrade? – squillman May 29 '14 at 14:58
  • Thanks. Connection string added to question. We didn't change the compatibility level, and nor did we reindex or run any maintenance scripts after the upgrade which I'm thinking now may have been very naive. – Edmond Bramhall May 29 '14 at 15:03
  • 1
    Definitely regenerate statistics.... and then profile. 2012 had serious optimizer changes.... – TomTom May 29 '14 at 15:44
  • 1
    @Edmond Update statistics can have a huge impact. The query optimizer was completely rewritten for 2012. If the optimizer is using bad execution plans then that can be all of your pain right there. Even though all the execution plans have been regenerated there might be one that's not great that's causing your problem. After that, then it's off to a profiler trace. Regarding compatibility levels, you'll want to review the deprecated features list before updating. If your db or client code uses something deprecated you will need to change code prior to updating compatibility. – squillman May 29 '14 at 15:53

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.