SQL Server unusual behavior on LOB insertion

Question

We are facing unusual behavior on a SQL Server 2008 R2 instance. We have a small .NET application, which we use to insert images and pdf files inside database as VARBINARY. Sometimes, we are facing query timeouts when the file size is bit large, sometimes larger than 3 MB, and sometimes this behavior is observed with 4 to 5 MB files.

IIS is installed on a separate machine.

Through trace we have found that no such insertion query is received on the SQL Server end during save image process from application.

This could be an application server issue, but it's the fifth time that we have resolved this issue by restarting SQL Server services. Why so?

During this problem rest of discrete data operations and small LOB insertion queries work fine.

• Version: SQL Server 2008 R2 Service Pack 3
• Edition: Standard
• Windows Server 2008 R2 Enterprise (SP1)
• Intel(R) Xeon(R) CPU E5-2630 v2 @2.60Ghz (2 Processors)
• Memory 36GB to SQL Server (out of total 64GB)

(We have requested IT to assign at least 20GB more to SQL Server as it's a dedicated DB instance.)

Optimize for adhoc option is set to 1. Adhoc query plans size is about 18% of total cached plans. We are going to clean these adhoc plans periodically using Kimberly's script. I have verified that things are normal on wait stats and performance counters side, and nothing received on Trace when query is submitted from app. LOB ndf file is on local drives.

Current workaround Thanks Kin for this idea. This time, when we faced the same issue, we just refreshed the system cache by executing the following DBCC statement, and issue is resolved:

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL

We are still in search of permanent solution as periodically clearing system cache is not possible for some of workloads (specially 24x7 cases).

Answer 1

If your SQL server trace is not showing anything, it is most likely on the application. Even if it is resolved by restarting the SQL service. I have faced a very similar issue in the past and the connection for the upload process was the culprit. Restarting the SQL service basically gives you another attempt at reconnecting. Since it works some of the time, this second attempt may be all that you need to get it going.

Look into how you build your connection for large files. Limit the time it takes to send the query to the server, and when you get past it, mimic the service restarting by trying it again.

Answer 2

Is the application (IIS) timing out or the connection between IIS and SQL Server timing out?

I would start by trying to increase the timeout in IIS to see if that's where the problem lies.

Answer 3

_{Community Wiki answer generated from comments on the question.}

If the request never gets to SQL Server at all, sounds like either application or network tracing is in order. Rebooting Windows sometimes resolves issues with applications too, doesn't mean the actual problem is in Windows. - Aaron Bertrand

Since other queries are working while you are experiencing issue with LOB, I would investigate that request for LOB insert does not reach database. Check web server logs for that request, add tracing/logging to your web app. Check if it is size related, e.g. load 10-15mb. - Stoleg

Check the possibility of bloated plan cache. I have seen bloated plan cache causing high CPU (pegged 99-100%) which in turn causes timeouts during load testing phase. Next time instead of restarting SQL services, free up proccache or systemcache. - Kin

Answer 4

Where there are WAN links between application server and SQL Server, possible packet-filtering operation of some net security device/layer.

Where periodic large file packets (image: streaming burst, relative to average insert activity/frequency, and some net-sec action triggered), might trigger resets (like DOS attack counteraction); might get connection timeouts for all retries, and stop on retries; connection pooling config's / contention; rare orphaned tcp connection or "half_opened" tcp, from the initial SQL Server connection event and SQL Server still "sees it" idle, but the connection on the far side is already gone.

Somewhere, somehow if these critters are preventing a proper connection back to the database engine, a SQL Server service restart will clear out all of the goofiness, and then connection pools resume making complete tcp, etc. and happiness returns.

If case same domain, still possible per network, configs, etc. and enterprise antivirus loving to do deep-dives (heuristics?) with streams of "non-ascii" data packets, latency loads the transmission, and 'smart' devices may be doing the offload stuff, and one side of tcp is faked out, and left half open.

Bounce SQL Server and same request goes through as expected - suspect rats on the wire!

select * from sys.dm_exec_connections
where net_transport ='tcp' 
and local_net_address !='127.0.0.1'
and parent_connection_id is not null;

Above DMV misses orphans (rare). With SQL trace, no connection trapped since never fully established. Maybe see related extended events, diagnostic logs, if you have time and are allowed. Easy to miss since default trace trap missed in between poll interval.

Drop into DOS on the SQL side and web side, and try netstat -p tcp etc. and put into a cmd file loop with now()s at each start, >> file, and you might catch incomplete tcp builds from your web app side.

If heavy activity, large scale ops, you might get lucky, but more often can be wasting time. Only helps if you get a catch, and present to LAN/WAN group as a 'concern'.

PIX firewalls w/ packet-filtering enabled? Question to ask WAN person, even if you just add the fact to your server book. Always start off by saying "Just curious..."