Skip to content

Dirceu Resende

DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS)

  • Consultancy
    • BI Consulting
    • Power BI Consulting
    • SQL Server Consulting
  • File
  • Series
    • Certification
    • Security and Audit
    • Performance tuning
    • What has changed in T-SQL?
    • Data Protection
  • Jobs
  • Data Platform Events
  • About
  • Contact

Other Languages

Subscribe to a blog by email

Enter your email address to subscribe to this blog and receive notifications of new publications by email.

Join 536 other subscribers

Blog Views

1.645.436 views

Categories

  • Apache / .htaccess (9)
  • Database (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Audit (15)
      • Azure (2)
      • CLR (53)
      • Query Development (83)
      • DMVs and Catalog Views (31)
      • Errors (22)
      • Tools (12)
      • Data Formatting and Validation (23)
      • Little Known Features (19)
      • Hacks (17)
      • Easy (30)
      • File Handling (13)
      • Maintenance (80)
      • Monitoring (35)
      • What not to do (7)
      • OLE Automation (19)
      • Performance tuning (22)
      • Python (1)
      • Safety (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Career and Courses (13)
  • Career, Courses and Certifications (28)
  • Cell Phones (1)
  • Events and Lectures (63)
  • Programme (57)
    • C # (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Uncategorized (10)
  • SEO (4)
  • Virtualization (5)

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

  • Black Friday discounts on SQL Server Trainings (Buy my kkkkk course) November 27th, 2020
  • SQL Server - The “new” GREATEST and LEAST functions November 27th, 2020
  • SQL Server - How to know the date of a user's last login November 9th, 2020
  • Azure in Free Practice # 07 - Administering Databases in Azure November 5th, 2020
  • Analysis Services - An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file 'tmcachemanager.cpp', function 'TMCacheManager :: CreateEmptyCollectionsForAllParents') November 5th, 2020
  • 8 July 2018
  • 2
  • Database Query Development DMVs and Catalog Views Maintenance Monitoring Performance tuning SQL Server

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

Views: 14.665
This post is the 3 part of 8 in the series. Performance tuning
  • SQL Server - Introduction to Performance Tuning Study
  • Understanding Index Functioning in SQL Server
  • SQL Server - How to Identify a Slow or “Heavy” Query in Your Database
  • SQL Server - Performance Tuning Tips: Implicit Conversion? NEVER!
  • SQL Server - Performance Comparison between Scalar Function and CLR Scalar Function
  • SQL Server - Performance Tuning Tips: What is the Difference Between Seek Predicate and Predicate?
  • SQL Server - Using Calculated Columns (or Computed Columns) for Performance Tuning
  • SQL Server - How to identify and collect time consuming query information using Extended Events (XE)
Reading Time: 16 minutes

Hello everybody!
In this post, I would like to demonstrate some ways to identify slow or heavy queries that end up consuming a lot of machine resources and take a long time to return results, whether due to excessive CPU, memory or disk usage.

The purpose of this article is to assist you in identification queries that have potential performance issues. Once these queries are identified, you should then consider whether you need to add more hardware to the machine or start performance and query tuning activities (which is not the focus of this post).

Wait event analysis

View content
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:

Transact-SQL
1
2
3
SELECT *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC

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:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
;WITH [Waits]
AS ( SELECT
         [wait_type],
         [wait_time_ms] / 1000.0 AS [WaitS],
         ( [wait_time_ms] - [signal_wait_time_ms] ) / 1000.0 AS [ResourceS],
         [signal_wait_time_ms] / 1000.0 AS [SignalS],
         [waiting_tasks_count] AS [WaitCount],
         100.0 * [wait_time_ms] / SUM([wait_time_ms]) OVER () AS [Percentage],
         ROW_NUMBER() OVER ( ORDER BY
                                 [wait_time_ms] DESC
                           ) AS [RowNum]
     FROM
         sys.dm_os_wait_stats
     WHERE
         [wait_type] NOT IN (
                                -- These wait types are almost 100% never a problem and so they are
                                -- filtered out to avoid them skewing the results. Click on the URL
                                -- for more information.
                                N'BROKER_EVENTHANDLER', -- https://www.sqlskills.com/help/waits/BROKER_EVENTHANDLER
                                N'BROKER_RECEIVE_WAITFOR', -- https://www.sqlskills.com/help/waits/BROKER_RECEIVE_WAITFOR
                                N'BROKER_TASK_STOP', -- https://www.sqlskills.com/help/waits/BROKER_TASK_STOP
                                N'BROKER_TO_FLUSH', -- https://www.sqlskills.com/help/waits/BROKER_TO_FLUSH
                                N'BROKER_TRANSMITTER', -- https://www.sqlskills.com/help/waits/BROKER_TRANSMITTER
                                N'CHECKPOINT_QUEUE', -- https://www.sqlskills.com/help/waits/CHECKPOINT_QUEUE
                                N'CHKPT', -- https://www.sqlskills.com/help/waits/CHKPT
                                N'CLR_AUTO_EVENT', -- https://www.sqlskills.com/help/waits/CLR_AUTO_EVENT
                                N'CLR_MANUAL_EVENT', -- https://www.sqlskills.com/help/waits/CLR_MANUAL_EVENT
                                N'CLR_SEMAPHORE', -- https://www.sqlskills.com/help/waits/CLR_SEMAPHORE
                                N'CXCONSUMER', -- https://www.sqlskills.com/help/waits/CXCONSUMER
 
                                -- Maybe comment these four out if you have mirroring issues
                                N'DBMIRROR_DBM_EVENT', -- https://www.sqlskills.com/help/waits/DBMIRROR_DBM_EVENT
                                N'DBMIRROR_EVENTS_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_EVENTS_QUEUE
                                N'DBMIRROR_WORKER_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_WORKER_QUEUE
                                N'DBMIRRORING_CMD', -- https://www.sqlskills.com/help/waits/DBMIRRORING_CMD
 
                                N'DIRTY_PAGE_POLL', -- https://www.sqlskills.com/help/waits/DIRTY_PAGE_POLL
                                N'DISPATCHER_QUEUE_SEMAPHORE', -- https://www.sqlskills.com/help/waits/DISPATCHER_QUEUE_SEMAPHORE
                                N'EXECSYNC', -- https://www.sqlskills.com/help/waits/EXECSYNC
                                N'FSAGENT', -- https://www.sqlskills.com/help/waits/FSAGENT
                                N'FT_IFTS_SCHEDULER_IDLE_WAIT', -- https://www.sqlskills.com/help/waits/FT_IFTS_SCHEDULER_IDLE_WAIT
                                N'FT_IFTSHC_MUTEX', -- https://www.sqlskills.com/help/waits/FT_IFTSHC_MUTEX
 
                                -- Maybe comment these six out if you have AG issues
                                N'HADR_CLUSAPI_CALL', -- https://www.sqlskills.com/help/waits/HADR_CLUSAPI_CALL
                                N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', -- https://www.sqlskills.com/help/waits/HADR_FILESTREAM_IOMGR_IOCOMPLETION
                                N'HADR_LOGCAPTURE_WAIT', -- https://www.sqlskills.com/help/waits/HADR_LOGCAPTURE_WAIT
                                N'HADR_NOTIFICATION_DEQUEUE', -- https://www.sqlskills.com/help/waits/HADR_NOTIFICATION_DEQUEUE
                                N'HADR_TIMER_TASK', -- https://www.sqlskills.com/help/waits/HADR_TIMER_TASK
                                N'HADR_WORK_QUEUE', -- https://www.sqlskills.com/help/waits/HADR_WORK_QUEUE
 
                                N'KSOURCE_WAKEUP', -- https://www.sqlskills.com/help/waits/KSOURCE_WAKEUP
                                N'LAZYWRITER_SLEEP', -- https://www.sqlskills.com/help/waits/LAZYWRITER_SLEEP
                                N'LOGMGR_QUEUE', -- https://www.sqlskills.com/help/waits/LOGMGR_QUEUE
                                N'MEMORY_ALLOCATION_EXT', -- https://www.sqlskills.com/help/waits/MEMORY_ALLOCATION_EXT
                                N'ONDEMAND_TASK_QUEUE', -- https://www.sqlskills.com/help/waits/ONDEMAND_TASK_QUEUE
                                N'PARALLEL_REDO_DRAIN_WORKER', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_DRAIN_WORKER
                                N'PARALLEL_REDO_LOG_CACHE', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_LOG_CACHE
                                N'PARALLEL_REDO_TRAN_LIST', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_TRAN_LIST
                                N'PARALLEL_REDO_WORKER_SYNC', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_WORKER_SYNC
                                N'PARALLEL_REDO_WORKER_WAIT_WORK', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_WORKER_WAIT_WORK
                                N'PREEMPTIVE_XE_GETTARGETSTATE', -- https://www.sqlskills.com/help/waits/PREEMPTIVE_XE_GETTARGETSTATE
                                N'PWAIT_ALL_COMPONENTS_INITIALIZED', -- https://www.sqlskills.com/help/waits/PWAIT_ALL_COMPONENTS_INITIALIZED
                                N'PWAIT_DIRECTLOGCONSUMER_GETNEXT', -- https://www.sqlskills.com/help/waits/PWAIT_DIRECTLOGCONSUMER_GETNEXT
                                N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', -- https://www.sqlskills.com/help/waits/QDS_PERSIST_TASK_MAIN_LOOP_SLEEP
                                N'QDS_ASYNC_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_ASYNC_QUEUE
                                N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', -- https://www.sqlskills.com/help/waits/QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP
                                N'QDS_SHUTDOWN_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_SHUTDOWN_QUEUE
                                N'REDO_THREAD_PENDING_WORK', -- https://www.sqlskills.com/help/waits/REDO_THREAD_PENDING_WORK
                                N'REQUEST_FOR_DEADLOCK_SEARCH', -- https://www.sqlskills.com/help/waits/REQUEST_FOR_DEADLOCK_SEARCH
                                N'RESOURCE_QUEUE', -- https://www.sqlskills.com/help/waits/RESOURCE_QUEUE
                                N'SERVER_IDLE_CHECK', -- https://www.sqlskills.com/help/waits/SERVER_IDLE_CHECK
                                N'SLEEP_BPOOL_FLUSH', -- https://www.sqlskills.com/help/waits/SLEEP_BPOOL_FLUSH
                                N'SLEEP_DBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DBSTARTUP
                                N'SLEEP_DCOMSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DCOMSTARTUP
                                N'SLEEP_MASTERDBREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERDBREADY
                                N'SLEEP_MASTERMDREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERMDREADY
                                N'SLEEP_MASTERUPGRADED', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERUPGRADED
                                N'SLEEP_MSDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_MSDBSTARTUP
                                N'SLEEP_SYSTEMTASK', -- https://www.sqlskills.com/help/waits/SLEEP_SYSTEMTASK
                                N'SLEEP_TASK', -- https://www.sqlskills.com/help/waits/SLEEP_TASK
                                N'SLEEP_TEMPDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_TEMPDBSTARTUP
                                N'SNI_HTTP_ACCEPT', -- https://www.sqlskills.com/help/waits/SNI_HTTP_ACCEPT
                                N'SP_SERVER_DIAGNOSTICS_SLEEP', -- https://www.sqlskills.com/help/waits/SP_SERVER_DIAGNOSTICS_SLEEP
                                N'SQLTRACE_BUFFER_FLUSH', -- https://www.sqlskills.com/help/waits/SQLTRACE_BUFFER_FLUSH
                                N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', -- https://www.sqlskills.com/help/waits/SQLTRACE_INCREMENTAL_FLUSH_SLEEP
                                N'SQLTRACE_WAIT_ENTRIES', -- https://www.sqlskills.com/help/waits/SQLTRACE_WAIT_ENTRIES
                                N'WAIT_FOR_RESULTS', -- https://www.sqlskills.com/help/waits/WAIT_FOR_RESULTS
                                N'WAITFOR', -- https://www.sqlskills.com/help/waits/WAITFOR
                                N'WAITFOR_TASKSHUTDOWN', -- https://www.sqlskills.com/help/waits/WAITFOR_TASKSHUTDOWN
                                N'WAIT_XTP_RECOVERY', -- https://www.sqlskills.com/help/waits/WAIT_XTP_RECOVERY
                                N'WAIT_XTP_HOST_WAIT', -- https://www.sqlskills.com/help/waits/WAIT_XTP_HOST_WAIT
                                N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', -- https://www.sqlskills.com/help/waits/WAIT_XTP_OFFLINE_CKPT_NEW_LOG
                                N'WAIT_XTP_CKPT_CLOSE', -- https://www.sqlskills.com/help/waits/WAIT_XTP_CKPT_CLOSE
                                N'XE_DISPATCHER_JOIN', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_JOIN
                                N'XE_DISPATCHER_WAIT', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_WAIT
                                N'XE_TIMER_EVENT' -- https://www.sqlskills.com/help/waits/XE_TIMER_EVENT
                            )
         AND [waiting_tasks_count] > 0 )
SELECT
    MAX([W1].[wait_type]) AS [WaitType],
    CAST(MAX([W1].[WaitS]) AS DECIMAL(16, 2)) AS [Wait_S],
    CAST(MAX([W1].[ResourceS]) AS DECIMAL(16, 2)) AS [Resource_S],
    CAST(MAX([W1].[SignalS]) AS DECIMAL(16, 2)) AS [Signal_S],
    MAX([W1].[WaitCount]) AS [WaitCount],
    CAST(MAX([W1].[Percentage]) AS DECIMAL(5, 2)) AS [Percentage],
    CAST(( MAX([W1].[WaitS]) / MAX([W1].[WaitCount])) AS DECIMAL(16, 4)) AS [AvgWait_S],
    CAST(( MAX([W1].[ResourceS]) / MAX([W1].[WaitCount])) AS DECIMAL(16, 4)) AS [AvgRes_S],
    CAST(( MAX([W1].[SignalS]) / MAX([W1].[WaitCount])) AS DECIMAL(16, 4)) AS [AvgSig_S],
    CAST('https://www.sqlskills.com/help/waits/' + MAX([W1].[wait_type]) AS XML) AS [Help/Info URL]
FROM
    [Waits] AS [W1]
    INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY
    [W1].[RowNum]
HAVING
    SUM([W2].[Percentage]) - MAX([W1].[Percentage]) < 95; -- percentage threshold
GO

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?

How to identify time consuming queries using Trace

View content
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.

How to identify “heavy” ad-hoc queries with DMV's

View content
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):

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
SELECT TOP 100
    DB_NAME(C.[dbid]) as [database],
    B.[text],
    (SELECT CAST(SUBSTRING(B.[text], (A.statement_start_offset/2)+1,  
        (((CASE A.statement_end_offset  
            WHEN -1 THEN DATALENGTH(B.[text])
            ELSE A.statement_end_offset  
        END) - A.statement_start_offset)/2) + 1) AS NVARCHAR(MAX)) FOR XML PATH(''), TYPE) AS [TSQL],
    C.query_plan,
 
    A.last_execution_time,
    A.execution_count,
    A.total_elapsed_time / 1000 AS total_elapsed_time_ms,
    A.last_elapsed_time / 1000 AS last_elapsed_time_ms,
    A.min_elapsed_time / 1000 AS min_elapsed_time_ms,
    A.max_elapsed_time / 1000 AS max_elapsed_time_ms,
    ((A.total_elapsed_time / A.execution_count) / 1000) AS avg_elapsed_time_ms,
    A.total_worker_time / 1000 AS total_worker_time_ms,
    A.last_worker_time / 1000 AS last_worker_time_ms,
    A.min_worker_time / 1000 AS min_worker_time_ms,
    A.max_worker_time / 1000 AS max_worker_time_ms,
    ((A.total_worker_time / a.execution_count) / 1000) AS avg_worker_time_ms,
  
    A.total_physical_reads,
    A.last_physical_reads,
    A.min_physical_reads,
    A.max_physical_reads,
  
    A.total_logical_reads,
    A.last_logical_reads,
    A.min_logical_reads,
    A.max_logical_reads,
  
    A.total_logical_writes,
    A.last_logical_writes,
    A.min_logical_writes,
    A.max_logical_writes
FROM
    sys.dm_exec_query_stats A
    CROSS APPLY sys.dm_exec_sql_text(A.[sql_handle]) B
    OUTER APPLY sys.dm_exec_query_plan (A.plan_handle) AS C
ORDER BY
    A.total_elapsed_time DESC

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:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
USE [dirceuresende]
GO
 
CREATE PROCEDURE [dbo].[stpCarga_Historico_Execucao_Consultas]
AS BEGIN
 
 
    IF (OBJECT_ID('dirceuresende.dbo.Historico_Execucao_Consultas') IS NULL)
    BEGIN
    
        -- DROP TABLE dirceuresende.dbo.Historico_Execucao_Consultas
        CREATE TABLE dirceuresende.dbo.Historico_Execucao_Consultas
        (
            Id_Coleta BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
            Dt_Coleta datetime NOT NULL,
            [database] sys.sysname NOT NULL,
            [text] NVARCHAR(MAX) NULL,
            [TSQL] XML NULL,
            [query_plan] XML NULL,
            last_execution_time datetime NULL,
            execution_count bigint NOT NULL,
            total_elapsed_time_ms bigint NULL,
            last_elapsed_time_ms bigint NULL,
            min_elapsed_time_ms bigint NULL,
            max_elapsed_time_ms bigint NULL,
            avg_elapsed_time_ms bigint NULL,
            total_worker_time_ms bigint NULL,
            last_worker_time_ms bigint NULL,
            min_worker_time_ms bigint NULL,
            max_worker_time_ms bigint NULL,
            avg_worker_time_ms bigint NULL,
            total_physical_reads bigint NOT NULL,
            last_physical_reads bigint NOT NULL,
            min_physical_reads bigint NOT NULL,
            max_physical_reads bigint NOT NULL,
            total_logical_reads bigint NOT NULL,
            last_logical_reads bigint NOT NULL,
            min_logical_reads bigint NOT NULL,
            max_logical_reads bigint NOT NULL,
            total_logical_writes bigint NOT NULL,
            last_logical_writes bigint NOT NULL,
            min_logical_writes bigint NOT NULL,
            max_logical_writes bigint NOT NULL
        ) WITH(DATA_COMPRESSION=PAGE)
 
        CREATE INDEX SK01_Historico_Execucao_Consultas ON dirceuresende.dbo.Historico_Execucao_Consultas(Dt_Coleta, [database])
 
    END
 
    
    DECLARE
        @Dt_Referencia DATETIME = GETDATE(),
        @Query VARCHAR(MAX)
 
 
    SET @Query = '
IF (''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb''))
BEGIN
 
    INSERT INTO dirceuresende.dbo.Historico_Execucao_Consultas
    SELECT
        ''' + CONVERT(VARCHAR(19), @Dt_Referencia, 120) + ''' AS Dt_Coleta,
        ''?'' AS [database],
        B.[text],
        (SELECT CAST(SUBSTRING(B.[text], (A.statement_start_offset/2)+1, (((CASE A.statement_end_offset WHEN -1 THEN DATALENGTH(B.[text]) ELSE A.statement_end_offset END) - A.statement_start_offset)/2) + 1) AS NVARCHAR(MAX)) FOR XML PATH(''''),TYPE) AS [TSQL],
        C.query_plan,
 
        A.last_execution_time,
        A.execution_count,
 
        A.total_elapsed_time / 1000 AS total_elapsed_time_ms,
        A.last_elapsed_time / 1000 AS last_elapsed_time_ms,
        A.min_elapsed_time / 1000 AS min_elapsed_time_ms,
        A.max_elapsed_time / 1000 AS max_elapsed_time_ms,
        ((A.total_elapsed_time / A.execution_count) / 1000) AS avg_elapsed_time_ms,
 
        A.total_worker_time / 1000 AS total_worker_time_ms,
        A.last_worker_time / 1000 AS last_worker_time_ms,
        A.min_worker_time / 1000 AS min_worker_time_ms,
        A.max_worker_time / 1000 AS max_worker_time_ms,
        ((A.total_worker_time / a.execution_count) / 1000) AS avg_worker_time_ms,
    
        A.total_physical_reads,
        A.last_physical_reads,
        A.min_physical_reads,
        A.max_physical_reads,
    
        A.total_logical_reads,
        A.last_logical_reads,
        A.min_logical_reads,
        A.max_logical_reads,
    
        A.total_logical_writes,
        A.last_logical_writes,
        A.min_logical_writes,
        A.max_logical_writes
    FROM
        [?].sys.dm_exec_query_stats A
        CROSS APPLY [?].sys.dm_exec_sql_text(A.[sql_handle]) B
        OUTER APPLY [?].sys.dm_exec_query_plan (A.plan_handle) AS C
 
END'
 
    
    EXEC master.dbo.sp_MSforeachdb
        @command1 = @Query
 
 
END
GO

How to identify “heavy” procedures with DMV's

View content
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):

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
SELECT TOP 100
    B.[name] AS rotina,
    A.cached_time,
    A.last_execution_time,
    A.execution_count,
 
    A.total_elapsed_time / 1000 AS total_elapsed_time_ms,
    A.last_elapsed_time / 1000 AS last_elapsed_time_ms,
    A.min_elapsed_time / 1000 AS min_elapsed_time_ms,
    A.max_elapsed_time / 1000 AS max_elapsed_time_ms,
    ((A.total_elapsed_time / A.execution_count) / 1000) AS avg_elapsed_time_ms,
 
    A.total_worker_time / 1000 AS total_worker_time_ms,
    A.last_worker_time / 1000 AS last_worker_time_ms,
    A.min_worker_time / 1000 AS min_worker_time_ms,
    A.max_worker_time / 1000 AS max_worker_time_ms,
    ((A.total_worker_time / A.execution_count) / 1000) AS avg_worker_time_ms,
    
    A.total_physical_reads,
    A.last_physical_reads,
    A.min_physical_reads,
    A.max_physical_reads,
    
    A.total_logical_reads,
    A.last_logical_reads,
    A.min_logical_reads,
    A.max_logical_reads,
    
    A.total_logical_writes,
    A.last_logical_writes,
    A.min_logical_writes,
    A.max_logical_writes
FROM
    sys.dm_exec_procedure_stats                 A
    JOIN sys.objects                            B    ON  A.[object_id] = B.[object_id]
ORDER BY
    A.execution_count DESC

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:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
USE [dirceuresende]
GO
 
CREATE PROCEDURE [dbo].[stpCarga_Historico_Execucao_Procedures]
AS BEGIN
 
 
    IF (OBJECT_ID('dirceuresende.dbo.Historico_Execucao_Procedures') IS NULL)
    BEGIN
    
        -- DROP TABLE dirceuresende.dbo.Historico_Execucao_Procedures
        CREATE TABLE dirceuresende.dbo.Historico_Execucao_Procedures (
            Id_Coleta BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
            Dt_Coleta datetime NOT NULL,
            [database] sys.sysname NOT NULL,
            rotina sys.sysname NOT NULL,
            cached_time datetime NULL,
            last_execution_time datetime NULL,
            execution_count bigint NOT NULL,
            total_elapsed_time_ms bigint NULL,
            last_elapsed_time_ms bigint NULL,
            min_elapsed_time_ms bigint NULL,
            max_elapsed_time_ms bigint NULL,
            avg_elapsed_time_ms bigint NULL,
            total_worker_time_ms bigint NULL,
            last_worker_time_ms bigint NULL,
            min_worker_time_ms bigint NULL,
            max_worker_time_ms bigint NULL,
            avg_worker_time_ms bigint NULL,
            total_physical_reads bigint NOT NULL,
            last_physical_reads bigint NOT NULL,
            min_physical_reads bigint NOT NULL,
            max_physical_reads bigint NOT NULL,
            total_logical_reads bigint NOT NULL,
            last_logical_reads bigint NOT NULL,
            min_logical_reads bigint NOT NULL,
            max_logical_reads bigint NOT NULL,
            total_logical_writes bigint NOT NULL,
            last_logical_writes bigint NOT NULL,
            min_logical_writes bigint NOT NULL,
            max_logical_writes bigint NOT NULL
        ) WITH(DATA_COMPRESSION = PAGE)
 
        CREATE INDEX SK01_Historico_Execucao_Procedures ON dirceuresende.dbo.Historico_Execucao_Procedures(Dt_Coleta, [database], rotina)
 
    END
 
    
    DECLARE
        @Dt_Referencia DATETIME = GETDATE(),
        @Query VARCHAR(MAX)
 
 
    SET @Query = '
INSERT INTO dirceuresende.dbo.Historico_Execucao_Procedures
SELECT
    ''' + CONVERT(VARCHAR(19), @Dt_Referencia, 120) + ''' AS Dt_Coleta,
    ''?'' AS [database],
    B.name AS rotina,
    A.cached_time,
    A.last_execution_time,
    A.execution_count,
 
    A.total_elapsed_time / 1000 AS total_elapsed_time_ms,
    A.last_elapsed_time / 1000 AS last_elapsed_time_ms,
    A.min_elapsed_time / 1000 AS min_elapsed_time_ms,
    A.max_elapsed_time / 1000 AS max_elapsed_time_ms,
    ((A.total_elapsed_time / A.execution_count) / 1000) AS avg_elapsed_time_ms,
 
    A.total_worker_time / 1000 AS total_worker_time_ms,
    A.last_worker_time / 1000 AS last_worker_time_ms,
    A.min_worker_time / 1000 AS min_worker_time_ms,
    A.max_worker_time / 1000 AS max_worker_time_ms,
    ((A.total_worker_time / a.execution_count) / 1000) AS avg_worker_time_ms,
    
    A.total_physical_reads,
    A.last_physical_reads,
    A.min_physical_reads,
    A.max_physical_reads,
    
    A.total_logical_reads,
    A.last_logical_reads,
    A.min_logical_reads,
    A.max_logical_reads,
    
    A.total_logical_writes,
    A.last_logical_writes,
    A.min_logical_writes,
    A.max_logical_writes
FROM
    [?].sys.dm_exec_procedure_stats                 A   WITH(NOLOCK)
    JOIN [?].sys.objects                            B   WITH(NOLOCK)    ON  A.object_id = B.object_id'
 
    
    EXEC master.dbo.sp_MSforeachdb
        @command1 = @Query
 
 
END
GO

How to identify “heavy” queries with WhoIsActive

View content
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).

How to identify “heavy” queries with sp_BlitzFirst

View content
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.

How to identify “heavy” queries with Management Studio (SSMS)

View content
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:

How to identify “heavy” queries with Perfmon

View content
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.

How to identify “heavy” queries using monitoring software

View content
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.

That's it folks!
I hope you enjoyed this post and see you next time!

tags: dmv'sperfmonperformanceperformance tuningsqlsql server

You may also like ...

  • SQL Server - How to use EXECUTE AS to execute commands as another user (Impersonate login and user)

  • SQL Server + AD Authentication - Kerberos + NTLM = Login failed for user 'NT AUTHORITY \ ANONYMOUS LOGON'

  • SQL Server - How to use regular expressions (RegExp) in your database

  • Next SQL Server - Using CROSS JOIN (Cartesian product) to create rows in the query result
  • Previous SQL Server - How to recover the source code of deleted objects (View, Stored Procedure, Function and Trigger)

Comments

  • Comments2
  • Pingbacks0
  1. Samuel Almeida Tocci said:
    3 January 2020 to 17: 18

    Great post Dirceu, but an error occurred while executing the script
    FOR XML could not serialize the data for node 'NoName' because it contains a character (0x0000) which is not allowed in XML
    I tried including STUFF, and also REPLACE (B. [text], CHAR (0), ”) or REPLACE (B. [text], CHAR (0x0000),”) and it didn't work out, would you know to indicate a solution?

    Reply
  2. Tássio Carlini said:
    9 July 2018 to 11: 10

    Great post Dirceu, thanks for sharing, helped me a lot = D.

    Reply

Leave a Comment Cancel reply

Dirceu Resende © 2020. All Rights Reserved.