(Optional) You can run a post-upgrade stored procedure (Wgn_Queryflags_Migrate) on the CMS to optimize CA Data Protection search and report performance.
This stored procedure (SP) migrates key data to a new database column and can take a long time. The CMS runs as normal while data is being migrated. However, we recommend that you schedule the SP to run during off-peak periods, typically outside of office hours.
After the SP has completed, we strongly recommend that you defragment your database indexes (see below).
Note: During testing, we observed migration rates of up to 6 million events per hour. However, actual migration rates in a production environment depend on your hardware configuration and data distribution.
The procedure differs, depending on your database engine.
Oracle CMSs
In Oracle SQL*Plus, connect to the CA Data Protection database as the schema owner. If there is no separate account for the schema owner, connect as the primary user.
Important! If your Oracle database has separate accounts for the primary user and the schema owner, you must run the Wgn_Queryflags_Migrate SP as the schema owner (for example, WGNOWNER). Do not run this SP as the primary user (for example, WGNUSER). This is because the SP is created under the schema owner and there are no grants or synonyms. If you do not have separate accounts for the primary user and the schema owner, run the SP as the primary user.
Run the following commands:
SQL> set serveroutput on size unlimited SQL> EXEC wgn_queryflags_migrate TimeOutMins => 180;
SQL Server CMSs
Log in to SQL Server Management Studio as the primary user. Then run the following command:
EXECUTE dbo.wgn_queryflags_migrate @TimeOutMins = 180
Prototype
The SP prototype is shown below:
Wgn_Queryflags_Migrate ( BatchSize IN NUMBER, UpdateBatchSize IN NUMBER TimeOutMins IN NUMBER RestartMigrate IN NUMBER );
Where:
Defaults to 10,000. Specifies the number of events migrated in each batch.
(SQL Server only) Defaults to 10,000. Specifies the maximum number of rows updated in each batch.
Defaults to zero. Specifies the period (in minutes) before the SP times out. Use this parameter to restrict how long the SP runs. For example, if you schedule the SP to start at midnight, set this parameter to 180 (four hours) to stop the SP at 3am.
When you restart the SP after a timeout, the SP resumes migrating events at the point where it stopped.
If this parameter is set to zero, the SP never times out.
Defaults to zero. Restarts the SP process.
Oracle Example: This example sets the batch size to 50,000 with a three hour timeout. You must run a preliminary command before executing the SP:
SQL> SPOOL Wgn_QueryFlags_Migrate.txt SQL> set serveroutput on size unlimited SQL> EXEC Wgn_QueryFlags_Migrate (Batchsize => 50000, TimeOutMins => 180); SQL> SPOOL OFF
SQL Server Example: This example sets the batch size to 50,000 with a three hour timeout and limits the number of updated rows in each batch to 10,000:
EXECUTE dbo.Wgn_Queryflags_Migrate @Batchsize = 50000, @UpdateBatchsize = 10000, @TimeOutMins = 180
The Queryflags optimization can be globally enabled or disabled.
To enable the queryflags optimization, use the following commands:
EXEC wgn_QueryFlags_Enable
EXECUTE dbo.wgn_QueryFlags_Enable
To disable the queryflags optimization, use the following commands:
EXEC wgn_QueryFlags_Disable
EXECUTE dbo.wgn_QueryFlags_Disable
After the SP has completed, some database indexes will be heavily fragmented becuase of the data updates. We strongly recommend that you run your standard index maintenance routines at the earliest convenient opportunity to remove any temporary performance degradation due to this index fragmentation.
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.
Native_Index_Unreviewed_All.sql: This script optimizes performance when searching for all unreviewed events.
EXECUTE Native_Index_Unreviewed_All.sql
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
(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.
|
Copyright © 2015 CA Technologies.
All rights reserved.
|
|