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
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
Have a view on Remote server which gets a set of information. Have a
Have a view on prod database server which gets other set of information.
- View on prod database server, which combines the above both data.
The actual query runs of the third view and it runs as
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.