Forum Discussion
Min and Max memory setup on SQL server
sudhanshu3992 16GB sounds too small for such environment. My developer laptop has 64GB. I would recommend also to upgrade to BizTalk 2020 from supportability perspective. I would recommend here to increase to 32GB at least and then put MAX server memory to around 26-28 GB instead. Adding a cpu core to SQL might also be a good idea as it sounds too much to be on 95-100%. With increased memory it may be less, but still sounds like you need more CPU power. To offload SQL pressure, you can change default 500ms maxreceiveinterval (polling) to something larger, e g 2000ms (2s). Try to avoid low latency polling SQL intensively if not really necessary. If needed limit that to one or few hosts only and increase the other batch hosts. For pure receive hosts they don't use dequeue polling since they get their items from other sources so you can even put more, e g 30000ms (30s).
Given your logon failures, you may actually also hit MaxConcurrentAPI NTLM netlogon limit. Search and you will find a script to measure if you need to change it (there is some potential area in 15-45s which is not really measurable here though). You can try to increase MaxConcurrentAPI to 10 or 20 on BizTalk and SQL servers (I assume domain controllers already have higher setting) to see if that helps as well, but you should reconfigure SQL to use Kerberos (cached tickets) instead so it doesn't need to go to domain controller on each new connection. BizTalk uses quite a lot of SQL connections.
Also, try to minimise or disable tracking if not needed as it will reduce SQL load as well.
Some customers have lots of host instances. You need to find good balance as this increases the number of pollings/dequeues also. It depends on your network, cpu, memory, and load. Some may be fine with 40+ BizTalk host instances if having really powerful lots of cpu/128GB SQL server, others it is better with 10-20. But as mentioned, you can also increase polling interval in host settings as a balance here.
You can also increase large message size limits in BizTalk group settings. Then your messages are kept larger and not split into lots of small sub-transactions (parts). If BizTalk server has enough memory this may be good if you process larger messages than 100KB. This can have good effect on SQL and general performance as well.