Performance degradation and deadlocks seen when using SQL database with vRO 7.3.x and 7.4.x

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:
When using an external SQL database with vRO 7.3.x and vRO 7.4.x, you see these symptoms:

• Utilization on the SQL database has increased significantly.
• Degradation in performance of vRO workflow execution.
• Deadlocks arising in the SQL database resulting in workflow errors similar to:
  • Unable to create resource element, reason : org.springframework.transaction.TransactionSystemException: Could not commit JPA transaction; nested exception is javax.persistence.RollbackException: Transaction marked as rollbackOnly.
  • Caused by: java.sql.SQLException: Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Environment

VMware vRealize Orchestrator 7.x

Cause

• This issue occurs due to columns in some tables having the type of varchar instead of nvarchar.
• This results in conversions being done over the primary key values of the tables where the queries are being executed. These conversions are leading to index scans being executed instead of index seek.

Resolution

This is a known issue affecting VMware vRealize Orchestrator 7.3.x and 7.4.x.

Currently, there is no resolution.

Notes:

• This issue is not experienced in the embedded vPostgres DB in vRO 7.5.
• Support for SQL DB has been removed from vRO 7.5.

Workaround:
To work around this issue, convert the columns from varchar to nvarchar.

1. Stop the vco service on all vRO nodes by running this command:
   
   service vco-server stop
    
2. Backup the vRO SQL DB before making any changes.
3. Run these commands in the vRO SQL DB [attached file update_db_statements.txt]
   
   DROP INDEX idx_reselemcatparcatid ON VMO_ResourceElementCategory;
   GO
   ALTER TABLE VMO_ResourceElementCategory DROP CONSTRAINT pk_vmoreseltcat;
   GO
   ALTER TABLE VMO_ResourceElementCategory ALTER COLUMN id nvarchar(100) not null;
   GO
   ALTER TABLE VMO_ResourceElementCategory ALTER COLUMN parentCategoryId nvarchar(100);
   GO
   ALTER TABLE VMO_ResourceElementCategory ADD CONSTRAINT pk_vmoreseltcat PRIMARY KEY CLUSTERED ( id );
   GO
   
   CREATE INDEX idx_reselemcatparcatid ON VMO_ResourceElementCategory(parentCategoryId);
   GO
   DROP INDEX idx_reselemcatid ON VMO_ResourceElement;
   GO
   ALTER TABLE VMO_ResourceElement DROP CONSTRAINT pk_vmoreselt;
   GO
   ALTER TABLE VMO_ResourceElement DROP CONSTRAINT uk_vmoreselt;
   GO
   ALTER TABLE VMO_ResourceElement ALTER COLUMN id nvarchar(100) not null;
   GO
   ALTER TABLE VMO_ResourceElement ALTER COLUMN categoryId nvarchar(100) not null;
   GO
   ALTER TABLE VMO_ResourceElement ADD CONSTRAINT pk_vmoreselt PRIMARY KEY CLUSTERED ( id );
   GO
   ALTER TABLE VMO_ResourceElement ADD CONSTRAINT uk_vmoreselt UNIQUE (tenantId, categoryId, name);
   GO
   CREATE INDEX idx_reselemcatid ON VMO_ResourceElement(categoryId);
   GO
   
   ALTER TABLE VMO_ResourceElementContent DROP CONSTRAINT pk_vmoreseltcontent;
   GO
   ALTER TABLE VMO_ResourceElementContent ALTER COLUMN id nvarchar(100) not null;
   GO
   ALTER TABLE VMO_ResourceElementContent ADD CONSTRAINT pk_vmoreseltcontent PRIMARY KEY CLUSTERED ( id );
   GO
   
   ALTER TABLE VMO_ConfigElementCategory DROP CONSTRAINT pk_vmocfgeltcat;
   GO
   ALTER TABLE VMO_ConfigElementCategory ALTER COLUMN id nvarchar(100) not null;
   GO
   ALTER TABLE VMO_ConfigElementCategory ADD CONSTRAINT pk_vmocfgeltcat PRIMARY KEY CLUSTERED ( id );
   GO
   
   ALTER TABLE VMO_ConfigElement DROP CONSTRAINT pk_vmocfgelt;
   GO
   ALTER TABLE VMO_ConfigElement ALTER COLUMN id nvarchar(100) not null;
   GO
   ALTER TABLE VMO_ConfigElement ADD CONSTRAINT pk_vmocfgelt PRIMARY KEY CLUSTERED ( id );
   GO
   
   ALTER TABLE VMO_ConfigElementContent DROP CONSTRAINT pk_vmocfgeltcontent;
   GO
   ALTER TABLE VMO_ConfigElementContent ALTER COLUMN id nvarchar(100) not null;
   GO
   ALTER TABLE VMO_ConfigElementContent ADD CONSTRAINT pk_vmocfgeltcontent PRIMARY KEY CLUSTERED ( id );
   GO
   
   ALTER TABLE VMO_ScriptModule DROP CONSTRAINT pk_vmoscriptmodule;
   GO
   ALTER TABLE VMO_ScriptModule ALTER COLUMN id nvarchar(100) not null;
   GO
   ALTER TABLE VMO_ScriptModule ADD CONSTRAINT pk_vmoscriptmodule PRIMARY KEY CLUSTERED ( id );
   GO
   
   ALTER TABLE VMO_Workflow DROP CONSTRAINT pk_vmoworkflow;
   GO
   ALTER TABLE VMO_Workflow ALTER COLUMN id nvarchar(100) not null;
   GO
   ALTER TABLE VMO_Workflow ADD CONSTRAINT pk_vmoworkflow PRIMARY KEY CLUSTERED ( id ASC);
   GO
   
   ALTER TABLE VMO_WorkflowContent DROP CONSTRAINT pk_vmoworkflowcontent;
   GO
   ALTER TABLE VMO_WorkflowContent ALTER COLUMN id nvarchar(100) not null;
   GO
   ALTER TABLE VMO_WorkflowContent ADD CONSTRAINT pk_vmoworkflowcontent PRIMARY KEY CLUSTERED ( id ASC);
   GO
   
   ALTER TABLE VMO_WorkflowToken DROP CONSTRAINT pk_vmoworkflowtoken;
   GO
   ALTER TABLE VMO_WorkflowToken ALTER COLUMN id nvarchar(100) not null;
   GO
   ALTER TABLE VMO_WorkflowToken ADD CONSTRAINT pk_vmoworkflowtoken PRIMARY KEY CLUSTERED ( id ASC);
   GO
   
   ALTER TABLE VMO_WorkflowTokenContent DROP CONSTRAINT pk_vmoworkflowtokencontent;
   GO
   ALTER TABLE VMO_WorkflowTokenContent ALTER COLUMN id nvarchar(100) not null;
   GO
   ALTER TABLE VMO_WorkflowTokenContent ADD CONSTRAINT pk_vmoworkflowtokencontent PRIMARY KEY CLUSTERED ( id ASC);
   GO
   
   ALTER TABLE VMO_WorkflowCategory DROP CONSTRAINT pk_vmoworkflowcat;
   GO
   ALTER TABLE VMO_WorkflowCategory ALTER COLUMN id nvarchar(100) not null;
   GO
   ALTER TABLE VMO_WorkflowCategory ADD CONSTRAINT pk_vmoworkflowcat PRIMARY KEY CLUSTERED ( id ASC);
   GO
    
4. Start the vco service on all VRO nodes by running this command:
   
   service vco-server start