Too many times when things don’t work the way we want them to we “Fix the Symptoms, not the cause”. Usually the symptom is slow performance. Today I’ll share a few war stories.
One application I designed was used to manage funds and the fund distributions. The database was nicely normalized. I had a user that wanted to see everything in one query, and created a view based on nearly every table in the database. They would call me nearly daily saying that the system had quit working; users were not able to modify data. Of course the query open locking every table in the entire database.
Originally the solution was to cancel the query and wait until users weren’t performing data entry anymore. In this case the solution was to create a data mart containing the results of the query and populate it nightly with the new activity during the day. The user could then run their query against the data mart without contention with other work.
Another application was experiencing poor performance due to a bad schema design. To fix the symptoms views were created with a better schema, based on the old table structure. Then the views were indexed to persist the data in a better form.
Of course, the better solution would have been to create the correct schema and use it instead.
Another system used SQL Server as a data store exactly as it came out of the box. Auto create statistics and auto update statistics were both turned on. No index defragmenting was executed. As the data grew performance got slower and slower.
To fix the slow performance a bigger machine with more cores and more memory was purchased resulting in improved performance. At least it ran better for a period of time.
When they finally got to the root cause they found they were able to work quite well on the original server simply by updating statistics and re-organizing indexes overnight. Additionally they added transaction log backups because the database could not run in simple recovery mode. Disk space for the data files and the log files was pre-allocated. These are all easy things to do, and sadly, cost much less than fixing the symptoms.
Why not share your war stories? Leave out the identifying details so you don’t get in trouble, and leave your comment here or drop an email to btaylor@sswug.org.
Cheers,
Ben