After adding nolock, the speed is more than doubled.

Today, the optimization statements are very interesting. The nolock prompt is added to the normal select statements to increase concurrency and reduce blocking. The speed is faster than that without nolock,

Today, the main two tables are 24 GB, the other is 34 GB, and the number of rows is more than million rows. The main resource is consumed in the following SQL statement:

 

select orderID from bigtable(nolock)binner join from biggertable(nolock) bt on b.orderid=bt.orderidwhere tb.orderDate <somedatetime and tb.orderDate>somedatetimeand tb.type in(3,4,5,6,7,8) 

All the queried data is in the index, with three indexes distributed on type, orderdate, and orderid. The required data can be obtained through the index crossover, which takes about 4 minutes 20 seconds. The limit is not

Add and modify indexes. Because the database is restored once a day, you can only change the statement syntax. As a result, if I run it more than twice, then the second physical reads, read-ahead reads

It should be 0, and the local memory is sufficient. Theoretically, it can accommodate the required data page, but there is no significant change in physical reads during each running.

When I remove nolock, the running time is 2 minutes 19 seconds, and the number of running times is more than 2 minutes. This is to turn on the set statistics Io on switch and find physical reads after the second time,

Read-ahead reads are all 0 times.

When I use nolock, other update transactions cause these data pages to be read to the memory and re-written to the disk due to memory pressure. After all, the two can be performed simultaneously, when I remove nolock,

Because of the S lock, the update is blocked, and thus the corresponding datapage is reserved in the memory.

The conclusion is: do not trust nolock to think that a performance problem can be solved. Nolock can be used securely in data warehouses and static tables!

For issues caused by nolock, see the following article.

Previusly committed rows might be missed if nolock hint is used