Skip to content

Latest commit

 

History

History
75 lines (51 loc) · 7.29 KB

troubleshoot-slow-running-queries.md

File metadata and controls

75 lines (51 loc) · 7.29 KB
Raw
title description ms.date ms.custom
Troubleshoot slow-running queries
This article describes how to handle a performance issue that applications may experience when using SQL Server.
08/22/2022
sap:SQL resource usage and configuration (CPU, Memory, Storage)

Troubleshoot slow-running queries in SQL Server

Original product version:   SQL Server
Original KB number:   243589

Introduction

This article describes how to handle a performance issue that database applications may experience when using SQL Server: slow performance of a specific query or group of queries. The following methodology will help you narrow down the cause of the slow queries issue and direct you towards resolution.

Find slow queries

To establish that you have query performance issues on your SQL Server instance, start by examining queries by their execution time (elapsed time). Check if the time exceeds a threshold you have set (in milliseconds) based on an established performance baseline. For example, in a stress testing environment, you may have established a threshold for your workload to be no longer than 300 ms, and you can use this threshold. Then, you can identify all queries that exceed that threshold, focusing on each individual query and its pre-established performance baseline duration. Ultimately, business users care about the overall duration of database queries; therefore, the main focus is on execution duration. Other metrics like CPU time and logical reads are gathered to help with narrowing down the investigation.

[!INCLUDE collect query data and logical reads]

Running vs. Waiting: why are queries slow?

If you find queries that exceed your predefined threshold, examine why they could be slow. The cause of performance problems can be grouped into two categories, running or waiting:

  • WAITING: Queries can be slow because they're waiting on a bottleneck for a long time. See a detailed list of bottlenecks in types of Waits.

  • RUNNING: Queries can be slow because they're running (executing) for a long time. In other words, these queries are actively using CPU resources.

A query can be running for some time and waiting for some time in its lifetime (duration). However, your focus is to determine which is the dominant category that contributes to its long elapsed time. Therefore, the first task is to establish in which category the queries fall. It's simple: if a query isn't running, it's waiting. Ideally, a query spends most of its elapsed time in a running state and very little time waiting for resources. Also, in the best-case scenario, a query runs within or below a predetermined baseline. Compare the elapsed time and CPU time of the query to determine the issue type.

[!INCLUDE establish runner or waiter perf type]

High-level visual representation of the methodology

:::image type="content" source="media/troubleshoot-slow-queries/slow-queries-flow.svg" alt-text="The screenshot shows a high-level visual representation of the methodology for troubleshooting slow queries.":::

Diagnose and resolve waiting queries

If you established that your queries of interest are waiters, your next step is to focus on resolving bottleneck issues. Otherwise, go to step 4: Diagnose and resolve running queries.

[!INCLUDE diagnose waits]

Diagnose and resolve running queries

If CPU (worker) time is very close to the overall elapsed duration, the query spends most of its lifetime executing. Typically, when the SQL Server engine drives high CPU usage, the high CPU usage is coming from queries that drive a large number of logical reads (the most common reason).

[!INCLUDE identify cpu bound queries]

Common methods to resolve long-running, CPU-bound queries

Recommended resources