SQL Server Performance Tuning resource Waits LCk

Source: Internet
Author: User
Tags session id

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,
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

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


(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.


(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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.