Hi All,

Yesterday night we did production upgrade from SQL Server 2014 STD to SQL 2014 SP1 and started seeing slowness.

I see query taking more time, in some instances about 3 times more duration. Is there any know bug with SP1 that is causing the slowness in the system.

Is there any options to improve performance or Rollback SP1. I am running Active/Active Cluster on Windows 2012 R2 STD.

Any Help Appreciated. Thank You.

-Kranp.

• Edited by kranp Thursday, July 16, 2015 4:13 PM

Hello,

Please compare the performance of the queries with the new and the old cardinality estimator as explained on the following post.

http://blogs.msdn.com/b/psssql/archive/2015/06/16/identifying-sql-server-2014-new-cardinality-estimator-issues-and-service-pack-1-improvement.aspx   

You can then adjust compatibility level based on the results and use the trace flags for the exceptions.

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

• Edited by Alberto MorilloMVP, Moderator Thursday, July 16, 2015 7:15 PM
• Proposed as answer by Shanky_621MVP Friday, July 17, 2015 4:56 AM

I am seeing some Query running slow even with trace flag 4199 enabled.

Hello,

Have you rebuilt indexes and update statistics with full scan after the upgrade? Please do.

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Yes, I did..

Rebuild/Reorg Indexes depending on fragmentation. Sys_dm_db_index_physical_stats

Update Stats using: sp_updatestats

Hello,

Have you rebuilt indexes and update statistics with full scan after the upgrade? Please do.

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

• Marked as answer by kranp 16 hours 12 minutes ago

Practically, with SP1 upgrade we should not be seeimg any changes in Execution Plan, ie. Query durations as the Compatibility mode for the DB is 2008. The new CE is enabled only if the trace flag 2312 is enabled. So even without 4199 trace flag which enables the hot fixes for new CE things should work as normal. This seems to be a new bug with SP1. Where is Microsoft? Please advise. Thank You.

Issue Resolved. I had to update stats with Full Scan and Rebuild All the Indexes which updated the Execution Plan for Slow Running Queries.

Thank You,

Kranp.