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.
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).