4

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.

  • Originally I was thinking of 1000 when I first started looking into the issue. Losing numbers in the event of a crash is not a concern, so I just wanted some validation as to whether there could have been anything else to consider. It makes sense to me that the cache should be set to something like 1000 given how frequently we use it. – bn25 Aug 19 at 20:13
  • 1
    So I made the change to up the value for CACHE to 1000 and I haven't yet seen any blocking regarding the sequence object. I did see a large-ish spike in waits around the usual time for PREEMPTIVE_XE_DISPATCHER but I'm assuming that is coincidental. – bn25 Aug 27 at 23:36
  • Great! I'll post as an answer if you'd like to accept pending further issues – LowlyDBA - John McCall Aug 28 at 13:56
0

The other 700+ sessions were waiting on LATCH_EX....We recently changed the CACHE value for this sequence from the DEFAULT (50) to 200.

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.