Voice of the DBA

Category: Editorial

  • The Growth of Terabytes

    Years ago, fresh out of university, I joined the IEEE. As one of the benefits, I got their magazine, with various articles that might expand my knowledge. In one issue, I remember reading about the challenges of video on demand. At the time, calculating what it would take to store digital movies and broadcast them to each TV seemed like an impossible task. This was in the days if hardwired 10Mbps Ethernet and dial-up Internet.

    Not too long ago I had some Internet bandwidth issues, and got some stuttering during video meetings. I run Rainmeter as a skin on my PC, and once I got things working, I was curious what sort of bandwidth I was using. These days, if I’m not doing any streaming of audio/video, my PC seems to need about 1-3kbps to keep Tweetdeck, Outlook, Slack, etc. going. Youtube seems to get bursts to 1.5Mbps every few seconds, but plenty of low < 100kbps times in between. Netflix seems to be a steady 2Mbps to get video.

    All of that doesn’t seem to add up to much for my household. Even when 5 of us lived here, with lots of streaming from the kids, our symmetrical 15MBps connection worked fine. I don’t know what our total usage was for the month, but I doubt we used 1TB a month. Actually, if I calculate it out, full use of 15Mbps for a month is about 4TB, so maybe the kids got there?

    Apparently, more and more people are getting there. A report shows that 14% of some weighted set of subscribers use over 1TB a month. This is over their broadband connection, and likely leaves out mobile usage. While lots of this usage and bandwidth is transient data, like streaming movies, this also means that some data professionals, and network professionals, need to manage the data that flows.

    For many data professionals, this isn’t necessarily a problem, and in some sense, this is good for us. Heavy usage pushes the network staff to increase the speed and capacity of links. That means those of us transferring data (CSVs, backups, etc.), can get our jobs done faster.

    So watch more movies, stream more music, and root for the carriers to increase speeds and capacities.

    Steve Jones

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

  • Looking to Role Models

    I’ve spoken at many conferences and user groups over the years. These have been one of the more enjoyable times in my career, and I encourage people to give it a try. You may not like it, but it can be exciting, rewarding, and certainly helpful in your career.

    One of the things that helped me start speaking and gain confidence that I could do it was seeing other people I personally knew deliver a presentation. They were a role model, and they gave me the confidence to start.

    In a few weeks, the Women in Technology virtual group is putting on a Data Platform WIT Day, with a lineup of speakers that are covering a wide variety of topics, and are great role models for anyone, but especially women in our industry.

    Many of us wander through our careers, and often look to others to help inspire us to set, work towards, and achieve goals. The SQL Server community has shared, inspired, and supported each other in a way I haven’t seen in other areas. I am glad to see that we have different groups that continue to do this, even in this difficult pandemic time.

    Steve Jones

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

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