What every dev needs to know about their databases that traces and logs won’t tell them

What every dev needs to know about their databases that traces and logs won’t tell them

By Zack Kendra

Traces and logs play a vital role in managing and optimizing database performance, providing useful information on database events, audit information, and queries that tell you where and how users are spending their time. To get the most from your database however, there are a number of areas you need to be mindful of that log and trace entries won’t help you with:

Capacity planning trends

One question logs and traces can’t answer is ‘Where are my resources going?’.  As application utilization increases, any issues in the underlying data layer can become more and more pronounced. At a trace level you can see that things may be running slow, but there won’t be any obvious reasons why. To find the answer you need to take a step back and look at capacity: how big are your tablespaces and disks, what’s been allocated and what’s being used? Looked at in isolation, a log may show that a database can’t grow any further. Most devs have a tendency to blame that particular database when in actuality it may be another database or disk issue that is the root cause. Looking at capacity as a whole you’ll be able to see size breakdowns of everything on a given disk and flag potential issues in advance. For example, perhaps your writes have increased, but your deletes have not, and that’s causing tablespaces to fill up faster than anticipated.

 

Figure 1: A Microsoft Azure SQL Server instance with regularly low DTU usage like this might signal you’re over subscribed.

Event root causes

Log and trace files can tell you about specific events, like when slow queries are happening regularly, or how a deadlock happened after connections spiked. In isolation however, you can’t correlate events, and resources like memory and CPU capacity aren’t typically tracked. Having a complete view of your storage stack can paint a fuller picture, letting you determine if there are more requests, more connections, or more queries running at a given point in time, or if a limited resource is causing a slowdown. With aggregated data, you can more easily see where waits are negatively impacting performance, helping you find a root cause sooner.

Figure 2: What are you waiting for? A visualization like this will tell you more about root cause than a trace or log file.

Historical performance patterns

Determining a baseline for database performance isn’t possible with logs and trace files alone. While you’ll be able to spot problems, you need to be able to answer if those are part of a new trend or the same as it’s always been, and how long they’ve been recurring. With historical data you can see how often you were calling something in the past, and how long those queries took versus today. That’s important context for how you optimize performance as application usage evolves, allowing you to pinpoint solutions faster whether it’s changing the way a query is written, or adjusting the way an application handles data to make calls less frequently.

Figure 3: Most organizations will find cyclical performance patterns by looking back, ideally as far as 3-6 months.

Query tuning tips

While logs and traces can point to slow queries, they won’t tell you why. By executing a query plan you can drill down on a particular log entry or trace to see the details of its slow performance, the cost of the query, and the rows involved. Depending on the tool you use to manage your database, these query plans can be visualized to more easily determine the root cause of a problem.

Figure 4: Is there a better Query explain plan for your app? A trace or log won’t be able to tell you.

To quickly resolve database issues and optimize the performance of your database you need to step away from the granularity of log and trace entries to see the forest for the trees. Capacity details, aggregated data, historical context and deeper explanations are all important to ensuring you get the most from your database.

One way to go beyond traces and logs is with a database performance monitoring platform like SelectStar. Get started with a free 14-day trial today.

LET'S GET STARTED!

Try SelectStar for 14 days. No credit card required.

TRY IT NOW!