Say Goodbye to I/O Bottlenecks, Hello to Better SQL Server Performance

by bluemedora_editor on March 13, 2017

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.

Monitoring Latency Read Delay with SelectStar
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.

Memory Pressure
Monitoring Buffer Pool Size with SelectStar

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

TempDB Database
Monitoring TempDB Metrics with SelectStar

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.

This post first appeared on the SelectStar blog. Read the full post here

Get started

Try BindPlane for free. No credit card required.

Sign up
True Visibility
BindPlane for VMware vRealize Operations

True Visibility allows cloud management teams to use VMware vRealize’s powerful machine learning and capacity planning engine across their entire hybrid cloud environment.

Azure Monitor...everything
BindPlane for Microsoft Azure Monitor

Make Azure Monitor your first-pane-of-glass across your entire multi-cloud, multi-database or hybrid platform environment.

Thank you for contacting us. Your information was received. We'll be in touch shortly.