Management Packs vRealize Operations

Using vRealize Operations Manager to Diagnose SQL Query Slowness

By Troy Bailey, Blue Medora

Slow running SQL queries are one of the most common problems database administrators (DBAs) face. This is usually manifested in a call from the help desk or to the database team saying “my application is running slowly,” or “my database is slow.” This lag can quickly translate to a bad customer experience or even loss of revenue. With VMware vRealize Operations (vROps), the slow behavior can be identified quickly before it becomes a problem for your day-to-day operations. The Management Pack for Microsoft SQL Server both uses VMware vRealize Operations’ patented analytics engine to proactively identify and eliminate slowdowns in your database environments.

Figure 1_SQL Query blog
Figure 1 – Metric graph from a Microsoft SQL Server instance

Metric graphs in vROps are one of the ways that a DBA can visualize the data being collected from the Management Pack for Microsoft SQL Server. The metric in Figure 1 is “High Query Execution” on the SQL Server MSSQL-D1 MSSQLSERVER instance. This metric provides the largest amount of time a single query has been executed since the last compilation. You can also see vRealize Operations’ predictive analytics or dynamic thresholds represented by the darker gray bar behind the metric line.

Using this metric above and vRealize Operations’ analytics, a DBA could define an alert definition that would notify the DBA when query execution time is going up and when it’s not behaving normally.

Alerting in vROps is highly configurable and user-friendly to set up. Alerts are made of symptom(s). You can have a single symptom or multiple symptoms in your alert definition. In this example (Figure 2), we will be using a single symptom to define the alert but you can make them as smart as you would like. We’ll create the symptom based around our “High Query Execution” metric. We are also using the dynamic thresholds that the vROps analytics is calculating.

Figure 2_SQL Query blog
Figure 2 – Symptom using High Query Execution metric and dynamic thresholds

Now that we have our symptom defined, we need to create my alert definition using it. We define a new alert definition using our newly created symptom and give it a criticality. Now our DBA will receive our alert any time our High Query Execution is above normal. Using vRealize Operations, it’s that simple to define and setup intelligent monitoring for your infrastructure.

Figure 3_SQL Query blog
Figure 3 – Alert definition using “Elevated High Query Execution” symptom

Intelligent alerting provides visibility into potential issues before they evolve into incidents and outages. With advanced alerting, you have fewer incidents and resolve incidents faster when they do occur.

For more information on VMware vRealize Operations, visit the product page on the VMware website. Find out more about the Management Pack for Microsoft SQL Server by visiting the product page on Blue Medora’s website and keep up with the VMware Cloud Management blog for more insight into VMware cloud management products.