Database Guide › SQL Server Guidelines › Performance Enhancements for Searches and Reports

Performance Enhancements for Searches and Reports

(Optional) You can the following post-upgrade stored procedures on the CMS to optimize CA Data Protection search and report performance.

Enable or Disable Queryflags Optimization

The Queryflags optimization can be globally enabled or disabled.

To enable the queryflags optimization, use the following commands:

Oracle

EXEC wgn_QueryFlags_Enable

SQL Server

EXECUTE dbo.wgn_QueryFlags_Enable

To disable the queryflags optimization, use the following commands:

Oracle

EXEC wgn_QueryFlags_Disable

SQL Server

EXECUTE dbo.wgn_QueryFlags_Disable

Optimized Indexing for Searching Unreviewed Events (Optional)

When using SQL Server 2008 or later versions, you can further optimize performance by creating filtered indexes on the Wgn3EventParticipant table. There are two options provided in the \NativeSchemaSQL\MSSQL folder.

Index for Searching All Unreviewed Events

Native_Index_Unreviewed_All.sql: This script optimizes performance when searching for all unreviewed events.

EXECUTE Native_Index_Unreviewed_All.sql

Index for searching for Triggered Unreviewed Events

Native_Index_Unreviewed_Triggered_Only.sql: This script optimizes performance when searching for unreviewed events that are triggered.

EXECUTE Native_Index_Unreviewed_Triggered_Only.sql

RLS Cardinality Workaround (Optional)

(SQL Server only) You can run a post-upgrade stored procedure Wgn_stats_Set_No_Recompute on the CMS as a workaround to a known MS SQL Server issue. The first time you execute the stored procedure, it seeds the Row Level Security table with large volumes of data, and stores the statistics for later use.

Note: During testing, we observed this operation typically took up to two hours and causes the database to expand by 9 GB. Once this operation is complete, the space can be reclaimed.

The prototype is shown below:

Wgn_stats_Set_No_Recompute (@iNo_recompute IN BIT,@debug IN INT)

Where

iNo_recompute = 1specifies that the performance optimization is on.

iNo_recompute = 0 specifies that the performance optimization is off.

Examples:

To switch the performance optimization on, use the following command:

EXECUTE dbo.Wgn_stats_Set_No_Recompute @iNo_recompute = 1

To switch the performance optimization off, use the following command:

EXECUTE dbo.Wgn_stats_Set_No_Recompute @iNo_recompute = 0

By using this stored procedure, you can improve the RLS cardinality estimates and the query plans.