Reset Search
 

 

Article

Optimizing your SQL Database for Best Performance

« Go Back

Information

 
Date4/19/2017
TitleOptimizing your SQL Database for Best Performance
SummaryThe following article is designed to walk the user through some best practices for optimizing your SQL database and its performance. Shrinking the database releases unused space and reduces the size of the database. Changing the recovery mode to Simple will automatically reclaim the SQL transaction log space to keep space requirements small. The SQL transaction log of every change can easily grow to be larger than the actual data. Re-indexing the database, another SQL tool, makes finding records quicker and easier. Periodic cleanup of the MMS Login Log and the student Edit Logging will help keep these tables from becoming too large. Deleting obsolete Scheduling Checkpoint tables and Scheduling Audit Trail tables will reduce the size of the database.
Summary2
Prior To Implementation

You will need to backup your database before making any changes to it. The following Knowledge Based Article will walk you through this:
     Backing Up the SQL Database

Resolution
  1. Make sure the database is set to Recovery model Simple:
  1. Log into the SQL Server Management Studio.

  2. Expand the Databases list.

  3. Right click on your MMS Database and select Properties.

User-added image
  1. Click on Options.
User-added image
  1. Change the Recovery model from Full to Simple.

  2. Click on OK.  This operation can take anywhere from 10 minutes to several hours depending on your database Transaction Log file size (*_LOG.LDF or *_1.LDF).

  3. Best practice: Repeat for the user database, usually named MMSUSERS.

  1. To shrink the database

  1. Log into the SQL Server Management Studio.

  2. Expand the Databases list.

  3. Right click on your MMS Database and select Tasks > Shrink > Files.

  4. File type is 'Data' by default.

  5. A properties display will give you the Currently allocated space for the file and how much Available free space this file uses.

User-added image
  1. Click OK. This operation can take anywhere from a few minutes to several hours depending on the file type size.

  2. Repeat for File type of 'Log', which generally takes a shorter amount of time.

  3. Best practice: Repeat for the user database, usually named MMSUSERS.

  1. CR recommends scheduling nightly maintenance that includes backup, shrink and re-index operations. The following Knowledge Based Article will walk you through this: Scheduling SQL Backups

Adding Rebuild Index to the maintenance list.

  1. Additional cleanup that can improve performance.
  1. The Login Log records every login attempt, successful or failed, into the MMS Web Portals and into MMS Generations. The following Knowledge Based Article will walk you through removing older records:How To Purge the Login Log from the MMS Web Portals.

  1. The MMS Generations Edit Logging table tracks the changes to student information. This table can easily grow to over a million records. The following Knowledge Based Article includes steps to clear the log:  Use and Maintenance of Edit Logging.

  1. Scheduling Checkpoint files are a copy of the current state of the entire scheduling data. This includes student schedules, departments, rooms, teachers, constraints, and sections, for that exact point in the scheduling process. Once the new school year has started, these files should be removed. The following Knowledge Based Article includes steps to delete checkpoints no longer needed: Managing Checkpoints  

  2. The MMS Generations Audit Trail tables are saved copies of the Student Scheduling and Student Study Hall tables that allow you to compare where you are now to where you were at the time the tables were created to produce reports of student scheduling changes. The following Knowledge Based Article includes steps to delete audit trail tables no longer needed: Tracking Student Schedule Changes

Additional Information

CR also offers the above services for our self hosted clients. If you would like to have your database optimized by CR services professionals, please contact your local account representative or salesperson at CR contact us.
 
MMS Online Customers: we handle the technical issues for you.
 
Additional KBAs are available at CR Knowledge Base.
Additional Tech Bytes are available at CR Tech Bytes.
Also look at the Microsoft help website for information on Recovery model, shrink and maintenance plans.

Applies to VersionsMMS Generations: All Versions.
Key WordsSQL, Database, Backup, Recovery Mode, Maintenance Plans, Reindex, Shrink, Log, LDF, MDF, Audit Trail, Edit Logging, Purge, Login Log

Feedback

 

Was this article helpful?


   

Feedback

Please tell us how we can make this article more useful.

Characters Remaining: 255