Queries Taking Forever? Fine Tune Your Database’s Performance
In Article 2 we talked about maintaining databases like you would your car. Now it’s time to supercharge the engine. Tuning databases for peak performance brings several advantages to working with data, from faster response times to allowing more queries to run concurrently.
Most database servers contain automatic query optimizers. They do some of the performance work for you. While that’s helpful, queries can only work with what’s in the database already. If something is missing (like an index), it can’t help. Eventually a specialist will need to identify where slowdowns are, and how to fix them.
While maintenance plans focus on the database structure itself, performance tuning focuses on the beginning and endpoints of the server:
- The server hardware
- Query performance
Get Everything You Can Out of the Server
Most servers today waste hardware resources. They perform well below what they are capable of, using only a fraction of the processing power they contain.
How much memory sits idle? How much CPU speed is really utilized? Could the drives load data more quickly? In order to find these out, a DBA engineer/consultant uses diagnostic tools to build a profile of the server’s average daily use.
From there, they identify areas where they can improve performance at the server level. For example, consolidating several databases onto one server system. No performance loss, you've cut power usage to a fraction, lower licensing costs, and your server gives you everything it has.
Fine-Tuned Queries Bring Data to Users More Often
Once you know the hardware’s working to the best of its ability, optimizing the queries run through the database becomes the next improvement point. For this you can take advantage of one of the automatic tools mentioned earlier. In Oracle, it's called the Tuning Advisor. In SQL Server, the Profiler.
The Profiler determines if a certain query is causing slowdowns. Is a query eating CPU resources at certain times? If so, the server monitoring will show a CPU spike. The Profiler checks for queries run at that time. The specialist then recommends the best way to eliminate its effect. Like rescheduling the query, so it can still run without draining resources away from other queries.
What is the biggest advantage of optimizing databases in this way? It allows for growth. The database is equipped to expand as your business grows. Handling mission-critical data without faltering. Which means protecting your data again moves into our focus. In Article 4, we'll cover how to preserve your data with disaster recovery planning.
Questions? Email our DBA team at info@planetmagpie.com. Or call us at 408-341-8770 to schedule an appointment.