I have linked server query which runs of a view created in the remote server. Whenever i run the query i can see that a Prepared plan gets created in the plancache. But that gets removed from the plan cache in few minutes. I know that when there is memory pressure,unused plans,Adhoc plans gets removed from the plan cache. I also checked for other users or other operations(no sql cong chnage or DBCC FREEPROCCACHE) which can clear plan cache.No operations are going on.

My question is , Is there any way that this periodic query plan clearing is related to the fact that the query is running of from the linked server.?

Running on: SQL Server 2016 Standard 96 GB allocated to SQL Server out of 124 GB

Additional Info:

  1. Have a view on Remote server which gets a set of information. Have a

  2. Have a view on prod database server which gets other set of information.

  3. View on prod database server, which combines the above both data.

The actual query runs of the third view and it runs as exec sp_executesql

Don't know why the question is down voted.I have done my research on why plans get cleared from plan cache.I am trying to understand if there is anything more than the usual reason as there is linked server also involved.

  • Hi, could you add the query used (or a close example) to the question? – Randi Vertongen Sep 9 '19 at 14:35
  • 2
    Why don't you run EXEC linkedserver.db.sys.sp_executesql 'Query against the view'; so that the plan is not your local instance's concern? Why do you care if the plan gets cached locally or not? Is compile time high? – Aaron Bertrand Sep 9 '19 at 14:37
  • @AaronBertrand : Yes the compile time is huge as the query is pretty complex.Yeah i run as exec sp_executesql.Please see the added info to the question. – user9516827 Sep 9 '19 at 14:47
  • @AaronBertrand :When i do a select to the view in remote server ,i see that, in the plan the most of the work is being done in remote server as it shows as Remote Query.So locally i am just doing the sorting(or doing some other operations).But atleast i want that plan to stay in the plan cache. – user9516827 Sep 9 '19 at 15:08

Are you running sp_executesql as a parameterised call or are there values being passed in each time that slightly change the text?
You might be seeing the effect of parameter sniffing being used that might generate different plans against each call.
See https://www.scarydba.com/2013/09/05/sp_executesql-parameters-and-parameter-sniffing/
As a result it may be that the plans are being generated, just not often re-used?
(I assume you are parameterising the sp_executesql calls as the match for the plan is based on the text hash and any differences will cause a new entry generated in the plan cache)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.