We start with the deployment of our client. It’s a restaurant with an interesting workload for SQL case studies.
Their POS is Softrestaurant and it’s used to communicate in real-time with waiters, kitchen, and cash register.
- 1 SQL Enterprise Server (8 Core 8GB RAM)
- 16 Stations
- 3 Cashiers
- 7 Waiter Display
- 6 Production Monitors
- 11 Android Tablets
We had everything running smoothly until we added tablets to the environment. Suddenly, complete orders were disappearing without leaving any record in logs, and we started debugging.
Among the generic errors that appeared, one stood out:
The transaction (Process ID 71) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
We proceeded to gather some statistics. sqlfingers.com query
We found a couple of fragmented indexes so we deployed a Maintenance Plan.
The server had shared mechanical storage with other VMs. We changed storage to SSDs in Raid 5, only for production databases and Temp.
Now, after all the changes, we have zero incidents.
Leave a Reply