SQL Server Performance Tuning resource Waits LCk

I. Overview

This time the introduction of instance-level resources waits for lck type locks, and an introduction to Lck locks can refer to "SQL Server lock and transaction dispel". The following is also the use of sys.dm_os_wait_stats to view and find the most time-consuming Lok lock.

 
select wait_type,
waiting_tasks_count,
wait_time_ms ,
max_wait_time_ms,
signal_wait_time_ms from sys.dm_os_wait_stats where wait_type like ‘LCK%‘ order by wait_time_ms desc

Find out as shown:

1. Analysis and introduction

Highlights several of the most time-consuming lock meanings:

Lck_m_ix: Waiting to get intent exclusive lock. In the additions and deletions in the search will involve the intention to lock it.
Lck_m_u: Waiting for an update lock to be acquired. In the modification of the deletion there will be an update lock involved.
Lck_m_s: Waiting to acquire a shared lock. The main is the query, modify the deletion will also involve a shared lock.
Lck_m_x: Waiting to get exclusive lock. In the additions and deletions will be involved in the lock.
lck_m_sch_s: Waiting to get schema share lock. Prevent other users from modifying the table structure.
Lck_m_sch_m: Waiting to get schema modification locks such as adding columns or deleting columns this time using schema modification locks.

The following table is a statistical analysis

td>77.878

lock type	Lock wait Times	lock waits Total time (seconds)	Average time to wait (milliseconds)	max wait Time
lck_m_ix	26456	5846.871	221	47623
lck_m_u	34725	425.081	a	6311
lck_m_s	613	239.899	391	4938
lck_m_x	4832		4684
lck_m_sch_s	397	77.832	196	6074
lck_m_sch_m	113	35.783		2268

Note: In Wait_time_ms time, the schedule includes the Signal_wait_time_ms signal wait time, which means that Wait_time_ms not only includes the wait time required for the lock request, but also includes the signal waits for the thread runnable. This conclusion can also be concluded that the Max_wait_time_ms maximum wait time is more than just the waiting time required for the lock application.

2. Replay Lock Wait Time

- reset
DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR); 

- Session 1 update SID=92525000, not submitted
begin tran
update [dbo].[PUB_StockTestbak] set model=‘mmtest’ where sid=92525000

 
- Session 2 queries the ID, because the update of session 1 has not been submitted and takes up the x lock, the query here will block
select * from [PUB_StockTestbak] where sid=92525000

Manually cancel the query for session 2, which takes 61 seconds, such as:

Then statistics resources wait for lck, such as:

Summary: You can see that the resources waiting for LCK statistics is still very correct. So it is necessary to find out the type of lock with the highest performance consumption. A more targeted solution to the blocking problem.

3. Causes of the waiting phenomenon and cause

Phenomenon:

(1) The more questions the user concurrency, the worse the performance is. The application is running slowly.

(2) The client often receives error 1222 has exceeded the lock request timeout period.

(3) The client often receives error 1205 deadlock.

(4) Some specific SQL cannot be returned to the application in time.

Reason:

(1) The more users have concurrent access, the more blocking will be.

(2) There is a large number of lock applications without proper use of the index.

(3) The shared lock does not use NOLOCK, and the query brings blocking. The advantage is that it must be free from dirty reading.

(4) The processed data is too large. For example, there are thousands of updates in one update, and more concurrency.

(5) Do not select the appropriate transaction isolation level, complex transaction processing and so on.

4. Optimize the lock waiting time

In optimizing lock-waiting optimizations, there are a number of pointcuts like the previous few that describe the CPU and I/O time-consuming troubleshooting and processing scenarios. We can also write our own SQL to listen to the lock waiting SQL statement. The ability to know which library, which table, which statement has blocked waiting, is who blocked it, blocking the time.

A threshold value can be set from the average wait time (in milliseconds) above and the maximum wait time as a reference. Using the information provided by Sys.sysprocesses to count, about sys.sysprocesses use can refer to "SQL Server performance tuning from user session state analysis". This view listens for blocking information over a period of time. You can set a monitor statement to run every 10 seconds, storing blocking and blocking.

Thoughts are as follows:

- For example, find out the blocked session ID such as 2 seconds in time and who blocked its session ID
SELECT spid,blocked #monitorlock FROM sys.sysprocesses
where blocked>0 and waittime>2000

- Get the session ID and blocking ID of the temporary table line by line through while or cursor, and get the sql statement text through exec dynamic execution for storage
exec(‘DBCC INPUTBUFFER(‘+@spid+‘)’)

exec(‘DBCC INPUTBUFFER(‘[email protected]+‘)’)  

SQL Server Performance Tuning resource Waits LCk