Check the cause of the statement running time exception (sqlserver)

Some developers often reflect the following situation: I have a statement or a job that ran for half an hour yesterday. How can I run for two hours today?

Is there a problem with the database?

The abnormal running time of database statements is actually a complicated situation. Because the data is constantly changing, a good statement may be executed tomorrow.

This scenario cannot be completely retraced within the estimated time. Even if there is backup data at the time, there is no way to know and create the server pressure at that time.

But now we don't want to investigate the cause of yesterday's statement running time exception, but to find out the cause of the current statement running exception. The current situation is still in progress,

Therefore, we can troubleshoot the problem based on the current situation of the statement;

In fact, there are many issues to consider:

1. Check whether the index is normal (whether the index is damaged or whether an index has been deleted );

2. Check whether the statistical information is out of date;

3. Whether the statement execution plan is offset (related to indexes, statistics, and data volume );

4. Check whether the statement has a bug;

5. Whether or not a blocking occurs;

6. Whether the system resources encounter bottlenecks;

.........

It is difficult for us to solve these problems if we consider all these situations. Generally, we need to solve this problem in a quick way. The following methods are mainly used for troubleshooting: 5 and 6.

Because these two aspects are the most common.

Let's simulate the troubleshooting process:

1. Create test tables and data

 Use   [  Master  ] 
 Go 

 /*  * *** Object: Table [DBO]. [a] script Date: 01/17/2012 16:46:34 *****  */ 
 Set Ansi_nulls On 
 Go 

 Set Quoted_identifier On 
 Go 

 Set Ansi_padding On 
 Go 

 Create   Table   [  DBO  ] . [  A  ] (
 [ ID  ]   [  Int  ]   Identity ( 1 , 1 ) Not   Null ,
 [  Name  ]   [  Varchar  ] (100 ) Null 
) On   [  Primary  ] 

 Go 

 Set Ansi_padding Off 
 Go 


 Insert   Into A Values ('  AA  ' ),( '  Bb  ' ),( '  CC  ' )

2. Manufacturing congestion: open two sessions and run the following statements respectively.

  --   Session 1   
   use  master 
  go  
  begin   Tran  
  Update  A  set  name =  '  ABC   '  where  id =  2  
  --   rollback  

--Session 2
Select * FromA
 

Because the update Statement of session1 cannot be submitted, the process of session2 will be blocked at this time.

3. Analysis and troubleshooting:

First, we need to query whether the database is blocked at this time:

 
--Blocked
Select * FromSYS. sysprocessesWith(Nolock)WhereBlocked<>0
 

We can see the blocking record, 53 blocks 56, and the blocked resource is dbid 1 file 1 page 307;

Next, we need to know the blocking and blocked statements in two ways:

A. DBCC inputbuffer

B. SYS. dm_exec_ SQL _text

Method 1 compared with Method 2:

Advantage: method 1 can display the Statement of the inactive session. method 2 can only check the active SESSION (through sp_who2 active can show whether the session is active );

Disadvantage: method 1 can only be queried one by one, and method 2 can be queried together;

Method 1:

 
--No1:
DBCCInputbuffer (53)
Go
DBCCInputbuffer (56)
 

Method 2:

 --  NO2:  
 Select 
S. session_id, R. blocking_session_id,
S. Host_name , S. login_name,
Databasename =  Db_name (R. database_id), R. Command, R. status,
Current_execute_ SQL =   Substring (T. Text ,
R. statement_start_offset /   2   +  1 ,
 Case 
 When Statement_end_offset =   -  1   Then   Len (T. Text )
 Else (R. statement_end_offset - Statement_start_offset) /   2 +  1 
 End ),
S. program_name,
S. status,
S. cpu_time, memory_usage_kb = S. memory_usage *   8 , S. Reads, S. writes,
S. transaction_isolation_level,
C. connect_time, C. last_read, C. last_write,
C.net _ transport, C. client_net_address, C. client_tcp_port, C. local_tcp_port,
R. start_time,
R. wait_time, R. wait_type, R. last_wait_type, R. wait_resource,
R. open_transaction_count, R. transaction_id

 From SYS. dm_exec_sessions s
 Left   Join SYS. dm_exec_connections C
 On S. session_id = C. session_id
 Left   Join SYS. dm_exec_requests R
 On S. session_id = R. session_id
 And C. connection_id = R. connection_id
 Outer Apply SYS. dm_exec_ SQL _text (R. SQL _handle) T
 Where S. is_user_process =   1    --  If this condition is not specified, all processes (system and user processes) are queried)  
  And S. session_id In ( 53 ,56 )

We can see that one or two statements of the method can be found, and method 2 can only find one statement;

Here, we can determine that the reason for slow statement execution is blocked. Let's check the reason for blocking. You can view the following statement:

 
SelectRequest_session_id, resource_type,Db_name(Resource_database_id)AsDbname, resource_description,
Request_mode, REQUEST_TYPE, request_statusFromSYS. dm_tran_locksWhereRequest_session_idIn(56,53)
Order ByRequest_session_id
 

As you can see, 56 is in the wait status, and it is waiting to get a share lock at, but it is occupied by an exclusive lock at (grant stands

Resources have been obtained and are running), So 56 must wait until the exclusive lock on 53 is released before it can continue to run; so we turn to investigate the reason why 53 exclusive lock is not released; it may be

53. Other resources are occupied by other processes and are waiting for other processes to release the lock. It may also be because the update statement updates too much data and takes a long time.

The lock can be released in a timely manner. There is also our current situation where no transaction has been submitted (which can be seen directly in the Statement). Blocking troubleshooting methods are similar.

 

What if the statement is not blocked by other statements? The reason we need to further find out is wait. We already have wait-related queries. Let's check them.

More specific information:

 --  Wait & lock  
  Select Lo. request_session_id As   [  Session  ] ,
Db_name (Lo. resource_database_id) As Dbname,
Lo. resource_type As   [  Type  ] ,
Lo. resource_description,
Lo. request_mode,
Lo. request_owner_type,
Lo. request_status,
 Case   When Lo. resource_type =  '  Object '   Then   Object_name (Lo. resource_associated_entity_id)
 When Lo. resource_associated_entity_id Is   Null   Or Lo. resource_associated_entity_id =  0 
 Then   Null 
 Else   Object_name (P.Object_id )
 End   As Associated_entity,
Wt. blocking_session_id, wt. resource_description
 From  
SYS. dm_tran_locks Lo With (Nolock)
 Left   Join SYS. partitions P With (Nolock)
 On Lo. resource_associated_entity_id = P. partition_id
Left   Join SYS. dm_ OS _waiting_tasks WT With (Nolock)
 On Lo. lock_owner_address = Wt. resource_address
 Where Lo. request_session_id >  50 
 And Lo. request_session_id =  56  
 Order  By   [  Session  ] , [  Type  ] 

As you can see above, 56 encountered a wait when obtaining the shared resource. Of course, the wait here is still blocked by 53, but there are multiple reasons to wait. Let's check

Current wait information:

--Current wait info
SelectWait_type,Count(0)AsNum_waiting_tasks,
Sum(Wait_duration_ms)AsTotal_wait_time_ms
FromSYS. dm_ OS _waiting_tasksWith(Nolock)
WhereSession_id>50
Group ByWait_type
Order ByWait_type
 

Here we can see the lock wait (wait_type), there are many resource types of waiting, the value focuses on:
Memory: cmemthread, resource_semaphore
Cmemthread:
Description and cause: indicates a problem with the plan cache (a large number of plans are added or removed );
Solution: Use parameterized queries or set mandatory database parameterization)

Resource_semaphore:
Description and cause: memory-intensive queries cannot obtain the requested memory; other processes consume too much memory;
Solution: Add appropriate indexes or increase memory for the database

IO: io_completion, async_io_completion, writelog, pageiolatch _*

CPU: cxpacket, sos_scheduler_yield
Cxpacket:
Description and reason: Parallel Processing wait type, parallel synchronization wait;
Solution: you can modify the parallelism value (or disable it;
Sos_scheduler_yield:
Description and reason: After the task is executed to the end of the time segment, the dispatcher can run the task for other tasks;
Solution: CPU with better processing capability

Network: async_network_io, db1__send
Async_network_io: The NIC bandwidth is saturated or the client cannot remove the result in time;
Db1__send: the network bandwidth is insufficient to support the volume of image transactions or the volume of image databases exceeds the limit;

Lock blocking: LCK _*

We can count the 20 most waiting types in our database:

 --  Total wait info 
  Select   Top   20 Wait_type, Sum (Waiting_tasks_count) waiting_tasks_count,
 Sum (Wait_time_ms) As Total_wait_time_ms,
 Sum (Signal_wait_time_ms) As Total_signal_wait_time_ms
 From SYS. dm_ OS _wait_stats With (Nolock)
Where Wait_type Not   In 
 --  System wait type  
 ( '  Lazywriter_sleep  ' , '  Request_for_deadlock_search  ' , '  Sqltrace_buffer_flush  ' ,
'  Xe_timer_event  ' , '  Ft_ifts_scheduler_idle_wait  ' , '  Logmgr_queue  ' , '  Checkpoint_queue  ' ,
 '  Sleep_task  ' ,'  Broker_io_flush  ' , '  Broker_task_stop  ' , '  Broker_to_flush  ' , '  Broker_eventhandler  ' )
 Group   By Wait_type
 Order  By Total_wait_time_ms Desc 

Through this, we can see what aspects of DB wait are mainly concentrated. If it takes a long time to wait for CPU, Io, memory, lock, etc., it means we

The database needs to be optimized in some aspects.

The above is the preliminary troubleshooting process for statements with abnormal running time in terms of blocking and waiting.