Min and Max memory setup on SQL server

%3CLINGO-SUB%20id%3D%22lingo-sub-1729443%22%20slang%3D%22en-US%22%3EMin%20and%20Max%20memory%20setup%20on%20SQL%20server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1729443%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20are%20running%20a%20BT%202010%20environment%20which%20processes%20over%20150%2C000%20messages%20per%20day%20during%20the%20peak%20business%20hours.%20We%20have%20an%20SQL%20Server%20instance%20running%20on%20a%20separate%20Windows%20server%202008%20R2%20instance.%3C%2FP%3E%3CP%3EFor%20past%20few%20weeks%2C%20we%20are%20experiencing%20intermittent%20outages%20and%20the%20error%20logged%20on%20BizTalk%20APP%20server%20says%20%22Login%20is%20from%20an%20untrusted%20domain.....%22.%20upon%20checking%20the%20logs%20on%20database%20server%20we%20have%20NETLOGON%20errors%20(unable%20to%20contact%20AD%20for%20credential%20validation)%20due%20to%20%22Unavailable%20Memory%22.%20CPU%20Utilization%20and%20Memory%20utilization%20is%2095-100%25%20on%20SQL%20server%20most%20of%20the%20times.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFollowing%20are%20the%20settings%20on%20SQL%20server%20-%20RAM%3A%2016%20GB%2C%20SQL%20Server%20Min%20and%20Max%20memory%20Allocation%20is%2012%20GB.%20ENTSSO%20is%20also%20running%20on%20the%20same%20SQL%20server.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20suggest%20any%20change%20in%20setting%20on%20SQL%20server%20which%20can%20reduce%20High%20Utilization%20and%20free%20up%20some%20memory%20for%20background%20windows%20processes%20and%20ENTSSO.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1729443%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBizTalk%202010%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMemory%20Issues%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPerformance%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esql%20server%202008%20r2%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1786397%22%20slang%3D%22en-US%22%3ERe%3A%20Min%20and%20Max%20memory%20setup%20on%20SQL%20server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1786397%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F815019%22%20target%3D%22_blank%22%3E%40sudhanshu3992%3C%2FA%3E%26nbsp%3B16GB%20sounds%20too%20small%20for%20such%20environment.%20My%20developer%20laptop%20has%2064GB.%20I%20would%20recommend%20also%20to%20upgrade%20to%20BizTalk%202020%20from%20supportability%20perspective.%20I%20would%20recommend%20here%20to%20increase%20to%2032GB%20at%20least%20and%20then%20put%20MAX%20server%20memory%20to%20around%2026-28%20GB%20instead.%20Adding%20a%20cpu%20core%20to%20SQL%20might%20also%20be%20a%20good%20idea%20as%20it%20sounds%20too%20much%20to%20be%20on%2095-100%25.%20With%20increased%20memory%20it%20may%20be%20less%2C%20but%20still%20sounds%20like%20you%20need%20more%20CPU%20power.%20To%20offload%20SQL%20pressure%2C%20you%20can%20change%20default%20500ms%20maxreceiveinterval%20(polling)%20to%20something%20larger%2C%20e%20g%202000ms%20(2s).%20Try%20to%20avoid%20low%20latency%20polling%20SQL%20intensively%20if%20not%20really%20necessary.%20If%20needed%20limit%20that%20to%20one%20or%20few%20hosts%20only%20and%20increase%20the%20other%20batch%20hosts.%20For%20pure%20receive%20hosts%20they%20don't%20use%20dequeue%20polling%20since%20they%20get%20their%20items%20from%20other%20sources%20so%20you%20can%20even%20put%20more%2C%20e%20g%2030000ms%20(30s).%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGiven%20your%20logon%20failures%2C%20you%20may%20actually%20also%20hit%20MaxConcurrentAPI%20NTLM%20netlogon%20limit.%20Search%20and%20you%20will%20find%20a%20script%20to%20measure%20if%20you%20need%20to%20change%20it%20(there%20is%20some%20potential%20area%20in%2015-45s%20which%20is%20not%20really%20measurable%20here%20though).%20You%20can%20try%20to%20increase%20MaxConcurrentAPI%20to%2010%20or%2020%20on%20BizTalk%20and%20SQL%20servers%20(I%20assume%20domain%20controllers%20already%20have%20higher%20setting)%20to%20see%20if%20that%20helps%20as%20well%2C%20but%20you%20should%20reconfigure%20SQL%20to%20use%20Kerberos%20(cached%20tickets)%20instead%20so%20it%20doesn't%20need%20to%20go%20to%20domain%20controller%20on%20each%20new%20connection.%20BizTalk%20uses%20quite%20a%20lot%20of%20SQL%20connections.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlso%2C%20try%20to%20minimise%20or%20disable%20tracking%20if%20not%20needed%20as%20it%20will%20reduce%20SQL%20load%20as%20well.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESome%20customers%20have%20lots%20of%20host%20instances.%20You%20need%20to%20find%20good%20balance%20as%20this%20increases%20the%20number%20of%20pollings%2Fdequeues%20also.%20It%20depends%20on%20your%20network%2C%20cpu%2C%20memory%2C%20and%20load.%20Some%20may%20be%20fine%20with%2040%2B%20BizTalk%20host%20instances%20if%20having%20really%20powerful%20lots%20of%20cpu%2F128GB%20SQL%20server%2C%20others%20it%20is%20better%20with%2010-20.%20But%20as%20mentioned%2C%20you%20can%20also%20increase%20polling%20interval%20in%20host%20settings%20as%20a%20balance%20here.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20also%20increase%20large%20message%20size%20limits%20in%20BizTalk%20group%20settings.%20Then%20your%20messages%20are%20kept%20larger%20and%20not%20split%20into%20lots%20of%20small%20sub-transactions%20(parts).%20If%20BizTalk%20server%20has%20enough%20memory%20this%20may%20be%20good%20if%20you%20process%20larger%20messages%20than%20100KB.%20This%20can%20have%20good%20effect%20on%20SQL%20and%20general%20performance%20as%20well.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

We are running a BT 2010 environment which processes over 150,000 messages per day during the peak business hours. We have an SQL Server instance running on a separate Windows server 2008 R2 instance.

For past few weeks, we are experiencing intermittent outages and the error logged on BizTalk APP server says "Login is from an untrusted domain.....". upon checking the logs on database server we have NETLOGON errors (unable to contact AD for credential validation) due to "Unavailable Memory". CPU Utilization and Memory utilization is 95-100% on SQL server most of the times. 

 

Following are the settings on SQL server - RAM: 16 GB, SQL Server Min and Max memory Allocation is 12 GB. ENTSSO is also running on the same SQL server.

 

Please suggest any change in setting on SQL server which can reduce High Utilization and free up some memory for background windows processes and ENTSSO.

1 Reply

@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.