Database Tables Defined as varchar and nvarchar Causing Performance Issues

Products

• Service Virtualization

Issue/Introduction

Found SQL waits spiking over the month of December and attribute this to database columns being defined as nvarchar.

For example

CREATE TABLE component
(
comp_uuid varchar(36) NOT NULL,
reg_uuid varchar(36),
lab_uuid varchar(36),
comp_typ int NOT NULL,
comp_url nvarchar(1024) NOT NULL,
comp_name nvarchar(1024) NOT NULL,
display_name nvarchar(1024) NOT NULL,
is_hidden smallint DEFAULT 0,
host_name nvarchar(100),
lisa_ver nvarchar(100),
os_name nvarchar(100),
java_ver nvarchar(100),
is_sec_enabled smallint DEFAULT 0,
console_url nvarchar(1024),
last_start_tm datetime,
last_end_tm datetime,
last_update_tm datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
is_deleted smallint DEFAULT 0,
constraint PKcomp_uuid primary key (comp_uuid)
)

 

looks like jdbc may have been updated - new version sends parameters as Unicode - but data is ASCII- so things get very slow.

 

Can you confirm this issue and how it would be addressed?  How many tables are affected? 

Environment

All supported DevTest releases.

Cause

N/A

Resolution

 

Please make the following configuration change:

1. Go to your Enterprise Dashboard Server

2. Stop the Enterprise Dashboard Service

3. Open the file LISA_HOME/dradis.properties in a text editor

4. Update the value of property "dradis.db.url" as shown below by adding a new property "sendStringParametersAsUnicode=false" at the end of the connection string. Sharing an example:

dradis.db.url=jdbc:sqlserver://[SERVER]:[PORT];databaseName=[DATABASENAME];sendStringParametersAsUnicode=false

where SERVER is the machine where SQL Server is running
where PORT is the port SQL Server is running on
where DATABASENAME is the name of the EDB database.

5. Start the Enterprise Dashboard service.

Resolved the database spikes.