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 24×7 cases).
Best Answer
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.