Voice of the DBA

Author: way0utwest

  • Daily Coping 31 Aug 2021

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

    Today’s tip is to give others the gift of your full attention.

    We have a team meeting every couple weeks. We update each other on things that have changed, but it’s not a heavy concentration meeting. However, I’ve been making it a point to close applications on other screens, put my hands on a cup of coffee or glass of water, and just pay attention to others.

    It’s not always that interesting and it was a struggle at first, but I am working to be more present for the time I spend with others, rather than multitasking in some way.

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

  • Eyes Wide Open

    Not many of us work in startup environments, but many of us do work with new databases that are created for new applications. These might be carefully designed, thrown together, or your database might be constructed by an ORM. In any case, I find many people make decisions and write database code for today, solving the problems that they see in front of them. they often do this with little data and a single system. They might have their eyes on using some new technology, and they decide on a data strategy without really considering what they will need later.

    That seems to be what happened with Expensify, which started in the financial industry. Their system had requirements for low response times, multiple locations, and detailed logging. This required a robust database architecture, which turned out to be helpful when the company pivoted to a new business model. Their CTO talks about some of the problems he sees with startups making database decisions. I think many of these lessons are helpful for all organizations that are trying to ensure their database can grow and meet their needs.

    I think one of the most common problems I see is that developers and leaders get enamored with new technology. There is a lot of promise in some of the platforms and designs that are being put forth. Some are even proving themselves in high-profile situations, but not all. For most of us, however, we aren’t going to be solving the same problems in the same way. As the article notes, we’re not Google, but we’re also no Uber, Facebook, or Spotify. Choosing to mimic their choices because of their success doesn’t necessarily map to our business model. I find no shortage of companies that struggle to adopt some new platform because they built a proof-of-concept and assumed the way the system works with small amounts of data. This becomes an issue later with the moderate or large amounts that they have over time.

    I also see companies creating complexity, with the chance that they will need to deal with many petabytes or exabytes of data at some point. Face it, most of us will barely deal with terabytes of data in any particular system. We ought to plan for a high-performing system at that scale, not worry about a future that will not likely come. At the same time, we aren’t going to be dealing with megabytes of data, so if your developers only test on MBs, they are going to miss problems.

    I like the advice to go into your decisions with your eyes wide open. Don’t copy others, and realistically think about what you will need. I believe that engaging a data professional early is helpful. Developers do some amazing things when they build software, but so often the majority of them don’t really think about the challenges of a database system. They don’t consider low response times or ensuring there are HA and DR (two separate things) strategies. They also forget about the challenges of aggregation and reporting lots of data. Most humans work with a few rows of data at a time, which is what developers do on their systems. When you need to aggregate things, or all your customers are generating a workload, that’s when a data professional can help ensure you’ve properly indexed entities and planned for a demanding workload.

    I do like the common sense advice that most startups won’t outgrow a relatively modest single database server. Many applications might not as well, but that doesn’t mean you can put all your eggs in that one server basket. Make sure if it dies that you have multiple people that can recover it and ensure your system is quickly running in another place. There are different ways to handle this, but engage someone that knows your platform and have them ensure you have some staff, operations or developers, that understand how HA and DR work in your environment.

    Lastly, be secure. I really like the idea of always using stored procedures. I know this becomes a pain for developers, who now write code in two places, but this really helps you ensure better security, and maybe more importantly, ensures you can tune one part of your code regularly, the database side, without impacting the other side.

    Steve Jones

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

  • Daily Coping 30 Aug 2021

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag. 

    Today’s tip is to donate some items to a charity.

    I know this happened a lot last year. The local Goodwill stopped taking some things because so many people have up some of their possessions.

    I’ve had a few things ready, so I’m giving away some bikes that still work, but the kids don’t ride anymore. I’m also giving away a few more dress clothes. I haven’t worn in years, they’re in good shape, and they don’t fit me. I rent suits now.