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: )

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