1. Dedicated disk for mdf file
Objective: Improve database performance by isolating data files (mdf) on a dedicated disk.
Action: Configure the system so that mdf files are stored on a separate disk from other system files and log files. Invest in one or more high-performance hard disks dedicated to SQL Server databases. See the KB1046 article for even more information with RAID solutions.
2. Archiving mode on change of state (global to the project)
Objective: Reduce the amount of archived data by retaining only significant changes of value.
Action: Configure the archive mode so that it only saves data when value changes occur, rather than every period.
3. Limit HDS traces (especially on files as they generate continuous disk access), remove Audit traces especially
Ojective: Reduce the load on the system by limiting HDS traces and removing Audit traces that are not essential under normal circumstances. More information on traces in our KB1131 article
Action: Configure the tracing parameters to limit continuous insertions, especially for files, and disable Audit traces that are not required.
Double-click on the HDS icon in the Windows bar:
Then open “General parameters…” in the “Debug” menu:
Finally, validate with no more than the following options ticked:
4. Recovery Model in the database options to be set to “Simple”
Ojective: Simplify backup and restore management using a simple recovery model. It also reduces the size of the log file (.ldf file) to a minimum.
Action: Change the database recovery model to “Simple” to reduce the complexity and resources required for backup and restore operations. See article KB487
5. Monitor HDS system variables: if there are too many pending records on an ongoing basis, reduce the amount of data to be archived and the frequency of archiving
Ojective: Ensure optimum performance by monitoring system variables and adjusting archiving parameters accordingly.
Action: Implement continuous monitoring of HDS system variables, particularly the number of pending records. If this number becomes too high, reduce the amount of data to be archived and the frequency of archiving to lighten the load on the system. You can also archive your system variables for long-term analysis but take care to archive them in a proprietary archiving unit. For more information on system variables, see Help and the third part of article KB1131.
6. Limit the amount of memory used by SQL Server
Objective: SQL Server has the property of allocating all the necessary RAM available at a given time, thus creating a potential conflict with the needs of other applications. This operation is useful for configuring an instance of SQL Server according to the memory requirements of other applications running on the same computer. In the case of PcVue, you need to assess the memory used by sv32.exe and HDS.exe and the total memory available on your computer.
Action: Define the maximum server memory dedicated to SQL Server
Here 2GB for example (2000 MB)
7. Avoid triggering the urgent shrink
Objective: Do not block access to the database. As explained in KB876, the emergency purge, triggered by the HDS if the database reaches the maximum size threshold defined in the project, is optimised for speed and, during the cycle, the system will be very busy and access to the database may be blocked.
Action: Prioritise regularly scheduled maintenance tasks with more optimised processing so as not to block access to the SQL server by PcVue. See KB876 article.
Created on: 20 Dec 2024