Expert Diagnostic Optimization Series------------------lock is a big part.

Several previous sections of the server have been described in the SQL Server database diagnostics and tuning methods. In addition, this article can be said to be able to complete the routine problem diagnosis and optimization, this article is the lock in SQL Server. For the convenience of reading a series of articles on the Guide link:

SQL Server fully optimized-------Expert for SQL Server Diagnostic series

　　　　

Before reading this article, everyone should know that locks are a major factor in your performance, so why should SQL Server introduce locks? That is to solve the problem that the following data inconsistencies occur when multiple users concurrently operate on the database. I think in order to ensure the consistency of data, even the sacrifice of more is worth it! This article focuses on how to find the point of this sacrifice and how to minimize your sacrifice!

Do you still remember waiting for the third ring of the Beijing chapter?

　　　　

Wait a lot of time is waiting to get the lock on the object! When there are many locks in the database, the system cannot provide normal service instantaneously. At this time to observe the use of system resources, you will find that the CPU usage is not high, memory consumption is not high, there are many unused memory, network bandwidth is also adequate, hard disk is not busy, through the Database management tool query, SQL server data is normal and correct, However, when users of the system access this database, they need to wait a lot longer, more connections time out, and the database is unresponsive.

This is like the early peak, the front also hit! 11 car even hit very spectacular, for the database 11 chain, also very to force!

--------------Blog Address---------------------------------------------------------------------------------------

Expert Diagnostic Optimization Series http://www.cnblogs.com/double-K/

No more nonsense, just open the whole-----------------------------------------------------------------------------------------.

There are two main types of waits for locks: and Lck_ and Pagelatch_

Pagelatch_: Latches used internally in lightweight databases, not described here

Lck_: Eight catty half of the big lock here just say it!

Note: The basic knowledge of lock-related, please do your own Baidu study!

• Common performance counters for diagnostic locks

1. Lock Requests/sec Locks Requests per second
2. Lock waits/sec Number of locks waiting per second
3. Lock wait Time (ms) lockout latency
4. Average wait Time (ms) Average latency
5. Number of Deadlocks/sec deadlocks per second
6. Latch waits/sec latch Wait number
7. Average Latch Wait Time (ms) latch Average Latency

Counter but more introduction, will not use the friends please Baidu itself. Directly on the example:

In this example, the customer reflects that a particular point-in-time system is particularly slow and seriously impacting the business, so we conduct a comprehensive analysis in the usual order.

　　

The CPU looks at around 10 points and at around 6 o'clock in the evening appears 90% above the peak.

　　

　　

　　

　　The page life cycle and the lazy writer can see no significant pressure on memory

　　

Take 10 points as an example (why not look at six points?) I've analyzed it silently. The disk queue is not high, but there is a high disk pressure at 10:15. So is this a problem or two? Let's keep looking.

　　

The number of transaction activities reached a very high value at 10 o ' Day.

　　

The number of user connections at 10 is also high, then the problem is clear, is 10 points when the user is connected too much pressure caused the system slow! don't be naïve. This theme is the lock, the protagonist has not played how can end? Repeat, don't jump to conclusions!

　　There is a lot of connections, and another reason is The connection execution statement takes a long time to release, then other apps can only open new connections, so the number of connections will be high,

　　

Log Refresh number Puma is this point in the INSERT, delete, or update?

　　

Forwarded records/sec? Update! Large number of update! No primary key table update!

Does that look like an update? How did you see that? Here are not too many instructions, see: a hidden IO performance in SQL Server killer-forwarded record

----------------------------------------- down to the chase --------------------

I have a lot of update system will be very slow? Can not run?

Let's look at the lock-related counters

　　　

　　　　

Number of LOCK requests! A lot of lock requests are generated at this point in time!

　　　　

　　　　

Lock waits, lots of lock waits

　　　　

　　　　

Look at the waiting time, the peak point has reached 70 seconds!! What's the concept of waiting for 70 seconds? It is the entrance to the school, or an early peak!!

　　　　

God, fortunately there is no deadlock ....

------------------------------ Statements and pending diagnostics --------------------

I can find 2 main problems through the counter: 1. At 10, a large number of update updates, causing the system to block large areas, statements run too long. 2. A large number of disk reads after 10 points and 15 minutes, causing the disk queue to burst.

Let's take a look at the statement and the waiting situation:

　　

Statements and waiting for a general response are normal, with fewer long statements and no serious wait. So, the system problem point is at a certain point in time (this is the user response to the system is slow, the beginning has been mentioned)

　　

Let's go deep down to 10 and see what's going on!

First we look at the statement situation!

　　

In the above image we just show a part of the problem point of statement, the main can be seen as follows:

1. Problem point-in-time there are a lot of update operations
2. Update operation is severely blocked (lock)
3. And is an update of a program loop call
4. Long statement run time
5. CPU High is because this point in addition to update a large number of queries to cause high CPU (in general, the system large area lock waiting time when the CPU resources are not effectively utilized, the CPU will be low)

Then we look at the situation of waiting, and see how it is done, unexpectedly locked so much!

　　

Statement overall waiting to see the whole day but 10 points a lot, and caused the system to die (the default 30 seconds timeout, many should time out, so the user experience is very poor!) ), the statement of the CPU and read and write are not many, also indicates that the mutual lock is very serious!

　　　

A large number of statements are locked by 195, and 195 is actually the same update, the client's program has frequent this update, and at the time of 10, there will be another program of a large batch of cycle updates, which is the cause of this large area lock blocking!

Second question, why is disk 10 point 15 so high? Is it related to the update?

　　　

Here you can see the second question 10 point 15 when there are a lot of big logical read queries, but also with the new nothing, but with the business has no connection is unclear. Cause the system disk pressure to become larger, and the theme of the relationship is not here to say.

• A small misunderstanding about the lock

Does select Block update?

Simple small Code on the previous paragraph

　　

Create TableA (aint)Insert  intoaSelect object_id  fromSys.objectswhere object_id between 1  and  +begin Tran Select *  fromA with(Holdlock) whereA= 3--------------A new session to executeUpdateASetA=  -whereA= 3

　　

Here with (Holdlock) is to let the query hold s lock, mock your query is not over.

　　

Sp_who2 or select Session_id,status,command,blocking_session_id,wait_type from sys.dm_exec_requests where session_id = 58 take a look.

　　

　　

　　High-energy WARNING: The query will also block the update Oh ~ ~

--------------Blog Address---------------------------------------------------------------------------------------

Expert Diagnostic Optimization Series http://www.cnblogs.com/double-K/

-----------------------------------------------------------------------------------------------------

Summary: The statement runs for a long time, one of the most likely causes is blocking, and most of the blocking is due to the waiting between resources.

It is necessary to make a comprehensive analysis of the system when the statement is tuned. (as this article says)

Lock optimization can be said to be more in-depth problem analysis, reduce the mutual impact of locks, the main can also start from the statement optimization, reduce consumption and shorten the time. In addition, we can start from the aspect of business design and reduce the contention of hot resource.

Locks are primarily sacrificed to maintain data consistency. We can do nothing to reduce his influence.

　　　　　

PS: Confined to space this article does not tell you some basic knowledge, please learn by yourself, such as isolation level, lock matrix compatibility and so on .....

----------------------------------------------------------------------------------------------------

Note: This article is original, welcome reprint, please in the article page obvious location give this article link!
If you think this article is not bad please click on the lower right corner of the recommendation , thank you very much!

For the convenience of reading a series of articles on the Guide link:

SQL Server fully optimized-------Expert for SQL Server Diagnostic series

Expert Diagnostic Optimization Series------------------lock is a big part.