Voice of the DBA

Tag: Performance

  • Finding Bad Queries

    T-SQL Tuesday #200 was in July, hosted by Brent Ozar, and it was a great topic: How do you recognize a bad query? In the age of AI, when lots of people will get queries written by others (people or AIs), how can you easily and quickly review code? Review is already a challenge in the software world, and I am sure it’s going to be even more challenging as people let machines author more database code.

    Lots of you might hope that an AI agent will write better code than your average developer, but I don’t know if I’d count on that. There is a ton of poor query examples on the Internet and that’s where AI models are trained. You need some sort of feedback loop, good testing, and strong guidance if you want better query code. I think it’s as likely as not that AIs will produce poor queries just like humans.

    Just faster.

    How can you recognize poor queries? How do you judge if there are issues? Brent’s roundup shows what a number of people think, and there are some good “quick checks” to use to evaluate your code. The things that people wrote in their blog posts are the types of things that I’d want added to Flyway’s Code Analysis rules to catch problems before they get deployed to production. After all, anything that gets to production usually lives for 10 years (my rule of thumb).

    Getting feedback early in the development process is critical. We know from over 50 years of software development that finding problems early is less expansive than finding them later. Whether we use humans or AI agents, we don’t want unnecessary time spent on simple bad code things that we know cause issues. Shift-left is still a challenge, even in the automated era of DevOps and guided AI agent skills. We need reliable, repeatable ways to analyze code and limit the downstream effects of poor quality.

    Especially these days, where the volume of changes is overwhelming CI/CD systems. This was a problem already in organizations with too many teams in one codebase. It’s a growing problem in more organizations as users use AI and submit more changes.

    Plus wasting time gets expensive. Human labor is slow and spending time on more tokens for rework gets expensive.

    Don’t let bad queries flow through to CI/CD systems if you can avoid it. Learn to provide quick feedback early to whoever, or whatever, writes your SQL.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • Fixing P1 Queries

    When we look at the performance of software, we use Pnn notation to indicate the latency of an issue. A P95 problem is one that exceeds the time that 95% of the other queries take. In other words, this is the 5% slowest things happening, which can include database slowdowns that impact your application.

    For many years in software development, we have tended to work on the P95 or P90 issues, the slowest items. This is primarily because those can make a big difference to the system’s performance. If I fix the slowest things, then the system feels faster. Certainly, I know lots of DBAs and developers will apply this logic to database queries. They focus on the slowest queries and tune them to improve the system. If most things are quicker, especially the things most users notice, then the system feels faster.

    We’ve often limited our work on latent items because the time involved in working on other issues is hard. If you choose to work on items in the P70 range (30% slowest) or the P50 items, does that make sense? There are slower items, so doesn’t it make sense to focus on those P95 or P99 items when trying to increase the speed of the user experience? In software, I think this does make sense.

    However, in the database world, we aren’t often thinking of one user’s experience. We’re thinking about the volume of requests from all clients. We might find there are queries that seem fast, say 400ms queries, but if these are running thousands of times a minute, we might see more benefit from clients if we reduce this to 200ms than if we speed up a 30s query to 2s. For the database world, we often think about the rate of items, not just their latency (or speed).

    For database queries, how do we choose what to focus on if it’s not the slowest queries? we might not also want to tackle the most frequent queries either. My question might be: do we need to choose?

    In the age of AI, especially if tokens aren’t too expensive, do we need to spend engineering time deciding? Would it make sense to let our AI coding agent evaluate and propose changes to all the queries? With a team of agents, we might have time to investigate the entire workload and try to speed everything up. This does mean we need a good system for guiding agents, separating out work, and ensuring that each agent isn’t trying to add an index for every query. That’s something humans have done, and agents likely will try if they don’t have to coordinate their work.

    The effort of this work isn’t zero, and I’d bet there will be a significant amount of time to get this set up in some organizations, though I’d hope as people discover ways to organize agents for database work, with controls to scope their efforts and validation techniques, this becomes easier and easier. I’d like to think that at some point, DBAs will work in the same way software developers are hoping they work: thinking about problems, evaluating potential solutions, and managing AI helpers who type the actual code.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • What is CPU Usage?

    I had a request from a customer recently who asked if we could give them a report of their database server instances and include CPU usage. This request was filtered through an account executive, so something was lost in translation, but I was confused and asked for clarification, as asking for CPU usage is kind of like asking how fast you were traveling in your car. There needs to be more context.

    If someone asked you for a report of CPU usage for a database, what would you expect? How would you report this? I’m sure the person asking might make a difference. A fellow DBA, your DBA manager, or maybe an executive could all view this differently. I want to know how things are performing, if there is a trend, or maybe if we are getting value for the hardware we’ve provisioned, depending on my role.

    If you were looking at your monitoring system, what would you look for? Redgate Monitor shows the last 24 hours of CPU with the last two hours in a focus window as the default, though you can easily change this. Perhaps you’d prefer this boiled down to min/max/average values over a time period? If there’s something else you’d consider, let me know. Maybe I can get it added.

    Certainly, the values you expect might depend on what you are trying to measure. Is there an acute performance issue? Then you might worry about the spot values or perhaps the last xx minutes of trending CPU values. If this is capacity planning, then peaks and average values can matter. If you are looking forward, you might even want longer trend lines and future extrapolation.

    Of course, the experienced DBA knows that other metrics need to be taken into account when looking at the CPU. 100% usage isn’t necessarily an issue if the workload isn’t creating unnecessary processes stacking up or too much latency in returning results. Perhaps the amount of memory used should be included to determine if there are CPU or RAM (or other) bottlenecks.

    There are a lot of factors that might go into how you examine the performance or efficiency of a database server, whether SQL Server or any other platform. A well-performing system has to balance lots of factors to meet the demands of a workload. A poor-performing system doesn’t do this well and often there are many more factors than how much of your CPU capacity is being used.

    The other thing this request showed me is how poorly many people communicate, and especially how much can be lost as a message moves from one person to another. This is an area where AI can succeed or fail as well. IF we communicate poorly with AI, we may waste time and money while receiving useless results. If we let AIs provide summaries to us, or from us, the model may choose to emphasize, highlight, reword, or hide things we think are important. After all, the model is predicting what should come next, not understanding the information.

    Communication is crucial and is becoming more important as we become more distributed and allow technology to sit between us.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • T-SQL Tuesday #200: When I Look at a Query …

    This month is a milestone for T-SQL Tuesday. It’s number 200, which doesn’t sound big, but this is a monthly party (started by Adam Machanic). We have 12 blog party events a year. 200 means this has been running for almost 17 years (16 years and 8 months).

    I don’t care who you are, that’s impressive. Think about where you were, what you were doing, and what was happening in 2009.

    I haven’t been running it that long, but I am glad I took it over from Adam and have kept it going. Thanks to everyone who writes and reads the posts and especially the hosts.

    I tried to get Adam to host, but he declined. Fortunately Brent Ozar stepped up with a great initiation. My response below.

    At First Glance

    There are two things that immediately stand out to me when I see a query and create concern.

    1. cross joins
    2. functions in the where/on clause

    While there are other things I might see, these two stand out and usually I can guess there will be issues.

    For cross joins, I don’t see this as much when people use SQL Prompt or some other helper because they tend to use inner/left outer/right outer explicitly, or cross join. If you explicitly use a cross join, I might ask why, but these clauses require an ON clause, which means you’re deciding to join tables.

    Where I see people using old style joins, like this:

    select *

from a, b

where a.id > 23 and b.saledate > current_date

or (a.id is null and b.saledate is null)

    I get worried. This happens in Oracle, and PostgreSQ, and it’s easy to forget to join a and b, especially when there are multiple tables. Usually cross joins happen with legacy join conditions.

    The other area is using functions in the WHERE clause. A common example is

    select *

from customer

where upper(customername) = ‘Steve’

    This function in the WHERE clause ruins the ability to see the data. The index is something like (‘Adam’, ‘bill’, ‘Steve’, ‘WILLIAM’). This can’t be used when the UPPER is applied. This often results in more reads, more scans than a system might otherwise take.

    There are plenty of other issues that can indicate performance issues, but these two are the ones I’ve often run into and the ones that would have helped 2004 Steve write and review better code.