Important Note:
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 an essential and difficult task that requires in-depth database knowledge.
Database maintenance is therefore not part of our Support Services. If you need support or assistance from DocuWare, and 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 knowledge, 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 ensure that the partitions are not on the same physical or virtual hard drive
Maintain your indexes:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15
fragmented indexes can slow down your system eventually 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.
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-clustered-indexes?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-nonclustered-indexes?view=sql-server-ver15
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 referring too.
Please review these indexes monthly to ensure that you have no overlap in your indexes, as this can negatively impact performance.
sys.dm_db_missing_index_details
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-details-transact-sql?view=sql-server-ver15
This Script is very crucial and will provide you relevant information about missing indexes
Split your tempdb:
https://docs.microsoft.com/en-US/troubleshoot/sql/performance/recommendations-reduce-allocation-contention
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:
Memory (RAM)
CPU
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 number 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
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-ver15
MSSQL Server 2017 Express + Other editions
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-ver15
MSSQL Server 2019 Express + Other editions
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15
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
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/requirements-for-using-memory-optimized-tables?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/in-memory-oltp-in-memory-optimization?view=sql-server-ver15
Server memory configuration options
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver15
Monitor memory usage
https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/monitor-memory-usage?view=sql-server-ver15
SQL Server Design Considerations
https://docs.microsoft.com/en-us/system-center/scom/plan-sqlserver-design?view=sc-om-2019&viewFallbackFrom=sc-om-1711
Backup Overview (SQL Server)
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-overview-sql-server?view=sql-server-ver15