Select from sequence with SqlCommand.ExecuteScalar() returns NULL when high disk usage

Question

I encounter that SqlCommand.ExecuteScalar() returns NULL sometimes in production environment.

I've crossed a lot of similar questions here, the most close one is: SqlCommand.ExecuteScalar returns null but raw SQL does not. But the advice given is not about my case.

The code sample is here:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT NEXT VALUE FOR Seq_Revision";
        command.CommandType = CommandType.Text;

        return (long)command.ExecuteScalar(); //<---ExecuteScalar() here returns NULL sometimes
    }
}

Seq_Revision here is simple MSSQL sequence like this:

CREATE SEQUENCE [dbo].[Seq_Revision] 
 AS [bigint]
 START WITH 0
 INCREMENT BY 1
 MINVALUE -9223372036854775808
 MAXVALUE 9223372036854775807
 CACHE  10 
GO

And I'm pretty sure that it will never actually return NULL.

---

Also I observe similar strange (non-repeatable behaviour) when NULL is returned in this code sample, while I'm sure there is an entity with this ID:

NHibernate.ISession.Get<FooEntity>(entityId)

---

What is interesting, returning NULL by this method correlates well with the time frames when there is a high disk activity on SQL node (disk queue length > ~50).

It may be important: we use AlwaysON cluster with 2 nodes, one of the nodes is used in read mode (ApplicationIntent=READONLY in the connection string).

MSSQL version is:

Microsoft SQL Server 2014 (SP2-CU5) (KB4013098) - 12.0.5546.0 (X64) 
    Apr  3 2017 14:55:37 
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

Lowery

ExecuteScalar returning null means: no rows were returned. If a row was returned with a null value, it would be DBNull.Value. That doesn't answer "why?", but it may be useful context – Trygve

@MarcGravell, I know that. But do you believe that SELECT NEXT VALUE FOR Seq_Revision returns 0 rows? Sounds impossible. – Lowery

I don't know; it is a good question – Trygve

NEXT VALUE FOR does not support read only database. See Limitations and Restrictions at learn.microsoft.com/en-us/sql/t-sql/functions/… => remove ApplicationIntent=READONLY from connection string – Nanci

@RaphaelHinterndorfer, one of the SQL nodes is indeed used in READONLY mode, but definitely not in these scenarios. "ApplicationIntent=READONLY" connection string is used for other queries. The given code sample "new SqlConnection(connectionString)" has no-readonly connection string. Moreover, if it was the reason, it wouldn't work never, but it returns NULL very-very rare. – Lowery

Can you test for this condition eg if (command.GetType() == typeof(DBNull)) or it catch it with an InvalidCastException and then check for the value or retry after aThread.Sleep(1000); just to rule out a timing thing with the disk. Also a good place to add some logging to help form a minimal reproducible example, my guess is its intermittent and only reproducible under IO stress. – Garment

What I would suggest, is to install some sort of a crash-dump analysis tool and set it to take crash dumps during this failure. Analyzing the said crash dump would give a lot of insight. – Ptisan

Answer 1

I think that the problem could be related to sequence caching.

Maybe there is something unhandled that result in the loss of sequence numbers remaining in the cache.

Try to disable cache in your sequence:

ALTER SEQUENCE [dbo].[Seq_Revision] 
 NO CACHE
GO

or try to use higher value for cache:

ALTER SEQUENCE [dbo].[Seq_Revision] 
 CACHE 100
GO

Snail

Thank you, we will try this theory. But that won't explain why I sometimes get NULL from ISession.Get<>(id), while I'm sure an entity with this ID does exist: NHibernate.ISession.Get<FooEntity>(entityId) – Lowery

ISession.Get hits the database or session cache to retrieve the entity data. If the entity exists it is returned, otherwise NULL will be returned. This means that if there are unhandled problems with session cache.. you can get NULLs – Snail