A bizarre situation - SQL query finds no records when record is present.

I am trying to troubleshoot a bizarre situation, and looking for either an explanation of the cause, or assistance in determining the cause. The situation is esoteric (to say the least), so I'll need to paint a picture.

I am using SQL 2019 Enterprise for testing.

Our database has Allow Snapshot Isolation set to true, and the Is Read Committed Snapshot On is also true.

We have a table called T_App_View, with essentially these important fields (plus some audit fields not shown):

CREATE TABLE T_App_View
(
   C_Id int not null,
   C_Type varchar( 20 ) not null,
   C_Entity varchar( 250 ) not null,
   C_Specialisation varchar( 250 ) null,
   CONSTRAINT APPVIEW_PKC PRIMARY KEY NONCLUSTERED( C_Id )
)

We have an index on (Type, Entity, Specialisation):

create unique index APPVIEW_I01 on T_App_View( C_Type, C_Entity, C_Specialisation )

And from a historical hangover we have a view against the table that is used for queries:

CREATE VIEW dbo.VS_App_View AS SELECT * FROM dbo.T_App_View WITH CHECK OPTION

The table is used to define "UI views" for our application, and contains about 1000 records.

As part of commissioning a new database, we use a tool to load data into this table, and then the data basically sits there unchanged. Each time the tool is run it deletes any old data in the table, before populating the new data.

The tool deletes the old data row-by-row rather than using a truncate table statement or similar.

When our web tier is asked to provide the "definition" of a view, it performs a query on VS_App_View using the three compound key components of Type, Entity, and Specialisation. A dynamic cursor is used (a historical hangover from many years ago). A typical query would look similar to this:

SELECT appview.C_Id,
 appview.C_Entity_Id,
 appview.C_Specialisation,
 appview.C_View_Type
FROM dbo.VS_App_View appview
WHERE appview.C_View_Type = 'DETAIL'
 AND appview.C_Entity_Id = 'a'
 AND appview.C_Specialisation = 'Finance'

We are using the standard isolation level of Read Committed (which means Read Committed Snapshot in reality). Notice the select statement is not using an option of READCOMMITTED in the query.

The fun part

Sometimes when we do a lookup in SQL Server, the above query finds no matching records. This bug is very evasive, we cannot reproduce it, and it appears maybe once or twice a month, and other months not at all. It is not limited to specific key values, it just appears to happen randomly (or the pattern is obscure enough that we cannot yet see it).

Our QA team experienced the find failure the other day and one of our developers jumped into SSMS and ran the query manually and sure enough it came back with no records found. And now the bizarre bit:

The developer ran the query directly against the T_App_View table, and the record appeared. He then re-ran the query against the View, and the record appeared.

We have the Query Store enabled for this database and I can see two plans matching the above query (one for a null specialisation, and one for a non-null specialisation). The query plans look odd (due to the dynamic cursor presumably) because even though the index guarantees a maximum of one matching record, the query plan still shows a temporary table being constructed along with a clustered index for that table.

I feel like I am reasonably proficient in SQL Server but this one has me stumped. ChatGpt suggested it is a query plan issue, but I'm skeptical as I've never heard of a query plan not finding a record. I've only experienced query plan issues in terms of performance impacts.

Has anyone ever come across such a situation? Is Snapshot Isolation part of the problem here, or database statistics, or the fact we are deleting an entire table of records individually and then repopulating them (resulting in bad statistics)? Should we have used WITH( READCOMMITTEDLOCK ) for this query perhaps? Any suggestions on how to troubleshoot effectively, as in what to look at and for?

Thanks in advance for any help.