Original: SQL Server optimizes row_number () with lock hints-programmers need to know
Some of the old pages in the site still use row_number similar window functions for paging, at this time if the situation of digging graves may require a long wait and consumption is huge. Here we introduce a specific lock hint based on the row_number () feature to improve query speed.
Direct serving
The scripting environment can be found in "MapReduce" in SQL Server tuning tips for SQL Server
The following query is more common in pagination
Set StatisticsTime on Select * from (SelectProductID, RN=Row_number () Over(ORDER byProductID) from [bigtransactionhistory]) asTwhereT.rnbetween 15631801 and 15631802
This query executes 15S on my computer, which is still the case with all the data in memory!1-1
Figure 1-1
A simple execution plan execution so long is a bit of a mystery, after all, logical reading is more than 6W, and no physical read
, and CPU time and occupy time, excluding the blocking and other factors, we put the consumption on the query itself. This is a feature of Row_number (), which can be serialized in thousands of data to find the exact data points we want, However, the default implementation is to add a row lock for each row of data.
We turn on trace Flag 1200 to execute the lock on the execution of the statement capture again. You can see that row_number () does not have a lock upgrade on the implementation 1-2
Code
DBCCTraceon3604, -,-1)Select * from (SelectProductID, RN=Row_number () Over(ORDER byProductID) from [bigtransactionhistory]) asTwhereT.rnbetween 15631801 and 15631802
View Code
Figure 1-2
Our solution to this problem comes out: it can be manually upgraded in the form of lock hint
Here I use page lock, 1-3
And the two are the same from the execution plan, and the projections are exactly the same. 1-4
Code
Select * from (SelectProductID, RN=Row_number () Over(ORDER byProductID) from [bigtransactionhistory] with(paglock)) asTwhereT.rnbetween 15631801 and 15631802
View Code
Figure 1-3
Figure 1-4
You can see that our usual way of looking at execution plans is less appropriate here, requiring more detailed knowledge of resource consumption.
Note: We can also use trace Profiler to capture locks, but be cautious.
Row_number () The lock upgrade is not visible by default, the global performance bottleneck may rebound level
If your app doesn't care about dirty reading, nolock way more enjoyable:)
Other: When the data is blocked by the update, sometimes the business colleague will ask in the end which data is updated with wood?
Here is a simple query to find the exact update of the locked row, 2-1
Code
begin TranTTTUpdateDbo.[bigproduct] SetSize=111 whereProductID<1100--rollback when finish test--rollback Tran TTT--Open another sessionSELECT * from [bigproduct] with(NOLOCK)WHERE %%Lockres%% inch ( SELECTtl.resource_description fromSys.dm_tran_locks asTLINNER JOINSys.partitions asT2 ont2.hobt_id=tl.resource_associated_entity_idWHERET2.object_id = object_id('bigproduct') andTl.resource_type= 'KEY' )
Figure 2-1
Conclusion: any element within the system can be a stumbling block to balance. Find it, understand it, use it.
Think that the students have the harvest please praise.
SQL Server optimizes row_number () with lock hints-programmers need to know