SQL Server Collation Pushdown Performance Regression in Dremio 25.2.18 – Dremio Support

Summary

After upgrading to Dremio version 25.2.18, some customers may observe a significant query performance regression when querying SQL Server sources. This regression is caused by Dremio pushing explicit COLLATE LATIN1_GENERAL_BIN2 clauses into generated SQL, which can prevent SQL Server from using indexes efficiently.

This article explains why this behavior occurs, how it impacts performance, and how to mitigate the issue by disabling SQL Server collation pushdown using a supported configuration workaround.

Reported Issue

Following an upgrade from Dremio 25.2.5 to 25.2.18, SQL queries pushed down to SQL Server began including explicit collation clauses (COLLATE LATIN1_GENERAL_BIN2) on join and filter columns.

Observed impact:

Identical logical queries generated different SQL after upgrade
SQL Server execution time increased significantly (in some cases nearly doubling)
Performance regression was reproducible when running the generated SQL directly on SQL Server
Source configuration and metadata remained unchanged

Overview

Starting in Dremio 25.2.18, Dremio may explicitly push collation clauses when generating SQL for SQL Server sources. This behavior is intended to ensure correctness when comparing string columns across different collations.

However, in certain scenarios, pushing COLLATE LATIN1_GENERAL_BIN2:

Prevents SQL Server from using existing indexes
Forces additional computation during joins
Results in degraded query performance

Relevant Versions, Tools & Integrations

This article applies to:

Dremio Software: 25.2.18
Source: Microsoft SQL Server
Connectivity: JDBC
Related Issue: DX-91868

Cause

This issue is caused by how Dremio’s ARP (Advanced Relational Pushdown) framework handles non-ASCII string literals when generating SQL for SQL Server–based sources (MSSQL, Synapse, ADX).

When ARP detects non-ASCII characters in predicates, it explicitly adds:

COLLATE LATIN1_GENERAL_BIN2

to columns and literals in the generated SQL to enforce binary-safe comparisons.

However, in SQL Server, when a COLLATE clause is applied:

The string literal is first converted to the database’s default code page
Only after that is it converted to the explicitly specified collation

As a result:

Even though LATIN1_GENERAL_BIN2 is explicitly provided,
may be incorrectly converted or interpreted if the database default code page differs
This leads to incorrect character handling, unexpected comparison behavior, and
This behavior can also prevent SQL Server from using existing indexes efficiently, resulting in significant query performance degradation.

This behavior is a known defect in ARP SQL generation for SQL Server–based dialects and is tracked internally (DX-91868).

Steps to Resolve

Disable SQL Server Collation Pushdown

Dremio provides a JVM-level configuration option to disable collation pushdown for SQL Server.

Step 1: Update Dremio JVM Options

Add the following option to the DREMIO_JAVA_EXTRA_OPTS setting in the dremio-env file:

-Ddremio.jdbc.mssql.push-collation.disable=true

Step 2: Restart Dremio

Restart the Dremio service to apply the change.

Step 3: Validate Query Performance

After restarting:

Re-run the affected queries
Confirm that COLLATE LATIN1_GENERAL_BIN2 is no longer present in the pushed-down SQL
Verify that SQL Server query execution times have returned to expected levels

Next Steps

Continue using this alternate solution to maintain optimal query performance while a permanent fix is being delivered in a future release.
Monitor Dremio release notes for updates related to SQL Server pushdown behavior
Open a support ticket if additional performance issues are observed