Long Running Queries Advisor

The Long Running Queries Advisor page shows you details on the queries that are taking the longest run time on your database.

Long Running Queries Advisor – Help Guide

The Long Running Queries Advisor in Database Health Monitor helps identify queries that run for an extended time, impacting SQL Server performance. It provides details such as:

• Total Run Time – The cumulative execution time of the query.
• Average Run Time – The typical duration for each execution.
• Longest Run Time – The maximum time recorded for a single execution.
• Times Run – The total number of times the query has been executed.

This tool is useful for diagnosing slow queries, optimizing performance, and ensuring efficient SQL Server operation.

---

Understanding the Report

The Long Running Queries Advisor displays a problematic query and its execution details.

• Query Analysis
  • The SQL query shown is one that has been executed frequently and may be contributing to performance issues.
  • Reviewing the query can help identify inefficiencies, such as missing indexes, excessive joins, or unnecessary computations.
• Performance Metrics
  • High Total Run Time → Indicates a cumulative performance impact.
  • High Average Run Time → Suggests an inherently slow query.
  • High Longest Run Time → May indicate occasional performance spikes.
  • High Times Run → A frequently executed query may create resource contention.

---

How to Use This Information

1. Identify Problematic Queries

Check if the query is running too often or taking too long. A high Total Run Time with a low Average Run Time suggests frequent execution, while a high Average Run Time indicates an inherently slow query.

2. Optimize the Query

Consider:

• Indexing: Ensure appropriate indexes exist for WHERE and JOIN conditions.
• Query Execution Plan: Use SET STATISTICS IO ON and SET STATISTICS TIME ON to analyze the execution plan.
• Reducing Unnecessary Operations: Avoid SELECT *, unnecessary joins, or inefficient subqueries.
• Temp Table Review: If temporary tables are used (#tempFiles in the example), evaluate if they are needed or can be replaced with table variables or optimized queries.

3. Investigate Blocking or Deadlocks

• Using the deadlock and blocking reports in Database Health Monitor.

4. Monitor Execution Trends

• If this query has suddenly slowed down, check for:
  • Recent schema or index changes.
  • High CPU or memory pressure on the server.
  • Statistics that need updating (UPDATE STATISTICS command).

---

Next Steps

• Use Database Health Monitor to track long-running queries over time: DatabaseHealth.com.
• Optimize SQL Server performance with Stedman Solutions’ Managed Services: Learn More.
• If you need expert SQL Server performance tuning, contact us here: Contact Stedman Solutions.

By using the Long Running Queries Advisor, you can identify slow queries, optimize SQL Server performance, and prevent bottlenecks before they affect your business. 🚀