Re: Memory usage with linked servers

Mark (swozz_@xxxxxxxxxxx) writes:
So for linked servers, I guess that SQLServer isn't aware that it is
receiving the same information that it has already received from the
previous query and just allocates some more memory for the "new" results.

My question is "is this normal behaviour or can I somehow give SQLServer
enough information for it to determine if it has already cached the data
from the linked server in order to stop it allocating more memory".

One way to determine whether this normal, is to play with some other
provider. SQLOLEDB to an other SQL Server, or maybe the Jet provider.

I don't think SQL Server can ever rely on cached data for linked servers,
as it will not be notified when data changes. Thus, there is not really
any reason why SQL Server's own memory will grow.

BTW I am fairly sure that this isn't a memory leak in the OLEDB driver
.DLL - after SQLServer unloads the OLEDB driver .DLL the memory
allocated to SQLServer in task manager does not go down (despite the
fact that the DLL no longer exists in any process).

Windows programming is not my best game, but if you unload a DLL, does
really all memory allocated by the DLL all of a sudden get deallocated?

I don't know how much work it is to make a test shot with the provider
out of process, but that would be a good way to remove all doubts.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Assertion error when using Free (non-local pointer)
    ... Hi all, my application uses callback function in DLL, ... The DLL sniff's the network card and then allocates new data dynamically ... memory wasn't allocated inside then Assertion error occur. ...
    (microsoft.public.vc.language)
  • Re: Assertion error when using Free (non-local pointer)
    ... Sharon Gorev wrote: ... > The DLL sniff's the network card and then allocates new data dynamically ... > memory wasn't allocated inside then Assertion error occur. ... Write a function in the DLL to free the memory, and call it from the App: ...
    (microsoft.public.vc.language)
  • Re: DLL and memory allocation question
    ... > Let's say I have a dll library that allocates some memory and I am as ... you can't deallocate the memory. ... (for example, a DLL), to be deallocated by code in that same module. ...
    (microsoft.public.vc.language)
  • Re: CLR Shared Memory, C# DLL load
    ... Is there some way to know in the C# dll when the dll is loaded? ... Is the dll reloaded each tiem a method is called from SQL Server or is the DLL loaded once when the C# dll is added to sql server with CREATE ASSEMBLY? ... I wold like to be able to share memory accross calls to methods on the same C# dll is why. ... To answer the remainder of your question, whenever you call into a managed function or SP for the first time, the host loads the CLR which at his turn creates an Application Domain and loads your registered assembly into that AD. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: /MD compiler option
    ... > dll or exe) gets its own copy of the runtime. ... > module gets its own heap. ... One side effect of this is that memory allocated ... The other way is to make sure that the dll that allocates the memory also ...
    (microsoft.public.vc.mfc)