SQL Server Performance Optimization Nolock_mssql

With the growth of time, the company's database will be more and more, query speed will be more and more slow. Open the database to see hundreds of thousands of of data, the query is inevitable not waste time.

To improve SQL query effectiveness, you will generally want to build indexes (index) as the first consideration. In fact, in addition to the establishment of the index, when we are in the SQL command, the syntax of the addition of a section with (NOLOCK) can improve the online query in the context of a large number of data set by the lock to improve the effectiveness of the query.

It is important to note, however, that the SQL select with(NOLOCK) may cause dirty read, or read invalid data.

The following is a brief description of the principle and use of SQL Server lock contention and Nolock,rowlock:

Description of Lock contention

Databases that use not only row-level locks use a technology called mixed-lock (lock escalation) to achieve higher performance. Unless it is explicitly known to be for the entire datasheet, the practice of these databases is to start using row-level locks, and then start using a large range of locking mechanisms as the modified data increases.

Unfortunately, this method of blending locks creates and magnifies new problems: Deadlocks. If two users modify records in different tables in reverse order, and the two records are logically unrelated, but physically contiguous, the operation throws a row lock and then upgrades it to a page lock. In this way, two users need to lock the object, resulting in a deadlock.

For example:

User A modifies some of the records in table A, and the resulting page lock locks not only the record being modified, but also many other records are locked.

User B Modifies some of the records in table B, and the resulting page lock locks User A and other data that is being modified.

User A wants to modify the data that User B locks (and is not necessarily modifying) in table B.

User B wants to modify or simply want to access data that user A is locked (and not necessarily modifying) in table A.

To solve this problem, the database often detects whether a deadlock exists and, if so, revokes one of the transactions so that another transaction can be successfully completed. Generally, it is to undo the transaction that modifies the amount of data, so the cost of rolling back is less. A database that uses row-level locks rarely has this problem, since two users are less likely to modify the same record at the same time, and there are fewer locks due to the extremely accidental modification of the data.

Also, the database uses a lock timeout to avoid allowing the user to wait too long. The query timeout is also introduced for the same purpose. We can submit those queries that have timed out again, but this only causes the database to clog up. If timeouts occur frequently, there is a problem with the way users use SQL Server. Normally, timeouts do not occur very often.

The SQL Server lock mechanism with mixed locks is not performing well in a server-loaded running environment. The reason is lock contention (lock contention). Lock contention causes deadlock and lock wait issues. In a multi-user system, many users will be at the same time modify the database, there are more users at the same time access to the database, at any time will produce locks, users are scrambling to obtain locks to ensure that their operation is correct, deadlock frequently occurs, this situation, the user's mood can be imagined.

Indeed, SQL Server won't have much trouble if you have only a small number of users. Internal testing and publishing, because fewer users, it is difficult to find those concurrency problems. But when you trigger hundreds of concurrent, continuous insert,update, and some delete operations, how to see if there is trouble, then you will be scrambling to unlock.

Lock contention resolution

SQL Server starts with row-level locks, but often expands to page locks and table locks, resulting in deadlocks.

SQL Server encounters a lock when it selects, even if the user does not modify the data. Luckily, we can handle it manually via SQL Server's two keywords:nolock and Rowlock.

They are used in the following ways:

SELECT COUNT (UserID) from the
Users with (NOLOCK)
WHERE Username like ' Football '

And

UPDATE Users with (rowlock)
SET Username = ' admin ' WHERE Username = ' Football '

The use of Nolock

Nolock can ignore locks and read data directly from the database. This means that locks can be bypassed to improve performance and scalability. But it also means that the likelihood of a code error exists. You may be able to read the uncommitted data that the transaction is running without verification. This risk can be quantified.

The use of Rowlock

Rowlock tells SQL Server to use only row-level locks. Rowlock syntax can be used in select,update and DELETE statements, but I am accustomed to using them only in UPDATE and DELETE statements. If you have the specified primary key in the UPDATE statement, the row-level lock is always thrown. But when SQL Server batches several of these updates, some data happens to be on the same page (page), which is likely to happen in the current situation, like in a directory where it takes a long time to create a file and you are updating the files. When the page lock is triggered, things start to get worse. If you do not specify a primary key at update or delete, the database of course thinks that a lot of data will be affected, which will trigger the page lock directly, and things can get worse.

Below write an example, to explain the role of NOLOCK, here using a database with more than 10,000 to test, first do not have to nolock to see:

declare @start DATETIME;
declare @end DATETIME;
SET @start = getdate ();
SELECT * from captions_t;
SET @end = getdate ();
Select DateDiff (MS, @start, @end);

Here for the effect is more obvious, using the select *, look at the execution results, as shown in the following figure:

The usage time shown here is 34720ms, below using NOLOCK to see:

declare @start DATETIME;
declare @end DATETIME;
SET @start = getdate ();
SELECT * from captions_t18 with (NOLOCK);
SET @end = getdate ();
Select DateDiff (MS, @start, @end);

The results of the operation are shown below:

The time used is 2563ms, the gap is reflected. Personal sense of time should not be so much, in short, performance is improved a lot.

Several small differences between Nolock and with (NOLOCK):

Synonyms in 1.SQL Server 2005, only supported with (NOLOCK);

2.with (NOLOCK) is a very easy to specify index.

3. When querying statements across servers, you cannot use with (NOLOCK) only Nolock, and with (NOLOCK) and nolock when querying the same server. For example: SELECT * FROM [Ip].a.dbo.table1 with (NOLOCK) will prompt for errors, select * from A.dbo.table1 with (NOLOCK) so that you can successfully query.

This is what this article describes the NOLOCK of SQL Server performance optimization, I hope to help.