SQL Server Disk configuration settings are one of the most important aspects of SQL Server performance tuning. Typically, disks are one of the slowest parts of the entire SQL subsystem. Without proper disk configuration, SQL Server can slow down, increase locks and waits. It can also significantly increase the overall memory and CPU usage. In short, disk management and optimization are crucial for SQL Server performance. In this post, we are going to focus on just that. Read along to know more.
Also Read: Why is SQL Server 2016 Faster than all earlier EditionsAlso Read: Performance Best Practices for SQL Server on Azure Virtual Machines
Before going into the specifics, there are some generic requirements that are recommended for SQL Server Performance. These are as follows:
Also Read: Performance Tuning Best Practices for SQL Server in a Virtual Machine
Note: The new SQL Server 2016 Installation program allows you to create seperate TempDB files during the setup process.
In the last couple of years, storage pools gained a lot of popularity. Instead of complex and expensive RAID implementations, storage pools are the way to go in the future. With storage pools, you can add disks/spindles as you need more performance. However, even though there is a common storage pool across workloads, it is important to isolate them at the logical level. This, in turn, would help you to isolate disk bottlenecks of a slow performing query. If you put everything in a single pool, there is no way to tell what is causing the disk performance to degrade. On a high level, isolate the System DB’s, User DB’s, TempDB’s and Log files at the logical level.
Modern storage system controllers already do a great job of distributing workloads across different disks internally and implementing RAID on top of it does not make a lot of sense. However, it is important to contact your storage vendor for the best practices.
Hopefully, this was helpful. If you have any comments or suggestions, please feel free to drop us a comment below in the comment section.
Comments are closed.