How to troubleshoot types used to wait-CodePudding

Before 20 minutes to zero, all waiting half an hour later to see the diagram below:
Hope leaders can give a prescription, and see what the problem is, in a production environment is only recently, no more than 2 months,
Simple database recovery type is not complete, hope leaders can give a recipe for how to troubleshoot
A code or to see if the wait is caused by what statement

CodePudding user response:

Suggest you check first slow SQL, a bit more direct,

 SELECT TOP 10 OBJECT_NAME (qt objectid, qt. DbId) AS procName, 
DB_NAME (qt. DbId) AS [DB_NAME], 
Qt. The text AS SQL_Full, 
The SUBSTRING (
Qt. Text, 
(qs. Statement_start_offset/2) + 1, 
(
(
CASE statement_end_offset 
THEN the WHEN - 1 DATALENGTH (qt. Text) 
The ELSE qs. Statement_end_offset 
END 
- qs. Statement_start_offset 
)/2 
) + 1 
) AS SQL_Part - statistics corresponding part of the statement 
, 
Qs creation_time, 
Qs last_execution_time, 
Qs execution_count, 
Qs last_elapsed_time/1000000 AS lastElapsedSeconds, 
Qs last_worker_time/1000000 AS lastCpuSeconds, 
CAST (
Qs. Total_elapsed_time/1000000.0/(
CASE 
The WHEN qs. Execution_count=0 THEN - 1 
The ELSE qs. Execution_count 
END 
) AS a DECIMAL (28, 2) 
) AS avgDurationSeconds, 
CAST (qs) last_logical_reads AS BIGINT) * 1.0/(1024 * 1024 * 8060 AS 
LastLogicReadsMB, 
Qs last_logical_reads, 
Qs. Plan_handle 
The FROM sys. Dm_exec_query_stats qs 
CROSS the APPLY sys. Dm_exec_sql_text (qs) sql_handle) AS qt 
CROSS the APPLY sys. Dm_exec_query_plan (qs) plan_handle) AS p 
WHERE qs. Last_execution_time & gt;=the CONVERT (CHAR (10), GETDATE (), 120) + '08:00' - after 8 PM today slow SQL 
AND qs. Last_elapsed_time & gt;=3 * 1000 * 1000 - only to take the record of the execution time is more than 3 seconds 
AND qt. [text] NOT LIKE '% Proc_DBA %' 
The ORDER BY 
Qs. Last_worker_time DESC