Debugging LINQ-to-SQL Hard Timeout

Question

I get a timeout error when trying to execute a LINQ query (-to-SQL)

System.Data.SqlClient.SqlException: Timed out. The wait period expires before the operation is completed or the server is not responding.

Now this is not just a case of a slow query:

• I run equivalent SQL in SQL Management Studio and finish quickly (2 seconds)
• I set my CommandTimeout to 2 minutes.
• When I execute the same query in unit test, it completes successfully and quickly. That is: I get this timeout when I run this request along with other requests. A timeout always occurs on the same call.

This last element made me think that I was getting some kind of dead end on the database side: the request is blocked somewhere by a lock, and the wait period expires, killing the blocked connection.

The problem with this idea is that I only make selections in the DataContext that cause the problems. (I have insertions found in another database / DataContexts.) I also have no explicit transactions. This is a bit confusing to me: the behavior of the request looks exactly like a dead end, but I never had a dead end that was not caused by some kind of transaction isolation problem before - and this is not like the case here (at first glance anyway).

I am looking for advice on debugging this problem. What things should I look for to determine the cause of this problem?

EDIT

Some notes that may be helpful:

• , :
  • , .
• union

, . ( ). , .

Answer 1

, ( 2 ??) :

;with Blockers AS
(SELECT
     r.session_id AS spid
         ,r.cpu_time,r.reads,r.writes,r.logical_reads 
         ,r.blocking_session_id AS BlockingSPID
         ,LEFT(OBJECT_NAME(st.objectid, st.dbid),50) AS ShortObjectName
         ,LEFT(DB_NAME(r.database_id),50) AS DatabaseName
         ,s.program_name
         ,s.login_name
         ,OBJECT_NAME(st.objectid, st.dbid) AS ObjectName
         ,SUBSTRING(st.text, (r.statement_start_offset/2)+1,( (CASE r.statement_end_offset
                                                                   WHEN -1 THEN DATALENGTH(st.text)
                                                                   ELSE r.statement_end_offset
                                                               END - r.statement_start_offset
                                                              )/2
                                                            ) + 1
                   ) AS SQLText
     FROM sys.dm_exec_requests                          r
         JOIN sys.dm_exec_sessions                      s ON r.session_id = s.session_id
         CROSS APPLY sys.dm_exec_sql_text (sql_handle) st
     --WHERE r.session_id > 50
)
SELECT Blockers.* FROM Blockers

.

Answer 2

, ?

SqlDataContext , .