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?

2
  • 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, 2016 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, 2016 at 20:11

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.