Every 60 seconds (sharp), I experience delays in the SELECT T-SQL statement inside the stored procedure, and I don't know why this is happening. I suspect an incorrect SQL server configuration or error, but I am posting this as a question to the community in the hope that someone will solve this mystery.
I have a stored procedure called uspLoadDocumentthat loads a document when a user clicks on a document list item. Usually the procedure is very responsive, but every 60 seconds it “hangs” for 5-6 seconds (on average) before it continues normally (without exception).
Facts about this delay;
- This only happens on my two development machines. This does not happen in production (Azure - SQL Server V12)
- This happens exactly every 60 seconds and lasts an average of 5-6 seconds
- No transactions are triggered (at least not in the code that I control)
- No delays occur when calling SPROC from SQL Server Management Studio
Technical information;
- Visual Studio Enterprise 2015 24720 Update 1
- Microsoft SQL Server 2014 - 12.0.4213.0 (X64) (Express)
- Dapper 1.42.0 (others do not use OR / M)
- C # 6.0 and async / await pattern from WebAPI controllers down to datalayer
- Full-text search is enabled and a full-text index on one of the internal tables enclosed in a select statement that hangs inside the stored procedure
SQL- SQL, () select . , 60 . .
, . , ,
select-statement, table-value. WITH SCHEMABINDING.
SCHEMABINDING . , , SCHEMABINDING.