As IT managers have looked to leverage higher performance from SQL server deployments, many have been adopting techniques that can improve the overall input and output queries have become a focus of the process.
Server tuning has always been one of the key goals of all DBAs, and increasingly large workloads are being imposed on SQL servers as big data has become a commercial imperative for all businesses.
System performance will always be tied to the underlying hardware on which the MySQL database will run. As such, the I/O performance of the hardware has increasingly come under the spotlight as a means to improve server performance.
Trial and error
What has become clear with I/O tuning for SQL servers is that patience and perseverance are essential. A great deal of trial and error is required before an optimum balance is achieved, and the first step will always be to benchmark and measure the existing I/O performance.
Here, tracking the IOPS (I/O operations per second) is a good way to gauge the current loads that are moving over your server configuration.
Admins are also encouraged to make use of the SQL Server Profiler that ships with each issue of the SQL database. This tool is invaluable to capture server events, which can in turn be used to track workloads. This enables you to see where bottlenecks are situated.
In addition, SQL Server 12 users can use the Extended Events function to give more detail insights into the workloads on their server I/Os.
One of the most effective methods of improving I/O performance is to increase the available memory. As installed memory is very low cost, IT managers don't need to focus on memory saving techniques as they strive to maximise I/O performance.
Focusing on the I/O events themselves, and reducing CPU time will be more productive and lead to real world performance increases.
Buffering factor
Memory buffering has also proven to be highly effective. Here, buffering server requests that are then sent to the SQL database in groups reduces overall I/O times.
The operating system will do a good job of caching these calls and sending them to the database in the most efficient way, reducing I/O events.
The configuration of storage area networks can also have a huge impact on I/O performance. Log files should be placed on their own volume, as should the tempdb routine that should have its own I/O subsystem.
Backups should also have their own I/O to prevent these from adversely impacting the overall performance of the server.
Overall, understanding the existing I/O profile of your SQL data is essential before tuning can begin. As no two installations will be the same, IT managers will have to develop their own bespoke approach that delivers the best performance for their particular installation.
