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 is nothing 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 within the system 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 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 and your productivity. 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 results 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 tends to break 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 “disk fragmentation” 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 the fragmented I/O will remain.
Software applications like Condusiv’s V-locity® 7.0 however, use proprietary methods to prevent these inefficiencies from occurring. One, V-locity’s IntelliWrite® technology helps prevent Windows from fragmenting the I/O in the first place when data is written, which also means no fragmentation issues when reading the data back. This, and other defragmentation technology, ensure the I/O consists of contiguous reads and writes. Two, V-locity’s IntelliMemory® technology will use existing available (i.e. not being used) RAM to cache read I/Os. With RAM being 10-15x faster than SSDs, you can see the performance gains from this. 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 can have about 30% performance improvement and HDDs as much as 100% performance improvement, with V-locity enabled.
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 will help ensure I/Os form contiguous reads and writes plus improve I/O latency times to minimize SQL server timeouts.