SOLIDWORKS PDM and SQL Server Performance Considerations

A well-performing system is a key factor in a productive environment. Slow performance from your SQL database or the hardware on which it is hosted, can turn what is otherwise a powerful suite of tools aimed at improving productivity into the bane of existence for any engineer who needs to interact with it.

What can affect performance in a SOLIDWORKS PDM vault database?

1. Hardware resources of SQL Server® (CPU, RAM, network bandwidth) and MS SQL settings.
2. SQL Server Recovery model and transaction log size. Simple Recovery results in a much smaller log file size and may help to reduce overall resource usage.
3. The size of tables (number of rows), data fragmentation, and the indexes quality of the SQL database for the vault.

In this guide, we cover, in detail, these three factors and how to address each of them.

Hardware Resources

More is better, right? More RAM, more storage, more power. For a good BASELINE, and some other considerations to keep in mind, have a look at the article below.

• SOLIDWORKS PDM System Resources Starting Point 

Recovery Model / Transaction Log

If you rely daily on full database backups, we recommend that that the recovery is set to Simple to limit the of the database transaction log (.LDF). Using Simple Recovery, the database can be recovered to the point of the last full or differential backup. After you set the recovery model to simple for a database, you can shrink the current transaction log file to regain space. 

Please refer to the article linked below for guidance on setting the Rcovery Model and shrinking the Transaction Log File.

• SQL Recovery Model and Transaction Log Management

Replicated databases require full recovery mode. In Microsoft® SQL Server, all newly created databases (e.g. a SOLIDWORKS PDM database) will have the recovery model option set to Full. 

With Full Recovery, the database can be recovered to the point of failure or to a specific point in time. If Full Recovery is used, then make sure the database files (MDF and LDF) are backed up on a regular schedule. The log file (LDF) can grow out of proportion compared to the database file (MDF) and may consume all the hard drive space. 

Data / Index Fragmentation

Many tables in the SOLIDWORKS PDM file vault database contain indexes to ensure data is found efficiently when lookups are done by various database operations. Over time as you perform a lot of file operations in the vault - for example after an upgrade where a lot of records may have been modified, or performed a migration where many new records were added - the various database table indexes becomes fragmented. You can update and refresh the indexes by running a maintenance plan to rebuild or reorganize the indexes. If the indexes are very fragmented, this operation may improve general performance with searching, browsing etc. in the file vault.  

SOLIDWORKS recommends rebuilding the file vault database indexes at least once a month to improve overall performance. In cases of larger databases and more activity - more frequent rebuilds such as once per week may prove beneficial. 

The article linked below covers in detail how to set up a Maintenance Task in MS SQL Server to Reorganize and Rebuild Indices.

• Enterprise PDM SQL Maintenance Plan: Reorganzing and Rebuilding Database Indexes

Shrinking Returns

A shrink maintenance task will cause index fragmentation and for this reason is not recommended to run it on a scheduled basis. However, on large databases or environments with hundreds of users, shrinking the SOLIDWORKS PDM database once per year may improve overall performance. 

Database shrink should be applied using the below main steps and is desired there are no other database connections opened when performing this task:

1. Before shrinking the database a complete database back-up should be made.
2. The target available free space for shrink should not be lower than 15%. If is already lower than 15% consider first to increase the initial size of the database.
3. A rebuild task should be applied after shrinking the database to avoid index fragmentation
4. Optional restart SQL Server once to force the update for the SQL Server buffer; especially in cases where the file size was reduced with several GBs. 

Detailed instructions on how to execute all these steps are available in SQL Server Management Studio help documentation. These instructions may be different depending on the SQL Server version used.

PDM Standard Considerations 

Please note that Microsoft SQL Express that is used for SOLIDWORKS PDM Standard databases does not have maintenance plan support. This just means that these tasks cannot be scehduled and will need to be executed manually.

We also have an article with instructions for how to automate maintenance tasks for PDM Standard using some creative scripting.

• SQL Maintenance For SOLIDWORKS PDM Standard