locked
% memory utilization of SQL Server that a BizTalk Server RRS feed

  • Question

  • Hi,

    What should be the % memory utilization of SQL Server that a BizTalk Server uses, in production, for optimum performance.

    Regards,

    Sharmishtha

    Wednesday, May 16, 2018 9:23 AM

Answers

  • Only monitoring / performance reports can give you the hard evidence you need

    Unless you have evidence to back it up, going from 4 to 8 cpus is diffcult to justify as you will have extra license costs on the SQL server

    The more memory the merrier as MSSQL will continue to load frequent used data in memory until all is used 
    Adding more memory is a low cost operation

    On a SQL Server with high CPU load but a low amount of memory can give drastic improvement in performance if you go to 32 Gb RAM or even 64 Gb
    12 Gb is really low in my opinion

    hth /Peter

    • Marked as answer by Sharmishtha Thursday, May 17, 2018 5:05 AM
    Wednesday, May 16, 2018 3:58 PM
  • There is no hard number set in stone which can be used to say that the performance is optimal.

    There can be lot of factors hampering optimal performance which can be app or resources crunch related.

    As for the evidence regarding your request, you can do following

    1) Collect the BHM reports on daily intervals check if there are any obvious bottlenecks highlighted there.

    2) You should run the Perfmon tool to capture the performance counter related to SQL server and BizTalk and then analyze them with a tool called PAL, it will help you understand if something at BizTalk level is going wrong.

    I know this will require some time, but that would be the best way in which you can collect the evidence you need.


    Mandar Dharmadhikari

    • Marked as answer by Sharmishtha Thursday, May 17, 2018 5:05 AM
    Thursday, May 17, 2018 2:49 AM
    Moderator

All replies

  • It all depends, please leave room for OS / monitoring tools requirements, set a fixed max memory allocation for this
    Failing the above can cause heavy swap file usage and thus perfomance degradation

    SQL Server should to be only service installed / running and should nor be used by other applications than the BizTalk System

    Do you have bottlenecks or monitoring which indicates issues?

    rgds/Peter


    Wednesday, May 16, 2018 1:30 PM
  • Thanks Peter.

    Currently, we have 6 BizTalk servers in 1 group and 2 sql servers ( not clustered) .

    SQL Server 12 GB RAM. I noticed the utilization to be 85%, so asked for 32 gb ram, 8 cpu. Currently, we have 4 CPU.

    What points should I give in defence for my request.

    Regards,

    Sharmishtha


    • Edited by Sharmishtha Wednesday, May 16, 2018 1:36 PM
    Wednesday, May 16, 2018 1:35 PM
  • Only monitoring / performance reports can give you the hard evidence you need

    Unless you have evidence to back it up, going from 4 to 8 cpus is diffcult to justify as you will have extra license costs on the SQL server

    The more memory the merrier as MSSQL will continue to load frequent used data in memory until all is used 
    Adding more memory is a low cost operation

    On a SQL Server with high CPU load but a low amount of memory can give drastic improvement in performance if you go to 32 Gb RAM or even 64 Gb
    12 Gb is really low in my opinion

    hth /Peter

    • Marked as answer by Sharmishtha Thursday, May 17, 2018 5:05 AM
    Wednesday, May 16, 2018 3:58 PM
  • There is no hard number set in stone which can be used to say that the performance is optimal.

    There can be lot of factors hampering optimal performance which can be app or resources crunch related.

    As for the evidence regarding your request, you can do following

    1) Collect the BHM reports on daily intervals check if there are any obvious bottlenecks highlighted there.

    2) You should run the Perfmon tool to capture the performance counter related to SQL server and BizTalk and then analyze them with a tool called PAL, it will help you understand if something at BizTalk level is going wrong.

    I know this will require some time, but that would be the best way in which you can collect the evidence you need.


    Mandar Dharmadhikari

    • Marked as answer by Sharmishtha Thursday, May 17, 2018 5:05 AM
    Thursday, May 17, 2018 2:49 AM
    Moderator
  • BHM (BizTalk Health Monitor)
    https://blogs.msdn.microsoft.com/biztalkhealthmonitor/

    PAL (Performance Analysis of Logs)
    https://archive.codeplex.com/?p=pal

    /Peter


    Thursday, May 17, 2018 7:43 AM
  • Yes. The more memory you have the better and good to use it also. Remember to set fixed max server memory and leave a few GB for the system. E g if you get 32GB, I would set max server memory to 28GB if you have 1 SQL instance. If you have two SQL instances, e g set 14GB+14GB=28GB. 
    Thursday, May 17, 2018 5:25 PM