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.
