Discrepancy in Azure SQL Server Query Performance: Index Seek in SSMS vs Index Scan in Java Application

Question

Hello,

I'm facing an issue with Azure SQL Server (PaaS) 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 (spring boot deployed over azure app service) using JDBC, the execution plan shows an "Index Scan" and the query takes significantly longer (~1 Sec) to execute.

What I've Tried:

1. Ensured that statistics are up-to-date on the tables and indexes involved.
2. Compared the execution context settings between SSMS and the Java application.
3. Added SQL into Stored prod and then it works fine, does index seek.
4. Removed plan id, cleared cache.

Question:

1. What could be causing this discrepancy in query performance?
2. Are there any debugging steps that I might have missed?

Any insights or suggestions would be greatly appreciated. Thank you!

Answer 1

Please read this article created by @Erland Sommarskog about how parameter sniffing can play a role in that scenario. I am mentioning him here in case he would like to talk to you about other possible reasons and how to deal with them.

Answer 2

Vipin Sharma Thank you for reaching out.

I agree with @Alberto Morillo . It depends on how connector is sending the query to the server, it's an index scan rather than a seek. So, the connection object, in this case JDBC being used is structuring the query in a way where the where clause is not getting optimized to use index. App service as a platform doesn't control this.

Regards,

Oury