SQLServer database maintenance
Useful scripts
Under LST\SYSTEM folder are distributed some SQL procedures to be run through SQLManagement Studio or other SQLServer tools for executing SQL code.
Currently Mechworks PDM includes:
•TSQL_Defrag_indexes.sql
defrags the database indexes; can improve database performances.
(change the database name at the beginning of the script if different from "DBWORKS")
•TSQL_compare-schema-of-two-sql-server-databases.sql
Mainly for administrators;
when a comparison is required (e.g. database replication process)
•TSQL_Find_Duplicate_Indexes.sql
Mainly for administrators;
Indexes rebuilding
A good practice of maintenance would suggest you to rebuild indexes that are heavily fragmented (30% or more).
Here is an article by Microsoft about Reorganize and Rebuild Indexes that focus on the topic.
Automatic check on database indexes fragmentation at startup for DBWArm Administrators
When MechWorks PDM starts with a DBWArm Administrator personality, MechWorks PDM checks if the main database indexes are not too much fragmented.
The check is made by running the script LST\System\CheckDatabaseIndexesFragmentation.vbs.
In case the script finds fragmentation on indexes, the following report is displayed:
Missing fields dialog for DBWArm Administrators
When MechWorks PDM starts with a DBWArm Administrator personality, the database missing fields are now displayed into a floating window:
