Maintaining and servicing databases is a complex and very time-consuming task that should be carried out by database experts. DocuWare has since provided limited support for the maintenance of the databases. This process requires a deep technical understanding of database structures. Our experience has shown that the care and maintenance of the database was not carried out, and this subsequently led to performance problems in the individual DocuWare systems. Defragmenting indexes, as well as maintaining and recreating required indexes, is a very important and difficult task that requires deep database knowledge.
Database maintenance is therefore not part of our Support Services. If you need support or assistance from DocuWare, therefore we can only offer this as a paid offer. In this case, we recommend that you contact our Professional Services department in advance of such a project by sending an email to professional.services.emea@DocuWare.com or professional.services.americas@DocuWare.com
This article describes what you can do to improve the database performance, as well, as maintaining it.
In addition to a deep DocuWare database knwoledge, we gathered the most important official Microsoft SQL Database MSDN (Microsoft Developer Network) articles that help you get started to maintain a MSSQL database correctly.
The Microsoft Developer Network is Microsoft's official knowledge base.
Best practice recommendations:
Separate your data
We recommend that you do not place the storage location for your database server on the Windows system partition. The Windows system partition (usually C:\) is already used for the swap file and other important system-relevant processes and may therefore already have a certain degree of utilization or load.
Instead, we recommend storing your databases to a separate partition. Ideally, the DocuWare databases are physically separated from other databases. This is not only recommended, providing the system with the needed performance, but also facilitates troubleshooting in case of emergency.
The following partitioning is recommended:
own physical or virtual partition for
- Databases (*.mdf files)
- Logs (*.ldf files)
- SQL Server Engine
Please make sure that the partitions are not on the same physical or virtual hard drive
Maintain your indexes:
fragmented indexes can slow down your system in the long run and slow down work processes. We recommend that you perform a monthly maintenance of these indexes to get the best possible performance. This depends on the system size, as well as the complexity of the indexes used.
Indexes should not overlap and should be unique.
A database index is an index structure in a database, separate from the data structure, that speeds up searching and sorting by specific fields.
An index consists of a collection of pointers (references) that define an ordering relation to one or more columns in a table. If an indexed column is used as a search criterion in a query, the database management system (DBMS) searches for the desired records using these pointers.
Without an index, the column would have to be searched one by one, while a search using the index will only have to look for the pointers where they are refering too.
Please review these indexes monthly to ensure that you have no overlap in your indexes, as this can negatively impact performance.
This Script is very crucial and will give you relevant information about missing indexes
Split your tempdb:
When the tempdb database is heavily used, SQL Server may experience contention when it tries to allocate pages. Depending on the degree of contention, this may cause queries and requests that involve tempdb to be briefly unresponsive.
The worst case scenario would be a long-running query that is blocking other processes.
Structure your file cabinets beforehand
we recommend that you build your file cabinets to an extent that is appropriate for your MSSQL server.
The more columns you have, the more fields are involved, the more fields are searched, the more complex and larger the database queries will be on your server.
It makes more sense to have 2 smaller file cabinets instead of a big one. Unless your database server is designed and has the performance and maintenance to handle this.
Please consider reducing the amount of total index fields, the more index fields you have, the more complex the queries will be, and the more load your SQL server will face.
Know your server:
The 3 biggest performance impacts are limited to these 3 sub-factors:
IO-Subsystem (e.G. Hard-Drives)
Microsoft recommends that a database fits at least 1 time completely in the RAM from their size.
The biggest part of a DocuWare database may be full text. If this is not used completely, this rule of thumb does not have to be observed.
The workflow history or the audit also use up space accordingly.
We do not recommend saving on hardware. Increasing RAM and CPU can solve problems in the short term and prevent failures that block your work processes.
If you encounter a high load on the CPU side, please review the current utilization and if the amount of cores is appropriate for your current used size and architecture.
Do not use MSSQL Express Edition for Productive environments that surpass our system recommendations:
MSSQL Server Express Edition has limitations when it comes to CPU and RAM utilization:
MSSQL Server 2016 Express + Other editions
MSSQL Server 2017 Express + Other editions
MSSQL Server 2019 Express + Other editions
Please also check the following official MSDN articles from microsoft, which may help you to keep the performance of your database server stable
Requirements for Using Memory-Optimized Tables and In-Memory OLTP and Memory-Optimization
Server memory configuration options
SQL Server Design Considerations