迁移至SQL Server 2014标准版后，Java应用查询缓慢服务器端正常求助

Hey there, let's tackle this issue you're seeing—where queries run fast directly on SQL Server 2014 STD but lag when called from your JBoss-hosted Java app after adjusting compatibility modes. I've worked through similar migration/compatibility headaches before, so here's a breakdown of what's likely going on and how to fix it:

Core Context Recap #

First, to make sure we're aligned:

• You migrated from SQL Server 2012 Enterprise to 2014 Standard (smart call ditching unused enterprise features!)
• Your stack: JEE 6 app on JBoss 7.2 (CentOS 7, JDK 1.7), SQL Server 2014 STD on Windows Server 2016 (both hosted on VMware)
• When compatibility mode was set to 110 (matching SQL Server 2012), everything ran smoothly. After changing it, app-side queries slowed to a crawl, but direct database execution stays fast.

Likely Causes & Troubleshooting Steps #

1. Outdated JDBC Driver Compatibility #

JDK 1.7 pairs best with Microsoft JDBC Driver 4.1 for SQL Server—it’s built for Java 7 and fully supports SQL Server 2014. If you’re still using an older driver (like sqljdbc4.jar for Java 6), it might not handle the 2014 compatibility mode’s optimizer changes correctly.

• Check: Dig into your JBoss lib directory and verify the JDBC JAR version (look for sqljdbc41.jar as the correct match here).
• Fix: Upgrade to the matching driver version if needed—this alone often resolves cross-version compatibility quirks.

2. Stale Execution Plans & Parameter Sniffing #

When you switched compatibility modes, SQL Server’s query optimizer behavior shifted—but old execution plans cached from the 110 mode might still be in use by your app. When you run queries manually, you’re probably triggering a fresh, optimized plan for the new mode.

• Check: Run this query on SQL Server to pull cached plans for your slow query (replace your_query_fragment with a unique part of the SQL):

  SELECT cp.plan_handle, st.text, qp.query_plan
  FROM sys.dm_exec_cached_plans cp
  CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
  CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
  WHERE st.text LIKE '%your_query_fragment%'
  

  Compare the plan used by your app (tie it to your app’s connection via the plan handle) with the plan you get when running manually. If they’re different, stale cache is the culprit.
• Fix: Clear the plan cache (do this during off-peak hours to avoid disrupting users):

  DBCC FREEPROCCACHE
  

  For targeted cleanup, you can free only the problematic plan using its plan_handle:

  DBCC FREEPROCCACHE (0x06000100A27E7C1FA821B106000000000000000000000000)
  

3. Implicit Data Type Conversion (Unicode vs Non-Unicode) #

A common Java-SQL Server gotcha: if your app sends Unicode strings (nvarchar) to columns defined as varchar, SQL Server does an implicit conversion that bypasses indexes. Newer compatibility modes make this issue more noticeable because the optimizer is stricter about index usage.

• Check: Look at your JDBC connection string—do you have sendStringParametersAsUnicode=false set? By default, Java sends all string parameters as Unicode.
• Fix: Add this property to your connection string. Example:

  jdbc:sqlserver://your-db-server:1433;databaseName=your-db;sendStringParametersAsUnicode=false;
  

4. Connection Pool Session Settings #

JBoss’s connection pool might be reusing sessions with old settings that clash with the new compatibility mode. For example, session-level optimizer hints or ANSI settings could be forcing suboptimal plans.

• Check: Enable JDBC logging in JBoss to capture the full session setup and query execution flow. Look for any SET statements that might be overriding compatibility mode behavior.
• Fix: Configure your connection pool to reset session settings on each checkout, or explicitly set required ANSI/optimizer settings during connection initialization.

Quick Test to Narrow It Down #

To confirm whether the issue is in app-db communication or query optimization:

1. Capture the exact SQL (with parameter values) your app is sending (use JBoss logging or a tool like WireShark).
2. Run that exact SQL with the same parameters directly in SSMS.
   • If it’s fast: The problem lies in how the app communicates with the DB (driver, connection settings, pool).
   • If it’s slow: The issue is parameter sniffing or query optimization in the new compatibility mode.

Let me know if any of these steps fix the slowdown, or if you can share more details (like the specific slow query or JDBC driver version) and we can dig deeper!

内容的提问来源于stack exchange，提问作者RJ Director - Issac