I'm facing an issue with SQL Server query performance and I'm hoping to get some insights from you all.
Problem Statement:
I have a SQL query that performs well (~6 ms) when executed directly in SQL Server Management Studio (SSMS), showing an "Index Seek" in the execution plan. However, when the same query is executed from my Java application using JDBC, the execution plan shows an "Index Scan" and the query takes significantly longer (~1 Sec) to execute.
What I've Tried:
- Ensured that statistics are up-to-date on the tables and indexes involved.
- Compared the execution context settings between SSMS and the Java application.
- Added SQL into Stored prod and then it works fine, does index seek.
- Removed plan id, cleared cache.
Question:
- What could be causing this discrepancy in query performance?
- Are there any debugging steps that I might have missed?
Plan when this is executed from Java: https://www.brentozar.com/pastetheplan/?id=SkWWezsya
Plan when this is executed from SSMS: https://www.brentozar.com/pastetheplan/?id=HJZPefokp
Any insights or suggestions would be greatly appreciated.