Debugging a tough LINQ-to-SQL Timeout

Question

I'm getting a timeout error when trying to execute a LINQ (-to-SQL) query

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

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

• I run the equivalent SQL in SQL Management Studio and it completes quickly (2 seconds)
• I'm setting my CommandTimeout to 2 minutes.
• When I execute the very same query in a unit test, it completes successfully and quickly. That is: I'm only getting this timeout when I'm running this query alongside other queries. The timeout always occurs at the same call.

That last item made me think that I'm getting some sort of database-side deadlock: the query is blocked by a lock somewhere, and the timeout period elapses, killing the stalled connection.

The trouble with this idea is that I'm only doing selects in the DataContext that's causing problems. (I do have inserts occurring in a different database/DataContexts.) I also have no explicit transactions. This is tripping me up a bit: the query behavior looks exactly like a deadlock, but I've never had a deadlock that wasn't caused by some sort of transaction isolation issue before -- and that doesn't look like the case here (at first glance anyway).

I'm looking for advice on how to debug this problem. What sorts of things should I be looking at to determine the cause of this problem?

EDIT

Some notes that may be useful:

• I'm querying against a view that references:
  • Other views in the same database
  • Synonyms that point to tables in another database via a Linked Server.
• This view is using union to join the results of several queries together

EPILOGUE

I ended up fixing the core problem by reworking my query. The original was calling a few tables more than once (via different views). The reworked version bypassed all of this, and the timeouts disappeared.

Answer 1

Are you sure the connection pool isn't being eaten up by uncollected resources?

Try wrapping your SqlDataContext in a using block and see if the issue persists.

Answer 2

run your code again from the application, while it is waiting (for 2 minutes??) run this in a query window:

;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

it will show you all the blocks at the time of the run.