Best Practice Information
Domain and Role/Product
<a class="s6m-tag-link sm-link" data-predicate="ds6w:what" data-tag-value="SQL Server" data-type="internal" contenteditable="false">SQL Server</a> <a class="s6m-tag-link sm-link" data-predicate="ds6w:what" data-tag-value="SOLIDWORKS PDM" data-type="internal" contenteditable="false">SOLIDWORKS PDM</a> <a class="s6m-tag-link sm-link" data-predicate="ds6w:what" data-tag-value="Performance" data-type="internal" contenteditable="false">Performance</a> <a class="s6m-tag-link sm-link" data-predicate="ds6w:what" data-tag-value="ds6wclassifier:what_BELCHAR__#ct#_Data Management & PDM" data-type="internal" contenteditable="false">Data Management & PDM</a>
What's Covered? - Jump to each section using the Contents List in the top right!
- SOLIDWORKS PDM Architecture
- Installation and Configuration
- Investigating Performance Problems
Revision History
| Rev # | Date | Description |
|---|---|---|
| 1.0 | Apr 2020 | Document updated for the latest software version. Revised for use by customers and reset as document version 1.0 |
| 2.0 | Sep 2026 | Document updated for the latest supported software versions. Several outdated chapters have been removed, consolidated, or replaced with current methods, technologies, and recommended solutions. |
| 3.0 | Nov 2026 | Document formatted for 3DSwym |
<strong>Note</strong>
All SolidPractices are written as guidelines. It is a strong recommendation to use these documents only after properly evaluating your requirements. Distribution of this document is limited to Dassault Systèmes SolidWorks employees, Partners, and customers that are on active subscription. You may not post this document on blogs or any internal or external forums without prior written authorization from Dassault Systèmes SOLIDWORKS Corporation.
This document was updated using SOLIDWORKS 2026 SP3.2. If you have questions or need assistance in understanding the content, please get in touch with your designated reseller.
Preface
This SolidPractice document describes various factors that influence the performance of Microsoft® SQL Server® when used as part of a SOLIDWORKS PDM environment. It focuses on aspects that can be influenced through the configuration, monitoring, and maintenance of SQL Server, together with methods for identifying and addressing performance-related issues.
It is difficult to define rigid rules for SQL Server performance because the appropriate configuration depends on factors such as workload, database size, hardware resources, and the wider PDM environment. However, a good understanding of the available options is helpful when defining the most appropriate strategy for specific requirements.
The expectation is that this document will be expanded and enhanced over future versions as field experience provides feedback on the areas where additional guidance is most useful.
The topics mainly cover the SQL Server Standard edition when used with SOLIDWORKS PDM Professional. When necessary, there are additional details about the SOLIDWORKS PDM Standard and the SQL Server Express and Enterprise editions.
Your Feedback Requested
We would like to hear your feedback and your suggestions for new topics. After reviewing this document, please take a few minutes to fill out a brief survey. Your feedback will help us plan and deliver content that directly addresses your challenges.
SOLIDWORKS PDM Architecture
Troubleshooting SQL Server performance is easier when you are familiar with the SOLIDWORKS Product Data Management (PDM) architecture. For example, most issues reported involve SOLIDWORKS PDM applications. If you understand how those applications work, it becomes easier to determine if a problem actually relates to SQL Server and if following the current document guidelines are appropriate.
SOLIDWORKS PDM uses a server/client application based on the SQL Server that hosts the application’s relational database. This means that SQL Server performance can influence the performance of all SOLIDWORKS PDM server services and client applications.
The main SOLIDWORKS PDM server-side components include Microsoft SQL Server, the SOLIDWORKS PDM Database Server, the SOLIDWORKS PDM Archive Server, and the SolidNetWork License (SNL) Manager Server. SOLIDWORKS PDM Professional environments can also include optional components such as the Web2 Server and Web API Server. The Web2 Server is configured using Microsoft Internet Information Services (IIS) and provides browser-based access to SOLIDWORKS PDM Professional vaults.
A SOLIDWORKS PDM setup can vary between simple configurations, as shown in Figure 1, to more complex configurations like those shown in Figure 2, where SOLIDWORKS PDM is in use with database replication and solutions such as Web2 and the EXALEAD® OnePart business discovery application. There are also cases where a production environment has additional add-ins for Enterprise Resource Planning (ERP) integration or for other third-party applications.
The main SOLIDWORKS PDM client application is SOLIDWORKS PDM File Explorer, which is built on top of Windows® Explorer. Computer-aided design (CAD) applications such as SOLIDWORKS, DraftSight®, AutoCAD®, or Inventor® have special add-ins that enable the applications to access SOLIDWORKS PDM from within a CAD session. There are other client applications for vault administration and configuration of the archive server.
This SolidPractice makes frequent reference to SOLIDWORKS PDM components without providing much detail. Therefore, having a good knowledge of SOLIDWORKS PDM architecture is helpful to having a better understanding of the guidelines in this document. To learn more about SOLIDWORKS PDM components, read the SolidPractice: “SOLIDWORKS PDM Professional Architecture,” which is available in Knowledge Base Article QA00000122037.
Additional Reading Material
Microsoft provides extensive SQL Server performance and troubleshooting documentation through Microsoft Learn and other support resources. The techniques described in this document to improve SOLIDWORKS PDM performance are based on generally accepted SQL Server best practices. However, the converse is not necessarily true, in that not all generic SQL Server performance best practices are beneficial to a SOLIDWORKS PDM system.
Installation and Configuration
Overview
SQL Server performs best when using a dedicated server computer. As a best practice, do not install other applications on the SQL Server computer. An exception to this recommendation is the SOLIDWORKS PDM Database Server Service, which typically runs more efficiently when installed on the same computer as SQL Server.
The following sections examine how the various aspects of SQL Server influence performance.
Server Sizing
SQL Server available resources can influence the performance of SOLIDWORKS PDM client applications. This is especially true over time as production SQL databases increase in size. As this occurs, adjustments to the server configuration or resources may become necessary. For implementations with tens or hundreds of active users, it is advisable to have a server setup that allows an increase to the available CPU and memory. Most of the supported virtual environments have options to adjust these resources dynamically.
Do not size a new SQL Server installation solely according to the minimum system requirements. Server sizing should take into consideration the expected SQL Server workload and concurrency, CPU utilization, memory requirements and pressure, database growth, and storage performance.
After deployment, collect a performance baseline over a representative period that includes normal and peak production workloads (see "How to make performance baselines" later in this document.) Use the collected data to determine whether adjustments to the available resources are necessary.
The recommendation is to monitor resource usage at least once every year or when there is a major change to the production environment. For example, when adding many new PDM users, when using a new third-party PDM add-in, or after activating replication for the database server or the archive server.
Operating System
Install SQL Server on a supported 64-bit Windows Server operating system. Support for Windows Server® operating system (OS) versions vary depending on which SOLIDWORKS PDM and SQL Server versions are in use. The following table depicts OS support for recent versions of SOLIDWORKS PDM.
| SOLIDWORKS Version | ||||
|---|---|---|---|---|
| Operating System Version | 2024 | 2025 | 2026 | 2027 |
| Windows Server 2025 | SP3 onwards | |||
| Windows Server 2022 | ||||
| Windows Server 2019 | ||||
With this in mind, you can determine which SQL Server platform supports your SOLIDWORKS production environment. The following table defines SQL support for recent SOLIDWORKS versions.
| SOLIDWORKS Version | |||||
|---|---|---|---|---|---|
| SQL Server Platform | 2024 | 2025 | 2026 | 2027 | |
| SQL Server 2025 | |||||
| SQL Server 2022 | |||||
| SQL Server 2019 | * | * | * | ||
| SQL Server 2017 | |||||
*SQL Server 2019 CU22 or later is required for SOLIDWORKS PDM 2024 and later.
This table shows Microsoft SQL Server operating-system compatibility. For a SOLIDWORKS PDM installation, verify that the Windows Server version is supported by both the selected SQL Server version and the applicable SOLIDWORKS PDM version.
| SQL Platform | Windows Server 2019 | Windows Server 2022 | Windows Server 2025 |
|---|---|---|---|
| SQL Server 2025 | |||
| SQL Server 2022 | |||
| SQL Server 2019 | |||
| SQL Server 2017 |
For an up-to-date list of system requirements, go to the SOLIDWORKS website, and navigate to Support > System Requirements.
Recovery Model
The recovery model of a SQL Server database determines how transactions are logged, how transaction log space is managed, and which restore options are available. New databases inherit the recovery model configured for the SQL Server model database. By default, SQL Server Standard and Enterprise editions use the Full recovery model, while SQL Server Express uses the Simple recovery model.
For a SOLIDWORKS PDM vault database hosted on SQL Server Standard, the Simple recovery model can be used when point-in-time recovery is not required. If the recovery requirements of the organisation require point-in-time restore capability, use the Full recovery model together with an appropriate transaction log backup strategy.
SOLIDWORKS PDM database replication uses SQL Server Always On Availability Groups and therefore requires the vault database to use the Full recovery model.
For more information about recovery models, see the applicable SOLIDWORKS Knowledge Base article and the Microsoft SQL Server documentation:
QA00000107569 For the Microsoft® SQL Server Recovery Model option, what is the difference between Simple Recovery and Full Recovery?
If you choose the Simple recovery model, you can restore only full or differential backups. You cannot restore backups to any specific point in time. SQL Server truncates the inactive portion of the transaction log after a checkpoint, unless another condition is preventing log truncation. The truncated log space is then available for reuse by the transaction log. You lose any modifications to the data added between the last full or differential backup and the failure.
Processor
SQL Server 2017 and later require a supported x64 Intel or AMD processor. Processor requirements for a SOLIDWORKS PDM SQL Server should be determined primarily by the expected workload and concurrency rather than database size or registered user count alone.
Use a modern multi-core processor that provides sufficient per-core performance and enough logical processors to accommodate the concurrent SQL workload. Increasing the number of processors can improve throughput where many PDM operations execute concurrently, but does not necessarily make an individual PDM operation faster.
Monitor processor utilization during representative normal and peak workloads. Short CPU spikes are normal. Sustained CPU utilization around 80–85% or higher should be investigated, particularly when it coincides with increased PDM response times.
Before increasing processor resources, verify that SQL Server is responsible for the CPU load and investigate high-CPU queries, inefficient execution plans, statistics and indexes, maintenance operations, other processes, and virtualization contention.
Adding processor resources does not provide linear performance scaling. The benefit depends on factors including workload concurrency, query parallelism, processor performance, memory and storage performance, NUMA configuration, virtualization overhead, and SQL Server edition limits.
In virtual environments, monitor both the SQL Server guest operating system and the virtualization host. CPU overcommitment or host contention can affect SQL Server performance even when the VM configuration appears to provide sufficient virtual processors.
Suggested Initial Logical CPUs
The number of logical CPUs required by the SQL Server hosting the SOLIDWORKS PDM vault database depends on the workload and should not be determined solely by the number of registered users.
The following values can be used as initial sizing guidance for a production environment. Actual CPU requirements depend on several factors. For example, the number of concurrently active users, database size and complexity, searches, BOM and reference operations, workflows, automated tasks, add-ins and API activity, database replication, and SQL Server maintenance operations.
| Concurrently Active PDM Users | Initial Logical Processor |
|---|---|
| Active users < 10 | 2 |
| 10 ≤ Active Users < 50 | 2-4 |
| 50 ≤ Active Users < 100 | 4-8 |
| 100 ≥ Active Users | 8 + and workload validation |
These values are starting points and should be validated during normal and peak workloads. Brief CPU spikes are expected; sustained utilization above approximately 80–85% should be investigated, especially if performance is affected. Before adding CPUs, confirm that SQL Server is the source of the load and rule out inefficient queries, maintenance activity, other processes, or VM resource contention. In virtual environments, also consider host CPU overcommitment and contention.
SQL Server editions CPU limitations
| SQL Server Platform | Express | Standard | Enterprise |
|---|---|---|---|
| SQL Server 2025 | 1 Socket or 4 cores | 4 sockets or 32 cores | OS Max |
| SQL Server 2022 | 1 Socket or 4 cores | 4 sockets or 24 cores | OS Max |
| SQL Server 2019 | 1 Socket or 4 cores | 4 sockets or 24 cores | OS Max |
| SQL Server 2017 | 1 Socket or 4 cores | 4 Sockets or 24 cores | OS Max |
Memory
Memory is an important SQL Server performance resource. SQL Server uses memory for purposes including caching data and index pages, caching execution plans and metadata, query execution memory grants, lock management, and other internal Database Engine operations.
The SQL Server buffer cache reduces storage I/O by retaining frequently accessed database pages in memory. When a required page is already present in memory, SQL Server can satisfy the logical read without first performing a physical read from storage. Sufficient memory can therefore significantly improve workloads that repeatedly access the same data.
Adding memory does not necessarily improve performance indefinitely. The benefit depends on the active working set and workload. SQL Server loads database pages into memory as they are accessed and does not need to cache the entire database. Therefore, total database size should not be used by itself to determine the amount of RAM required by the SQL Server.
Important:
- Follow the current SOLIDWORKS system requirements for minimum server memory. Production SQL Server systems can require significantly more memory depending on the workload.
- Size memory according to observed SQL Server workload and memory pressure rather than requiring installed RAM to equal or exceed the total database size.
- Reserve sufficient memory for the Windows operating system, PDM services, backup operations, monitoring software, antivirus software, and any other applications running on the server.
- If multiple SQL Server instances or additional applications share the same server, account for their memory requirements when configuring SQL Server.
- Allow capacity for future workload and database growth.
- For physical servers, consider future memory expansion when selecting DIMM capacity and slot usage.
RAM and Databases
Insufficient SQL Server memory can increase physical storage I/O because useful data and index pages cannot remain in the buffer cache for as long as required by the workload. Memory pressure can also affect query execution memory grants and other internal SQL Server operations.
You can check memory usage by using the PDM Status Report tool > Performance > Memory report, together with native SQL Server and Windows performance data.
, to understand the memory usage by database, by indexes, and to obtain an estimation of cache type.
This tool is available for Partners in the Knowledge Base article QA00000118777. While customers do not have direct access to this tool, Partners can share the tool with customers on a case-by-case basis.
Do not evaluate memory configuration solely by checking whether the largest or most important database consumes the most cache memory. Cache usage changes dynamically according to the active workload. Instead, determine whether the production workload is experiencing memory pressure, excessive physical reads, or contention from other databases or applications.
Sharing RAM
All databases on the server share the available RAM, and SQL Server manages which databases receive RAM. There is no way to configure one database to use more or less RAM than another database on the same server. There is also no way to specify that one database receives a higher priority than another database.
A common SQL Server configuration is to host test or development databases on the same SQL Server instance as production databases. This can affect production performance even when the test databases have relatively few users. Resource consumption depends on the workload performed by each database rather than simply on the number of users or queries.
For example, a large table or index scan against a test database can read a significant number of pages from storage into the SQL Server buffer cache. When available memory is constrained, introducing these pages can cause useful production database pages to be evicted from the cache. If production queries subsequently require the evicted pages, SQL Server must perform additional physical reads to load them from storage again.
A small number of resource-intensive test queries can therefore have a disproportionate effect on a production workload. Similar contention can occur for CPU, storage I/O, query execution memory, and other SQL Server resources.
Where workload isolation is important, avoid hosting test or other lower-priority databases on the same SQL Server instance as a performance-sensitive production SOLIDWORKS PDM database.
Limiting the memory that SQL Server can allocate
SQL Server dynamically adjusts its memory usage according to workload and available system resources. To prevent SQL Server from creating memory pressure for Windows and other applications, configure the max server memory (MB) option to reserve sufficient memory outside the SQL Server Database Engine.
The default value of max server memory (MB) is 2,147,483,647 MB. This value is effectively an unrestricted upper limit for normal server configurations and should not be interpreted as an appropriate production setting.
The max server memory setting controls the buffer pool and most memory allocations managed by the SQL Server Database Engine. Some allocations can occur outside this limit; therefore, the total memory used by the sqlservr.exe process can exceed the configured value.
Before configuring max server memory, consider the total physical RAM and the memory required by Windows, other SQL Server instances, SOLIDWORKS PDM services, backup operations, monitoring utilities, antivirus software, and other applications.
To configure Maximum server memory:
- Open SQL Server Management Studio and connect to the SQL Server instance.
- In Object Explorer, right-click the SQL Server instance and select Properties > Memory.
- Specify the required Maximum server memory (MB) value. For example, to configure a limit of 6 GB, specify 6144 MB.
The setting takes effect without restarting the SQL Server service.
The min server memory (MB) option is also available. In most SOLIDWORKS PDM environments, retain the default value of 0 unless there is a specific reason to configure it.
Maximum Buffer Pool Memory by SQL Server edition
The following memory limitations apply per SQL Server edition and version.
| SQL Platform | Express | Standard | Enterprise |
|---|---|---|---|
| SQL Server 2025 | 1410 MB | 256 GB | OS Max |
| SQL Server 2022 | 1410 MB | 128 GB | OS Max |
| SQL Server 2019 | 1410 MB | 128 GB | OS Max |
| SQL Server 2017 | 1410 MB | 128 GB | OS Max |
Storage Configuration
SQL Server can use direct-attached, SAN, shared, or software-defined storage. SAN storage can provide good performance if it delivers sufficient IOPS, throughput, and consistently low latency.
When storage is shared with other applications, monitor for contention because competing workloads can affect SOLIDWORKS PDM SQL Server performance.
RAID and other resilient storage technologies can be implemented in local or shared storage. Hardware RAID with protected write-back cache can provide good performance, but supported software-defined storage can also be suitable when correctly designed.
Regardless of the storage architecture, evaluate latency, IOPS, throughput, queueing, redundancy, and write durability.
Maximum Relational Database Size for SQL Server editions
| SQL Server Platform | Express | Standard | Enterprise |
|---|---|---|---|
| SQL 2025 | 50 GB | 524 PB | 524 PB |
| SQL 2022 | 10 GB | 524 PB | 524 PB |
| SQL 2019 | 10 GB | 524 PB | 524 PB |
| SQL 2017 | 10 GB | 524 PB | 524 PB |
Note: The preceding values are Microsoft SQL Server edition limits. SOLIDWORKS PDM Standard may have lower product-specific support limits; refer to the current SOLIDWORKS PDM system requirements.
File architecture
SQL Server databases for SOLIDWORKS PDM use the following types of physical files:
- Primary data files
Every database has one primary data file. The recommended file name extension is .MDF.
- Secondary data files
A database can also contain one or more secondary data files, typically using the .NDF extension. Data files can be logically grouped into SQL Server filegroups.
- Transaction log files
Transaction log files contain the information required by SQL Server for transaction processing and database recovery. Every database has at least one transaction log file, although additional log files can be configured. The recommended file name extension is .LDF.
By itself, SQL Server does not enforce the MDF and LDF file name extensions. However, these extensions help identify the different kinds of files and their use.
In a SOLIDWORKS PDM system, you typically find the following database files:
| Datafile Name | Logfile Name | Description |
|---|---|---|
| master.mdf | mastlog.ldf | SQL Server system database |
| model.mdf | modellog.ldf | SQL Server system database |
| MSDBData.mdf | MSDBLog.ldf | SQL Server system database |
| tempdb.mdf | templog.ldf | SQL Server temporary database |
| ConisioMasterDb.mdf | ConisioMasterDb_log.ldf | SOLIDWORKS PDM master database |
| SOLIDWORKS PDM Vault 1 database | ||
| SOLIDWORKS PDM Vault 2 database |
Creation of a SOLIDWORKS PDM vault generates the initial data and transaction log files using the configured SQL Server defaults.
For SOLIDWORKS PDM performance, the primary focus is normally on the vault database data and transaction log files and on the tempdb files. Other SQL Server system databases generally have a smaller impact on normal PDM workload performance but can still be relevant to maintenance, backup, and SQL Server Agent activity.
Optimizing Database File Size
For larger SOLIDWORKS PDM databases, size the database data and transaction log files appropriately when the database is created. Proper initial sizing reduces unnecessary file-growth events, which can temporarily affect SQL Server performance.
When configuring database files:
- Presize data and transaction log files according to the expected workload and foreseeable growth.
- Configure autogrowth as a contingency for unexpected growth rather than relying on it for routine capacity management.
- Use fixed-size autogrowth increments that are large enough to avoid frequent growth events.
- Monitor available storage and database-file growth and increase file sizes proactively where appropriate.
- If multiple data files are used in the same filegroup, configure them with appropriate and balanced sizes and growth settings.
Avoid routinely shrinking database files because repeated shrink and growth operations can increase file-system fragmentation and create unnecessary overhead.
Multiple Data Files
Additional data files can be added to a SOLIDWORKS PDM database when additional capacity, I/O throughput, or allocation scalability is required. Most databases perform well with a single data file, so additional files should normally be introduced only when there is a specific performance or storage requirement.
Alternatively, you can also use a SQL command similar to the following:
-- Create an additional data file
ALTER DATABASE [Vault_Name]
ADD FILE
(
NAME = N'Vault_Name_2',
FILENAME = N'D:\\SQLData\\Vault_Name_2.ndf',
SIZE = 1024 MB,
FILEGROWTH = 512 MB
);
GO
When multiple data files are used within the same filegroup, configure them with similar initial sizes and growth settings. SQL Server uses a proportional-fill algorithm that allocates more extents to files that have more free space. Balanced file sizes therefore help distribute allocations more evenly.
Placing data files on separate underlying storage devices can provide additional I/O throughput. However, with SAN, virtualized, or software-defined storage, separate drive letters do not necessarily represent separate physical storage, so the underlying storage architecture should be considered.
Presize the files appropriately and monitor their growth. If balanced growth across all files in a filegroup is required, SQL Server 2016 and later support the AUTOGROW_ALL_FILES filegroup option.
Understanding Storage and RAID for SQL Server
When designing a SQL Server system, the storage architecture can have a significant effect on database performance and availability. Consider the required capacity, IOPS, throughput, latency, redundancy, and write durability when selecting the storage configuration.
RAID remains one option for providing storage performance and fault tolerance, but modern SQL Server environments may also use SAN, SSD, virtualized storage, or software-defined storage. Changing the storage architecture after deployment can require significant planning and database migration.
Configure Tempdb
Tempdb is a SQL Server system database used for temporary objects, query processing, sorting, row versioning, and other internal operations. Its configuration can affect SQL Server performance when the workload makes significant use of temporary space.
Consider the following:
- Place tempdb on storage that provides adequate latency, IOPS, and throughput.
- If tempdb competes with PDM databases for storage I/O, consider placing it on separate underlying storage.
- Presize tempdb according to representative normal and peak workloads to minimize unnecessary autogrowth.
- Keep autogrowth enabled as protection against unexpected growth and use fixed-size growth increments.
- Configure multiple equally sized tempdb data files with identical growth settings.
As general guidance, use one tempdb data file per logical processor when eight or fewer logical processors are available. For systems with more than eight logical processors, start with eight files. If allocation contention remains, increase the number of files in groups of four and monitor the results.
Additional tempdb data files do not need to reside on separate physical disks unless storage I/O itself is a bottleneck.
Move tempdb files
By default, tempdb files are created in the SQL Server data location selected during installation. If the existing storage does not provide adequate performance, or if tempdb competes with other databases for storage I/O, consider moving it to another storage volume.
Use ALTER DATABASE to specify the new file locations. For example:
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'G:\\SQLData\\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'H:\\SQLData\\templog.ldf');
GO
Restart the SQL Server service to activate the new tempdb file locations. SQL Server recreates tempdb each time the service starts, so the existing files do not need to be manually moved.
Configure tempdb size and autogrowth
SQL Server uses tempdb dynamically, and its space requirements depend on the workload. If the tempdb is too small, frequent autogrowth events can introduce unnecessary overhead. There is no fixed size that is appropriate for every SOLIDWORKS PDM environment.
A practical approach is to determine the appropriate size by observing the production environment. Leave tempdb with autogrowth enabled and monitor its usage over a representative period, such as one or more weeks, including normal and peak workloads.
To review the current size and space usage in SQL Server Management Studio, right-click the tempdb database in Object Explorer and select Reports > Standard Reports > Disk Usage.
Use the maximum observed space usage of the tempdb data and log files as a starting point for determining the target size. Allow additional headroom for expected workload variations and concurrent activity to reduce the likelihood of autogrowth during normal operation.
With this target size in mind, use the ALTER DATABASE command to configure the initial size of tempdb. The following example sets the primary data file to 12 GB and the log file to 10 GB:
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'tempdev', SIZE = 12288MB);
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'templog', SIZE = 10240MB);
Regardless of the initial size selected, keep autogrowth enabled so that tempdb can accommodate unexpected increases in usage. The objective is to size tempdb so that autogrowth occurs infrequently during normal workloads rather than to disable it.
Use fixed-size autogrowth increments instead of percentage-based growth. Configure all tempdb data files with the same size and growth settings.For more information about tempdb, read the article “TempDB Database” at the following Microsoft Learn article:
Create multiple data files
Multiple tempdb data files can reduce allocation contention when many SQL Server operations use tempdb concurrently.
For new SQL Server installations, Setup automatically configures multiple tempdb data files according to the number of logical processors, up to eight files.
As initial guidance:
- For eight or fewer logical processors, configure the same number of tempdb data files as logical processors.
- For more than eight logical processors, start with eight data files.
- If allocation contention remains, increase the number of data files in groups of four and monitor the results.
- Keep all tempdb data files the same size.
- Configure identical fixed-size autogrowth settings for all data files.
- Additional data files do not need to be placed on separate physical disks unless storage I/O is itself a bottleneck.
- Normally, only one tempdb transaction log file is required.
For example, to configure four equally sized tempdb data files.
ALTER DATABASE tempdb
MODIFY FILE
(
NAME = tempdev,
SIZE = 2560MB,
FILEGROWTH = 512MB
);
GO
ALTER DATABASE tempdb
ADD FILE
(
NAME = tempdev2,
FILENAME = 'G:\\SQLData\\tempdev2.ndf',
SIZE = 2560MB,
FILEGROWTH = 512MB
);
GO
ALTER DATABASE tempdb
ADD FILE
(
NAME = tempdev3,
FILENAME = 'G:\\SQLData\\tempdev3.ndf',
SIZE = 2560MB,
FILEGROWTH = 512MB
);
GO
ALTER DATABASE tempdb
ADD FILE
(
NAME = tempdev4,
FILENAME = 'G:\\SQLData\\tempdev4.ndf',
SIZE = 2560MB,
FILEGROWTH = 512MB
);
GO
These values are examples only. Configure file sizes and growth increments according to the workload and available storage.
Database Replication
SOLIDWORKS PDM Professional database replication uses Microsoft SQL Server Always On Availability Groups (AOAG) and requires a supported SQL Server Enterprise edition.
The availability group contains one primary SQL Server replica for read and write operations and one or more secondary replicas that SOLIDWORKS PDM can use for supported read operations. All writing operations are directed to the primary replica.
The vault database must use the Full recovery model and must meet the SQL Server Always On Availability Groups requirements. Database maintenance should also be configured appropriately for an availability-group environment to minimize the impact on production operations.
For detailed information about configuring database replication, see the Knowledge Base article QA00000120772 “Using SOLIDWORKS® PDM 2017 or later, what are the recommendations to set up the Microsoft® SQL Server® Enterprise edition with ‘Always On availability groups’ (AOAG) for database replication?”
Configure SQL Services
Install and enable only the SQL Server components and services required by the production environment. Unused services consume system resources and can unnecessarily increase the system's attack surface.
- SQL Server Database Engine
Configure the Database Engine service to start automatically. - SQL Server Browser
The SQL Server Browser service helps clients locate named SQL Server instances, particularly when they use dynamic TCP ports. If the SQL Server instance uses a known static port and clients connect directly to that port, the Browser service is normally not required and can remain disabled. - SQL Server VSS Writer
The SQL Server VSS Writer service is used by backup applications that use the Windows Volume Shadow Copy Service (VSS). If no VSS-based backup or snapshot solution is used, the service can be disabled. Before disabling it, verify the requirements of any server-level or virtual-machine backup software.
- SQL Server Agent
For SQL Server Standard and Enterprise editions, configure SQL Server Agent to start automatically when scheduled SQL Server backups, maintenance tasks, monitoring, or other SQL Agent jobs are in use. SQL Server Agent is not available in SQL Server Express.
Use SQL Server Configuration Manager to configure SQL Server service startup settings. Use the Windows Services application where functionality is not exposed through SQL Server Configuration Manager, such as Windows service recovery actions.
For critical SQL Server services, consider configuring appropriate recovery actions so that Windows attempts to restart the service if it stops unexpectedly.
For SQL Server Agent, additional auto-restart options are available in SQL Server Agent Properties > General, allowing SQL Server Agent to restart the Database Engine or itself if either service stops unexpectedly.
Important note: Do not enable these SQL Server Agent auto-restart options on failover cluster instances.
Configure Error Logs
The SQL Server error log is an important source of information when troubleshooting SQL Server problems. SQL Server maintains the current ERRORLOG file and, by default, six archived error logs.
On busy systems, individual error logs can become large and difficult to review. Consider increasing the number of retained logs and recycling the log regularly.
In SQL Server Management Studio, expand Management > SQL Server Logs, right-click SQL Server Logs, and select Configure to specify the number of archived error logs to retain.
The following command creates a new SQL Server error log without restarting the SQL Server service:
EXEC sys.sp_cycle_errorlog;
If required, schedule this command as a SQL Server Agent job, for example once per day. Remember that SQL Server service restarts also create a new error log, so the number of retained files does not necessarily correspond directly to a fixed number of days.
Regular maintenance activities
Regular SQL Server maintenance helps maintain the performance, availability, and recoverability of SOLIDWORKS PDM databases.
For SQL Server Standard and Enterprise editions, many maintenance activities can be automated by using SQL Server Maintenance Plans and SQL Server Agent. SQL Server Express does not include SQL Server Agent; maintenance operations must instead be automated using scripts, Windows Task Scheduler, or another scheduling solution.
For such an example, see the Knowledge Base article QA00000121565 “How do I schedule maintenance tasks to rebuild table indexes for SOLIDWORKS® PDM Standard file vault databases that are hosted on the Microsoft® SQL Server® Express edition?”
Overview of SQL Server maintenance plans
Maintenance Plans can be created using the Maintenance Plan Wizard or the Maintenance Plan design surface in SQL Server Management Studio. They can automate database backups, integrity checks, index maintenance, statistics updates, cleanup tasks, and notifications.
For a typical SOLIDWORKS PDM production environment, consider the following maintenance activities:
- Back Up Database Task – Create full, differential, and, when using the Full recovery model, transaction log backups according to the required recovery objectives.
- Check Database Integrity Task – Run DBCC CHECKDB periodically to identify database consistency or corruption problems.
- Reorganize Index Task – As an initial schedule, consider running this task weekly for indexes that show meaningful fragmentation. Use the available fragmentation, page count, and usage filters to avoid reorganizing indexes unnecessarily.
- Rebuild Index Task – As an initial schedule, consider running this task monthly for indexes that are sufficiently fragmented and large enough to benefit from rebuilding. Schedule rebuild operations during periods of low SOLIDWORKS PDM activity because they can consume significant CPU, storage I/O, transaction log space, and temporary storage. Microsoft Maintenance Plans support filtering indexes based on characteristics such as fragmentation and page count.
- Update Statistics Task – Keep query optimizer statistics current where required. Index rebuild operations update the statistics associated with the rebuilt indexes, whereas index reorganizations do not.
- Maintenance and History Cleanup Tasks – Remove obsolete backup files, maintenance reports, and SQL Server Agent history according to the organization's retention policy.
- Notifications – Configure Database Mail and SQL Server Agent notifications so that administrators are informed when important maintenance jobs fail.
Do not use database shrinking as a routine maintenance operation. Database and log files should normally retain space that is expected to be reused.
The weekly reorganize and monthly rebuild schedules are starting points. Review the duration, resource usage, fragmentation levels, and measurable performance benefit of the maintenance tasks and adjust the schedule where necessary.
Backup and recovery
A complete SOLIDWORKS PDM backup strategy must include both the database and archive components of the vault.
Regularly back up:
- The SOLIDWORKS PDM vault databases.
- The physical file vault archives.
- The Archive Server configuration settings using Archive Server Configuration > Tools > Backup Settings.
The Archive Server configuration backup creates a backup.dat file containing Archive Server settings. It does not back up the file vault archives or SQL Server databases. SOLIDWORKS recommends scheduling this settings backup before the normal backup so that backup.dat can be included with the other backup data.
Determine database backup frequency according to the required recovery point objective (RPO) and recovery time objective (RTO). Environments using the Full recovery model should also have an appropriate transaction log backup schedule.
Where practical, follow the 3-2-1 backup rule:
- Keep at least three copies of important data, including the production copy.
- Store the copies on at least two different types of storage or independent storage systems.
- Keep at least one copy off-site or otherwise isolated from the production environment.
Do not rely on backup files stored only on the SQL Server, Archive Server, or the same underlying storage system as the production environment. A storage, hardware, security, or site-level failure could otherwise affect both the production data and its backups.
Suggested maintenance schedule
| Frequency | Suggested activity |
|---|---|
| According to the RPO | SQL database backups and, when applicable, transaction log backups |
| Regularly | Back up vault archives and Archive Server configuration settings |
| Weekly | Reorganize qualifying indexes |
| Weekly or periodically | Run database integrity checks according to database size and available maintenance window |
| Monthly | Rebuild qualifying indexes and review database/file growth |
| Periodically | Review SQL Server job failures, backup history, storage capacity, and maintenance effectiveness |
| Quarterly or according to recovery policy | Perform a test restore of the SQL database, archive files, and Archive Server configuration |
Verify backups
A backup should not be considered reliable only because the backup job completed successfully. Periodically restore recent backups in a test environment and confirm that the SQL database, file vault archives, and Archive Server configuration can be recovered together.
The SOLIDWORKS PDM database and archive contain different parts of the vault and must be protected as part of the same recovery strategy. SOLIDWORKS documentation identifies the file vault database and archive files as components of a complete vault backup.
Investigating Performance Problems
Overview
When poor performance is discovered or reported, there are several areas that you may need to investigate first:
- Check network performance (LAN & WAN).
- Check user authentication.
- Check for additional system loading such as new software or new hardware.
- Consider reproducing the issue while using SQL Server Extended Events to capture SQL traffic. Long-duration events can indicate a performance issue, but duration must be interpreted in context. Some SOLIDWORKS PDM operations, particularly searches, and retrieving file references, can legitimately take tens of seconds. Follow the Extended Events guidance later in this document.
- Rule out if the issue is SQL- related by using a half-splitting testing scenario where you test the same issue both outside and inside a PDM vault.
This document assumes that you have investigated and eliminated those other potential performance problems until only the SQL Server performance possibility remains.
Troubleshooting Methodologies
The following troubleshooting process is a guideline that you can use in conjunction with other methodologies or third-party diagnostics and troubleshooting tools.
Some of these diagnostic methods can add workload to SQL Server and are not recommended when average CPU utilization exceeds 90%. In such cases, contact SOLIDWORKS Technical Support to determine the most appropriate troubleshooting approach.
Setup check
When troubleshooting SQL Server performance, it is a strong recommendation that you start by confirming that the SQL Server and the SOLIDWORKS PDM vault have the correct configurations. In other words, determine if the configurations adhere to the main guidelines from the “Installation and Configuration” chapter.
The best practice to automate the setup check is to involve your local support and use tools such as the SOLIDWORKS PDM Status Report tool (available for Partners in the Knowledge Base article QA00000118777) that include Diagnostics and Performance reports that can help identify some of the most common SQL Server performance issues. To use the SOLIDWORKS PDM Status Report tool, connect to a vault and run one or more reports. To access additional technical details for the main reports, click the Find out more links within the application.
Collecting performance data
The preferred method of measuring SQL Server performance data is to use Microsoft solutions such as Windows Performance Monitor, or SQL Server Extended Events.
SOLIDWORKS Technical Support recommends the following three methods of data collection. There is more information about these methods later in this document:
- Windows Performance Monitor collection set – Use a small number of counters with a one-minute sampling interval for long-term monitoring.
- Extended Events for random performance indicators – Capture events such as deadlocks and blocked process reports. This can run for extended periods and may identify intermittent performance problems without requiring a broad SQL workload capture.
- SQL workload Extended Events capture – This is one of the most detailed collection methods and can help identify whether a specific SOLIDWORKS PDM operation, client computer, application, or file set is involved in a performance issue.
The following diagram explains how to use these data collection methods depending on how the issue is reproducible in a production environment.
How can you reproduce the problem?
Important:
- For reference crosschecking of SQL events, it is a best practice to collect and review the following logs with the Support team: SQL Server logs, SOLIDWORKS PDM client and service logs (COG files), and Windows Application logs (EVTX files).
- Random or intermittent problems, particularly those that coincide with elevated CPU utilization, may benefit from a targeted SQL workload Extended Events capture. A DBA or a SOLIDWORKS PDM administrator that is familiar with SQL Server Extended Events should perform this task.
- Before reporting an issue that is reproducible at any time to Technical Support, confirm that the problem is also reproducible in a test environment.
- For most performance cases, the data mentioned in this section should be enough to understand the main SQL performance issues. However, in some complex cases, Technical Support could ask to adjust the collection sets or to repeat the data collection with a different server or database configuration.
- In cases where the collected data cannot help identify the performance issue, the recommendation is to work directly with end users and review their actual complaint. This may reveal that the actual problem might not relate to only the SQL Server performance.
Analyzing performance data
Following the process in the previous section provides the main performance indicators required for SQL Server troubleshooting. Performance Monitor provides resource and counter trends, while Extended Events sessions (.xel files) provide detailed SQL execution and event information.
Correlate CPU, memory, storage, deadlocks, blocking, and query-duration information to identify whether specific applications, client computers, or SOLIDWORKS PDM operations are involved in the performance issue.
When analyzing a SQL query, it is important to identify the name of the SOLIDWORKS PDM stored procedures. In most cases, these stored procedures have a self-explanatory name. For example, XRef_GetTree means getting file references, SPA_AddDocuments means the operation to add documents, and so forth.
If a SOLIDWORKS PDM vault stored procedure appears in a long-duration SQL event, deadlock, or blocked process report, the stored procedure can help identify the SOLIDWORKS PDM operation involved and focus the investigation.
SQL queries that have stored procedures may include as parameters document IDs or user IDs, which you might use to reproduce the issue. Eventually, this should help solve the problem.
The next section covers additional information about how to analyze performance data.
Performance Data Collection Setup
SQL Server Extended Events
You can use SQL Server Extended Events to collect detailed information about database server activity and performance. For SOLIDWORKS PDM, Extended Events can help identify slow-running operations or intermittent performance incidents.
SOLIDWORKS Technical Support recommends two main types of Extended Events collection:
- SQL workload capture – Useful when reproducing an issue or when you need to understand the workload generated by SOLIDWORKS PDM operations, applications, or client computers.
- Event-specific capture – Useful for intermittent indicators such as deadlocks or blocked processes.
Extended Events sessions are available in SQL Server Management Studio.
SQL workload
SQL Server workload helps provide an understanding of the flow of queries. As an example, it can identify transactions that take a long time, consume too much CPU, or that have an unusual number of reads.
Use Extended Events sessions based on the Query Batch Tracking template that tracks all active sessions.
To configure a SQL Server workload session, follow these steps:
- Open SQL Server Management Studio.
- Connect to the production server instance.
- Expand the SQL Server instance > Management > Extended Events.
- Right-click Sessions > New Session Wizard.
- Type a new name for the session. For example, Query Batch Tracking.
- Click Next and then select the Use this event session template option.
- Select the Query Batch Tracking template, and then click Next.
- On the Capture Global Fields page, select the check boxes for the client_hostname and database_name fields.
- Click Next and proceed to the Specify Session Data Storage page. Then select the Save data to a file for later analysis option.
For the File name on server option, browse to a file location in which you wish to save the trace file.
For example, C:\\Temp\\ QueryBatchTracking.xel.
If you do not specify a file location, the trace file saves in the default location of the Log folder. For example, C:\\Program Files \\Microsoft SQL Server \\MSSQL[version].MSSQLSERVER\\MSSQL\\Log\\ QueryBatchTracking.xel. However, sometimes there is a change to the default location. Therefore, it is better to specify the location in which to save the trace file.
- Ensure that the Enable file rollover option is active and that the total stored data is approximately 500 MB. For example, Maximum file size = 100 MB and Maximum number of files is 5. A size of 500 MB is a recommendation only. You can add more storage as needed.
- Click Finish and then click Close.
- In the SQL Server Management Studio Object Explorer, expand the SQL Server instance and navigate to Management > Extended Events > Sessions.
- Right-click Query Batch (or the name you specified in step 5) > Start Session.
- After reproducing the issue or after 20 minutes when taking SQL workload samples, stop this extended session. To do this, repeat step 13, and then right-click QueryBatchTracking (or the name you specified in step 5) > Stop Session.
Important:
- All of these settings take effect immediately without stopping and restarting the server.
- After defining an Extended Events session, it is not mandatory to delete it. You can stop the session and keep it for use in the future.
- When troubleshooting a specific issue, you can use the Query Batch Tracking session example to start and stop while reproducing that specific issue. For performance cases where SQL Server CPU utilization is very high (for example > 80%-85%), the interval between starting and stopping a session should be a maximum of 5 minutes. However, if CPU usage exceeds 90%, you should not use this session.
- When sending trace files to Technical Support for analysis, the recommendation is to temporarily stop and restart the extended session. This ensures that the latest data capture writes to the Extended Events trace file. To do this, expand the SQL Server instance and navigate to Management > Extended Events > Sessions > right-click Query Batch (or the name you specified in step 5) > Stop Session. Note that if you want to continue using the session after you copy the trace file, make sure right-click the session > Start Session.
- You can also use the Query Batch Sampling template. This template captures by default only 20% of the active sessions, which means that you can run the session for intervals longer than just a few hours. However, data collected by this template is incomplete and not accurate enough. This template is mainly useful to get a general overview of the SQL workload and not to properly troubleshoot a performance issue. Therefore, avoid using this template unless SOLIDWORKS Technical Support provides explicit instruction to use it.
Deadlock reports and blocked processes reports
Use server-side Extended Events sessions because they are lighter and easy to configure to run on the server side. The following two Extended Events sessions are recommended:
Capturing locks and deadlocks using “system_health”
SQL Server enables the system_health Extended Events session by default. Among other diagnostic information, this session captures deadlock reports that can help identify locking-related performance problems.
To review the captured data:
- Open SQL Server Management Studio.
- Connect to the production SQL Server instance.
- Navigate to Management > Extended Events > Sessions > system_health.
- Make sure that the system_health session is started.
- Review the system_health event files when investigating deadlocks or other relevant SQL Server events.
The system_health session should remain enabled and running continuously because it is part of the default SQL Server diagnostic configuration.
If longer-term deadlock retention is required, consider creating a separate Extended Events session specifically for capturing deadlocks rather than modifying the built-in system_health session.
Capturing blocking locks
To capture blocking reports, follow these steps to create a new Extended Events session:
- Open SQL Server Management Studio.
- Connect to the production server instance.
- Expand the SQL Server instance and navigate to Management > Extended Events.
- Right-click Sessions > New Session Wizard.
- Type a name for the new session. For example, type BlockingLocks.
- Click Next > select the Do not use a template option > Next.
- On the Select Events to Capture page, double-click the blocked_process_report event (or press the arrow) to move the event to the Selected Events list.
- Click Next and proceed to the Specify Session Data Storage page. Then select the Save data to a file for later analysis option.
- For the File name on server option, browse to a file location in which you wish to save the trace file. For example, C:\\Temp\\ BlockingLocks.xel.
Note: If you do not specify a file location, the trace file will be saved in the default location of the ‘Log’ folder. For example, C:\\Program Files \\Microsoft SQL Server \\MSSQL[version].MSSQLSERVER\\MSSQL\\Log\\ BlockingLocks.xel. However, sometimes there is a change to the default location. Therefore, it is better to specify the location in which to save the trace file.
- Click Finish and then click Close.
- In Object Explorer, right-click the SQL Server instance > Properties > Advanced and set the Blocked Process Threshold event to 20 seconds. In test environments, you could use a value of 5 seconds instead.
- Expand the SQL Server instance and navigate to Management > Extended Events > Sessions > Blockings (or the name specified in step 5) > Start Session.
Important:
- It is a best practice to use a blocking extended session only when you require server performance analysis. If after capturing several weeks of data there are no reasons to continue capturing blocking incidents, you should stop the session and set the blocked process threshold value to 0 (zero) to disable the session. You can do this from the Advanced tab of the SQL Server instance Properties.
- Under no circumstances should the values for the blocked process threshold be lower than 5. If the value is lower than 20, the process captures more blocking events. However, for very busy SQL Servers, there may be an indication to increase the threshold to 30 seconds.
- All of these settings take effect immediately without stopping and restarting the server.
- When sending trace files to Technical Support for analysis, the recommendation is to temporarily stop and restart the extended session. This ensures that the latest data capture writes to the Extended Events trace file. To do this, expand the SQL Server instance and navigate to Management > ‘Extended Events > Sessions > right-click BlockingLocks (or the name you specified in step 5) > Stop Session. Then, right-click Start Session. If you want to continue using the session after you copy the trace file, make sure to right-click the session > Start Session.
Analyzing Extended Events data
You can open Extended Events sessions in SQL Server Management Studio for analysis.
Partners can use for Extended Events sessions the SOLIDWORKS Log Parser tool (Partner Edition), which is available in the Knowledge Base article QA00000117941. One of the advantages of using the Log Parser tool is that it can generate charts and extract a blocking chain details report. This section covers only the following main Extended Events that the Log Parser tool supports:
- For SQL workload analysis: rpc_completed and sql_batch_completed.
- For key performance indicators such as database locks, deadlocks or blocking chains: wait_info, xml_deadlock_report and blocked_process_report.
Windows Performance Monitor tool
Windows provides the Performance Monitor tool that you can use to record specific aspects (counters) of the system performance over long periods or time. You can start this tool from the command line by entering perfmon. The SQL Server installation adds other counters that are accessible with this tool.
If SQL Server counters are missing, verify that the SQL Server performance counters are correctly registered. Missing counters can result from installation, upgrade, or counter-registration issues. Use the applicable Microsoft troubleshooting guidance before considering a SQL Server Repair operation.
Monitoring performance at all times
When capturing data over long time intervals (such as several weeks), the recommendation is to avoid using a Performance Monitor data set with too many counters. For example, the counters in the following list should be enough to capture the main key performance indicators without creating significant overhead on SQL Server:
- Memory: Available Mbytes
- Processor: Processor\\% Processor Time (_Total)
- Processor: Process(sqlservr)\\% Processor Time
- SQLServer: General Statistics\\Processes Blocked
- SQLServer: General Statistics\\User Connections
- SQLServer: Locks\\Number of Deadlocks/sec
The Data Collector Properties include a specification for the sample interval. For long-term monitoring, consider using a sampling interval of one minute or longer.
Custom Performance Monitor collection data sets
For more complex performance investigations, refer to Knowledge Base article QA00000107445, which provides additional Windows and SQL Server counters suitable for targeted troubleshooting.
Select only the counters relevant to the suspected problem. Avoid collecting a large number of counters at short sampling intervals for extended periods because this can increase monitoring overhead.
The following counters are particularly useful when investigating SOLIDWORKS PDM SQL Server performance:
| Area | Performance counter | What to look for |
|---|---|---|
| CPU | Processor\\% Processor Time (_Total) | Sustained high utilization can indicate CPU pressure. Short spikes are normal. |
| Process(sqlservr)\\% Processor Time | Helps determine whether SQL Server is responsible for high CPU usage. | |
| System\\Processor Queue Length | A sustained queue together with high CPU utilization can indicate CPU contention. | |
| Memory | Memory\\Available MBytes | Monitor for sustained reductions from the normal baseline. |
| Memory\\Pages/sec | Sustained increases together with low available memory can indicate memory pressure. | |
| SQLServer:Memory Manager\\Memory Grants Pending | Sustained values above zero indicate queries waiting for execution memory. | |
| SQLServer:Buffer Manager\\Page life expectancy | Monitor changes from the normal baseline. Sustained or sudden reductions can indicate buffer-cache pressure. | |
| Storage | PhysicalDisk\\Avg. Disk sec/Read | Measures storage read latency. Sustained high values should be investigated. |
| PhysicalDisk\\Avg. Disk sec/Write | Measures storage write latency. Sustained high values should be investigated. | |
| PhysicalDisk\\Avg. Disk sec/Transfer | Provides an overall indication of storage I/O latency. | |
| PhysicalDisk\\Current Disk Queue Length | Use together with latency counters to identify possible storage contention. | |
| SQL workload | SQLServer:General Statistics\\User Connections | Helps correlate PDM user activity with resource usage. |
| SQLServer:General Statistics\\Processes Blocked | Repeated or sustained blocked processes can indicate locking or long-running transactions. | |
| SQLServer:SQL Statistics\\Batch Requests/sec | Provides a general indication of SQL Server workload activity. | |
| SQLServer:Locks\\Number of Deadlocks/sec | Repeated deadlocks should be investigated. | |
| SQLServer:Transactions\\Free Space in tempdb (KB) | Helps identify periods of high tempdb space consumption. |
Do not treat individual counter values as absolute limits. Compare counters with the normal system baseline and correlate related CPU, memory, storage, and SQL Server activity before identifying a bottleneck.
For storage investigations, focus primarily on the volumes containing the PDM database data files, transaction logs, and tempdb. In SAN, virtualized, or software-defined storage environments, also use the storage-platform monitoring tools where available.
Captured Performance Monitor logs can be opened in Performance Monitor and reviewed by selecting the relevant counters and time range.
Performance Baselines
One of the most difficult aspects of performance is trying to identify and quantify when the system is actually running “slowly,” and whether the slowness relates to server activities.
To address this, SOLIDWORKS Technical Support suggest that you monitor and record a performance baseline of the SQL Server within two weeks after deployment. The capture should include two or three representative workdays, including normal and peak workload periods. This establishes a benchmark against which you can compare any later investigations.
It is often worth waiting a few days after deployment before capturing the initial baseline so that normal production workload patterns are established and any initial configuration issues can be resolved.
You should then take equivalent measurements, perhaps every quarter. This allows you to make comparisons to the baseline measurements. It also means that you are prepared if you experience a sudden performance problem.
Measuring your system over time allows you to acquire the knowledge of what constitutes normal behavior, and what indicates an abnormal load, or degraded performance.
How to make performance baselines
The easiest way to generate performance baseline data is to run the SOLIDWORKS PDM Status Report tool once a day during working hours. To run the report, go to File > Run Diagnostics and Generate Performance Snapshot Reports.
For a more detailed baseline, collect the following data for two weeks:
- Windows Performance Monitor data as explained in the section “Monitoring Performance At All Times.”
- Key performance indicators for random performance as explained in the section “Deadlock reports and blocked processes reports.”
- Take two SQL Workload samples, each from a different weekday.
Note that a SQL Workload sample data set is more of a requirement when you find a performance issue and want a more in-depth assessment.
Schedule monitoring
Repeat the performance capture every few months and compare it to the baseline. This becomes a proactive approach to maintaining peak performance.
The SOLIDWORKS PDM Status Report tool can also be configured to generate scheduled Performance Snapshot reports using Windows Task Scheduler. These scheduled reports can be used to build a history of performance data for comparison with the baseline.
Compare performance benchmark data
Compare current performance metrics to the previous benchmark metrics and look for patterns that might indicate the source. This might lead you straight to a specific problem. However, it is often a combination of factors. Therefore, you need to investigate each of the different areas.
If you have not yet applied some recommendations from the “Installation and Configuration” chapter, when performance becomes an issue it is time to determine if it would be useful to apply any of those techniques.
Database Replication
For database replication, most of the troubleshooting guidelines still apply. However, it is a best practice to configure the recommended data collection sets for the primary site and for at least one secondary site.
Memory
Use Performance Monitor and Status Report > ‘Performance > Memory Report to look for signs of memory pressure or contention.
Consider:
- Moving applications off of the SQL Server hardware
- Disabling redundant OS services
- Disabling redundant SQL Server services
- Adding more RAM
Disk
Use Performance Monitor to look for disk hotspots.
Consider:
- Moving file locations
- Reconfiguring disks
- Adding more disks
Processor
Use Performance Monitor, Extended Events Sessions, and Status Report > Performance > CPU to see if the processors are the bottleneck.
Consider:
- Moving applications off of the SQL Server hardware
- Adding more processors
Indexes and Statistics
SQL Server uses indexes to provide fast access to information at the request of users or applications. As the table data grows or changes, the Database Engine maintains these indexes. Over time, the indexes can experience fragmentation, especially in databases like SOLIDWORKS PDM in which there is a lot of inserts, update, and delete activity. Fragmentation occurs in an index when the physical ordering on the disk does not match the logical order of the data (as defined by the index key), or when data pages that contain the index are dispersed across nonadjacent sections of the disk. Index fragmentation and low page density can increase the resources required to access and store index data and, depending on the workload, can reduce query performance. You can correct index fragmentation by reorganizing or rebuilding the index.
The easiest way to check for index fragmentation is to run Status Report > Performance > Index Fragmentation.
The Recommended operation column in the report states if a specific table or table index requires a specific maintenance task.
Reorganizing an index is an online operation and allows users to continue accessing the index while the operation is in progress. An offline index rebuild can block access to the affected index until the rebuild completes.
Where supported by the SQL Server edition and version, an index can be rebuilt online by using the ALTER INDEX ... REBUILD WITH (ONLINE = ON) option. Online rebuilds allow most concurrent access to continue, although brief blocking can still occur during some phases of the operation.
Periodically checking for index fragmentation and taking any necessary corrective action is important to maintain the performance of your SOLIDWORKS PDM system. The rate at which fragmentation occurs depends on user activity. In general, SOLIDWORKS Technical Support suggests checking index fragmentation at least monthly.
Use the following values to determine the best method to remove the fragmentation:
- Reorganize the index when index fragmentation falls between 5% to 30%.
- Rebuild the index when index fragmentation is greater than 30%.
For information about how to establish a maintenance plan that rebuilds the indexes for a SOLDIWORKS PDM database, see Knowledge Base article QA00000108683 PDM Professional, what is the best procedure to rebuild the table indexes in a file vault database using a maintenance plan in Microsoft® SQL Server®?
The purpose of the indexes is to improve the speed of reading data from the database. However, it is possible to degrade the overall performance with a poor index design because of the impact on database writing. You should not attempt to create or modify indexes manually because this can lead to performance problems or even data corruption.
Tempdb Files
Aside from the performance considerations discussed earlier, an important operational risk with tempdb is running out of available disk space.
Monitor tempdb usage and available storage periodically and adjust the configured file sizes proactively when required.
For more information, see the Microsoft Learn article Tempdb Database:
Transaction Log file
If inactive log records were never truncated from the transaction log, the log would eventually consume all of the disk space that is available to the physical log files.
Under the Simple Recovery model, log truncation automatically frees space for reuse after a checkpoint, unless another condition is preventing truncation.
If you are using the Full Recovery model, regular transaction log backups are required for normal log truncation. Other factors, such as long-running transactions, can also delay truncation, so pay extra attention to log file growth.
An excessive number of virtual log files can affect system performance. If log files grow to a large size because of many small growth increments, there can be many virtual log files. This can slow down database startup, recovery, and backup and restore operations.
The recommendation is to assign log files an initial size close to the expected requirement and to configure an appropriately large fixed growth_increment value to avoid frequent growth events.
For more information, see the Microsoft Learn article for Transaction Log:
