Your SEO optimized title page contents

High CPU Performance issue in SQL Server 2016/2017


There is a performance issue in SQL Server 2016 and SQL Server 2017 where the processor utilization will run very high for certain queries  https://support.microsoft.com/en-us/help/3216543/fix-many-consecutive-transactions-inserting-data-into-temp-table-in-sq

The queries that will experience this higher utilization are queries that insert many records into a table or temp table OUTSIDE of a transaction.  Not starting your own BEGIN/COMMIT transaction is the key to the issue.  There is a security feature called Common Criteria Compliance that will execute for every record that is inserted outside of a transaction causing the CPU to go higher on the SQL Server.  The CCC feature is disabled by default on the installation of SQL Server but has some additional CPU overhead  even when disabled unless the patch is installed.

The following are the fixes for SQL Server 2016 and 2017:

SQL Server 2017 Cumulative update 4 - https://support.microsoft.com/en-us/help/4056498

SQL Server 2016 SP1 Cumulative update 2 - https://support.microsoft.com/en-us/help/4013106

SQL Server 2016 requires that you enable Trace Flag 3427 in addition to installing the patch.

This issue is more likely to arise in reports where developers tend to avoid using Transactions when creating Temp tables for reports.  This is done to avoid blocking with reports. But any code that inserts a significant volume of records outside of a BEGIN/COMMIT Tran will experience the high CPU utilization.

To validate that you are experiencing the error or have it fixed if you have the patch installed test using the following code:

Query 1:

SET NOCOUNT ON
declare @nmbr int
set  @nmbr = 1
IF OBJECT_ID(N'tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
create table #tmp (id int)
while @nmbr < 1000001 -- 1 million rows
begin
insert into #tmp (id) values (@nmbr)
set @nmbr = @nmbr + 1
END

Query 2:

SET NOCOUNT ON
declare @nmbr int
set  @nmbr = 1
IF OBJECT_ID(N'tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
create table #tmp (id int)
Begin Tran

    while @nmbr < 1000001 -- 1 million rows
     begin
     insert into #tmp (id) values (@nmbr)
     set @nmbr = @nmbr + 1
     END
Commit tran

The two queries will have a difference in total time. The second query will execute faster.  BUT, the CPU on the server should be the same for both of these queries if you have the fix installed listed above.


Skip to main content