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

You could put an index on MemberType.

Answer 2

Do you have an index defined over the Status column and MemberType column?

Answer 3

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

e.g. see http://support.microsoft.com/kb/325487

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 4

Please check your Windows system event log for any errors specifically for the "Event Source: Dhcp". It's very likely a networking error related to DHCP. Address lease time expired or so. It shouldn't be a problem related to the SQL Server or the query itself.

Just search the internet for "The semaphore timeout period has expired" and you'll get plenty of suggestions what might be a solution for your problem. Unfortunately there doesn't seem to be the solution for this problem.

Answer 5

how many records do you have? are there any indexes on the table? try this:

;with a as (
select distinct Status
from MyTable
where MemberType=6
)
select count(Status)
from a

Answer 6

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