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

That is a long time. Is [MyTable] in your example above a Table or a View? If it is a View please post the View Definition and we can try and suggest optimizations. — codeulike
– codeulike, Commented May 12, 2009 at 21:20
it's a view. unfortunately, I don't have rights to view the view definition. — R.D
– R.D, Commented May 13, 2009 at 2:16
ouch, thats tricky. Maybe Cade Roux suggestion is best - beg the DBA to index the view. — codeulike
– codeulike, Commented May 13, 2009 at 20:12

codeulike · Accepted Answer · 2020-10-14 20:37:49Z

33

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?

edited Oct 14, 2020 at 20:37

answered May 12, 2009 at 16:33

codeulike

23.2k29 gold badges128 silver badges172 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Archmede Over a year ago

the link in your answer has expired

codeulike Over a year ago

@Archmede thanks, I removed the link as it wasn't important

Cade Roux · Accepted Answer · 2009-05-12 19:36:41Z

12

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

answered May 12, 2009 at 19:36

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Comments

Johnno Nolan · Accepted Answer · 2009-05-12 16:32:06Z

2

You could put an index on MemberType.

answered May 12, 2009 at 16:32

Johnno Nolan

29.7k19 gold badges114 silver badges160 bronze badges

1 Comment

JeffO Over a year ago

The RO access is probably more of an issue, but could ask DBA to add it to the table.

MicSim · Accepted Answer · 2009-05-12 16:46:54Z

2

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.

answered May 12, 2009 at 16:46

MicSim

26.9k17 gold badges95 silver badges138 bronze badges

2 Comments

user677526 Over a year ago

I know this was five years ago, but why would you assume this is a DHCP issue on a fixed network...? If he's running a database server, he probably has a fixed address scheme. I feel as though this is a non-answer.

Doug Deden Over a year ago

(And it's been another five years.) How would this be related to DHCP? Even if the machines in question have IP addresses via DHCP, a lease expiring would, in nearly all cases, just renew with the same address. If your client or your server are changing IP addresses in the middle of a process, you'd have a whole lot of other problems. And it wouldn't be easily reproducible.

great_llama · Accepted Answer · 2009-05-12 16:33:04Z

1

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

answered May 12, 2009 at 16:33

great_llama

11.7k4 gold badges36 silver badges29 bronze badges

Comments

user2462054 · Accepted Answer · 2018-08-06 22:19:25Z

My team were experiencing these issues intermittently with long running SSIS packages. This has been happening since Windows server patching.

Our SSIS and SQL servers are on separate VM servers.

Working with our Wintel Servers team we rebooted both servers and for the moment, the problem appears to have gone away.

The engineer has said that they're unsure if the issue is the patches or new VMTools that they updated at the same time. We'll monitor for now and if the timeout problems recur, they'll try rolling back the VMXNET3 driver, first, then if that doesn't work, take off the June Rollup patches.

So for us the issue is nothing to do with our SQL Queries (we're loading billions of new rows so it has to be long running).

DForck42 · Accepted Answer · 2009-05-12 19:32:03Z

0

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

edited May 12, 2009 at 19:32

answered May 12, 2009 at 19:25

DForck42

20.5k14 gold badges61 silver badges86 bronze badges

Comments

Himanshu N Tatariya · Accepted Answer · 2018-08-07 06:52:48Z

0

This is happen because another instance of sql server is running. So you need to kill first then you can able to login to SQL Server.

For that go to Task Manager and Kill or End Task the SQL Server service then go to Services.msc and start the SQL Server service.

answered Aug 7, 2018 at 6:52

Himanshu N Tatariya

2783 gold badges8 silver badges27 bronze badges

Comments

AcePL · Accepted Answer · 2019-01-23 12:55:54Z

While I would be tempted to blame my issues - I'm getting the same error with my query, which is much, much bigger and involves a lot of loops - on the network, I think this is not the case.

Unfortunately it's not that simple. Query runs for 3+ hours before getting that error and apparently it crashes at the same time if it's just a query in SSMS and a job on SQL Server (did not look into details of that yet, so not sure if it's the same error; definitely same spot, though).

So just in case someone comes here with similar problem, this thread: https://www.sqlservercentral.com/Forums/569962/The-semaphore-timeout-period-has-expired

suggest that it may equally well be a hardware issue or actual timeout.

My loops aren't even (they depend on sales level in given month) in terms of time required for each, so good month takes about 20 mins to calculate (query looks at 4 years).

That way it's entirely possible I need to optimise my query. I would even say it's likely, as some changes I did included new tables, which are heaps... So another round of indexing my data before tearing into VM config and hardware tests.

Being aware that this is old question: I'm on SQL Server 2012 SE, SSMS is 2018 Beta and VM the SQL Server runs on has exclusive use of 132GB of RAM (30% total), 8 cores, and 2TB of SSD SAN.

Collectives™ on Stack Overflow

How to avoid Sql Query Timeout

9 Answers 9

2 Comments

Comments

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

2 Comments

Comments

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related