SQL Server Performance

Use of MemToLeave with Linked server

Discussion in 'Performance Tuning for DBAs' started by rick_performance, Sep 13, 2005.

  1. rick_performance New Member

    A question about the use of the MemToLeave memory space.

    Will a series of linked server (insert statements with varibales provding values not table joins) calls made from a single stored proc execution progressivly use memory from the MemToLeave area or will each call de-allocate its usage when finished. If memory isn't de-allocated then I will only be able to do so many inserts before the memory space fills within a single proc call.

    I'm trying to determine if it is more efficient to have a single call inserting into the remote table based on a fetch from a local table or a series of insert statements in the form insert into linkedserver.db.dbo.table values (@v1,@v2) where the variables are supplied by a cursor looping through the local table.

    This is a SQL server 2000 to SQL server 2000 linked server - Same collation.

    I know the OLD DB provider gets loaded into memory for a linked server call but I'm not sure about the cumulative use of memory whith multiple calls from a single running process.

    Any help appreciated.

    Rick.
  2. satya Moderator

  3. rick_performance New Member

    I read the various descriptions of the memory counters and I have to say that I couldn't work out what told me the usage of the MemToLeave area! Do you have any further pointers on this ?

    cheers
    Rick.
  4. satya Moderator

    MemToLeave- Consists of a single contiguous region of addresses within the SQL Server process space that is set aside at startup. This area is static (the size cannot change) and is left unallocated by SQL Server for the use by components in the SQL Server process space, such as extended procedures, COM/OLE Automation objects, and linked servers. Memory allocations by SQL Server larger than 8 KB are also made from the MemToLeave area.

    For the default values mentioned, the MemToLeave area evaluates to 256 MB on SQL 7.0 and 384 MB on SQL 2000. The -g startup parameter can be used to increase the external needs setting.

    SQL Server grows and shrinks its memory usage dynamically by committing and de-committing buffers from the buffers reserved at startup.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. rick_performance New Member

    Thanks, but this I already understand. I want to determine the utilisation of the MemToLeave area by my various linked server statements. I want to determine if my series of linked server calls will eat away at this fixed area of memory and potentially run out of memory resource or if it handled well and memory is released to be used by the next linked server call, thus not running out of memory.

    I want to determine if I need to separate my calls to linked servers on a batch or process level in order to keep from using all the available memory in the MemToLeave space. (See original posting)

    The code is doing a series of remote insert statements, the volume of which is due to grow when a new business unit goes live, I need to determine if the growth will cause a memory issue with the MemToLeave space.

    I can't find any obvious help on the matter of monitoring this space, hence my posting.

    Regards
    Rick.
  6. derrickleggett New Member

    Rick, have you posted this on any of the MS newsgroup websites? I'm not sure what the answer to this one. Logically, I would think you would only see one grouping of items loaded into that memory per linked server, regardless of how many connections were actively using it. I also believe only the connection and loading of the object is handled in this spece, not the data. The only way you are going to know for sure though is to get one of the engineers to answer the question. The MS newsgroups are the best place for this generally.

    Let us know what you find. It's an interesting topic.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  7. rick_performance New Member

    Thanks, I'll do that.

    As to the use of memory and the data, I believe the OLE DB provider is loaded into this space and it is the OLE DB provider which communicates with the linked server and the data is returned through the provider back to the calling client.

    I have recently received an OLEDB out of memory error from my linked server process and I believe (from a bit of a witch hunt) that it wasn't due to numerous simultaneous linked servers but rather to the volume of data being returned through the linked server (a rogue linked server based report was run whilst the normal business interface was running).

    But without kowledge of how to monitor this memory and run tests I can't be sure, though I may try to replicate the issue to atempt to prove the issue.

    Thanks again.
    Rick.
  8. derrickleggett New Member

    Did you have perfmon open during this time to see if your memory counters got messed up? If not, you might want to test the process and monitor the counters during that time. Also, make sure you have the latest MDAC installed, to minimize the chances of memory leaks from out-of-date provider components.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  9. satya Moderator

    By any means the service pack fixes should be applied to all the SQL servers that are invovled in this case.

    Can also check whether you're addressing special parameter -g when starting primary SQL server instance as this addresses the memtoleave area affectively.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page