SQL Server 利用锁提示优化Row_number()-软件工程师需知

网站中一些老页面仍采用Row_number类似的开窗函数进行分页处理,此时如果遭遇挖坟帖的情形可能就需要漫长的等待且消耗巨大.这里给大家介绍根据Row_number()特性采用特定锁Hint提升查询速度.

  直接上菜

  脚本环境可在SQL Server优化技巧之SQL Server中的"MapReduce"找到

  如下查询在分页中比较常见

set statistics time on select * from (select ProductID, rn = ROW_NUMBER() OVER (ORDER BY ProductID)from [bigTransactionHistory]) as twhere t.rn between 15631801 and 15631802

这条查询在我的电脑上执行了15S,这还是数据全在内存中的情形!如图1-1

                                                                图1-1

一个简单的执行计划执行如此之长有点匪夷所思,毕竟逻辑读才6W多,且无物理读

,而且CPU时间与占用时间相差无几,排除了阻塞之类的因素后我们把消耗定位在这个查询本身上.这时提一个Row_number()的特点,它可在万千数据中将其序列化让我们找到我们想要的精确数据点,但就此默认的实现方式上是为每一行数据都加一个行锁.

我们开启Trace Flag 1200再次执行语句捕捉下执行时的锁.可以看到Row_number()在实现上未进行锁升级如图1-2

Code

dbcc traceon(3604,1200,-1)select * from (select ProductID, rn = ROW_NUMBER() OVER (ORDER BY ProductID)from [bigTransactionHistory]) as twhere t.rn between 15631801 and 15631802

View Code

                                                   图1-2

到此我们对此问题的解决方式也就出来了:可采用锁hint的形式手动为其升级

这里我采用页锁,如图1-3

而两者从执行计划上看是相同的,预估也完全一样如图1-4

Code

select * from (select ProductID, rn = ROW_NUMBER() OVER (ORDER BY ProductID)from [bigTransactionHistory] with(paglock)) as twhere t.rn between 15631801 and 15631802

View Code

                                         图1-3

                                                             图1-4

可以看到我们通常的查看执行计划的方式在此就不太适合了,需要我们对资源消耗有更详细的认知.

注:平时我们还可用Trace Profiler捕捉锁,但需注意慎用.

   Row_number()默认看不到锁升级,全局性能瓶颈下可能回升级

   如果你的应用不在乎脏读,nolock方式更愉快:)

其它:当数据被更新发生阻塞时,有时业务同事会问到底更新了哪条数据有木有?

这里写了个简单的查询以便找到具体更新被锁住的行,如图2-1

Code

begin tran tttupdate dbo.[bigProduct] set size=111 where ProductID<1100-- rollback when finish test--rollback tran ttt--open another sessionSELECT * FROM [bigProduct] with(nolock)WHERE    %%LOCKRES%% IN    (        SELECT             tl.resource_description        FROM sys.dm_tran_locks AS tl        INNER JOIN sys.partitions AS t2 ON            t2.hobt_id = tl.resource_associated_entity_id        WHERE             t2.object_id = OBJECT_ID('bigProduct')            AND tl.resource_type = 'KEY'    )

                                                          图2-1

结语:系统内任何元素都有可能成为影响平衡的绊脚石.找到它,理解它,利用它.

认为有收获的同学请点赞.

7楼gw2010

数据这么大还是扫描，是没有索引？，然后这个测试环境是怎么样的？单纯的运行两次这个查询吗？我还是没想通为什么加了这个会快些，为什么系统不直接优化？是否在实际环境中效果就不一样了?

Re: ShanksGao

@gw2010，先了解下这个语句的语义吧，呵呵这不是索引的事情.

6楼菜鸟就是我

这个很不错，感谢分享，我每次使用row_number都是with(nolock)，但没去考虑为什么要with(nolock)，单纯的为了不上锁的目的

Re: ShanksGao

@菜鸟就是我，呵呵,对一致性要求不高的情形下,nolock益处还是很大的.

5楼Youngming

主要是row_number()使用key S锁但是默认并不锁升级，大量的锁占用大量资源，这时候使用页锁可以大大节省锁占用的资源

4楼笑东风

好文要顶！

Re: ShanksGao

@笑东风，多谢文佳，够速度:)

3楼obscure

好文一定要顶！

Re: ShanksGao

@obscure，多谢兄台:)

2楼dandzm

占用时间是包括了cpu时间吗

Re: ShanksGao

@dandzm，是的

1楼Page-7y

不错，很有意义。收藏。

Re: ShanksGao

@Page-7y，谢谢:)，小细节值得注意