Aaron Bertrand

Performance-related fixes for SQL Server 2012

March 19, 2013 by in SQL Performance, System Configuration | 1 Comment
Database smarter with deeper insights for Microsoft SQL
SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Free Download →

Featured Author

Erin Stellato is a Principal Consultant with SQLskills and a Microsoft Data Platform MVP.

Erin’s Posts →

After SQL Server 2012 Service Pack 1 Cumulative Update #3 was released last night, I realized that there are a lot of performance-related fixes that are available for SQL Server 2012, and it might be difficult to keep track of which fixes are available in which cumulative updates.

So, here is a list of performance-related fixes available for SQL Server 2012 to date (scroll down to see a full legend).

Fix / KB Article SP1 Update RTM Update
Improved Metadata Discovery process performance in SQL Server Native Client SP1 CU3 RTM CU6
FIX: Poor performance when you run a SQL Server trace SP1 CU3 N/A
FIX: A query that runs against a DMV enters an infinite loop SP1 CU3 N/A
FIX: You experience poor performance when you run a query against an RCSI-enabled table SP1 CU3 N/A
FIX: High "log write waits" counter value SP1 CU3 N/A
FIX: Memory is not released when you use Linq to read events in Extended Events files SP1 CU3 RTM CU6
FIX: CPU spike when there is no load on a server after you install SQL Server 2012 SP1 CU3 N/A
FIX: Value of the NORECOMPUTE property for a partitioned index is not retained after you rebuild the index SP1 CU3 N/A
Performance issues in NUMA environments SP1 CU3 N/A
A database transaction log continues to grow after you upgrade to SQL Server SP1 CU2 RTM CU5
FIX: High CPU usage when you query a binary large object column by using the NOLOCK hint SP1 CU2 RTM CU5
FIX: Large queries that modify data run slower than expected when many locks accumulate SP1 CU2 RTM CU5
FIX: A transaction log restore operation takes longer than expected if a database file is removed SP1 CU2 RTM CU5
SQL Server 2012 experiences out-of-memory errors SP1 CU2 RTM CU5
FIX: Memory leak if you enable the AUTO_UPDATE_STATISTICS_ASYNC statistics option SP1 CU2 RTM CU5
Windows Installer starts repeatedly after you install SQL Server 2012 SP1 SP1 CU2 N/A
FIX: Error 17883 when you run a query on a server that has many CPUs and a large amount of memory SP1 CU2 N/A
FIX: "out of memory" error when you use ODBC SP1 CU1 RTM CU3
FIX: Performance of a SELECT statement that contains a LIKE operator and an ESCAPE clause is low SP1 CU1 RTM CU3
FIX: SQL Server stops responding and a "Non-yielding Scheduler" error is logged SP1 CU1 RTM CU3
FIX: SQL Server takes a long time to generate scripts for a partitioned table with data compression SP1 CU1 RTM CU4
FIX: Slow performance or deadlock when you restore a database and execute statements at the same time SP1 CU1 RTM CU3
FIX: Worker threads do not wake up immediately when multiple I/O-intensive tasks are running at the same SP1 CU1 RTM CU4
FIX: "Process appears to be non-yielding on Scheduler " error message when you run a query SP1 CU1 RTM CU4
FIX: Database is offline and in "In Recovery" state when you rebuild an index if the transaction log is full SP1 CU1 RTM CU4
FIX: Slow performance when you restore a database if CDC is enabled Service Pack 1 RTM CU2
FIX: Locked page allocations are enabled without any warning after you upgrade Service Pack 1 RTM CU2
FIX: Latch time-out error when you have heap tables and when data compression is enabled Service Pack 1 RTM CU2
FIX: An instance of SQL Server 2012 may end unexpectedly when FILESTREAM data is accessed Service Pack 1 RTM CU2
FIX: Low CPU spikes at a set interval even without user activity Service Pack 1 RTM CU1
FIX: Slow performance when an AFTER trigger runs on a partitioned table Service Pack 1 RTM CU1
FIX: Performance might decrease when you run a query that uses the sp_cursoropen stored procedure after you upgrade Service Pack 1 RTM CU1
FIX: It takes a long time to restore a database Service Pack 1 RTM CU1
FIX: Poor performance when you run a query that contains correlated AND predicates Service Pack 1 RTM CU1
FIX: Unnecessary updates to a subscriber after you reinitialize it if a new article is added Service Pack 1 RTM CU1
FIX: Slow performance occurs if high CPU usage is observed with contention over the QUERY_EXEC_STATS spinlock Service Pack 1 RTM CU1
FIX: Slow performance when you use DQS to perform a data cleansing against a large data source Service Pack 1 RTM CU1
FIX: "non-yielding on Scheduler" error if you use Service Broker to run a distributed query Service Pack 1 RTM CU1
FIX: Cluster disk validation takes long time unexpectedly if there are more than 20 disks or volumes under a single drive letter during failover cluster installation Service Pack 1 RTM CU1
FIX: Out-of-memory error when you run SQL Server 2012 on a computer that uses NUMA Service Pack 1 RTM CU1
FIX: Error message if you use more than 30 user-defined resource governor resource pools for an instance of SQL Server 2012: "Non-yielding IOCP Listener" Service Pack 1 RTM CU1
FIX: "System_health" session diagnostics collection task freezes SQL Server Service Pack 1 RTM CU1
FIX: Availability group failover takes a long time if a database in the availability group contains a FileTable Service Pack 1 N/A

 
My opinion remains that you should always stay up to date (after thoroughly testing them, of course) with cumulative updates – not just for performance-related fixes, but for many other fixes as well (I lost count of the number of "incorrect result" bugs, "assertion error" issues and access violations that have been fixed).

You should take note that several fixes available in the newly refreshed SP1 branch have not yet been made available in the RTM branch. Of course this situation will roughly flip-flop next month, and the month after, and until the cumulative update release cadence is synchronized between the RTM and SP1 branches.

Legend of SQL Server 2012 Updates

Update Build Released
RTM Cumulative Update 1 11.0.2316 2012-04-12
RTM Cumulative Update 2 11.0.2325 2012-06-18
RTM Cumulative Update 3 11.0.2332 2012-08-31
RTM Cumulative Update 4 11.0.2383 2012-10-15
RTM Cumulative Update 5 11.0.2395 2012-12-17
RTM Cumulative Update 6 11.0.2401 2013-02-18
Service Pack 1 (list of fixes) 11.0.3000 2012-11-15
SP1 Cumulative Update 1 11.0.3321 2012-11-20
SP1 Cumulative Update 2 11.0.3339 2013-01-21
SP1 Cumulative Update 3 11.0.3349 2013-03-18

 
I will try to keep this list up to date as new cumulative updates are released.