How to avoid Sql Query Timeout

Question

I have RO access on a SQL View. This query below times out. How to avoid this?

select     count(distinct Status)   from    [MyTable]  with (NOLOCK) where    MemberType=6 

The error message I get is:

Msg 121, Level 20, State 0, Line 0

A transport-level error has occurred when receiving results from the server (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

Answer 1

Your query is probably fine. "The semaphore timeout period has expired" is a Network error, not a SQL Server timeout.

There is apparently some sort of network problem between you and the SQL Server.

edit: However, apparently the query runs for 15-20 min before giving the network error. That is a very long time, so perhaps the network error could be related to the long execution time. Optimization of the underlying View might help.

If [MyTable] in your example is a View, can you post the View Definition so that we can have a go at optimizing it?

Answer 2

Although there is clearly some kind of network instability or something interfering with your connection (15 minutes is possible that you could be crossing a NAT boundary or something in your network is dropping the session), I would think you want such a simple?) query to return well within any anticipated timeoue (like 1s).

I would talk to your DBA and get an index created on the underlying tables on MemberType, Status. If there isn't a single underlying table or these are more complex and created by the view or UDF, and you are running SQL Server 2005 or above, have him consider indexing the view (basically materializing the view in an indexed fashion).