-3

I have an application that uses a T-SQL query and queries SQL Server. I just wanted to know if 2 users from 2 different machines running the application, does the SQL Server cache the results after the first user runs the application? And if so, where does the cache happen? Does it happen on the host server where the SQL Server is installed on? Or on the machine, that application is installed on?

My problem is that I have a query that runs very fast in SQL Server Management Studio, but is very slow when it gets called from the application.

Thank you!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Data 2020
  • 33
  • 3
  • SQL performance cannot for the most part be discussed in general terms. If its going slower than you require, you need to investigate, by using the profiler. – Dale K Jul 19 '23 at 01:05
  • 3
    https://www.sommarskog.se/query-plan-mysteries.html for more info. Please show us the full query, the table and index definitions, and please share the query plan via https://brentozar.com/pastetheplan. This question is not answerable otherwise – Charlieface Jul 19 '23 at 01:15
  • But what about the cache part? Where does the query result get cached? On the host server? or on the machine, that application is installed? – Data 2020 Jul 19 '23 at 01:20
  • 1
    The result only gets cached if you have setup caching somewhere (outside SQL Server). The query-plan gets cached in the server, so if you execute an identical query and the plan is still in the cache it can use that. – Dale K Jul 19 '23 at 01:22
  • 2
    Most likely what is going on here is that SSMS is getting a fast plan, the other apps are getting a slow plan. Exactly why remains to be seen, we can't tell you until we have the right info. – Charlieface Jul 19 '23 at 01:26
  • To help guide your investigations, though... plan reuse only works if 1) the submitted statements are byte-for-byte identical, including whitespace, and 2) the current `@@OPTIONS` value contains plan-affecting options, and plans will only get reused when they also have identical `@@OPTIONS`. So if you submit two identical statements but with different `@@OPTIONS` they will compile and cache two separate plans. – AlwaysLearning Jul 19 '23 at 01:55
  • Sql Server does not cache results of a query. It does cache the query plan and the data accessed. The duration of both of these caches depends on RAM and how much data is "touched". If you run identical queries (same parameters) against the same database using SSMS and your application, execution time will be similar after the first run. – mikkel Jul 19 '23 at 21:56

1 Answers1

0

A long-shot suggestion for you. Change the query in your application to end with OPTION(RECOMPILE). It may be that SQL server is hanging on to a no-longer-optimal execution plan. Worth a try.

O. Jones
  • 103,626
  • 17
  • 118
  • 172