Database 101-4: Why should you care about Index Maintenance?

A continuation of the blog post Database 101-3: What is Index Maintenance?

In our last post, we said why it is extremely important to perform routine Index maintenance for your SQL databases. I thought we’d walk through a few cautionary tales of how not maintaining these indexes can impact your pharmacy. (Spoiler alert: all are less than ideal situations for your staff and customers!)

Database Best Practices

• Run daily / weekly Index Defragmentation
• Ensure proper backups / disaster recovery
• Install appropriate database integrity checks

What happens when the above isn’t executed? Here are some real-world issues that have been called in to SoftWriters, and how each situation resulted.

Issue: Over the last few months our system has slowed down.

Discovery: There was no blocking on the SQL server. Ran an index fragmentation script. Over 100 indexes were over 80% fragmented.

Solution: Ran index maintenance overnight.

Results: Improved performance speeds achieved.

Best Practice: Run index maintenance at least once per week.

Issue: No one can log in. It shows an error about something missing?!

Discovery: FrameworkLTC error that a stored procedure was missing. SQL was missing the entire FWDB; other databases were in a state of disarray. Ticketing system showed a recent upgrade from an older version that required a database migration. IT at the pharmacy had reverted from a backup of virtual machine that was mid-migration. SQL backups were being run daily, but were left on the virtual machine.

Solution: Restored the databases.

Results: The pharmacy lost three weeks of data.

Best Practice: Establish more efficient disaster recovery procedures like moving backups offsite for disaster recovery.

Issue: Complete pharmacy down!

Discovery: All users were receiving the same error. Upon checking SQL, “FWDB (Suspect)” = Data corruption

Solution: Ran index maintenance overnight

Results: Restored the database

Best Practice: Follow better database maintenance procedures such as running index maintenance at least once per week.

Up next in this series: Database 101-5: How does the processing power (CPU) and memory (RAM) affect the database performance?

Rick Magill is a Solutions Consultant at SoftWriters, a Pittsburgh-based company that offers pharmacy management software solutions to pharmacies serving the long-term care community. Rick is an integral part of our customers’ success with his forward thinking and commitment to excellence.