This is the 22nd day of my participation in the August More Text Challenge

The following focuses on how to find slow statements in SQL Server, that is, SQL with a long running time. Generally, sp_WhoIsActive is the most comprehensive, informative and flexible one. However, due to its power, sp_WhoIsActive also has a certain amount of bloat. In many cases, it is not necessary to use so many features. In the following sections, a brief introduction will be given.

Method 1: Use the sp_WhoIsActive stored procedure

Sp_WhoIsActive is probably the best known stored procedure tool for performance lookup and troubleshooting, developed by Adam Machanic.

The first column returned by the query, DD hh:mm:ss.mss, is the execution time of the SQL statement, from which you can find the long running session. The second column is session_ID, or SPID.

exec sp_WhoIsActive;
Copy the code

KILL spid; Terminating a session

Method two: Usesys.dm_exec_requests

Sp_WhoIsActive is powerful, but relatively unwieldy. From this, you can get long-running sessions directly from sys. dM_EXEC_requests DMV

SELECT r.session_id,
       st.TEXT AS batch_text,
       qp.query_plan AS 'XML Plan',
       r.start_time,
       r.status,
       r.total_elapsed_time
FROM sys.dm_exec_requests AS r
     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
     CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE DB_NAME(r.database_id) = '{db_name}'
ORDER BY cpu_time DESC;
Copy the code

Then run the kill command

The following suggestions can be used to solve the problem of slow application query caused by long time report query:

  1. Reports (reports) and CRUD operations must be separated. At least use NOLock (with (NOLock)), or run at night and work offline.
  2. Check the current query, because if the amount of data is less than 2 million, the main problems are mostly query statements.
  3. Type of analysis report (report). If suitable for offline work, use offline system to report
  4. Reporting can be done using mirroring or other techniques.
  5. It is always a best practice to provide a separate database for reporting and CRUD operations, that is, a report database and an application database.

Simple alternative to sp_WhoIsActive

Some systems are too slow to use sp_WhoIsActive. This may be resource-related, such as CPU, memory, or TempDB pressure, or DMV.

At this point, you need a lightweight query that can still get the SQL text submitted by the user, along with some of the usual metrics.

Using only three Dynamic Management Views (DMVS)

To view common metrics, use just three views: DM_EXEC_requests, DM_EXEC_sessions, and DM_EXEC_INput_buffer.

SELECT s.session_id,
    r.start_time, 
    s.host_name, 
    s.login_name,
    i.event_info,
    r.status,
    s.program_name,
    r.writes,
    r.reads,
    r.logical_reads,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource
FROM sys.dm_exec_requests as r
JOIN sys.dm_exec_sessions as s
 on s.session_id = r.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, r.request_id) as i
WHERE s.session_id ! = @@SPID
and s.is_user_process = 1; -- Only user processes are displayed. If you want to display all processes, remove this condition. system processes
Copy the code

CROSS APPLY sys.dm_EXEC_SQL_TEXT (r.QL_HANDLE) to get the SQL text, but the i.event_info obtained above also seems to contain SQL statements.

  • The SQL_TEXT of the sp_WhoIsActive result is the exact running SQL text fragment.

  • Event_info contains information like @postType = 3, which is the text and the entire text that the user submitted to SQL Server.

About the sys. Dm_exec_requests

  • The sys. dM_EXEC_requests view returns information about each request being executed in SQL Server. That is, if you’re not executing, you can’t get it.

  • Sys. dM_EXEC_sessions returns each authenticated session on the SQL Server, one row per session. Sys.dm_exec_sessions is a server-wide view that displays information about all active user connections and internal tasks.

reference

  • How to find current long running queries in SQL Server and how to kill them instantly?
  • My alternative to sp_WhoIsActive