SQL Server performance optimization nolock, greatly improve database query performance

Original: SQL Server performance Optimization nolock, greatly improve database query performance

The company database grows with time, the data is more and more, the query speed is also more and more slow. Into the database to see a bit, hundreds of thousands of of the data, the query is really time-consuming.

To improve the query performance of SQL, we generally consider the index as the first consideration. In fact, in addition to the establishment of the index, when we are under the SQL command, in the syntax of adding a section with (NOLOCK) can improve the environment of large online query data set by lock, thereby improving the performance of the query.

However, it is important to note that the SQL select with (NOLOCK) may cause dirty read to 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 do not just use row-level locks use a technique called the Escalation Lock (lock) to obtain higher performance. Unless it is clear that it is for the entire data table, the practice of these databases is to start using row-level locks and then start using a wide range of locking mechanisms as the modified data increases.

Unfortunately, this method of blending locks produces and amplifies new problems: deadlocks. If two users modify records in a different table in reverse order, and the two records are logically unrelated, but physically adjacent, the operation will first raise the row lock and then upgrade to a page lock. In this way, two users are required to lock something, resulting in a deadlock.

For example:

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

User B modifies some records of table B, and the page lock that is raised 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 locks in table A (and is not necessarily being modified).

In order to solve this problem, the database will often detect the existence of a deadlock, if any, one of the transactions to be revoked, so that the other transaction can be completed successfully. In general, it is a matter of undoing the small amount of modified data so that the rollback is less expensive. A database with row-level locks rarely has this problem because two users are less likely to modify the same record at the same time, and have fewer locks due to the very accidental order of data modification.

Also, the database uses lock timeouts to avoid allowing users to wait too long. The introduction of query timeouts is also for the same purpose. We can re-submit those queries that have timed out, but this will only cause a blockage in the database. If a time-out occurs frequently, there is a problem with how the user uses SQL Server. The normal situation is that there are few timeouts.

In a running environment with high server load, the SQL Server lock mechanism using hybrid locks does not perform well. The reason for this is lock contention (lock contention). Lock contention causes deadlock and lock wait issues. In a multi-user system, many users will be in the database at the same time, there are more users at the same time access to the database, at any time to generate locks, users are scrambling to acquire locks to ensure that their operation is correct, deadlock occurs frequently, in this case, the user's mood is conceivable.

Indeed, if there are only a handful of users, SQL Server will not experience much trouble. When it comes to internal testing and publishing, it's hard to spot concurrency problems due to fewer users. But when you fire hundreds of concurrent, continue to insert,update, and some delete operations, how to see if there is trouble, then you will be scrambling to unlock.

Workaround for lock contention

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

SQL Server encounters a lock when a select is not modified, even if the user does not modify the data. Fortunately, we can do this manually with two keywords from SQL Server: Nolock and Rowlock.

They are used in the following ways:

　

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

And

UPDATE  with (Rowlock) SET = ' admin ' WHERE = ' Football '

Use of Nolock

Nolock can ignore locks and read data directly from the database. This means that locks can be avoided, which improves performance and scalability. But it also means that there is a possibility of code errors. You may read uncommitted data that is being processed by the running transaction without validation. This risk can be quantified.

Use of Rowlock

Rowlock tells SQL Server to use only row-level locks. The Rowlock syntax can be used in select,update and DELETE statements, but I am accustomed to using only the update and DELETE statements. A row-level lock is always raised if there is a specified primary key in the UPDATE statement. However, when SQL Server batches several of these updates, some data is exactly on the same page (page), which is likely to happen in the current situation, as in a directory where it takes a long time to create files, while you are updating those files. When a page lock is thrown, things start to get worse. If you do not specify a primary key when you update or delete, the database will of course think that a lot of data is affected, and then the page lock is thrown directly, and things get worse.

Here is an example to illustrate the role of NOLOCK, here using a database of more than 10,000 to test, first without nolock to see:

Declare @start DATETIME;Declare @end DATETIME;SET @start = getdate();Select *  fromcaptions_t18;SET @end = getdate();Select DateDiff(MS,@start,@end);

In order to be more effective here, a SELECT * is used to look at the results of the execution, such as:

The use time shown here is 34720ms, below use Nolock to look at:

Declare @start DATETIME;Declare @end DATETIME;SET @start = getdate();Select *  fromcaptions_t18 with(NOLOCK);SET @end = getdate();Select DateDiff(MS,@start,@end);

Running results such as:

This time the use of 2563ms, the gap is reflected in it. Personal feeling time should not be poor so much, in short, performance is improved a lot. Let's have a lot of tests.

Reference article: http://blog.sina.com.cn/s/blog_7034dbe00100ll9n.html