SQL Server optimizes row_number () with lock hints-programmers need to know

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