How default application driver settings may cause performance problems

At PTC, we have noticed an interesting performance issue concerning the JDBC application driver.  There have been several clients coming to us with SQL Server performance problems that they could not resolve on their own.  Their SQL Servers were showing a consistently high amount of CPU utilization, concurrency issues such as blocking and deadlocks and long running SQL statements.

When identifying the top offending SQL commands, we noticed something very interesting.  An analysis of the query plans and I/O statistics of the top SQL statements revealed data type conversion steps causing a high amount of scan counts and logical reads.  The data type conversions were occurring on columns with VARCHAR and CHAR definitions.

The JDBC driver contains a setting called ‘sendStringParametersAsUnicode’.  By default this is set to “true” causing all string values to be passed-in as Unicode values.  When the MS SQL Server optimizer processes SQL statements against tables with NonUnicode columns, SQL Server implicitly converts the string values.  Changing this JDBC driver setting to “false” will eliminated unnecessary logical reads, reduce CPU and increase the performance on your SQL Server.