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.
In this guide, we cover, in detail, these three factors and how to address each of them.
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.
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.
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.
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.
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:
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.
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.
About Shaun Johnson
Shaun has been using and supporting SOLIDWORKS since 2000. After spending five years in a support role, he went on to develop products and solutions in industries ranging from consumer electronics to military aviation ground support equipment. Once again in a support role, Shaun enjoys leveraging this experience to help others realize their designs and transform pixels into parts. Whenever he can, Shaun fires up his coal forge and takes a more ‘hands on’ approach to creating parts from raw steel.
Get our wide array of technical resources delivered right to your inbox.
Unsubscribe at any time.