Voice of the DBA

Tag: T-SQL

  • To Inifinity and Beyond With 1=1

    There is one thing that Aaron Bertrand won’t get mad at me for doing and that’s creating an infinite loop with a WHILE (1=1) pattern. He wrote about this recently in a tip, noting that it’s easy to create an infinite loop with code like this, something that never ends well.

    I have purposefully written infinite loops in the past. A long, long time ago Radio Shack would put out TRS-80s on display, and a few of us would type a quick BASIC program to perform some math work on the screen over and over with an infinite loop. It crashed at some point with an overflow, but for a period of time, it would look neat on the screen. Less neat to the salespeople who weren’t always sure how to stop it and demonstrate something else without resetting the machine.

    I’ve also created infinite loops by accident at various jobs. Those experiences have left me a little concerned about any loop that doesn’t have a defined time to end. I usually use a technique similar to Aaron, ensuring the loop ends. If needed, I add a call that can repeat the entire process if more data needs to be updated after the current loop ends.

    There are plenty of people who spotted the error in the code and think they wouldn’t create that problem in their looping code. However, I think few of us work alone on code for an employer, across time. Often someone else comes in to refactor or “improve” our code. The next person that adjusts this loop might join back to the main table and not realize they are creating an infinite loop. You’d hope they’d test this and find the problem, but I continue to see lots of “little fixes” deployed without being tested.

    This is one reason I want a DevOps style process for database code, complete with automated testing. If you write a piece of code, you are responsible for including a test in the pull request so that the CI system can verify your code passes the test. This isn’t a perfect system and it might not prevent all bugs, but at least a test will exist and someone can then check both the code and test and perhaps use the issue as a teaching moment.

    We try and avoid loops in SQL Server, but that isn’t always possible. As with most techniques in programming, we should strive to learn the best ways to use them, know the weaknesses, and avoid building code that has major flaws now. Maybe we also ought to add that we should ensure our code doesn’t lead less experienced developers in the future to make simple mistakes. I’d say the 1=1 looping code does this, and I agree with Aaron; it should be avoided.

    Steve Jones

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

  • Delaying Code Execution with Waitfor–#SQLNewBlogger

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

    One of the rarely used commands for me is the WAITFOR command. This is a command that intentionally introduces a delay in the execution of your code. I sometimes use this when I need to pause code for a brief time, but I never remember how this structure works.

    Hopefully this quick post helps me remember this in the future.

    WAITFOR

    This command does what it says; it waits for something. You have two choices here in what to wait for: a period of time or a specific time. The structure of the command is:

    waitfor <type> <time>

    The type can be either of the keywords TIME or DELAY. I often use DELAY, which is a period of time to wait for. When you use TIME, the execution stops until that specific time of day.

    As an example, if I want to pause code for 5 seconds, I use this:

    WAITFOR DELAY ‘00:00:05’

    If I run this in SSMS or ADS, the query time for this will be 5 seconds. The default is ‘hh:mm’, so remember that if you want seconds, you need to include the hours and minutes.

    For time, the parameter is a datetime format, so enter this as the time you would want to start code execution again.

    Practical Usage

    The main place I used this recently was in this code:

    EXEC msdb.dbo.sp_start_job @job_name = ‘Second Job with Two Errors’, @step_name=’Fourth Step’
    WAITFOR DELAY ’00:00:02′

    I was testing some job tracking, and needed a job that failed. It usually runs in less than a second, but without the delay, sometimes the failure isn’t picked up from the job history table.

    This is the type of place, often in testing or in some dependent process, where I want a delay.

     

    SQLNewBlogger

    This post too my about 10 minutes to write. I couldn’t remember how WAITFOR works, but SQL Prompt helped. As I worked through my testing, I stopped and took 10 minutes to write this up.

    You can do the same thing. Show your example, and how you use it. Be creative and impress someone who might read your blog before they interview you.

  • 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.