Microsoft SQL server - Memory and CPU requirements - based on existing Database Size?

[kszemes]

Hi,

We have a customer who stores CAD files in FileVaults (more tha 8TB), so Microsoft SQL Database contains theoretically only the metadata.

The size of the SQL Database is  greater than 200GB.

There is a dedicated VM for SQL server with 128GB Memory and 8vCPU.

The customer has some performance issues with for example complex queries.

Somethimes this query runs fast - 5-10 sec - but other time tis query runs 5-10 minutes! 

An SQL expert adviced to our customer to use at least 1-1,5 times more memory for SQL Server than the existing database size.

This can be a good way to handle performance issue?

Thanks,

Károly

[rleir]

SQL Performance Tips

• understand the relational data model
• choose 'key' fields carefully, for use as indexes
• avoid subqueries
• avoid storing large BLOB data
• (others: please chip in!)

[mmeadows-3]

Most Windchill system administrators are not SQL Server certified DBAs.  Fortunately, PTC has given SQL Server a lot of attention and provided various recommendations for optimizing it.

Start with general cleanup and performance tuning recommendations.

• Using SSMS as a sysdba user,
  • Run the "Disk Usage" report to see how large and how full the datafiles are.
  • Run the "Disk Usage by Top Tables" report to identify any transient data tables that are larger than they should be. 
• Using SSMS as the db user,
  • Clean up problem tables if necessary: MiscLogEvents, PageResults, etc.
  • Confirm there are no (or few) content files in the BLOBS.
• Apply performance indexes and remove duplicates
• Perform Index Maintenance regularly to maintain performance.
• Update Statistics regularly.
• Run SQLPerf and apply recommendations
  • If you haven't performed row compression, do it.  The database size will diminish
• Size caches to the current database size
• Collect data, submit to On-Demand System Scan, and apply recommendations

If these don't help, log a performance call with PTC Technical Support.  They will want you to perform the slow running actions and send them the performance tables.

windchill wt.util.jmx.ExportPerfTables -chunkSize 20000 perftables.gz

If the system is performing well and it is just these queries that suffer...  What do you mean by 'query'?  Are these customizations or reports?

[kszemes]

Hi,

thanks for cleanup and performance tuning recommendations summary.

I will try to catch a SQL Expert and we will review these and will make a plan to execute these steps.

They have lot of Query Builder based reports.

There are some queries that can be run to reproduce the performance issue.

But there are performance issues one or two day a week when the system got stuck without running these aformentioned queries.

The CPU utilization is on 100% in every Core for a couple of hours. The memory usage is 90% by default. (All free memory is reserved by MS SQL Server - 115 GB)

Thanks!

[mmeadows-3]

Ultimately, the answer may be to throw more resources at it.  But it feels like the slow reports are a symptom of an underlying issue.

You can use JConsole (windchill wt.util.jmx.SMJconsole) to try to identify what is running during these lockups.  Select the Windchill tab to see what is running at the moment.  The ElapsedTotalCpuSeconds column may be useful.

It may not be Windchill/DB related.  For example: Antivirus scanning can consume all processors for a period of time.  If Defender antivirus and a third-party antivirus are both running, it can lock up a server for hours.

PTC Tech Supt's performance team can help direct you to the source of the issue.

[TomU]

---

@kszemes wrote:

An SQL expert advised to our customer to use at least 1-1,5 times more memory for SQL Server than the existing database size.

---

I'd be cautious trusting this expert.  SQL server can perform worse with too much memory.  Even for the largest of installations, PTC never recommends any such ratio.

1.)  SQL Server will eventually consume all of the memory made available to it.  This is by design, and why it's important to configure an upper limit based on what is actually available on the server.

• https://www.ptc.com/en/support/article/CS211385
• Memory Dangerously Low or Max Memory Too High - Brent Ozar Unlimited®

2.)  Most SQL Server installations have only a fraction of the RAM compared to their data size.  It is possible to analyze SQL Server and determine if the RAM is actually the bottleneck.

• How Much Memory is "Normal" for SQL Servers? - Brent Ozar Unlimited®
• A Sysadmin's Guide to Microsoft SQL Server Memory (brentozar.com)

[kszemes]

• min server memory set to 0.
• maxserver memory set to 115GB

[vnamboodheri]

Hello @kszemes,

It looks like you have some responses from some community members. If any of these replies helped you solve your question please mark the appropriate reply as the Accepted Solution. 

Of course, if you have more to share on your issue, please let the Community know so other community members can continue to help you.

Thanks,
Community Moderation Team.

[kszemes · Accepted solution]

Thanks for eveyone for the previous recommendations!

Meanwhile It seems the performance problems were solved with the following steps:

1. Our customer bought a brand new Hyper-V Virtualization server cluster with a new Storage with SSD disks.
   Previously the storage contaned mixed disk types: SSD and non-SSD Disks.
   There were no dedicated SSD arrays for SQL and Windchill application virtual servers.
2. After migrating the VM's (Windchill Application Server's VM and SQL Server's VM) from the old location to the new server we realized that the Windchill start time did't changed despite the new super fast storage!
   And the OS booting process take almost 4 minutes for the Windchill Application Server VM!

So we started an investigation and we realized if the File Vault VHDX disks are not attached to the virtual machine teh boot process is 4 seconds.
But after attaching even just one FileVault disk, the system slowed down terribly.
One FileVault disk size is ~ 1.5TB and contains more than 3.000.000 - 5.000.000 small files and the VHDX type was Dinamically Expanding.

So the solution was to create brand new FIXED sized VHDXs with 64KB stripe size and copy all original FileVault content to the new ones.

After this step the new VM started 4 seconds with all new FileVault disk attached to it!

3. Performance Impact of Hyper-V CPU Compatibility Mode: after the migration the local IT turned off the Hyper-V CPU Compatibility Mode. So further performance gains could be achieved.

Nowadays the Windchill Service startup time is 90 seconds vs. 210-300 seconds before migration.

We are yet to implement the SQL Server performance tuning steps described in the PTC knowledge base articles suggested by mmeadows-3 as the client is satisfied with the current performance.

[AndrewK]

@kszemes Thanks for circling back and sharing your solution with the community!