We have been experiencing an issue with a sequence object we have that is called a lot. It is used to assign a value prior to a record being inserted into a table.
Today I saw that 700+ sessions (all trying to get the next value from the sequence object) were being blocked by a session that was trying to get the next value from the sequence object with a wait type of PAGELATCH_EX. The other 700+ sessions were waiting on LATCH_EX.
When I looked into the wait resource it was referring to sys.sysobjvalues.
I'm curious as to why this may be happening and if other people have seen this. We recently changed the CACHE value for this sequence from the DEFAULT (50) to 200. I assumed this would improve the performance of calling the sequence given we use it so heavily but possibly I was wrong.
We are using SQL Server 2012 SP4.
Best Answer
If you've got 700 waiting sessions, my first thought is that the cache needs to be much higher given the workload. I would recommend trying something in the range of 500-1000 (and maybe keep increasing) until you see the waits either stabilize or drop off. There should be a sweet spot you can find with a larger cache value.