SQL Server - How to Identify a Slow or “Heavy” Query in Your Database

One thing that should be very clear is that the goal of performance / query tuning is not always simply to reduce the execution time of a delayed query. Often, one should analyze the overall scope of the instance and identify the key performance issues presented and address them macro-wise.

A good starting point for our analysis is by consulting the sys.dm_os_wait_stats DMV, with the query below:

Result:

This query will present the waiting events that took the most time, that is, it will evaluate among everything that was executed in the instance, what most made these queries wait resources, be it disk, CPU, memory, network, etc. This is very useful for identifying where our biggest processing bottleneck is.

The big Paul Randal has provided a version of this query, which provides some statistics about these wait events, such as% wait time and a link to explain what this wait event is, and also filters events that are usually just warnings and no problems, making it easy the identification of what is really disturbing our instance.

Following script:

Result: (much more "clean", right? "

To make your life easier, I will list below some common events and their possible cause:

• ASYNC_NETWORK_IO / NETWORKIO: The wait networkio (SQL 2000) and async_network_io (SQL 2005 +) events can point to network issues (rarely), but can usually indicate that a client application is not processing SQL Server results fast enough. This event usually appears in RBAR (Row-By-Agonizing-Row) cases.
• CXPACKET: Event usually linked to the execution of queries using parallel processing, it may indicate that one thread has already completed its processing and is waiting for the execution of the other threads of the process to complete the execution. If this event has a very high wait_time, you may want to review the settings for MAXDOP, evaluate whether hint OPTION (MAXDOP x) may be useful in some queries, reevaluate the indexes used by the most disk-consuming queries (probably those that are using parallelism), and try to ensure that sargable arguments are being used.
• DTC: This wait event is not local. When using Microsoft Distributed Transaction Coordinator (MS-DTC), a transaction is opened on multiple systems at the same time and the transaction is only completed when it runs on all of these systems.
• OLEDB: This wait event indicates that the process has made a call to an OLEDB provider and is waiting for this process call on the destination server to return the data. This event is also raised when we execute commands in other instances using Linked Servers, BULK INSERT commands, and FULLTEXT-SEARCH queries. There is nothing to do on the local instance, but in cases of remote calls to other instances, you can analyze the target instance and try to identify the reason for the delay in processing and returning the data.
• PAGEIOLATCH_ *: This event occurs when SQL Server is waiting to read data to disk / storage from pages that are not in memory, generating disk contention. To decrease this I / O event, you can try increasing the disk speed (to reduce this time), adding more memory (to allocate more pages), or trying to identify and tune those queries that are generating this I / O event.

  A very common cause that generates this event is the lack of optimum indexes for this particular query, which can be resolved by creating new indexes with the help of Missing Index DMVs to avoid Scan operations (Read the post Understanding Index Functioning in SQL Server to understand it better).

• PAGELATCH_ *: The most common causes for this event are tempdb contention on heavily overloaded instances. The wait PAGELATCH_UP usually occurs when multiple threads are trying to access the same bitmap, while the PAGELATCH_EX event occurs when threads are trying to insert data on the same disk page, and the PAGELATCH_SH event indicates that some thread is trying to read data from a page being modified
• IO_COMPLETION: This wait event occurs when SQL Server is waiting for I / O operations to complete processing, which are not readings of indexes or data on disk, but readings of bitmap allocations from disk (GAM, SGAM, PFS), transaction log, write of buffer buffers on disk, read VLF headers from transaction log, read / write merge join / eager spools on disk, etc.

  This is a normal event, as it usually appears just before any I / O requesting operation begins, but can be a problem if the wait time is too high and your list of highest waits includes ASYNC_IO_COMPLETION, LOGMGR, WRITELOG or PAGEIOLATCH_ *.

  When there is an I / O problem in the instance, the SQL Server log usually displays “I / O requests are taking longer than X seconds to complete” messages, which can happen if the disks are really slow or are processing operations that consume a lot of I / O, such as BACKUP, ALTER / CREATE DATABASE and AutoGrowth events.

  To address this issue, look for queries that have very high disk read / write times, including Disk avg counters. read time, Disk avg. write time, Avg. disk queue length, Buffer cache hit ratio, Buffer free pages, Buffer page life expectancy and Machine: memory used

• SOS_SCHEDULER_YIELD: This event occurs when SqlOS (SOS) is waiting for more CPU resources to finish processing, which may indicate that the server is CPU overloaded and unable to process all tasks that are requested. However, this does not always indicate that it is a general instance problem, as it may indicate that a particular query that needs more CPU.

  If your query has some parallelism inhibitors (Ex: HINT MAXDOP (1), serial UDF functions, system table queries, etc.), it can cause processing to be done using only 1 CPU Core, causing the SOS_SCHEDULER_YIELD event can be raised even though the server has multiple colors available for processing and with low CPU utilization on the instance.

  To try to identify this, try to find the queries that are most CPU consuming and / or that the CPU time is less than the execution time (when parallelism occurs, the query sometimes consumes CPU 10s and executes on 2s, which means that it has been parallelized across multiple CPU cores)

• WRITELOG: When a transaction is waiting for the WRITELOG event, it means that it is waiting for SQL Server to capture the log cache data and write it to disk (in the transaction log). Because this operation involves disk writing, which is often much slower than memory access or CPU processing, this type of event can be quite common in instances that do not have a very fast disk. If you encounter this event a lot, an alternative to reducing this wait is to use the Delayed Durability, available from SQL Server 2014
• LCK *: Very common event that is not directly related to performance, this event occurs when a transaction is changing an object and it locks that object to prevent any other transaction from modifying or accessing data while it is processing. Thus, if another transaction tries to access this object, it will have to wait for the removal of this lock to continue processing, generating precisely this wait event. This is very confounded as a performance issue, because the query will actually take as long as it takes before the lock is released and it can process your data, making the user feel sluggish in the environment, even more so. is a widely used query in the system. In this case, an analysis of the session generating the lock is appropriate to assess why it is taking so long to process and release the object.

To delve into the analysis of wait events, I recommend reading the posts below:
- https://www.sqlskills.com/help/sql-server-performance-tuning-using-wait-statistics/
- https://www.sqlskills.com/blogs/erin/the-accidental-dba-day-25-of-30-wait-statistics-analysis/
- Wait statistics, or please tell me where it hurts
- List of common wait types
- Using Wait Stats to Find Why SQL Server is Slow
- What is the most worrying wait type?

As I had already shown in the post SQL Server - How to identify and collect time consuming query information using Extended Events (XE), using an Extended Event (XE), we can collect all queries that take more than X seconds to process on the instance and then analyze which queries they are, allowing us to sort the results by execution time, disk consumption (read or write) and also CPU consumption:

Thus, in conjunction with wait event analysis, we can begin the work of identifying potential CPU, Disk, or runtime consumption aggressors.

As I had already shown in the post SQL Server - How to identify and collect time consuming query information using Trace (SQL Server Profiler)Using a simple trace, we can collect all queries that take longer than X seconds to process in the instance and then analyze which queries are these, allowing us to sort the results by runtime, disk consumption (read or write) and also consumption CPU:

Thus, in conjunction with wait event analysis, we can begin the work of identifying potential CPU, Disk, or runtime consumption aggressors.

As I mentioned in the beginning of this article, the concept is “heavy” is relative. It can be runtime, disk consumption for reading, disk consumption for writing, CPU time, etc.

Using the database statistics and the sys.dm_exec_query_stats DMV, we can query the queries that were executed on the instance and apply the runtime, disk and CPU filters and sorting, including allowing to identify the number of times this query was executed, average execution time and last execution time, besides informing the complete query (text) that is being executed and also the specific excerpt of this object / query (TSQL) of these collected statistics and also the execution plan of this query.

Query Used (Sorting by Runtime):

Result:

As you can see, in the example above I filtered the data to return to me the queries that take the longest to execute, but you can sort by the column you want, being able to fine-tune your query according to your environment and need, along with the analysis. of wait events.

Remember that as we are querying bank statistics data, if the service is restarted, this data is lost. For this reason, I suggest creating a routine that collects this data from time to time and stores it in a physical table, so when that happens, you can continue your analysis of where you left off and don't have to wait for hours or days to have data to continue analyzing. .

Stored Procedure to store Ad-hoc queries from all databases:

Using an idea and structure similar to the previous example, SQL Server provides us with DMV sys.dm_exec_procedure_stats, where we can view execution statistics grouped by object and execution plan, where the same object can return more than 1 record in this DMV if he has more than one execution plan (parameter sniffing case, for example).

Query Used (Sort by Number of Runs):

Result:

Remember that as we are querying bank statistics data, if the service is restarted, this data is lost. For this reason, I suggest creating a routine that collects this data from time to time and stores it in a physical table, so when that happens, you can continue your analysis of where you left off and don't have to wait for hours or days to have data to continue analyzing. . If the object is changed or recompiled, a new execution plan will be generated for it and the data from this view will be zeroed, which reinforces the need to have this history to identify the effect of these plan changes.

Stored Procedure to store SP statistics for all databases:

Utility widely used by DBA's around the world, the sp_WhoIsActive is a Stored Procedure that performs several queries in SQL Server's DMV's, especially in the sys.dm_exec_sessions and sys.dm_exec_requests DMV's to identify the queries that are currently running and return their execution statistics, including, until I developed a "lite" version from sp_WhoIsActive and shared it here on the blog, in the post SQL Server - Query to return running queries (sp_WhoIsActive without consuming TempDB).

Using this SP, we can track the queries running on the instance and evaluate which are consuming the most CPU, Disk, Memory and also, wait events of each query and if any session is causing locks on the instance:

For this reason, I find it very interesting to have a routine that collects data from this Stored Procedure / View every X minutes and stores it in a physical table for X days, so that you can analyze the past in case of a slowness and you want to. identify exactly what was running at any given time and how was the resource consumption (CPU, disk, memory, wait events, etc.) of each such query.

Having this level of information can be very helpful in investigating performance issues (and even other types of issues as well).

This Stored Procedure, developed by the great Brent ozar, is a true Swiss army knife to quickly identify performance issues. When executed, sp_BlitzFirst performs a series of general checks on the SQL instance and logs to try to find possible performance issues and tell you what it is and how to fix it.

Sp_BlitzCache performs various checks on cached execution plans in the instance and looks for situations that usually lead to poor performance during execution, such as missing indexes, implicit conversions, etc.

Example of running sp_BlitzFirst (Instance Overview):

Sp_BlitzCache execution example (Parses cached execution plans):

Here's how it works in practice:

Download SP's this link here.

Another commonly used way to identify tunable queries and potential instance performance issues is by using SQL Server Management Studio (SSMS) reports and Activity Monitor:

And analyzing his results, where he already shows some queries that he thinks consumes a lot of instance resources:

SSMS also offers us a number of ready-made reports, ranging from measuring disk space used by each table, to performance reports (Index Usage Statistics, Index Physical Statistics, and Object Execution Statistics):

Object Execution Statistics Report:

SSMS even has reports assisting the migration of physical objects (Tables and Stored procedures) to objects in memory (In-Memory OLTP), through the “Transaction Performance Analysis Overview” report:

Another important ally in identifying potential problems in the SQL Server instance is Perfmon (Performance Monitor), a utility that is already installed on Windows. With it, we can add a series of counters, according to what you want to analyze, and the tool shows us graphically and with numbers, how is the CPU usage, the number of readings / second, the number of bytes written / second and a gigantic series of counters and metrics that we can use to analyze server performance.

Accessing Perfmon:

Graphic view:

Textual view:

Counter Types:

Video of MVP Osanam Giordane About Perfmon:

No artigo Perfmon Counters, from MCM Fabricio Catae, he gives us a list of counters that are commonly used to monitor SQL Server instances, which are:

Logical Disk

• Avg Disk Sec / Read
• Avg Disk Sec / Transfer
• Avg Disk Sec / Write
• Current Disk Queue Length
• Disk Bytes / sec
• Disk Read Bytes / sec
• Disk Write Bytes / sec
• Disk Reads / sec
• Disk Transfers / sec
• Disk Writes / sec

Memory

• % Committed Bytes In Use
• Available MB
• Committed Bytes
• Free System Page Table Entries
• Pool Nonpaged Bytes
• Pool Paged Bytes

Network Interfaces

• Bytes Received / sec
• Bytes Sent / sec
• Total Bytes / sec

Processor

• % Processor Time
• % Privileged Time

System

• Context Switches / sec
• Exception Dispatches / sec
• Processor Queue Length
• System Calls / sec

Additionally, use these counters per instance of SQL:
Buffer Manager

• Database pages
• Free list stalls / sec
• Free pages
• Lazy writes / sec
• Page life expectancy
• Page lookups / sec
• Page reads / sec
• Readahead pages / sec
• Stolen pages
• Target pages
• Total pages

General Statistics

• Connection Reset / sec
• Logins / sec
• Logouts / sec
• User Connections

SQL Statistics

• Batch Requests / sec
• Safe Auto-Params / sec
• Forced Parametrizations / sec
• SQL Compilations / sec
• SQL Re-Compilations / sec

To dig deeper into monitoring SQL Server instances using Perfmon, be sure to check out the Posts by Ninja Perfmon.

And finally, a very practical and quick way to identify “heavy” queries is using third party software, usually paid for, to monitor the performance of your instance and thus give you a graphical and intuitive view of how your SQL instance is doing. Server.

For that, I separated some tools I already used and found very cool:

RedGate SQL Monitor (Paid)

ApexSQL Monitor (Paid)

Quest Spotlight (Paid)

SQLSentry SentryOne (Paid)

SQL Performance Monitor (free)

See more tools by accessing this link here.