Overview of SQL Server Timeouts
Many businesses use Microsoft SQL servers to manage their databases for good reason. SQL servers have built-in features that make them less expensive than other database management systems that require additional software to match a SQL server’s functionality. Moreover, SQL servers are easy to install and integrate into your network since 80% of the world’s computers run on Windows. They also include automatic compression and encryption for data passing into the database and require no modification to your software applications to retrieve and store data through SQL.
SQL servers, however, do suffer from one drawback — there are no built in SQL solutions to streamline I/O between the SQL server and the databases. As a result, your system may experience substantial SQL server timeouts as the small fragments are read from and written to the database. The time needed to find and reassemble fragments also contributes to both latency issues and SQL server timeouts.
Here are three facts to understand about SQL server timeouts:
There Are Two Types of SQL Server Timeouts
A SQL server may experience two types of SQL server timeouts:
- Connection timeout: A connection timeout occurs when the application server is unable to reach the SQL server within a defined connection time. This timeout allows the application server to terminate its attempts to establish a connection and return an error rather than waiting indefinitely for a response from the SQL server. These types of timeouts can be difficult to troubleshoot because the SQL server cannot log an error if no connection is ever made.
- Query timeout: A query timeout occurs when a command cannot be completed within a defined query time. These timeouts are usually caused by poor SQL performance. This makes sense because a noisy I/O stream between the SQL server and the databases will increase the time that it takes for the SQL server to respond to queries from the application servers.
SQL server timeouts impose a tax on your system’s performance. It is not a matter of running poorly. Rather, timeouts cause your system to run inefficiently. Most systems have the capacity to run faster. However, they fail to manage the resources they have to operate in the most efficient way.
The result of these inefficiencies include:
- Costs in time and effort for database administrators to troubleshoot timeouts.
- Loss of productivity when workers cannot access the system due to timeouts.
- Dissatisfied customers who rely on access to your databases but experience slow operation or error messages instead.
SQL Server Timeouts Are Inherent
To improve SQL performance and reduce SQL server timeouts, particularly query timeouts, you need to understand where the noisy I/O between the SQL server and databases comes from.
Windows handles virtual environments logically rather than physically. This means that rather than being biased toward keeping long reads and writes together, Windows breaks reads and writes into pieces. This allows SQL server to prioritize fragments, but also results in delays between the application server and SQL server and the SQL server and databases. At each of these places, the fragments slow SQL performance during writes and produce conditions in the databases that will guarantee slow performance when data is retrieved.
One reason is that the fragments are stored, well, as fragments. In other words, the SQL server deliberately creates a fragmented storage unit. Anyone familiar with the term “disc defragmentation” knows that fragmented discs slow down performance.
SQL Server Timeouts Can Be Eliminated
Common solutions, such as more memory, faster processors, and optimizing and tuning SQL can only go so far because the fragmenting of the I/O is a feature, not a bug. Even with these steps, the I/O will still be fragmented and the inefficiencies that result from a fragmented I/O will remain.
Software applications like V-locity® 7.0 however, uses existing RAM to gather fragments so that the I/O consists of contiguous reads and writes. This improves performance between the SQL server and databases, as well as between the SQL server and application server.
This improvement is not merely theoretical. Laboratory tests show that SSDs show about 30% performance improvement and HDDs show about 100% performance improvement.
Although all SQL servers have noisy I/O, your system is not stuck with the inherent inefficiencies of SQL. Software applications like V-locity® 7.0 can gather I/O fragments together to form contiguous reads and writes to minimize SQL server timeouts.