> New option for indexes - OPTIMIZE_FOR_SEQUENTIAL_KEY
> Turns on an optimization within the database engine that helps improve throughput for high-concurrency inserts into the index. This option is intended for indexes that are prone to last-page insert contention, typically seen with indexes that have a sequential key such as an identity column, sequence, or date/time column.
> We have decided that our current prototype does not meet our customer’s expectations. We will therefore not carry this capability forward. We will update you on our plans if we have a replacement candidate.
I'm going to make a guess that's wrong and say that the mechanism for OPTIMIZE_FOR_SEQUENTIAL_KEY reuses code written to reduce PFS contention in user databases
We have a job table that looks like this
CREATE TABLE [dbo].[Clearing](
[Skey] [decimal](19, 0) IDENTITY(1,1) NOT NULL,
[BsAcctId] [int] NULL,
[Status] [varchar](20) NULL,
CONSTRAINT [csPk_Clearing] PRIMARY KEY CLUSTERED ( [Skey] ASC )
)
with a covering index like this
CREA...
> Update 4 - Our worker processes maintain a db connection pool. ODBC does not tell us when a fail over happens, so connections to an old primary stay in the pool until we try to use them and they fail. We suspect after we failover DB1 -> DB2 -> DB1, then old connections to DB1 might not fail like they should.
Sorry, I was just kidding about closing it. I think it's a decent question about making assumptions when using READPAST and UPDLOCK. Even if the specific issue the OP is having was due to {made-up ODBC problem}.
It seems to me that the question is asking "why might I get the same row in multiple sessions when using select...with(updlock, readpast)?" and the answers are pointing out reasons that might happen.
Maybe not every reason that might happen, given the OPs update.
> After the second fail over and all the worker processes get new connections to the new primary, the query starts failing in the sense that multiple processes start getting the same jobs.
The answers still seem relevant to me with that context. They are attempts to answer the question. They might not be right for this specific OP (thus no checkmark), but they could be helpful to future visitors dealing with queue problems.
Maybe I'm biased because I answered.
Perhaps Joe can arbitrate once he finishes deliberating.
@ErikDarling Did you get any responses to "What Are Your Weirdest Waits?" I was surprised I didn't see any blog comments.
@JoeObbish yah, I agree. a TRUNCATE TABLE is simply logging the page numbers that have been effectively deleted. Rollback would simply undo the delete operation by marking the pages as allocated instead of unallocated.
> Paul Randal says: August 20, 2016 at 5:14 PM Yup – it's really confusing to many people. What they should say is that the individual rows are not deleted, so they are not logged.
We have a job table that looks like this
CREATE TABLE [dbo].[Clearing](
[Skey] [decimal](19, 0) IDENTITY(1,1) NOT NULL,
[BsAcctId] [int] NULL,
[Status] [varchar](20) NULL,
CONSTRAINT [csPk_Clearing] PRIMARY KEY CLUSTERED ( [Skey] ASC )
)
with a covering index like this
CREA...
