Voice of the DBA

Author: way0utwest

  • Building Great Software

    Most of us will work on software for our organization, and we might not want to or care about the end result being great. We do want it to work, and we want clients to find it useful. If we have external customers using our systems, maybe we want it to be great. In most of my experience, people are often proud of their work, sometimes ashamed, but not many people spend a lot of time making their corporate applications great.

    Often because we don’t have (or aren’t allowed) the time to do so.

    Basecamp is a popular SaaS project management solution, and Hey is a reimagined email service that many people love. One of the founders of the company wrote an interesting post on software being built that starts with this sentence: “The speed at which a product is developed doesn’t inherently make the product better or worse.”

    It’s a bit of a shot at AI, but it also goes into the fact that we often measure our software process in ways that aren’t about the output. Most organizations have abandoned lines of code as a metric, but I do see commits or PRs being used, as well as other metrics. Trying to decide if your developers are effective isn’t a horrible idea; after all, we should be ensuring that they are getting something done, but none of those metrics necessarily help us make better software.

    I see that at Redgate, as we incorporate AI into our work. A lot of the things that make software take time aren’t solved with AI. They’re solved with deep understanding of the problem space, what your customers need, and what helps them work well. AI accelerates some things, but we still need product people empathizing deeply with customers and designers watching for UX issues that create friction for customers. AI can help speed up the experiments and outputs in some ways, but just adding in chatbots or AI agents that write code isn’t necessarily useful.

    There are a lot of decisions in building software. What to do, what not to do, what’s more important than something else, and of course, what approach to take in the architecture. That’s before we even get to performance, which is something that far too often gets ignored, at least for the code being run against databases. Building great software is hard, but it can be done, and AI can help.

    You just need talented humans guiding the process.

    Steve Jones

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

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

  • Why Use TRY_PARSE(): #SQLNewBlogger

    Someone asked why I would use TRY_PARSE after I posted a question at SQL Server Central: Getting the Average. Isn’t is slower?

    A fair question. This quick post looks at why.

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    A Quick Setup

    The question above has the setup code, but what if I add another row? For example, I’ll run this .

    insert dbo.commission
    (
        salesperson
      , commission
    )
    values
    (‘Steve’, ‘A’)

    Now, let’s look at the data and run the query from the question.

    2026-07_0371

    This works. However, let’s remove the (slow) TRY_PARSE() from the aggregate.

    2026-07_0372

    Error. Why? I can’t convert “A” in the AVG to a number. It fails.

    You might think, I’ll never get bad data like this. But you might? A user might enter something you don’t expect. An AI might model this as a string, which is bad, but it happens. If it’s an EAV type table, or there are other data  items and you’re trying to extract the numbers from here, TRY_PARSE is helpful.

    SQLNewBlogger

    I wrote a post last week and this is a followup that really just took less than 5 minutes to setup and run. Plus I responded for the user in the post.

    This showcases me thinking about a question and situation and really gives an interviewer something to ask me. This lets them dive into my thought process and gives them confidence I don’t just write code without thinking.

    Add to your blog with short posts like this (or drop on LinkedIn).

  • T-SQL Tuesday #201: Temp Tables

    This month we have a new host, which I am grateful for. So many people have stopped blogging that it’s a challenge to keep this going. Jeff Taylor has an invite asking us about a core T-SQL topic that can affect the performance of your app and your server: temp tables.

    This is an interesting one for me, as I’ve changed my mind on these over the years, especially as Microsoft has made improvements to the SQL Server storage engine and query processor that have helped tempdb to perform better.

    They’ve also added things that can impact and load tempdb.

    If you want to host a T-SQL Tuesday, ping me. I’m always looking for new (or returning) hosts.

    My Answer

    My answer to Jeff’s question of temp tables as friend or foe is yes.

    They are friends.

    They are foes.

    Most things that we struggle with in database work are tradeoffs. We have to balance the demands. It’s why we say “it depends” so often because we have to find a way to do more of one thing, while accepting less of another.

    If I use temp tables in a query, I can potentially run a query to get a smaller data set that I can query with to get the results I need. I can reduce the memory grant, which might be required if the initial query scans a lot of data.

    Suppose I have a 100mm row table. If I am doing a complex join of this data on unindexed columns with multiple other tables, perhaps I want to do something like this (delivereddate might not be indexed):

    select c.customerid, o.orderid, o.delivereddate, oho.shipperid, o.salespersonid, ,qty, oh.price
into #limitedorders
from orderheader oh inner join orderdetail od on oh.orderid = oh.orderid
where customerid = 12

    This can get me a filtered list of things, which I can then join this temp table with the customer, shipper, salesperson, and other tables, which will be a smaller, quicker join.

    However, a temp table can be a problem as well. If you have a query that does something like this:

    select *
into #orderlist
from orderheader oh inner join orderdetails od
on oh.orderid  = od.orderid
where oh.orderdate > dateadd(year, –10, getdate()

    And then you have some sort of query that aggregates across the years.

    select c.customername, sum(t.qty * t.price)
from #orderlist t
inner join customer c
on t.customerid = c.customerid
where customerid = 12

    I’ve done a lot of work in the first query to gather data, allocate space in tempdb, copy this over, use memory, etc. Then I am going a simple query to aggregate things, and filtering it. In this case, the developer likely followed a pattern of gather data, then sum it from other queries. It might have worked for them, but if I have 1mm orders a year, this will suck up resources.

    My Advice

    My advice is like Jeff’s. In general, try to work with a query to solve your problem. Use joins, beware of views, and make sure you’ve indexed well. Use CTEs to break down your problem, and don’t use temp tables.

    If you struggle to get a single query, or you have poor performance because of memory grants or other issues, then think about shrinking your dataset down using a temp table and indexed columns. Then join this to get other data that you need.

  • Imagine the Physical World

    Brent Ozar has a series of database animations posts, where he tries to explain what work is done by SQL Server during certain operations, such as Index Seeks and Page Splits. These show how the engine might need to read or update various pages as it tries to perform operations. Both experienced and novice SQL Server people might think that these are interesting, but not that useful.

    I think they’re great.

    For most of my IT career, I’ve drawn parallels between what I’ve asked the computer to do, and how this might play out in the real world if we weren’t working on digital systems. Most of the constructs we have, connecting to a system, sending a query, configuring a firewall, sorting data, etc. can be related to a physical action.

    If you want to understand page splits, imagine you had the index from a book printed out (or a TOC), and broken out across multiple pieces of paper. If I asked you to insert something in the middle, or to lengthen an entry that doesn’t fit, what would you have to do? You’d need a new piece of paper, you’d put it in a place between the others, you’d copy over some data, you’d erase some data from existing pages and more. Exactly what Brent’s animation shows.

    That’s real work.

    The more I think about the work the computer does, even if it’s measured in ms or ns, the more I can think about whether I can find an efficient way to complete my task. It’s not that I mind the computer reading 1mm rows (or 100mm), but if I can reduce the work, I reduce the latency, the computer, the disk, the cost, the everything. My customers are happy when I try to reduce the work and make everything run faster.

    This view has helped me look for and find better ways to implement solutions over the years. I know that not everything needs to be optimized, after all, sometimes we do more work in the physical world because of expediency, but we know the tradeoff. We can judge if it’s worth it.

    Too often I see people think the computer is doing the work and they dismiss the effort. It’s the same attitude people have in the real world when someone else has to move/lift/pay for something. It means less to them.

    Treat everything as if you had to do the work yourself. You’ll treat others better and appreciate the effort spent, whether it’s another human, a software program or an AI LLM. Your results will show better judgment, and hopefully, quality.

    Steve Jones

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

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