Configuring Windchill & SQL Server RAM Usage Optimization

[CHASEONHO]

Hi,

We have installed PDMLINK and SQL SERVER on the same server PC.

Sometimes performance is worst.
The RAM usage of SQL Server is increased but not decreased.

So we want to set the maximum RAM usage of SQL SERVER.

Is it best to give 60% of the server PC usage to PDMLINK and 20% to SQL SERVER?

We do not plan to add additional servers.

Thanks, 

Best Regards,

SeonHo Cha

[BrianToussaint]

I have my SQL usage set at 40% due to all that is running on there but at one time saw where it should be set at 50%.  One thing that helps keep SQL with low RAM usage is rebooting the computer as the RAM for SQL grows over time.  I reboot once a week.  Windchill should set the values that it needs to run on the configuration of the machine that you have.

My server has 80Gb of ram and I have 32Gb for SQL and let Windchill set it while installing.  With everything running I am at 83% of ram used.  The biggest thing for me with my last upgrade was the amount of ram that solr now takes to run versus 11.0 m030.

[TomU]

SQL server will use all of the RAM available on the system unless you limit it to something less.  This is by design and not a problem.

---

No matter how much memory you put in a system, SQL Server will use all it can get until it’s caching entire databases in memory and then some.  This isn’t an accident, and there’s a good reason for it.

---

To improve performance, SQL Server caches data in memory.  SQL Server doesn’t have a shared-disk model: only one server’s SQLserver.exe can touch the data files at any given time.  SQL Server knows that once it reads a piece of data from the drives, that data isn’t changing unless SQL Server itself needs to update it.  Data can be read into memory once and safely kept around forever.  And I do mean forever – as long as SQL Server’s up, it can keep that same data in memory.  If you have a server with enough memory to cache the entire database, SQL Server will do just that.

https://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/

This same site states: "leave 4GB or 10% of total memory free, whichever is LARGER on your instance to start with, and adjust this as needed."

Now obviously that isn't going to work if you're also trying to use this same server for other applications, like Windchill.  In that case you probably need to spend some time reading these:

• https://www.brentozar.com/archive/2013/09/how-to-prove-your-sql-server-needs-more-memory-video/
• https://www.brentozar.com/archive/2018/05/how-to-tell-if-your-sql-server-has-too-much-memory/
• https://www.brentozar.com/archive/2018/11/how-much-memory-is-normal-for-sql-servers/
• https://www.brentozar.com/blitz/max-memory/