We are frequently called by customers to fix applications performance problems with our SQL Server Performance Tuning service. When talking to these customer I often find out that they’ve had their development work done via Upwork or Fiverr. It seems that fast application performance is rarely a requirement when people get their software built on these platform and if it isn’t a requirement the developers will take shortcuts to get the work done more quickly. So here are some recommendations on how to avoid poor application performance:
Understand the nature and costs of performance problems
Don’t assume that performance problems will appear slowly and that you’ll have time to react without loosing customers. It is very common for a performance problems to appear suddenly because multiple factors together have caused a tipping point. The symptom of this tipping point will be an application which is completely unusable because of timeouts or errors. You’ll have an emergency on your hands and the cost of a quick fix will be high. Some examples of common quick fixes are:
- Upgrading parts of your hardware. For example, move the databases to Solid State Drives.
- Migrating the entire application to a bigger and more expensive server.
- Purchasing a quick-fix solution like RamDisk and hope that it will fix the problem.
Make performance a #1 priority from the start
If you don’t make it clear to the developer that performance is a key requirement then he’ll probably end up taking shortcuts to keep his costs down. So at the start of the project, ask the developers the following questions and make sure these points are followed up on for the duration of the project:
- Will the database be normalized? Can I see the entity relationship diagram and have it reviewed by a DBA?
- How will you ensure that my tables have covering indexes for the main queries running against the database?
- What types of stress testing do you plan on doing and with how much data in the database?
- What database maintenance plan will you put in place once the application goes into production?
- What kind of monitoring will be done to alert us about performance problems?
- Do you plan on using any performance anti-patterns like server side cursors or EAV data models?
Do a Stress test
You should do stress tests against your application regularly. The nice thing about stress tests is that if they are properly set up the first time it is very easy to repeat them for each cycle of changes that you release to your application. Think about the amount of users the application is likely to have in 1-2 years and load load a corresponding amount of data into the database. Data can be generated using SQL scripts, you can simulate calls against the application database using PowerShell scripts, the server can be monitored for bottlenecks via Performance Monitor, and you can collect statistics about the performance of your database by using SQL Server Profiler traces.
Hire someone to validate the work
There are contractors on oDesk and Elance that specialize in QA testing. Our SQL Server Tune-Up will also tell you if your SQL Server has been set up correctly with index defragmentation jobs or if there are any other performance anti-patterns. Unless you have a lot of time and the technical knowledge to check each detail (in which case you might as well do the work yourself), then you can save time and money by using these services. But make sure that you hire someone that is not affiliated with the contractor or agency doing the work.