Slow Performance in OnGuard with SQL Server 2022

Symptom

Database-intensive OnGuard operations present a noticeable performance loss when the database is hosted in SQL Server 2022 and the OnGuard login for SQL (the SQL-authentication login) does not have db_owner permission to the database.

Resolution

Apply the Microsoft KB5026806 - Cumulative Update 5 for SQL Server 2022 or a later update. Then perform the following procedure to enable trace flag 12502 on the SQL Server:

1. In SQL Server 2022 Configuration Manager, open the Properties for the SQL Server service.
2. Go to the Startup Parameters tab.
3. In the Specify a startup parameter field, enter “-T12502” and then click Add.
4. The new trace parameter is added to the Existing parameters field:
   
5. Click OK.
6. Restart the SQL Server service.

Applies To

SQL Server 2022

Additional Information

SQL Server 2022 probes the registry excessively to confirm the login permissions when the login does not have the db_owner permission. This extra activity is intensive enough to visibly slow database query execution.

© 2024 Honeywell International Inc. All Rights Reserved.