Voice of the DBA

Category: Editorial

  • 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

  • A New Community Summit

    The PASS Summit has been an annual event for a lot of my career. I attended the first one in 1999 and most of the rest through the years. After last year’s virtual Summit, I was concerned that we wouldn’t have an annual event where the most, brightest, and friendliest data platform people came together. Redgate purchased the assets out of the PASS bankruptcy and has committed to future Summits. This year’s Summit is virtual, and more importantly…

    It’s free, and you can register today

    Free to attend the main conference, which means you have the opportunity to go to the largest Data Platform focused conference for no charge. Just ask your boss for a little time to learn, get inspired, and interact with your colleagues around the world.. There are still pre-cons that cost money (US$200), but all the proceeds go to the speakers. A great opportunity to learn from experts and support them at a cost you likely won’t see at a major event.

    I don’t love virtual events, and I do hope that we get back to live conferences, but I do like that everyone can come to the Summit this year without asking their employer, or their household, to fund the effort. Microsoft is a major sponsor and will keynote at the event, likely with some exciting SQL Server and data platform news coming. Lots of other sponsors will be there as well, which I’m pleased about because sponsors are an important part of events and user groups in our community.

    If you want to know what types of sessions are coming, the best of Summit 2020 is available for you to watch, again for no cost. This year there are three themes, Back to Basics, Reach for the Clouds, and Level Up. I’m glad for the cloud track as the cloud has continued to grow faster than I expected, with so many friends, customers, and clients looking to move portions of their data workloads into a cloud situation. I’m also glad to see the career advancement Level Up track becoming a place of focus, as actively managing your career is something I feel strongly about. Hopefully, I’ll get to deliver a session in that track. As of the time I’m writing this, I know I have one session, but not sure if the program is complete.

    While Kathi and Grant are managing the process, community volunteers are reviewing, rating, and making choices about sessions. That’s one thing I’m glad to see. There is a strong community effort in helping to program the conference. While I know paid professional staff organizes things and configures the platform (we contracted with a few people that have helped run the conference in the past), it’s the community that should really help decide which speakers and sessions they want to see. I’m hoping that some of the fun things like networking, the photo walk, and more are a part of the event.

    There’s no good reason to not attend the Summit this year. Unless you’re buried with a deadline the week of Nov 8, 2021, ask your boss for some time, register, and come join me in watching some sessions, chatting with old friends, making new ones, and getting excited about what you can do with data in 2021.

    Steve Jones

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

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

  • The Slow Upgrade of SQL Server Versions

    Every so often Brent Ozar releases a report of his Constant Care customers, showing the versions in use. While I don’t know this client group is representative of the overall SQL Server customer base, I do think it’s close enough to draw some rough conclusions.

    The top version adoption graph that the summer 2021 report shows has 33-34% of the installed based on 2016, about 34% newer (and under support) versions, leaving the last third running older versions. However, the graph I found interesting is the lower one, which shows the distribution over time.

    What I see in this graph is that the older versions (2008-2014) held about 45% of installs in Q1 of 2020. Now they have 31%. The 2017/2019 versions went from 19% to 34%. Those are fairly close numbers, which indicates that a fair amount of these older versions were possibly replaced with newer versions. There could be brand new instances as well as retirement of old ones that weren’t replaced, but in general I’d think this shows that the ten-ish year lifecycle of an instance is probably a good guess. This is about 12 years for 2008, and 7 for 2014, but that’s around ten.

    I tend to think that many of us dislike upgrading our instances across major versions too often. While some DBAs might want to, there aren’t often good business reasons for doing so, and many DBAs are just busy. Taking time to upgrade major versions for the same application, especially on the same hardware, doesn’t always seem worth it.

    In my experience, business people want a long lifetime for a major version. It’s just too expensive and time consuming to both doing this too often. I’ve also found after a couple years, DBAs know the ins and outs of a version, what works well, what tricks are needed to keep things tuned and running. Not to mention that a workload on one version isn’t always faster, and sometimes it’s slower, on a new version. There are definitely DBAs that might not want to bother changing versions and potentially having issues with their current workload from changes, like the change in the cardinality estimator in 2014.

    With modern hardware, for most applications, I think that many instances can run for 10 years. This might be even more true when we factor in that many database servers are virtualized, which means adding a few more cores or RAM is much simpler than in years past.

    How often do you upgrade versions? I’m sure some of you do this regularly, and I know some applications might benefit from upgrades more often then every decade, but I bet a lot of you don’t bother to change this too often. After all, “working” is a feature, and if you have that one, why take chances?

    Steve Jones

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