I have one SQL Server 2016 instance that every night BizTalk inserts messages into the database. BizTalk is managed by a third party company, and the way they are inserting the messages is causing a lot of RESOURCE_SEMAPHORE_QUERY_COMPILE waits on the instance and "A time out occurred while waiting to optimize the query. Rerun the query." on the SQL Error Log.
They told us they run 200 connections and what I captured from sp_WhoisActive is a batch that runs thousands of inserts like you can see below (the three dots represent the other inserts):
SET XACT_ABORT ON BEGIN TRAN DECLARE @eip INT, @r__ int, @e__ int SET @eip = 0 DECLARE @V1 nvarchar(40);INSERT [DWH].[Market_Message_ID] (MarketMessageNumber, Date_Created, Date_Imported, Message_Name, VersionNumber, TxRefNbr, Import_Start, Import_End, Import_Status, Import_By, Version) VALUES (N'341', N'28/02/2017 19:00:46', N'28/02/2017 19:00:46', N'DSO_MIM_341_1488195501479_9088551', N'11.00.00', N'0000000369457220', N'28/02/2017 19:00:46', N'28/02/2017 19:00:46', N'Success', N'BizTalk', N'1'); SELECT @e__ = @@ERROR, @r__ = @@ROWCOUNT IF (@e__ != 0 OR @r__ != 1) SET @eip = 1 SELECT @V1 = SCOPE_IDENTITY( );INSERT [DWH].[Market_Message_341] (MarketMessageID, Jurisdiction, MPRN, ReadDate, ReadingReplacementVersionNumber, UOM_Code, IntervalValue, IntervalPeriodTimestamp, IntervalStatusCode) VALUES (@V1, N'DSO', N'10000064887', N'26/02/2017 00:00:00', N'1', N'KWT', N'113.255', N'2017-02-26T00:00:00+00:00', N'VVAK'); SELECT @e__ = @@ERROR, @r__ = @@ROWCOUNT IF (@e__ != 0 OR @r__ != 1) SET @eip = 1 ... IF (@eip != 0) ROLLBACK ELSE COMMIT SET XACT_ABORT OFF
There are two types of inserts, one on Market_Message_ID table and the other on Market_Message_ID, because they are using literals I was testing on a test server the use of Plan Guides to reduce the compilations. I've also compared using optimize for ad hoc workloads configuration and changing the database Parameterization parameter to force.
On the three tests the wait event on the top was always the RESOURCE_SEMAPHORE_QUERY_COMPILE event, and on the plan guide I was never able to see the queries using this on the XML execution plan.
I've used the following syntax to create the plan guide:
DECLARE @stmt nvarchar(max); DECLARE @params nvarchar(max); EXEC sp_get_query_template N'INSERT [DWH].[Market_Message_341] (MarketMessageID, Jurisdiction, MPRN, ReadDate, ReadingReplacementVersionNumber, UOM_Code, IntervalValue, IntervalPeriodTimestamp, IntervalStatusCode) VALUES (N''1'', N''DSO'', N''10000064887'', N''26/02/2017 00:00:00'', N''1'', N''KWT'', N''113.255'', N''2017-02-26T00:00:00+00:00'', N''VVAK'');', @stmt OUTPUT, @params OUTPUT; EXEC sp_create_plan_guide N'CPSTemplateGuide', @stmt, N'TEMPLATE', NULL, @params, N'OPTION(PARAMETERIZATION FORCED)'; GO
I'm I doing something wrong or is the nature of the batch that it is not using the plan guides or even get rid of the wait event using the other configuration options?
The production instance is SQL Server 2016 RTM CU3 on a Windows 2012 R2 Standard VM with 8 CPUs and 64GB of RAM. SQL Max Memory 60GB
The test instance is SQL Server 2016 RTM CU3 on a Windows 2012 R2 Standard VM with 4 CPUs and 16GB of RAM. I'm using SQL Query Stress to simulate the inserts on the database using 50 threads.