How to check queries, settings and indexes for database performance

Start a database performance review with the queries that cause delays. Then check stored procedures, database settings and indexes. Measure response time before and after each change.

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.

Check database space and settings

Check the space used by the data and log files. Compare it with their allocated size and expected growth. Database file space is disk storage. It is different from server memory. Allocate enough space for normal operations and expected growth. Set suitable file growth increments. Monitor free disk space. 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.

Check indexes and table fragmentation

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. Check index use across a representative work period, including scheduled reports. Before you remove an index, check its role in queries and constraints. Test each proposed index change. 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. In SQL Server, a primary key can use a clustered or nonclustered index. A separate clustered index can use different columns.

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.