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.
Case Study: Oracle GoldenGate for AWS – Migrate Oracle on premise to Amazon Web Services (AWS) A leading content streaming service required the live-migration of its critical 24X7 billing system with near-zero downtime. Performance Tuning Corporation…