none
Transactional Replication - Distributor

    Question

  • Hello:

    I have SQL Server 2008 R2 Transactional Replication setup & i am seeing that Distributor database is consuming 60% of the MAX memory.

    Distributor is on dedicated server and i am not seeing any documentation where it talks more about memory usage by Distributor. even though it is just like any other database but still from replication standpoint, why it is using 60% of SQL Server MAX memory.

    Any links or info is greatly appreciated.

    Thanking in advance.

    Thanks

    Jay

    Wednesday, October 18, 2017 11:32 PM

All replies

  • Hi Jay,

    >> even though it is just like any other database but still from replication standpoint, why it is using 60% of SQL Server MAX memory.

    Did you mean the distribution database buffer pool usage? If so, it might not be a problem, perhaps your distribution database is a large one. BTW, what is the size of your distribution database and how much memory is allocated to SQL Server on your machine?

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 19, 2017 9:45 AM
  • How did you determine the distribution database is using 60% of the max memory?

    SQL Server is designed to use 100% of available resources all the time.  If this server is dedicated to distributor, it is normal and expected behavior for it to use 100% of the server resources.

    Thursday, October 19, 2017 11:30 AM
  • Thank you for your reply. Distributor database size is 98GB. but still it doesn't explain that why it is taking 65% of SQL Server MAX memory.

    I want to understand how Distributor database make use of memory & what exactly it is done.

    Thanks

    Thursday, October 19, 2017 1:40 PM
  • Unless you configure SQL Server for max memory to cap the amount of memory sql server will use, it will take all available memory. I would check to see what sp_configure 'max server memory (MB)' returns to see if that corresponds with the 65 Gigs or so. Secondly the distributor is normally IO bound. Given a choice on what to invest in to improve distributor performance I normally get the most performance boost from investing in the IO subsystem. Do a wait stat analysis to determine what your distributor is waiting on, or what the bottleneck is and address the component which is being stressed accordingly. SQL Memory would be consumed by execution plans for the replication stored procedures. You can look in the cache using some of the DMVs to determine which
    Thursday, October 19, 2017 2:03 PM
    Moderator
  • Thank you Hilary for your reply.

    MAX SQL Server memory is 110GB.

    in last 10 mins i am seeing last wait type on SOS_Scheduler & WriteLog with Wait_Time of 4 to 9 ms.

    store proc i am seeing is add_replcmd, delete, cleanup standard ones.

    From yesterday, i am seeing memory consumed by Distributor is down from 65GB to 42GB.

    Thank you again.

    Thanks


    • Edited by aaditya2000 Thursday, October 19, 2017 2:26 PM
    Thursday, October 19, 2017 2:24 PM
  • I am sorry, I misread your post - I thought you had 98 Gigs of Ram, but your distributor database size is 98 gigs. Is your max memory setting configured appropriately for the physical memory on your server?

    Secondly WriteLog wait stats could be related to a slow disk subsystem handling your t logs. It could also be related to committing transactions - which could be normal.

    I would not be concerned with anything I see so far.

    Is there a problem which is making you focus on distributor performance?

    Thursday, October 19, 2017 2:28 PM
    Moderator
  • The only concern is raised that Server has reached 95% of the memory and distributor database is using most of the memory. i am still trying to understand that why distribution database requires or took that much memory. its get data from PUB and Writes to SUB, so pretty much all about IOs.

    So why accumulating big chunk of memory?

    Thanks

    Thursday, October 19, 2017 3:06 PM
  • It is likely all the execution plans for all the stored procedures it executes. Is there a performance problem you are trying to address? Having SQL Server using up all the available memory is not normally a concern.
    Thursday, October 19, 2017 3:10 PM
    Moderator
  • Thank you for your reply Hilary.

    SQL Server using up all memory and Distributor database using most of the memory will be my concern.

    besides store procedures for replcmd, repltran, then delete job, cleanup job etc.. why distributor database will take 65GB of memory?

    The only way i think that it is using most memory because it reads from CMD & TRAN tables before sending to Subscriber?

    Thanks

    Thursday, October 19, 2017 4:10 PM
  • SQL Server does not have any special treatment for the distributor database, vs any other database. 

    If that is the only database with any usage, that is completely normal and expected for it to be using the bullk of the resources.

    Friday, October 20, 2017 1:46 PM
  • You will need to look inside the sql server cache to find out what is consuming all of the space. The below link will be helpful.

    https://www.mssqltips.com/sqlservertip/1661/analyzing-the-sql-server-plan-cache/

    Note that the immediate sync option will consume more storage in your database and will result in higher cpu consumption. This does not necessarily translate into high memory consumption, but it might.

    Friday, October 20, 2017 2:01 PM
    Moderator