FNMS on SQL 2019 and Legacy Cardinality Estimator (CE)

[darren_haehnel]

This year we migrated from Win/SQL 2012 servers to Win/SQL 2019 servers for our FNMS environments.  One area we struggled with post migration was the duration of the "Inventory Import and License Reconcile" batch job.  Ours is scheduled to start at 7pm and pre-migration would finish around 2:30am (+/- 1hr).  After migration, the job would run past 4:30am (much longer still when everyone had the ARL issue with double wildcard matches).  

Our DBA's compared the old and new servers and looked for any opportunities to get us back to prior performance levels.  We also analyzed the database using a leading observability tool.  We experimented a bit with enabling the Legacy Cardinality Estimator (Cardinality Estimation (SQL Server) - SQL Server | Microsoft Learn) and found our batch job duration improved by roughly 25%, returning us to the 7-8 hour run time, with this one "simple" change.  

I'm curious if anyone else has experimented with this setting, found similar results, or if there might be any negative consequences.  So far, we have left the Legacy CE enabled and found no trouble.  

[mfeinman]

I have seen, in the past, where an "improperly" set SQL Server Compatibility level could cause the Import / Reconciliation process to run long.  For SQL Server 2019, the recommendation for this setting is "SQL Server 2019 (150)".  Are your databases set to that compatibility level?

(I have no experience with the setting you've described.)

--Mark

[darren_haehnel]

Hi @mfeinman , 

We have our SQL 2019 server configured per the FNMS upgrade guide.  It's running at compatibility level 150.  

[ChrisG]

When running on SQL Server 2019, be careful to satisfy the prerequisites as described in the System Requirements document:

Note: Three points to check:

1. Ensure that you have installed Cumulative Update Package 5 for SQL Server 2019 or later.
2. As FlexNet Manager Suite uses SQL CLR, and this is not compatible with the memory-optimized tables feature introduced in SQL Server 2019, disable TempDB_Metadata using either of the following processes, and then restart SQL Server:

   
   ALTER SERVER CONFIGURATION 
       SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF
   GO

   or

   EXEC sp_configure 'tempdb metadata memory-optimized', 0
   GO
   RECONFIGURE
   GO

3. In SQL Server 2019, Microsoft introduced changes in Intelligent Query Processing that may produce errors in pages of the web interface (for example, in the All Purchases page). Two possible workarounds that prevent the errors are, for the compliance database in FlexNet Manager Suite, to either:
   1. Turn off the following six Intelligent Query Optimizer components (as described in this Microsoft topic):

      ALTER DATABASE SCOPED CONFIGURATION 
          SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;
      ALTER DATABASE SCOPED CONFIGURATION 
          SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;
      ALTER DATABASE SCOPED CONFIGURATION 
          SET INTERLEAVED_EXECUTION_TVF = OFF;
      ALTER DATABASE SCOPED CONFIGURATION 
          SET DEFERRED_COMPILATION_TV = OFF;
      ALTER DATABASE SCOPED CONFIGURATION 
          SET TSQL_SCALAR_UDF_INLINING = OFF;
      ALTER DATABASE SCOPED CONFIGURATION 
          SET BATCH_MODE_ON_ROWSTORE = OFF; 

      or
   2. Run with compatibility level set to SQL Server 2017 (140).

[darren_haehnel]

Thanks @ChrisG.  This was performed as prescribed in our situation and still, performance was not acceptable.

[ChrisG]

Those are interesting observations. Thanks for sharing @darren_haehnel.

Also possibly related to this (or at least in the same general space) are the following recommendations (as per information in the System Requirements documentation) to follow when using SQL Server 2019:

In SQL Server 2019, Microsoft introduced changes in Intelligent Query Processing that may produce errors in pages of the web interface (for example, in the All Purchases page). Two possible workarounds that prevent the errors are, for the compliance database in FlexNet Manager Suite, to either:

1. Turn off the following six Intelligent Query Optimizer components (as described in this Microsoft topic):

   ALTER DATABASE SCOPED CONFIGURATION 
       SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;
   ALTER DATABASE SCOPED CONFIGURATION 
       SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;
   ALTER DATABASE SCOPED CONFIGURATION 
       SET INTERLEAVED_EXECUTION_TVF = OFF;
   ALTER DATABASE SCOPED CONFIGURATION 
       SET DEFERRED_COMPILATION_TV = OFF;
   ALTER DATABASE SCOPED CONFIGURATION 
       SET TSQL_SCALAR_UDF_INLINING = OFF;
   ALTER DATABASE SCOPED CONFIGURATION 
       SET BATCH_MODE_ON_ROWSTORE = OFF; 

   or
2. Run with compatibility level set to SQL Server 2017 (140).

[Steffen]

Hi Darren,

I ran into the same problem about one year ago sql server 2019 and FNMS 2021R1.

I created a support case (02518130)

The end of the sorry was that I switchend back db compatibility level to 110 (sql 2012) even improving on original reconciliation time! Flexera gave me approval for this level to be run even though its not the official recommended level.

Using the official recommended settings (including disableling Optimizer components) for sql 2019 did not solve the additional 10h reconciliation time for me.

We are now running FNMS 2022R1 still on a 110 level with great performance.

By the way I also managed to get Cognos up and running on sql 2019 even though this is offically not supported.

Hope this  might help

Greetings

Steffen