Some users may be experiencing issues when trying to access customer resources like the Case Portal or the Product Licensing Center. Our team is aware of the issue and is working to resolve it. Click here for more information.
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.
‎Dec 28, 2022 12:30 PM
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
‎Jan 04, 2023 04:31 PM
Hi @mfeinman ,
We have our SQL 2019 server configured per the FNMS upgrade guide. It's running at compatibility level 150.
‎Jan 05, 2023 11:59 AM
When running on SQL Server 2019, be careful to satisfy the prerequisites as described in the System Requirements document:
Note: Three points to check:
- Ensure that you have installed
Cumulative Update Package 5 for SQL Server 2019
or later.- 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:orALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF GO
EXEC sp_configure 'tempdb metadata memory-optimized', 0 GO RECONFIGURE GO
- 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:
- Turn off the following six Intelligent Query Optimizer components (as described in this Microsoft topic):
orALTER 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;
- Run with compatibility level set to SQL Server 2017 (140).
‎Jan 05, 2023 07:39 PM
Thanks @ChrisG. This was performed as prescribed in our situation and still, performance was not acceptable.
‎Jan 06, 2023 09:01 AM
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:
- Turn off the following six Intelligent Query Optimizer components (as described in this Microsoft topic):
orALTER 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;
- Run with compatibility level set to SQL Server 2017 (140).
‎Jan 04, 2023 11:02 PM
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
‎Jan 05, 2023 12:41 AM
User | Count |
---|---|
8 | |
6 | |
3 | |
3 |