Monday, 11 July 2016

Database Maintenance

Hi Everyone,

Today i want to share with you some ideas of how you should take care of you database. Database is really highly loaded system: you doing a billions of operations,creating of millions of records every day/week/month. And all of these things should go together with highest security, performance and redundancy.
To ensure a health of your database you should take case of it regularly, the same like you do with your own health. Otherwise, some day you will see something like this:

This really means that something wrong happened with your database. It might be hardware, software, network or other failure.

To ensure you database health, just schedule a regular maintenance plan and let system exceute it every week.

To Schedule the Weekly Maintenance Plan by using the Maintenance Wizard in SQL Server Management Studio, schedule the following tasks:

  • Database Integrity Check: Checks the logical and physical integrity of the database. Here you can run various of operations such as:
    • DBCC CHECKCATALOG (DBNAME)
    • DBCC CHECKDB (DBNAME)
  • Rebuild Index: Helps remove gaps in data pages and eases the data retrieval process 
    • DBCC DBREINDEX
  • Shrink Database: Removes space by moving pages from the end of the file to the front, and then deallocates the excess space back to the file system
  • Update Statistics: Internally updates statistical information about tables and indexes used by SQL Query Optimizer during data retrieval requests
  • Recompile stored procedures
You can find more information on creating maintenance plans and other advises in this and this articles from Microsoft. 
An example of the recommended maintenance plans you can see in the following screenshot

Also I highly recommend you to establish some monitoring procedure. This may significantly simplify control, as you will be automatically notified by monitoring system about some performance or other degradation.
For example in Acumatica we are using PRTG Nework Monitor tool to keep eaу on database server and all databases. This tool will automatically send notification to all admins, if there will be any abnormal speed or reply.
In general this tool can save a lot of your time and make you customer happier.

Have a nice deployment!

No comments: