Sample Header Ad - 728x90

MSSQL - Lock escalation on partitioned indexed view

1 vote
0 answers
128 views
I'm using partitioned tables where I set LOCK_ESCALATION to AUTO to prevent lock escalation to table level, but keep it on partition level. On the top of that table I have indexed view which is partitioned on the same scheme, but updates on underlying table are escalating locks on whole indexed view, which basically cancels the effect of partition level escalation on the underlying table... I would also like to escalate the indexed view locks only to partition level, but I can't find a way how to set it on indexed view as ALTER VIEW doesn't allow to specify SET options. I wasn't able to find any other way how to enable that, so I'm assuming that it's not possible. Or does anyone know about any way how to set it?
Asked by Lukas.Navratil (355 rep)
Oct 11, 2019, 10:10 AM