Reindexing job reported as completed, even though it didn't process a single record.

[rafalbroll-datapharm]

Describe the bug
While running the reindex job for newly defined custom search parameter, FHIR Server reports completion, even though no record is indexed (neither can be found using the new search param).

Hosting details:
The FHIR Server is hosted inside the Azure Container App, with assigned resources:
1 CPU, 2 GB RAM

The data instance sizes:
The entire Resources table is ~60 GB, 978 676 rows
The biggest Resource row (measured as datalength(rawresource)) is 5 MB, average is 58 KB

FHIR Version?
R4B, [3.4.342]

Data provider?
SQL Server

To Reproduce
Steps to reproduce the behaviour:

1. Set up the custom search parameter running the command:

curl --location --request PUT "http://${FHIR_HOST}/SearchParameter/datapharm-product-family" \
--header 'Content-Type: application/json' \
--data-raw '{
 "resourceType" : "SearchParameter",
 "id": "datapharm-product-family",
 "url" : "https://medicines.org.uk/productFamily",
 "version" : "0.0.1",
 "name" : "datapharm-product-family",
 "status" : "active",
 "date" : "2023-02-01",
 "publisher" : "Datapharm Ltd",
 "contact" : [
   {
     "telecom" : [
       {
         "system" : "other",
         "value" : "https://www.datapharm.com/"
       }
     ]
   }
 ],
 "description" : "Searching by product family",
 "jurisdiction" : [
   {
     "coding" : [
       {
         "system" : "https://medicines.org.uk/productFamily",
         "code" : "UK",
         "display" : "United Kingdom"
       }
     ]
   }
 ],
 "code" : "datapharm-product-family",
 "base" : [
   "MedicinalProductDefinition"
 ],
 "type" : "token",
 "expression" : "MedicinalProductDefinition.extension.where(url='\''https://medicines.org.uk/productFamily'\'').value"
}'

2. Run the reindexing Job

{ 
    "resourceType": "Parameters",  
    "parameter": [
           {
            "name": "targetSearchParameterTypes",
            "valueString": "https://medicines.org.uk/productFamily"
          }    
      ]
}

3. Check the results by calling GET /_operations/reindex/<reindex job id>

Expected behavior
The result is

...
{
    {
       "name":"totalResourcesToReindex",
       "valueDecimal": 18514.0
    },
    {
       "name":"resourcesSuccessfullyReindexed",
       "valueDecimal": 18514.0
    },
    {
       "name":"progress",
       "valueDecimal": 100.0
    },
    {
       "name":"status",
       "valueString": "Completed"
    }
}
...

and attempts of searching by that parameters succeed.

Actual behavior
The result is

...
{
    {
       "name":"totalResourcesToReindex",
       "valueDecimal": 18514.0
    },
    {
       "name":"resourcesSuccessfullyReindexed",
       "valueDecimal": 0.0
    },
    {
       "name":"progress",
       "valueDecimal": 0.0
    },
    {
       "name":"status",
       "valueString": "Completed"
    }
}
...

and attempts of searching by that parameter fail.

---

Applying maximumNumberOfResourcesPerQuery:

In addition, we were experimenting with adding the parameter maximumNumberOfResourcesPerQuery while running the Reindexing Job. That partially deals with the problem, but sometimes it indexes only 99% of the records.
ie. for Reindex Job defined like that:

{ 
    "resourceType": "Parameters",  
    "parameter": [
           {
            "name": "targetSearchParameterTypes",
            "valueString": "https://medicines.org.uk/productFamily"
          } ,
          {
             "name": "maximumNumberOfResourcesPerQuery",
             "valueInteger": "5000"
         }
      ]
}

we got that response

...
{
    {
       "name":"totalResourcesToReindex",
       "valueDecimal": 18514.0
    },
    {
       "name":"resourcesSuccessfullyReindexed",
       "valueDecimal": 18513.0
    },
    {
       "name":"progress",
       "valueDecimal": 99.99
    },
    {
       "name":"status",
       "valueString": "Completed"
    }
}
...

[EXPEkesheth]

@rafalbroll-datapharm - Are you provisioned on Cosmos DB/ SQL persistence layer?

[rafalbroll-datapharm]

@rafalbroll-datapharm - Are you provisioned on Cosmos DB/ SQL persistence layer?

It's the SQL Server.

[fahadnadeem1995]

@rafalbroll-datapharm I am facing the same issue with Azure FHIR Service with SQL server.

[rafalbroll-datapharm]

@rafalbroll-datapharm I am facing the same issue with Azure FHIR Service with SQL server.

Just to clarify, which scenario do you experience? Having a completed task with 0.0% progress, or the latter - having 99% of completion?

[fahadnadeem1995]

@rafalbroll-datapharm It shows completion with 0.0% progress although indicating that a few resources reindexed successfully. And a few resources do return as a result of using that custom search parameter in the query.

Please have a look at the screenshot below:

[EXPEkesheth]

Thanks for reporting , we will investigate further
#AB125852

[EXPEkesheth]

@rafalbroll-datapharm - we recently made improvements to the reindexing logic, can you please execute reindex and let us know if you still see the issue. Looking forward to your response

[fahadnadeem1995]

@rafalbroll-datapharm I opened a ticket with Microsoft and the support team told me about the fix/improvement to the reindexing logic they made afterwards. I then retried and it began working.

[rafalbroll-datapharm]

@rafalbroll-datapharm - we recently made improvements to the reindexing logic, can you please execute reindex and let us know if you still see the issue. Looking forward to your response

Many thanks. Which FHIR server version has those changes introduced?