7 Things That Can Help Prevent Database Performance Issues

November 20, 2017

7 Things That Can Help Prevent Database Performance Issues

By: Ethan Burkhart, Sogeti USA

How often do you hear, “Why is everything responding so slowly?” in your office?

We’ve all had this happen: we click on something and it doesn’t return the results we’re looking for, or it hangs in one place, or it may seem to take longer Monday mornings compared to other times throughout the week. These issues can range from being a small annoyance to bringing critical business systems down. As with any complex issue, there are many potential root causes, but below are some tips that can help fix these pesky performance issues before they are noticeable.

  • Ensure your underlying SQL scales well
    • Whether you’re using a complex 3rd party application or manually running simple queries against the database – ensure the SQL scales well with increased data volumes! This includes ensuring your “where” clauses can utilize indexes to prevent full table scans. An example of this is not using “NOT IN” clauses, as they prevent the database optimizer from utilizing indexes.
  • Ensure your database is properly indexed and has updated statistics
    • All relational database management systems (RDBMS) utilize object statistics to come up with the most efficient way to execute SQL statements against the database. Most RDBMSs gather stats automatically and indexing is something that is an on-going task, but between these two, many performance issues can be resolved.
  • Set up a test environment
    • The importance of this cannot be overstated. To test new functionality, potential fixes and improvements and any other changes, you must have an environment set up that can emulate the production environment. One common mistake made here is that many test environments are often set up but then not maintained. Periodic refreshes from production to test are required to ensure that the test environment remains a good testing platform for production.
  • Test application logic
    • Some performance issues are a result of application logic issues that were not properly tested before implementing into production. A good example of this is an application that is holding a full table locked to delete data but also attempting to select data from that same table simultaneously. Deadlocks can also be a result of poor application logic.
  • Ensure proper baselines and SQL profiles are utilized
    • Most RDBMSs have some type of optimizer that determines the most efficient way to execute each SQL statement, but as with anything, it can make mistakes and is not always correct. Almost every modern RDBMS has tools to manually manipulate the execution plan for specific SQL statements and these typically include baselines and SQL profiles.
  • Be proactive!
    • This is most likely the most important step on this list. Performance issues are never fun to troubleshoot when it’s severely impacting business operations. Most performance issues can be identified and resolved before ever becoming noticeable to the end-user.
  • Keep an open dialog between developers and DBAs
    • Developers and DBAs must work in unison to constantly test and analyze performance of new and existing functionality to check for regression of existing statements. It is also important to check for potential improvements for newer queries because teams must be aware of changes being made in production before they are made so the proper amount of testing can be completed and the implementation can be a success.

Have additional questions on improving database performance? Sogeti can help! Please contact Lauren Ouart at This email address is being protected from spambots. You need JavaScript enabled to view it. with your questions.

© Technology First 2018. All rights reserved.