When it comes to performance issues with your Microsoft SQL Servers, reviewing key input/output metrics can give you all the information that you need to determine what may be causing the slowdown. I/O bottlenecks may occur for a number of reasons — but once you target the reason behind it, it makes it easier to improve your SQL Server performance.
What causes I/O bottlenecks?
Most I/O bottlenecks form because of lack of storage to support the needs of the database, which grows over time as more queries are written. As your databases — including tempDB outside of your user database — consume more space over time, your processes suffer as a result.
Often times, pinpointing which aspect of the storage stack may be causing the issue can be challenging. Instead of leveraging disk space, more SQL Server subset systems leverage SAN or NAS storage, as well as virtual machines.
How to uncover what’s causing the bottleneck
There are a number of key metrics that I recommend tracking to see what may be causing I/O bottlenecks.
Seeing your latency metrics indicates how long it takes for your input/operation to complete. Using these metrics, you can see the total time it takes for a request, starting when it’s kicked off by the disk controller to when it ends the processing the request.
The highest write/read latencies will indicate which databases are causing the issues, giving you enough information to focus on driving better performance for those databases. As a reference point, Microsoft recommends that anything over .015ms requires immediate investigation when monitoring I/O on SQL Server.
Take a look at how your physical memory impacts your databases. Often times, this gets overlooked as more organizations rely on virtual machines and only a subset of their database relies on physical memory.
To accurately view memory pressure in your SQL Server workloads, I recommend checking out a few key metrics like:
- Buffer pool size
- Buffer cache hit ratio
- Pages per second
- Page faults per second
- Page splits
If you notice I/O bottlenecks, take a look at your tempDB database. The tempDB is exactly as it sounds: a temporary database where your SQL Server can perform tasks with temporary objects, like grouping and sorting.
Because it lives on your disk, it can consume a large chunk of your storage and memory. As a result, your users can start experiencing performance issues without your actual database throwing issues. Managing the tempDB database can save you bottleneck issues in the long run.
Prevent future I/O bottleneck issues
Monitoring metrics in real time can be challenging when you have other priorities that are more pressing than a lapse in performance for a database that appears to be working well enough.
Given that, leveraging a database monitoring platform that offers alerts and recommendations — with thresholds that you can set — enables you to passively monitor for performance issues without draining your resources in the process.
Interested in getting more visibility into your Microsoft SQL Servers? Try a free trial of SelectStar and within minutes of installation, see key metrics and drive better performance across your infrastructure and databases.