This article is part 10 in a series on the top 10 most common mistakes that I have seen impact SQL Server Performance and Resiliency. This post is not all-inclusive.
Most common mistake #10: Storage
For the final post in the top 10 items that influence the performance and resiliency of the databases, we will talk about storage. Storage is by far the number one cause of performance issues I have experienced, and over the last 20 years of my career, the changes with storage have been huge. When I first started as a professional, we were using internal spinning disks and then quickly changed up to internal RAID arrays. Five or so years later, we moved to external RAID cages. Eventually the road took us to SAN and NAS storage and most recently SSD. With all these changes, it is easy to see why we have administrators who focus on nothing but storage. If you are fortunate enough to have a Storage Administrator, do you as a DBA still need to understand what the storage is doing?
How can you identify if you are having some sort of performance bottleneck? There are a number of indicators that can provide you with the evidence your database is having a storage issue. A common indicator used for a number of years is the storage latency. Storage latency information is collected by using the Performance Monitor in Windows. Add the counters Average Disk/sec Read and Average Disk/sec Write.
The storage latency can be monitored in real time or the data can be recorded by starting a data collection.
According to Microsoft Best Practices, the latency on the disk the log file resides should be less than five milliseconds, and the data file latency should be less than 20 milliseconds. In my experience, I have seen log file latency climb as high as 10 millisecond and sometimes a little higher during spikes without any end user impact. In addition take location note of the TempDB database as we talked about in Tip 8. You will want to ensure you are keeping the TempDB on the fastest storage you can.
There are additional tools you may want to consider using to dig deeper into the performance of your storage systems such as SQLIO and SQLIOSIM both from Microsoft. A couple popular third party tools include IOMeter and CrystalDiskMark.
If you suspect you may be having performance issues with your SQL Server, you may want to consider having us perform a Health Check on your SQL Server or to get answers about your general questions.
Please do not miss my other blogs regarding this topic.
Top 10 Tips for SQL Server Performance and Resiliency
1. Improper Backups
2. Improper Security
3. Improper Maintenance
4. Not having a baseline
5. Max Memory settings
6. Change History
7. Disaster Recovery Plans
8. Configure TempDB
9. Shrinking Your Database