Contact Us
The Hidden Cost of sendStringParametersAsUnicode

When investigating performance degradation in SQL Server, we often look at missing indexes, outdated statistics, or bloated execution plans. However, sometimes the root cause lives entirely outside the database engine, deep within the application configuration.

In a recent performance optimization case, we encountered a classic hidden performance bottleneck: the default behavior of Java database drivers when handling string parameters.

The Problem: Implicit Conversions and Table Scans

The client experienced severe CPU spikes and sluggish query performance. The underlying culprit turned out to be the default setting of the JDBC driver parameter sendStringParametersAsUnicode = true.

  • The Mechanism: When this setting is TRUE (which is the default), the Java driver transmits string variables as Unicode (NVARCHAR) to the database. This happens transparently under the hood, regardless of how the developer originally declared the string variables in the application code.
  • The Conflict: If your database tables store text in standard non-Unicode columns (VARCHAR or CHAR), a fundamental data type mismatch occurs. SQL Server is then forced to perform an implicit data type conversion on every single row to evaluate the predicate, because it cannot directly compare Unicode and non-Unicode data without a conversion step.
  • The Consequence: This runtime conversion completely invalidates existing non-clustered indexes on those columns. As a result, the query optimizer is forced to abandon efficient seek operations in favor of costly table scans, severely elevating CPU usage, creating resource bottlenecks, and degrading overall system concurrency.

The Solution and Verification

As soon as the client reconfigured the application to set sendStringParametersAsUnicode = false, the performance metrics shifted dramatically. Using our SMT monitoring tool, we verified the recovery across multiple vectors:

 

  • CPU Utilization: Total CPU pressure dropped significantly as queries stopped churning through unnecessary table scans.
  • Latch Contention: We observed a massive drop in the ACCESS_METHODS_DATASET_PARENT latch, which typically accumulates during heavy Index Scan operations and excessive concurrent data reads.
  • Signal Waits: Pressure on processor queues decreased sharply, reflected by a major reduction in SOS_SCHEDULER_YIELD signal waits.

 

A/B Testing Results

To quantify the fix, we utilized the A/B testing comparison features in SMT, comparing the historical intervals before and after the hotfix:

  • Resource Consumption: In more than 90% of cases, key metrics such as CPU time, logical reads, and total runtime were significantly higher before the fix compared to the optimized period.
  • Query Hashes: Individual query performance hashes improved dramatically across the board, confirming system-wide relief.

 

Key Takeaway: Never overlook application-tier connection string defaults or treat them as mere boilerplate. A simple configuration change can mean the difference between full index utilization and crippling implicit conversions. When your application and database speak different data-type languages, your hardware pays the price. Taking the time to audit these underlying driver settings is often one of the highest-impact investments you can make for your database's long-term health and performance.

More tips and tricks

Tool to measure Index Selectivity
by Michal Tinthofer on 05/06/2012

Sometimes when you plan to change your index design is good to know the columns of your tables. But not just a data type and max size.

Read more
SMT 00.5.32 Released!
by Michal Tinthofer on 31/08/2017

After some time, we have finally released big set of changes and fixes for currently known issues. Most visible is new Waiting Task report for operational analysis, added timeline button for Index Usage & recommendation, new functionality to search for an

Read more
SMT 00.5.34 Released!
by Michal Tinthofer on 18/10/2017

Today we introduce to you another patch which was focused on several “quality of life “improvements. Let's have a look!

Read more