1

All of sudden BizTalk load has stated failing in SQL Server which was running for years without any issues.

BizTalk will compile an insert SQL statement from flat file & insert data into SQL Server. It takes only few minutes but recently it started having issues & talking hours. There is nothing changed in code or at the hardware level.

When I clean SQL cached plan before load runs from BizTalk server it finishes without any issues. This is a work around solution for now. I have tried to turn on optimize for ad hoc workloads but that didn't help so I turned it off.

I have IDERA Diagnostic Manager for monitoring, but it’s not collecting any data when CPU reaches Over 90% & SQL Server becomes unresponsive. Cannot even run basic DMV's and sp_whois active is unable to collect any data. I am seeing SQL recompilation increased since issue started as per IDERA monitoring tool & locking increase too.

SQL Server 2012 Enterprise edition dedicated SQL Server.

Min memory set to 14 GB & max set to 28 GB. Total memory on server is 32GB. It's a virtual server (VM) with 8 CPUs & max degree of parallelism set to 4 & cost threshold for parallelism set to 50.

Rebuild index job is running as below using Ola Hallengren's script & it finishes in 1 hours or so a normal time.

EXECUTE admin.dbo.usp_IndexOptimize
@Databases = 'USER_DATABASES, -admin',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@OnlyModifiedStatistics = 'Y'

SQL Server error logs few error since issue started:

AppDomain 2 (mssqlsystemresource.dbo[runtime].1) is marked for unload due to memory pressure. AppDomain 2 (mssqlsystemresource.dbo[runtime].1) unloaded.

06/20/2016 08:12:15,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 54 06/20/2016 02:13:14,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 128 06/19/2016 22:45:59,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 69 06/19/2016 22:03:46,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 96 06/19/2016 18:14:35,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 86 06/19/2016 15:18:44,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 43 06/18/2016 17:38:06,spid14s,Unknown,last target outstanding: 1208 avgWriteLatency 45 06/17/2016 07:46:11,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 97 06/16/2016 22:46:21,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 26 06/15/2016 10:06:29,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 139 06/15/2016 08:02:44,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 42 06/15/2016 07:51:54,spid15s,Unknown,last target outstanding: 2 avgWriteLatency 19 06/15/2016 07:42:21,spid15s,Unknown,last target outstanding: 2 avgWriteLatency 68 06/13/2016 22:34:08,spid16s,Unknown,last target outstanding: 2 avgWriteLatency 29 06/12/2016 15:18:25,spid15s,Unknown,last target outstanding: 850 avgWriteLatency 4 06/11/2016 08:18:26,spid15s,Unknown,last target outstanding: 2 avgWriteLatency 47 06/08/2016 11:23:20,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 54 06/05/2016 22:41:03,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 55 06/05/2016 22:33:42,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 24 06/03/2016 05:20:27,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 40 06/03/2016 04:32:09,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 99 06/03/2016 04:22:58,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 79 06/03/2016 04:19:27,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 162 06/03/2016 03:30:42,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 38 06/03/2016 01:04:19,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 66 06/02/2016 22:56:54,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 83 06/02/2016 22:51:26,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 40 05/31/2016 22:53:19,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 68 05/31/2016 22:40:15,spid14s,Unknown,last target outstanding: 2320 avgWriteLatency 0 05/29/2016 20:56:04,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 86 05/29/2016 20:15:08,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 106 05/29/2016 19:33:31,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 46 05/29/2016 19:29:10,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 61 05/29/2016 18:55:11,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 77 05/29/2016 17:09:56,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 61 05/28/2016 19:03:20,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 511 05/28/2016 09:31:48,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 101 05/28/2016 07:54:42,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 63 05/28/2016 07:36:40,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 150 05/28/2016 06:04:58,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 78 05/26/2016 22:59:37,spid14s,Unknown,last target outstanding: 2 avgWriteLatency 106

Error: 8628, Severity: 17, State: 0. A time out occurred while waiting to optimize the query. Rerun the query.

Error: 4014, Severity: 20, State: 10. A fatal error occurred while reading the input stream from the network. The session will be terminated (input error: 10054, output error: 0).

So disk is on SSD will this latency is normal for SSD or may be the OS level it's limiting? & because of that recompilation, network & optimize query error started appearing in SQL Error logs?

  • I suspect its the query - Can you post the actual execution plan for the query ? output of dbcc memorystatus() in text mode will be helpful when you are experiencing the problem. – Kin Shah Jun 22 '16 at 18:36
  • How to get execution plan from SQL servers as all SQL injection happens from biz talk server so how to query that out of from CACHESTORE_SQLPLAN ? – AJ's SQL Jun 22 '16 at 20:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.