Linked Server Memory Allocation
Question
Friday, July 15, 2016 4:12 PM
I am trying to understand how linked server query allocates Memory.
In couple of articles, it was mentioned that Linked server does not use memory from the buffer pool.
I am trying to understand more exactly what this means?
Does this mean linked server query data is not fetched into buffer pool of the local server.
Lets take this scenerio: assume SQL 2014 is being used on both the servers.
Server A - 100GB - SQL Server is using 80GB. Windows - 20GB
Server B - 100GB - SQL Server is using 80GB. Windows - 20GB
A Linked server Query from Server A goes to Server B and fetches some data.
for argument sake, say - it is fetching 2 GB data from the linked server.
So, this 2Gb data comes from the buffer pool of the ServerB into Windows memory(not buffer pool) on ServerA.
Is this right way to interpret linked server memory allocation?
Hope it Helps!!
All replies (5)
Friday, July 15, 2016 4:50 PM ✅Answered | 1 vote
Does this mean linked server query data is not fetched into buffer pool of the local server.
Correct; how should the local SQL Server know when the data on the other SQL Server has change to refresh/drop data from the pool? The other SQL Server holds the data in buffer pool to answer queries running through the linked server.
Olaf Helper
Friday, July 15, 2016 5:00 PM ✅Answered | 1 vote
There are several concepts here.
"Linked server does not use memory from the buffer pool", is referring to the memory used to create and maintain the linked server. The point of this statement is the "Max Server Memory" option does not control linked server memory allocation.
I suggest you read:
https://mssqlwiki.com/tag/sql-server-2012-memory-architecture/
Friday, July 15, 2016 5:16 PM ✅Answered | 1 vote
Does this mean linked server query data is not fetched into buffer pool of the local server.
Yes this is definitely not going to happen for version < 2008 R2. But from 2012 since memory allocation changed its quite likely that data which is brought from other server might be kept in memory allocated from max server memory setting. I don't know exactly how it is handled neither it is documented.
Lets take this scenerio: assume SQL 2014 is being used on both the servers.
Server A - 100GB - SQL Server is using 80GB. Windows - 20GB
Server B - 100GB - SQL Server is using 80GB. Windows - 20GBA Linked server Query from Server A goes to Server B and fetches some data.
for argument sake, say - it is fetching 2 GB data from the linked server.So, this 2Gb data comes from the buffer pool of the ServerB into Windows memory(not buffer pool) on ServerA.
Is this right way to interpret linked server memory allocation?
There are 2 scenarios here if Linked server involved Microsoft OLEDB connection then memory comes from Max server memory setting. This has changed from 2012 onwards. While if it is provider like oracle, Mysql etc then memory is allocated outside buffer pool i.e windows would allocate memory.
I am not actually sure how the processing happens but if a query involves join and other operators these would be performed on destination server and data generated out of it would come via network on source server. Data wont be kept in memory which would come from max server memory setting if linked server is between two sql servers, and would be outside if linked server is between sql server and Oracle, mysql etc.
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
Friday, July 15, 2016 9:09 PM
Thank you Shashank - I just tired some testing . with linked server setup between two sql servers(SQL 2016) and there is no activity on the local sql except the linked server query - which is pretty straight forward - Select * from [linkedserver].db.dbo.test
I limited local SQL server memory to 2.5GB(local server has upto 16GB).
In the perfmon, I monitored, Memory -- AvailableMBbytes; SQL Server Memory Manager Target Server Memory;Total Server Memory; Free Memory
test 1: Linked Server set up as Server Product
While query was running (it returns 15 GB of data), I did not see any change in Total\ Targer server memory counters(they flucated a bit, probably some internal process) but AvailableMBbytes from Memory, reduced dramtically - Is this a good test to say - linked server query is pulling the data into Windows Memory Part. also, the page file size did not increase.
same behavior is observed with Linked Server set up as SQLNCl(i guess, SQL Native Client).
I am trying to set up OLEDB connections between these two sql servers and try...
Hope it Helps!!
Saturday, July 16, 2016 2:48 PM
As per what was told to be by Bob Dorr to me
Max server memory controls SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and CLR memory (basically any “clerk” as found in dm_os_memory_clerks). Memory for thread stacks, heaps, linked server providers other than SQL Server, or any memory allocated by a “non SQL Server” DLL is not controlled by max server memory.
Regarding you suggestion that windows was one providing memory, it is too early to reach conclusion. Available Bytes counter says how much memory is left for process, its server wide counter so just relying on that is not correct. I guess you need to fire some more SQL Server memory counters like PLE, database pages,
SQL Server: Buffer Manager: Database Pages
Page Life Expectancy
SQL Server: Memory Manager- Free Memory (KB)
SQL Server: Memory Manager--Database Cache Memory (KB)
SQL Server:Buffer Manager--Free Pages
SQL Server:Buffer Manager--Free List Stall/sec
**
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it