I'm running a parametrized 'SELECT' query using EF Core over a large SQL Server database. When running the exact same query (with the same parameter values) from SQL Server Management Studio (SSMS), I observe very uneven performances result.
In the most extreme cases (depending on the values of the parameters), the performance is improved by a magnitude of 2000 when executed from SSMS. For instance, the query can take 10ms when executed from SSMS, and 20 seconds from EF Core.
- I'm capturing the query generated by EF Core using SQL Server Profiler, the text is copied without any change to SSMS.
- The performances are also measured from SQL Server Profiler.
- The query has a 'TOP 10' clause so that the volume of data returned cannot affect the performances.
My guess is that SQL Server is not using the same query plan in both cases.
- What could explain such a discrepancy?
- Are there some connection options that could influence the way the query plan is selected?
- What could I do to investigate that issue more in depth?
1条答案
按热度按时间7uzetpgm1#
Based on the very valuable comments from @Sergey et @RossBush, I have been able to find the reason for that behavior:
After clearing the cache using
dbcc freesystemcache('MyDbName')and running the query from both sides with the same parameter values, I now have the exact same execution time.That issue beeing cleared, I have been able to adjust the indexes to improve the overall performances.