Voice of the DBA

Category: Editorial

  • Five Minute Refactoring

    I’ve listened to, and been a part of, no shortage of arguments about the technical merits of a particular programming solution. I’ve seen two (or more) developers argue about the way to solve a particular problem for hours, even days at times. The Internet is full of discussions and debates in forums on the “best” way to write code for all sorts of specific issues.

    These arguments are sometimes referred to as religious wars for the devotion and belief that each proponent has in their solution. I tend to try and avoid becoming embroiled in any sort of zealotry as I am always cognizant of the fact that I need to be effective. I need to get things done and can’t spend a lot of time arguing about a solution, whether for or against it. i need to get work completed.

    When I was managing people I tried to limit the devolution of my staff into a never ending argument, but I ran across a better solution recently. Al Shalloway wrote a blog about limiting discussions to five minutes. He notes that two level-headed people ought to be able to come to a consensus in five minutes. Beyond that he says it’s likely that one of them is arguing about covering possibilities rather than current realities.

    That’s interesting, and like many rules, I’m sure there are exceptions. A manager can make a decision to continue a discussion if they really think that it is warranted. However as a default, the idea that a team should choose the easiest solution to implement if they can’t decide makes sense. It’s the least amount of work that might be wasted, and we want to avoid wasting time and work when we can.

    We won’t always agree, and despite our best intentions, we will make mistakes and we will make poor decisions. Don’t let indecision continue indefinitely and don’t rashly make decisions, but don’t let the fear of mistakes prevent you from choosing a path. Be decisive and move forward, adjusting as you learn more that helps you refine your solution.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • The Challenges of Space

    It wasn’t that long ago in my career when a survey showed the vast majority of SQL Server databases were < 10GB in size. These days I’m sure the average has risen as there are some very, very large SQL Server databases. I know a few people managing PB sized systems, which certainly will move the average upa bit. It’s likely, however, that the majority of databases are still very small, powering smaller applications with < 100GB.

    However almost every databases continues to grow, and as systems grow, new challenges come into play. While we can still store a 100GB backup file on a thumb drive or SD card, it’s still a significant amount of data to write, which can take more time than our clients would like. Archivals, backups, restore, all tend to come from non-flash media, so larger systems certainly mean longer RTOs if we have issues, even if we’re just restoring a copy of a database to extract some deleted information.

    However there can be more fundamental problems. I ran across a post from the CSS Engineers at Microsoft that talks about exhausting NTFS resources as more and more writes occur on a filesystem. I’d never heard of this, or expected it to be an issue, but apparently it’s occurring more often. I suspect even the NTFS developers hadn’t considered the scales to which we probably stress the file system daily. What is interesting is that this can occur with DBCC operations, database snapshots, and even bcp operations. There are workarounds, and certainly I’m sure that Microsoft has people working hard to increase the capacities of ReFS, which I expect will take over from NTFS, just as NTFS has largely displaced FAT.

    As we grow to larger and larger scales of data sizes, with multi-terabytes on laptops and petabytes on servers, I think we’ll need to adapt a number of our techniques and operations in the future. We might end up needing different types of backups (anyone considering lookup table backups or daily backups of a set of rows, like orders?), as well as the possibility of continuing to spread the load of our server across multiple drives. I just hope that Microsoft recognizes this and looks to ensure mount points work smoothly across all of their technologies and features. At some point, 26 drive letters won’t be enough for the majority of us.

    Steve Jones

  • The Rename Game

    As I’ve been talking about database development, Continuous Integration, and Continuous Deployment, I keep hearing complaints and concerns about the problems that occur when we make database changes. In particular, I often hear about breaking changes made to databases, that require some manual scripting work.

    It seems that the most common breaking change is adding a not null column to a table with existing data, but certainly there are other problematic issues. The other common change is renaming objects. In the past, I’ve rarely renamed anything once I’ve deployed tables to production, but I’ve run into quite a few developers that note they regularly change names. This week I’m wondering how common this is for most of the data professionals out there.

    How often do you rename objects?

    It could be tables, views, columns, constraints, whatever. If you rename anything in your database, I’m curious if it’s a regular event. Is this an event that causes you issues with your software development process, let us know. I’d also be curious with how you manage tracking and deploying the code from development through to production when you are making these types of breaking changes. Are things still scripted and able to be automated or do you force manual steps.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 1.9MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Practical Hekaton

    I got into a debate recently with someone about the In-Memory OLTP tables (formerly Hekaton) feature that was released with SQL Server 2014. This person asked if anyone was actually using the technology, given all the limitations that were placed on these structures in this first release.

    I’ve had quite a few people actually get upset with Microsoft releasing a “feature” that doesn’t just work as a replacement for the regular tables in SQL Server. They feel that Microsoft should have spent more time working to make the transition to these tables seemless, perhaps not releasing in-Memory tables until SQL Server 2016.

    I feel differently. I think that Microsoft should get the technology working and stable, and then release it. Even if it has limitations, I’d like to see an implementation available early and give companies the chance to see how it performs in real world workloads. If you can’t workaround the limitations, then you can’t use it, but then does it matter if the feature is available in 2014 or 2016? I’m not sure it does.

    We would really like to publish more guidance here, and help people understand the feature better. If anyone is out there actually using Hekaton in production, or has a development project underway that should get to production soon, we’d like to contact you and work out a way to publish information. You’re welcome to write an article, or we can write one for you with a little interview time, either way is fine; we’d just like to get some practical, real world information out for everyone to learn from.

    Please contact us if you’re using In-Memory OLTP tables and natively compiled stored procedures.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and LibSyn.