A Practical Guide to Peak Database Performance

Database optimization is a lot easier than you think. Here is a guide to all the simple steps to maximize your database performance.

You know how they say, “America runs on Dunkin?”

A database runs on queries.

Optimizing these queries can optimize your entire database. They can be a select query or a modification query.  

In both of these query types, you try to speed the query time up as much as possible so the database can run efficiently and fast enough for the application and the users.

Why do you want this? It powers your application.

What’s the workflow for optimizing queries to make your database more efficient?

Check your stored procedures. These are the basic business rules that are in the form of data queries. Over time, they can get pretty complicated. We had a client whose database needed 20 seconds to run these queries.

Once we were able to sort out what was going on and optimize everything, the same query took milliseconds.

Some queries have functions running inside the query, so the database has to run a function first, then the query itself. In certain cases, there are functions embedded inside other functions that require your database to perform multiple operations, and calculate several values before completing the query.  

Achieving peak database performance can be found at the other end of a complete audit and overhaul of your stored procedures.

Configurations, Settings, and Other Loose Ends Boost Database Performance:

Now, check the database size. See how much space has been allocated for it. Some databases consume only 300 MB but are allocated 1,500 GB. The rest of that was held for future usage, anticipating that more data would expand the memory consumption of the database.

The rest of that was held for future usage, anticipating that more data would expand the memory consumption of the database.

It’s one of those “old habits die hard” scenarios where people got used to requisitioning more memory as a process where you needed approvals, then ordering more space, so you always “planned in advance” by buying a lot more memory than you need.

Today, you can instantly spin up the memory and receive more in real-time as needed. The best practice is only to allocate the space you are using. This is an effective optimization.

Then check the database settings. What’s the transaction isolation level? Does your database have any specific parameters?

There are a lot of settings that are “set and forget.” The database has a bundle of default configurations, or a legacy system sets everything up. In many cases, a new database will “inherit” the default settings from the previous database.

There can be a lot of inefficient settings that have been holding back your data layer for years, even decades. This is a great low-hanging fruit way to optimize your database without code simply and quickly.

Fixing Indexes and Tables Complete Basic Database Optimization:

At this point, you should analyze your indexes and the fragmentation of your tables.

Look for missing and unused indexes. Unused indexes are one of the worst performance killers of your database. You need to remove unused indexes and add missing indexes. Optimize existing indexes to speed up your queries.

See if there is a clustered index in place.

A clustered index defines the order in which table records are stored. There can be only one clustered index per table. Your clustered index is created on the primary key column.

Make sure that your tables have optimized indexes to keep performance optimal. Also, see if you can compress your indexes. If you are saving money with a remote DBA, make sure they are checking for these things on a consistent basis.

See how the database gets used in live production. In production, there are lots of issues that come to light. It could be data mismatches. It could be untuned heavy queries with a lot of JOINs and encapsulation.

Simple monitoring tools will tell you where you are consuming too much memory or taking too long to perform something.

As you improve and relaunch into production, you will see what is improving and still needs work. After several iterations of these fixes, keep your monitoring tool searching for inefficiencies that come up with everyday use.

These are the main optimizations for a database.

Saving your business time and money with Remote DBA Services is something we specialize in.   

 I am happy to talk with you about it at your convenience, so please contact us to discuss further.