March 9, 2017 at 9:26 am
Hi Everybody,
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.
March 9, 2017 at 10:19 am
I've been using plan guides to force OPTION(recompile) on some long-running select statements and it works but your sql statement and the parameters have to match exactly. Because development uses an ORM, the parameters change names a lot even for the same query , so my plan guides only match and execute some of the time.
USE [OurDB]
GO
/****** Object: PlanGuide AccountSearch10 Script Date: 3/9/2017 9:01:11 AM ******/
EXEC sp_create_plan_guide @name = N'[AccountSearch10]', @stmt = N'SELECT Account02.ACCOUNT_ID AS Account02_ACCOUNT_ID6,Account02.LAST_MODIFIER AS Account02_LAST_MODIFIER172,Account02.LAST_MOD_DATE_TIME AS Account02_LAST_MOD_DATE_TIME174,Account02.TRANS_SEQ_NUM AS Account02_TRANS_SEQ_NUM176,Account02.CLIENT_ID AS Account02_CLIENT_ID8,Account02.CATEGORY AS etc etc etc etc
FROM ACCOUNT AS Account02 INNER JOIN PROPERTY AS AccountProperty13 ON Account02.ACCOUNT_ID=AccountProperty13.ACCOUNT_ID INNER JOIN (LEGAL_ENTITY AS RecordedLegalEntity14 INNER JOIN ADDRESS
etc etc
WHERE ((CollateralGroupRequest13.CLIENT_ID = @DerivedTable01_CLIENT_ID31 AND ServicedAccount02.ACCOUNT_ID = Account02.ACCOUNT_ID AND (ServiceRequestTransactionType14.REQUEST_TRANSACTION_TYPE = CollateralGroupRequest13.REQUEST_TRANSACTION_TYPE AND ServiceRequestTransactionType14.REQUEST_TYPE IN ( @DerivedTable01_REQUEST_TYPE92, @DerivedTable01_REQUEST_TYPE103,
etc
etc
', @type = N'SQL', @params = N'@DerivedTable01_BUSINESS_OBJECT_STATUS10 varchar(8000),@DerivedTable01_CLIENT_ID31 int,@DerivedTable01_REQUEST_TYPE92 varchar(8000),@DerivedTable01_REQUEST_TYPE103 varchar(8000),@DerivedTable01_REQUEST_TYPE114 varchar(8000),@DerivedTable01_REQUEST_TYPE125 varchar(8000),@DerivedTable01_REQUEST_TYPE136 varchar(8000),@DerivedTable01_BUSINESS_PROCESS_STATUS157
etc etc
', @hints = N'option(recompile)'
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply