Is The Database A Poor Step-Child?
Databases are so important to us, in many ways, they are the number one dependency we deal with in ColdFusion and yet, in many ways, we treat them as ignored step-children and I fear the advent of Hibernate ORM in CF 9 will make matters even worse. If client variables are in use, this becomes even more critical, tuning the database will always certainly yield more benefits than any amount of JVM tuning. By tuning I mean looking at indexing, affinities, CPU allocation, hard-drive layout, etc. All of theses things can yield immediate benefits or cause immediate pain if they are not right.
Typically, when we get new clients we try to persuade them to spend some time on our database review and report offering but most of the time they decline, wanting to focus all time/spend on the CF-web server, eventually we are typically able to persuade them to let us look at the database. They would genuinely benefit greatly if we started out at the database. Here are the main things we look at and report on...
- Table Indexing
- Back-up Redundancy Schema
- Security Settings - Ports etc
- Security Settings - User Accounts
- Use of Views
- Use of Stored Procedures
- CPU Affinity
- CPU Allocated to Database
- Service Pack Level
- Data File Location
- Log Files Location
- Compatibility Level (SQL Server Specific)
- Heavy/bad processes such as Auto_Shrink
- Data Type usage (looking for the most efficient data types)
- Unnecessary Services
- Long Running SQL