0

I have a very heavy update transaction based table

I am using an index to speed up pagination query

However the results read from the table is not important. I mean it doesnt have to be fully updated

So what actually these options do? I mean lets say if I set Allow row locks to false what happens? would it increase the speed?

Can i make such an index to be updated periodically instead of each transaction?

Lets say every 10 minutes index to be updated instead of every table update transaction is that possible?

enter image description here

Here actual execution plan of such query

https://pastebin.com/Vuwirip9

MS SQL Server 2017

thank you

Furkan Gözükara
  • 22,964
  • 77
  • 205
  • 342
  • 2
    I'd suggest asking this on DBA page. Will mark this question to be moved there so you can get crowd that will be able to help you faster. – Evaldas Buinauskas Dec 26 '18 at 21:00
  • *However the results read from the table is not important*. If this is the case, why not use `NOLOCK` table hint and leave these global settings alone? You seem to only care about a *single* table. Besides, if you disable page locks you can't reorganize your index. Updating an index (defrag / reorg) every 10 min soulds insane. Instead, just show us your query... the query plan and we can help troubleshoot the query itself. [Check out this blog](https://www.brentozar.com/archive/2009/03/getting-help-with-a-slow-query/) and [XY Problem](https://en.wikipedia.org/wiki/XY_problem) – S3S Dec 26 '18 at 21:00
  • @scsimon yes i am already using no lock. but you know each time it sorts entire table based on a column to fetch certain pages. what i am trying to achive is minizing index update COST when table is updated. – Furkan Gözükara Dec 26 '18 at 21:25
  • So you are trying to optimize the `INSERTS` and `UPDATES`, not `SELECT` queries. This still needs to go on dba.stackexchange.com but we still need to see the execution plan. Meanwhile [Paul White](https://dba.stackexchange.com/users/1192/paul-white) has written a [blog on this](https://sqlperformance.com/2015/12/sql-plan/optimizing-update-queries). Also, [Michael Swart](http://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/) has as well. – S3S Dec 26 '18 at 21:38
  • @scsimon you mean XML of execution plan right? here it is > https://pastebin.com/Vuwirip9 . Also yes i am trying to optimize insert and updates. thank you – Furkan Gözükara Dec 26 '18 at 21:46
  • Yes, but better [At this site](https://www.brentozar.com/pastetheplan/?id=rk3HKOWWV) which I've posted for you. This isn't an `UPDATE` query though... is this just a segment of it? I'm not sure why you chose to use a CTE or a sub-query in the CTE just to join back to it. This could be simplified if you'd like that? – S3S Dec 26 '18 at 22:01

0 Answers0