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

User · Answer

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

User · Answer

Your query is probably fine   quot The semaphore timeout period has expired quot  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You could put an index on MemberType

User · Answer

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

[sql] How to avoid Sql Query Timeout

Examples related to sql

Examples related to sql-server