Common causes of intermittent SQL Server connectivity issues:
- Queries/transactions holding long running exclusive locks on tables. Queries waiting on these locks may be timing out.
- A faulty switch, network card, cable or other network infrastructure.
- Another device on the network with the same IP address.
- Incorrect connection protocol being used.
- Virus scanners and incorrect exclusion lists.
- Network/Port security scanners.
- VM snapshot backups.
- Under-powered SQL Server or TLog backups over an under-powered network connection or large TLog file growth settings.
- SQL Server under extreme memory pressure.
- An application doing slow row-by-row processing while SQL Server is sending the data fast; this often shows up as a high value for ASYNC_NETWORK_IO waits.
The obvious question is “What changed?”!
When different parts of the system are managed by different people, the culprit can be a seemingly innocuous change that is not well documented or communicated to the various stakeholders.
Is a server alias being used?
Have TCP/IP dynamic ports been enabled for the TCP/IP protocol?
See How to Configure a SQL Server Alias for a Named Instance on a Development Machine
Is the SQL Server a named instance?
If you don’t specify the protocol in the connection string, then it will use the protocol specified for the database driver. The protocol order, by default, is Shared Memory, TCP/IP and then Named Pipes.
To list connections and the protocol they are using:
SELECT * FROM sys.dm_exec_connections
–WHERE session_id = @@SPID;
To specify which protocol to use in a connection string, add tcp: before the server name:
var connectionString = “Data Source=tcp:Server\Instance; …”;
Is IPv6 being used?
Was the server upgraded?
If so, there might be queries that are timing out due to optimiser/cardinality estimator changes.
Are all applications experiencing intermittent connections?
If you have a monitoring tool connected to the SQL Server, is it able to connect even when the problem is occurring?
Are there any errors in the SQL Server or application server logs?
If all application servers lose network connectivity regularly at the same time, it might be a hardware issue such as a faulty switch or network card.
Does it happen to all application servers?
If you have several application or web servers, are they all affected?
[As an aside, if you’ve only got one, you might want to consider setting up another for load balancing and troubleshooting.]
Are all queries in the application affected, or just some queries?
Some queries/transactions may be holding long running exclusive locks on tables. Queries waiting on these locks might be timing out.
Do timeouts occur at regular days/times?
Could it be regularly scheduled port security scans, or VM backups, or large transaction log file growths? Use a fixed transaction log file growth setting (128MB is commonly used) rather than a percentage.
During the timeouts, is the application server able to ping the SQL Server?
Set up a script to ping the SQL Server every 10-15 seconds. If you are not able to ping the SQL Server next time the application has query timeouts, then it is not a SQL Server problem.