Database Maintenance and Performance

Whether you are running LIMS version 4.x with its back end Access database or version 5.x + with a SQL Server database, periodic database maintenance will help ensure optimum database performance.

Version 4.x

The overall size of your LimsData database and the rate at which the database grows can affect system performance. You can improve database performance by periodically compacting your database.

We recommend you compact LimsData at least once a month but preferably once a week. In addition to minimizing the size of the LimsData database, compacting also rewrites the database’s tables in primary key order, recreates indexes, and updates database statistics, all of which contribute to improved system performance.

Use the Compact LimsData option on the Admin menu in LIMS and follow the prompts to compact the database. Note that you need exclusive control of LimsData to compact. Use the View Current Users option on the Admin menu to ensure no others are logged on to the LIMS before compacting.

Version 5.x +

If you do not have the luxury of an IT department with database administrators overseeing your SQL Server database, there are a couple of maintenance tasks you should perform periodically. First, in SQL Server Management Studio (SSMS) you can check the integrity of the database by executing the following script:

USE [YourDatabaseName];

DBCC CHECKDB;

Put the correct database name in the USE statement. The DBCC CHECKDB command will check the integrity of the entire database. The command will list lots of data and will end with a count of allocation and consistency errors. If all is well, both will list zero errors as shown below.

Next, index fragmentation can have a significant impact on database performance. Periodically checking index fragmentation and rebuilding indexes when necessary will help maintain performance. In the File Library at LIMS.com you will find two scripts you can use in SSMS for both tasks. In SSMS, use File | Open | File… or Ctrl+O then select the script file. Make sure you add the correct database name in the USE statement at the top of each script. Use Query | Execute, F5, or the Execute toolbar button to run the script.

Use the IndexFragmentation script to view the fragmentation percent in descending order for each index in the database. You will always see fragmentation on very small indexes, for example those with fewer than 10 fragments. The screen below shows significant fragmentation on several large indexes including those on the Sample and SampleAnalysis tables, the two largest tables in most LIMS databases.

A common practice is to rebuild indexes when fragmentation exceeds 40% on larger indexes with more than 10 fragments. While we can rebuild individual indexes or all indexes on a single table, it’s easier and more practical to rebuild all indexes for a database this size. Use the IndexRebuildAll script to rebuild all indexes in the designated database. The screen excerpt below shows the results of running the script.

We recommend you always make a backup of the database before rebuilding indexes and rebuild at a time of low activity in the LIMS. Rebuilding indexes may take the index and its table offline while rebuilding. Rebuilding all indexes in most LIMS databases will take less than a minute to complete