Voice of the DBA

Tag: T-SQL

  • A Lack of Error Handling

    I saw a post by Brent Ozar the other day, and it was a bit disturbing to me. A survey he posted on Twitter showed most people don’t bother with error handling in T-SQL. I’m not surprised, though I wonder if people think about the “rare” as including all the one single statement queries they write. I certainly don’t expect error handling for simple queries when they are a single statement used to return data to a client.

    However, in stored procedures and multi-statement batches, I see the same thing as the results Brent published. Very little error handling. I’ve tried in my demos to add it and be sure that I set a good example, even though most of the time I’m showing code that just always works. I know what data I’ll enter for a demo, and I don’t run into issues. 

    Many years ago, decades actually, when I was in college and early in my career, I saw many application software packages written without a lot of error handling. This included C/C++ at the time, which seems crazy. In the last decade, I’ve seen a lot more robust error handling (and testing) added to the work of many application developers. It’s the request that my professors and more than a few bosses always made, but in the late 80s/early 90s, few people actually followed through with. In the 2010s, this seems to be more common, and not surprisingly, software quality has improved.

    In many ways, database developers are less mature than application software developers in many ways. Less error handling, less automated testing, and that’s understandable. We haven’t had great tools or patterns to help us easily adopt these practices as a habit. In addition, the way in which code is compiled and then executed doesn’t make this easy for us. The platform and structure of the language create complexity that isn’t present in application languages.

    If you haven’t written this in the past, as Brent notes, there’s not need to worry about going back now. Either your code is working or not working, and if it’s the former, no reason to revisit it. However, I might ask that you experiment with error handling for new code. Learn how to use these structures to protect against your users entering strange data. Something they are likely to do.

    Steve Jones

  • Finding Failed Job Steps

    Kendra’s query was a good starting point, and I used most of it in the first CTE shown below. This query basically looks at msdb.dbo.sysjobhistory and msdb.dbo.sysjobactivity, joining them on the job_id, which is the PK. However, we are only looking at steps, which is anything with a step_id > 0. The step_id = 0 is for the overall job.Recently a customer was asking for a way to alert on job steps that failed, but the job succeeded. They really wanted a custom metric for SQL Monitor, which I submitted, but this post looks at the query in general, trying to determine if a job step failed.

    Note: Let me start by noting that this is based on work by Kendra from her post, SQL Agent Jobs: Checking for failed steps at the end of a job.

    Based on Kendra’s query, I looked through what is happening in msdb.dbo.sysjobhistory and msdb.dbo.sysjobactivity. In Kendra’s query, she is looking for a specific job, but I wanted all jobs. This lead me to build a CTE that queries for the data.

    As you can see in the code below, I use most of Kendra’s query to join these two tables together. First, we look for steps, so step_id != 0, and we look for failures. A status of 0 is a failure here, where 1 is success.

    Update: Changed the query after the comment and another bug I noticed.

    WITH cteActivity (job_id, start_execution_date)
AS ( -- get the latest job execution for all jobs
    SELECT job_id,
           MAX(start_execution_date) AS start_execution_date
    FROM msdb.dbo.sysjobactivity
    GROUP BY job_id)
, cteJobStep (Job_ID, Step_Name, run_date, run_time)
    AS (SELECT jh.job_id,
               jh.step_name AS JobStepName,
               jh.run_date,
               jh.run_time
        FROM msdb.dbo.sysjobhistory jh
            INNER JOIN cteActivity ja
                ON jh.job_id = ja.job_id
        WHERE jh.run_status = 0 --step failed
              AND jh.step_id != 0
              --         and jh.job_id = CONVERT(uniqueidentifier, '8C673935-F8C1-4E7D-94D3-1F3CAE50D7DC')
              AND --this block ensures that we just pull information from the most recent job run
            (
            -- The start time of the step, converted to datetime
            CONVERT(DATETIME, RTRIM(jh.run_date))
            + (jh.run_time * 9 + jh.run_time % 10000 * 6 + jh.run_time % 100
               * 10
              ) / 216e4 >= ja.start_execution_date -- the time the job last started
            )

    Next, I kept most of Kendra’s query, but I commented out the line that limits this to a specific ID. I just want all step failures. I did keep the part that only checks the latest execution of the job.

    The outer query just counts these up and returns a number. This lets me know how many job steps have failed during their latest execution.

    This is a good first step, but this is something I could add in SQL Monitor as a custom metric or in any tool for alerting. When I have steps failing, I might want to know.

  • Better SQL is a Good Career Investment

    Many of us reading this are data professionals, and we likely know quite a bit about SQL in general. We may use T-SQL, specifically, with SQL Server, but many of the skills we have would be portable to other dialects, such as those use in Oracle, PostgreSQL, etc. We’d certainly need to brush up on best practices and which language constructs are better suited for a specific platform, but most of the knowledge transfers.

    I ran across an interesting post on the value of SQL in a career, mostly for someone that might be moving into the data science or machine learning/AI type role. The post notes that lots of these job descriptions mention SQL, but focus quite a bit on R, Python, modeling, etc. In this case, SQL matters, and I’d agree with the author that it matters a lot. Most of the work in those fields is data prep, and SQL makes this much easier at scale than other languages.

    What about those of us that have been working with SQL Server (or some other platform) for awhile? Is learning more about SQL a good investment in our career? In most cases, does our boss even know if we have mediocre or amazing T-SQL skills? Do they care?

    They may not, but I think they should care, and more importantly, you may care. When you know more about the language and how to structure queries to solve problems, you’ll work quicker. You will write code that performs better, resulting in a lower workload on the instances. Your code will last longer, have fewer bugs, and co-workers will trust your work.

    The more you practice with code, the more you solve new problems and learn what works well and what doesn’t, the better you will be at your job.  The time you put into learning to write better queries will pay back with less stress and more time for other tasks. While your boss might not notice your code is better, they certainly will see you as more capable, relaxed, and trustworthy. All good impressions to make at review time.

    Steve Jones

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

  • Using Framing for a Running Total–#SQLNewBlogger

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

    I’ve been writing about window functions, which I find very handy. In this post, I want to build a running total using the framing of the window. This continues from my last post on aggregates.

    I’ve been looking at baseball data. Imagine that someone wants to know how many total home runs a player had at each stage of his career. In this case, if a player hit 1, then 4, then 5 home runs, we’d expect a running total to show:

    • Year 1: 1
    • Year 2: 5
    • Year 3: 10

    This is a cumbersome query without window functions, and inefficient, as I really need a subtotal query for each of the main rows. It’s difficult to write, read, and it’s slow. With a window function, however, I can use this query. You can see the framing with the ROWS section in the OVER() clause.

    SELECT
              yearid
            , hr
            , SUM (b.hr) OVER (ORDER BY b.yearID 
                               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TotalHR
    
FROM     dbo.batting AS b
    
WHERE    b.playerID = 'griffke02'
    
ORDER BY b.yearID;

    This gives me results that look like this:

    2021-07-23 15_53_08-window_queries.sql - ARISTOTLE.BaseballData (ARISTOTLE_Steve (57))_ - Microsoft

    Simple and easy to see and read.

    This works as the window is scanned and totaled using the OVER() clause. In this case, the partition is the entire window, meaning all rows for this player. I’ve set the ordering to be the year, so as we move through the years, the SUM() is calculated using the part of the window that goes from the beginning, with the UNBOUNDED PRECEDING marker, until the current row.

    You can think of this as we scan from year 1989 first. In this case, the entire preceding section is nothing, and the current row is 1989. Therefore the sum is 16.

    Next we look at year 1990. There is a preceding row (1989) and this current row. We sum those to get 16+22=38. We repeat this with each row, always going back to the first row.

    For the ROWS clause, we can use the between to determine the start and end portion of the partition that we scan. This means we can use:

    • unbounded preceding
    • unbounded following
    • current row
    • an integer

    We can combine these 4 choices to get what we need. If we were looking only for a best 3 year time frame of home runs, we could get a sum like this:

    SELECT
              yearid
            , hr
            , SUM (b.hr) OVER (ORDER BY b.yearID
                               ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS TotalHR
    
FROM     dbo.batting AS b
    
WHERE    b.playerID = 'griffke02'
    
ORDER BY b.yearID;

    This gives me a grouping of the SUM for the current 1, as well as 1 before and after, for each row.  My results are shown below. For the first year, there is no preceding row, so we sum the current and next row, 16+22 for 38. For the second row, we have 16 preceding, 22 current, and 22 next, which sum to 60. You can check the math for others.

    2021-07-23 16_01_12-window_queries.sql - ARISTOTLE.BaseballData (ARISTOTLE_Steve (57))_ - Microsoft

    There’s more to this, but for now, a quick running total is using SUM() and then the ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW clause.

    SQLNewBlogger

    This was a quick 15 minutes to write. I took part of what I’d done in the last post, changed the query quickly, and then started to explain part of a clause. I’ll keep this around and use it to expand on some other places where the framing can be useful and affect how I work with data.

    A good chance for you to also show how you might build a running total, or even running count, with your own data.