WorkSpace has always had a companion program called ‘Database Maintenance’ (or ‘System Utilities’ back before 2009) that is installed with it. Unfortunately, it is a widely unknown portion of the program. I am sure people see it in the Start Menu, but most people never click on it, which is unfortunate. The ‘Database Maintenance’ program should be run at least every 14 days in order to keep your server at its peak ‘Health’.
This article is meant to give you a little bit of a crash course on what exactly our ‘Database Maintenance’ does to your database.
Our Database Maintenance program has four main ‘Categories’ of routines:
- Index Defragmentation
- Log File Maintenance
- File/Data Compaction
- Integrity Check
Each of these categories can be run independently of each other using the new ‘Database & Server Health’ feature located in the ‘Help’ menu (version 2014.250 and newer).
1. Index Defragmentation:
SQL databases contain objects called ‘indexes’ that work a lot like the Dewey Decimal system of a library. Each table inside of your Office Tools Professional database contains at least one indexed column that we use when we need to search for a specific value (such as an Employee or a Work Code). Indexes store a sorted and partitioned copy of that data in an ultra-fast section of memory, so when ask SQL server for some specific data (such as ‘All time cards for employee ‘Joe Demo’), the database first scans the indexes for ‘Joe Demo’ to get all the row numbers for Joe’s time cards, then retrieves just those records for the application. Without an index, the database would have to read each and every single record in the table (in its entirety) to find all the records. A well-designed (and well maintained) index can improve the performance of a query by 1,000 fold or more. So creating and maintaining indexes are extremely important.
One of the things that happens to indexes are that they get fragmented, just like your hard disk does. So rather than the index being in one continuous part of memory, it is divided into numerous different parts (sometimes THOUSANDS of parts) and stored wherever there is space. And just like when your computer’s hard disk is fragmented, your database becomes extremely lethargic. In order to fix this fragmentation, our maintenance plan evaluates each index for it’s size, use, and fragmentation level and will either perform a INDEX_REORGANIZE, an INDEX_REBUILD_ONLINE, or even an INDEX_REBUILD_OFFLINE depending how bad the index is.
2. Log File Maintenance
SQL Databases contain a ‘Log File’ that keeps track of every single transaction that happens in the database. Using these log files, you can actually restore a database to the state it was at any point in time before. So if something bad happens to your database (either corruption, accidentally, or maliciously), you can recover your data as it was the instant before the event happened. So the Log files are extremely important parts of the system, and they require their own special maintenance as well.
Log files automatically grow as you add more and more data to your database. Each time the file grows, it creates a ‘Virtual Log File’ (VLF) inside that it uses to store data. Using these VLFs the SQL Server is able to find a single operation in the log file much, much faster than if it had to scan the entire log file. The downside to this is that if there are too many VLFs, the time it takes to insert data into the log file can actually increase, which harms performance. Part of our maintenance routine is to scan these VLFs and see if they are hurting performance. If the maintenance routine determines that they are, then we adjust several factors of the log file, including how large each ‘increment’ is when the log file grows. We can then compact these VLFs together to regain our lost performance (and without losing any log data).
3. File/Data Compaction
As you use your SQL database, the file size both grows and shrinks. Each time the file needs more space, it grows by a certain increment. And as data is added to the database (as a chunk), it is saved into any spot that has enough memory to store that chunk. Unfortunately, the data is not always stored close to other data from the same table. The data itself becomes fragmented, just like the indexes we talked about before. In order to resolve this issue, we run what is called a ‘Data Compaction’, which reorganizes the file by grouping all associated data together. This not only groups data together, but it can also free up space inside the file as well, which can then be reclaimed by the operating system as free disk space on your hard disc.
4. Integrity Check
Over time, your database will go through numerous changes. Data will be added and removed, tables will be added, modified, and removed. The database’s overall schema will change. Indexes will be added, rebuild, deleted, and re-created. Data columns will be added, modified, and removed. The database will go through thousands upon thousands of changes just as a natural course of its lifespan. But each change, no matter how small, has the potential to introduce corruption into the database. Indexes can have corrupt pages, table can have bad records, the schema can contain inaccurate references. These different types of corruption can cause anything from simple performance issues to complete schema failure and catastrophic data loss. But this is where the Integrity Check comes into play. The Database Integrity Check examines and analyzes the database in its entirety, and can detect and repair most any corruption it comes across. The database integrity check should be run on a regular, reoccurring, weekly schedule. The Integrity Check is your best weapon to prevent catastrophic data loss. WorkSpace has a built in Integrity Check that any OTP user with Admin rights can run on demand. If any corruption was detected or repaired, WorkSpace will immediately inform the user of the details, severity, and actions taken. In nearly all cases, that is where the corruption will end, and there will be no need for further action. But if you run an integrity check and WorkSpace reports that an issue was detected but was not resolved, please contact the Office Tools Technical Support team immediately. Our data specialists have decades of experience working with SQL Server databases and have been able to recover data that others though was completely irrecoverable.
So please, work with your IT Services and make sure that your SQL Server has a database maintenance routine scheduled to run every week. Doing this simple preventative maintenance can keep your database (and therefore your company) running at smoothly.