Voice of the DBA

Author: way0utwest

  • Column vs Row

    I’ve been working with databases for a long time. They’ve always been relational databases to me, unless they were a key-value, document, or NoSQL class of store. A few years ago at the Pass Summit, David Dewitt gave a keynote on changes to storage that Microsoft was implementing, talking about columnar storage.

    At first the structure felt confusing, but as he proceeded, it started to make sense. We don’t like SELECT * for many reasons, but one is that lots of unnecessary data gets moved off disk, into memory, and across a network. This is the nature of a row based store, which is what we usually have in relational databases.

    The columnar store puts all the columns together. The row values from different columns are separate, but if you are aggregating values in columns, the columnar store works very well.

    So well, that we have columnstore indexes in SQL Server, which copy your data into a column-oriented format. While this might seem wasteful, you decide what gets copied, and you get the benefits of this format, which dramatically speeds up some types of queries.

    The reason we have both stores is that we have a need for both to fulfill different query needs. I wouldn’t keep both stores for every table, but for some, it’s the best way to ensure your clients don’t spend a lot of time waiting for results.

    I see more clients using columnstore indexes, and I was still seeing some sessions, but not as many as a few years back when the technology was new. If you’ve never tried building a columnstore index, this might be something you experiment with in development systems and understand how this can change your query performance and storage needs. We have a great Stairway Series to get you started, so take some time this year and read through it and practice the examples.

    Steve Jones

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

  • Daily Coping 11 Feb 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 make an effort to have a friendly chat with a neighbor.

    Most of my direct neighbors, at least those whose property is adjacent to mine, keep to themselves. We all have our own tasks and work, and we’re rarely outside. Unlike previous places I’ve lived, I can’t call out to someone if I do see them outside, as they’re 1/4 mile or more away.

    There are also few of us. My road, which is a cul-de-sac, only has 5 properties on it, and I rarely see cars going to and from any houses.

    However, recently I was in the feed store getting a few things for my wife and I did see a neighbor in the aisle I walked by. I stopped for a few minutes and chatted, asking about his kids, who I hadn’t seen in years.

    It was a nice catchup, and a welcome break from the routine of sitting at my desk or quickly getting in and out of stores.

  • Daily Coping 10 Feb 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 send someone a message to say how much they mean to you.

    For many people, this is likely something with family or close friends. It certainly is for me, but I like to also reach out to others at times.

    Today, I sent a note to a friend that I’ve known for many years. Someone I’ve seen at events, and someone that has been to my house. Not many can say that. However, I wanted to just tell this person that I’ve enjoyed their company and thoughts in the past, but during this pandemic, I’ve appreciated and enjoyed the things they share about their life, that lets me keep in touch with them and remain somewhat close, even though we’re separated.

    Touching the important people in your life is incredibly helpful to coping, and to enjoying your life.

  • Anyone Can Click Through Once

    I was watching some of the Virtual Summit conference content (which is still available) trying to catch up on a few sessions that I missed during the event. One of the sessions I watched was a PowerShell panel. In the panel, Ben Miller (b | t | L) said that “anyone can click their way through something once.”

    I agree with that. It’s really easy to run through something once, even a long wizard or install process. Most of us find it easier to just get something done than work on a script to complete it. Often, I find myself doing this for tasks that I tackle rarely, like installing SQL Server. With the growth of containers, I rarely install SQL Server anymore, but the few times I might want a VM outside of a cloud system, I find it easier to just run the install than find a script and alter the settings.

    However, there are tasks I need to repeat. Anything that I might need to run more than once or twice, I usually do think about scripting. I look at T-SQL and PowerShell, both of which are very useful, but have different times and places where they fit better. The key, however, is that running a repeatable process is easier when it’s scripted.

    This is true whether you are doing something for yourself or for your employer. When you have to repeat a series of steps, it’s too easy to forget one or make a mistake. Maybe more importantly, as more companies adopt DevOps automation and pipelines, the need for command line automation and logging is critical.

    One of the panelists noted that they think PoSh is critical for your career and wouldn’t hire someone that didn’t know the technology. Not everyone agrees, but the tremendous growth of systems, the need to often deploy changes using automation, and the likelihood that you will see PoSh on the Microsoft platform, I tend to think this is something employers will strongly desire.

    Steve Jones

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