SQL server Performance Optimization-nolock and Performance Optimization-nolock

SQL server Performance Optimization-nolock and Performance Optimization-nolock

With the increase of time, the company's database will grow more and more, and the query speed will become slower and slower. When you open the database to view hundreds of thousands of data records, it is inevitable that the query will not waste time.

To improve the query efficiency of SQL statements, we generally consider creating an index first. In fact, in addition to index creation, when we run the SQL Command, we add a section in the syntaxWITH (NOLOCK)This improves the query performance by locking a dataset in a large online query environment.

However, you must note that,WITH (NOLOCK)SQL SELECT may cause Dirty Read, that is, reading invalid data.

The following is a brief description of the principles and usage of SQL Server lock contention and nolock and rowlock:

Lock contention description

Databases that use not only row-level locks use a technology called lock escalation to obtain high performance. Unless it is clearly known that it is for the entire data table, the practice of these databases is to start to use row-level locks, and then start to use a wide range of lock mechanisms as the number of data changes increases.

Unfortunately, this mixed lock method creates and magnifies new problems: deadlocks. If two users modify records in different tables in the opposite order, and these two records are logically irrelevant but physically adjacent, the Operation will first trigger a row lock, then upgrade to the page lock. In this way, both users need to lock the other side, resulting in a deadlock.

For example:

When user A modifies some records of Table A, the page lock caused not only locks the records being modified, but also locks many other records.

User B modifies some records of Table B, causing the page lock to lock User A and other data being modified.

User A wants to modify the data locked by user B in Table B (not necessarily being modified.

User B wants to modify or only wants to access the data locked (not necessarily being modified) by user A in table.

To solve this problem, the database often checks whether a deadlock exists. If yes, cancel one of the transactions so that the other transaction can be successfully completed. In general, it is to cancel the transaction with a small amount of data modified, so the rollback overhead is relatively small. This problem rarely occurs in databases that use row-level locks, because two users are unlikely to modify the same record at the same time, and there are fewer locks due to the accidental order of data modification.

In addition, the database uses lock timeout to Prevent Users From waiting too long. Query timeout is introduced for the same purpose. We can resubmit the time-out queries, but this will only cause database congestion. If a timeout occurs frequently, it indicates that the method of using SQL Server is incorrect. Normally, timeout is rare.

In a running environment with high Server load, the SQL Server lock mechanism using mixed locks does not perform well. The reason is Lock Contention ). Lock contention causes deadlock and lock wait problems. In a multi-user system, many users modify the database at the same time, and more users access the database at the same time, resulting in locks at any time, users are scrambling to obtain the lock to ensure the correctness of their operations and frequent deadlocks. In this case, the user's mood can be imagined.

Indeed, if there are only a few users, SQL Server will not have much trouble. During Internal testing and release, it is difficult to find the concurrency problems due to a small number of users. However, when hundreds of concurrent operations are triggered, and the INSERT, UPDATE, and DELETE operations are performed continuously, how can you check whether there is any trouble? At that time, you can unlock them in disorder.

Solution to lock contention

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

Even if the user does not modify the data, SQL Server encounters a lock during SELECT. Fortunately, we can use two keywords of SQL Server for manual processing:NOLOCK and ROWLOCK.

They are used as follows:

SELECT COUNT(UserID)　FROM Users WITH (NOLOCK)　WHERE Username LIKE 'football'

And

UPDATE Users WITH (ROWLOCK)SET Username = 'admin' WHERE Username = 'football'

NOLOCK usage

NOLOCK can ignore the lock and directly read data from the database. This means that the lock can be avoided to improve performance and scalability. But it also indicates the possibility of code errors. You may read uncommitted data that is being processed by the running transaction without verification. This risk can be quantified.

Use of ROWLOCK

ROWLOCK tells SQL Server to only use row-level locks. The ROWLOCK syntax can be used in SELECT, UPDATE, and DELETE statements, but I am used to only use the UPDATE and DELETE statements. If a specified primary key exists in the UPDATE statement, the row-Level Lock is always triggered. However, when SQL Server batch processes several such updates, some data is exactly on the same page, which is likely to happen in the current situation, this is like creating files in a directory takes a long time, and you are updating these files. When a page lock is triggered, things begin to get worse. If a primary key is not specified during UPDATE or DELETE, the database considers that a large amount of data will be affected, which will directly trigger the page lock, and the problem will become worse.

Let's write an example to illustrate the role of NOLOCK. Here we use a database with more than 10 thousand entries for testing. Let's look at it without using NOLOCK:

declare @start DATETIME;declare @end DATETIME;SET @start = getdate();select * from Captions_t;SET @end = getdate();select datediff(ms,@start,@end);

Here we use Select * To see the execution result, for example:

The usage time shown here is 34720 ms. The following uses NOLOCK to look at it:

declare @start DATETIME;declare @end DATETIME;SET @start = getdate();select * from Captions_t18 with (NOLOCK);SET @end = getdate();select datediff(ms,@start,@end);

The running result is as follows:

The time used this time is 2563 ms. The gap is shown. I personally feel that the time should not be so much worse. In short, the performance has improved a lot.

There are several differences between nolock and with (nolock:

1. synonyms in SQL Server 2005, only with (nolock) is supported );

2. with (nolock) is easy to specify the index.

3. For cross-Server Query statements, you cannot use with (nolock) but can only use nolock. For the same Server query, both with (nolock) and nolock can be used. For example, select * from [IP]. a. dbo. table1 with (nolock) will prompt an error, select * from. dbo. table1 with (nolock) can be queried successfully.

The above content is all about nolock, which introduces SQL server performance optimization. I hope it will be helpful to you.