Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #203: Risk and Reward

    This is timely, as I’m currently on a boat in the Adriatic taking a break with my wife. I think I’m in Croatia (or off the coast) today, but who knows. Any who cares.

    In any case, the invite this month from Pat Wright is an interesting one, asking about our design decisions and the risk vs reward calculation that we might make. I’ll give you a few thoughts and tie this in to my time away.

    Evaluating Risk

    Most of the time that I have had to consider risk and reward in the past relates to the effort to fix a problem. Not necessarily the time, or data loss, because those are factors that are secondary to me to the ability to get a fix in. Time can matter, but if the organization has a tolerance for time, then I can work with that.

    This also goes to whether the organization is willing or likely to try to fix things, or even if they are willing to let me fix things. If not, then I am way more careful about how to approach a design decision.

    This also means that when a design decision is made, I’m cognizant of timing. For example, I’d be hesitant to implement some changes before I left for vacation, or approve those that others made before they left. I’m out of touch for two weeks, so last week I wouldn’t have started a AG seeding, or added a new node unless everyone on the team was confident they could handle a change.

    A few examples.

    We were considering replication to move data to a reporting system instead of using an ETL process. The idea was that this would be automatic and ensure that data was readable elsewhere. I get that, and it makes sense.

    However. In this case, we realized that too many of the schema changes we made would likely break replication. Since we were evolving the schema regularly, we’d be stopping and restarting replication, or more likely, tearing it down and rebuilding it. This would be more disruptive, and less reliable than ETL.

    Even though ETL was a time sync to update. At least with ETL, we could keep existing data moving, and only new things needed to be updated.

    A second case was looking at an EAV table for storing attributes in an application. This was pre-sparse columns, where the data modeling showed a need for many disparate types of attributes for an entity. However, we realized that the business was pivoting and hoping to win business. As a result, for many of these attributes, we didn’t necessarily expect many of our customers would need them.

    To put this in context, we wanted a secondary contact for some customers. We also wanted a picture for others and a default location for some. For the main table storing customer things, we didn’t want to keep adding columns that might be mostly null. As we experimenting with a few of these items, we realized that relatively few (< 5%) of rows would even have data.

    What’s more, these weren’t items that were queried often. They didn’t populate a screen for each customer, but were used in narrow cases, queried rarely, and were of disparate types. We could have had a few extra tables specifically for purpoases that were included joins for that data, but that created the complexity of knowing where these items were located. Our data model would have grown quite a with many small linked tables.

    We decided EAV was better, but sql_variant was not. We stored an attribute type and the data as string, as that’s most easily converted to numeric or date as needed. We also included a column that noted the stored type, so it could be handled by the application. The risks of lots of small queries here were outweighed by a simpler, expandable data model, for lots of rarely queried data.

    This worked well, and this table was very rarely queried. Performance wasn’t impacted much, and complex areas that had to query this table multiple times for many attributes just absorbed the hassles of many queries. Again rare.

    Risk and reward is often clear, but it’s always worth a little time to evaluate your decision and decide which choice makes the most sense for now, and for the foreseeable future.

  • T-SQL Tuesday #202: 100 Hours

    It’s time for T-SQL Tuesday again and this is a great prompt to start writing. I might have written about this before, but it’s a story that sticks with me. It’s a memorable one, and it was quite the outage, one that lasted for weeks.

    Thanks to Marlon for this invite on unforgettable outages..

    T-SQL Tuesday is the monthly blog party, and if you want to participate, feel free to write on a blog, Linked In, or anywhere else and link your post to the invite. If you’d like to host, ping me. You need to write an invite and a recap. I’m always looking for people to host, so jump in and join the party.

    New Year’s Eve

    I was a relatively new hire, a former intern, at a large electrical utility in Virginia. I worked as a network admin at a nuclear power plant in Surrey, VA. I showed up at work on New Year’s Eve at 5:00pm. We were planning on deploying a new database server running SQL Server, along with a new application to track radiation exposure for workers. This was a mandated change to our tracking, which needed to go live at midnight. I was supposed to be a bystander, helping developers from our internal group implement the server and then take over administration for the future.

    New hardware had arrived, and we had installed OS/2 1.3 already. This was the “hot” new OS to us and to the plant, which would run SQL Server 4.2, a real database. Instead of the previous BTrieve database on our Novell network. The developers installed SQL Server, showed us how to add logins and users, and we verified this was running. The devs then went into the plant (with escorts) to install their software on various machines with radiation badge readers. These machines were the “clients” that connected to the new database server to look up user names, verify their total exposure, and record new exposures.

    We finished installations well before midnight and then went to eat some food. At midnight, we turned the old system off, and the new one went live. This was accomplished by stopping one application on the client machines and running a new one. A nuclear plant is busy, with people constantly moving in and out of spaces where they could be exposed outside the reactor. We saw workers getting badges, logging the device to their account and then entering the plant. Quick and easy.

    After 30 minutes or so, some workers would return from the plant, run the app, log in and have their badge scanned, recording their exposure. Many were 0 rads, but a few might have picked up minor exposure.

    Then the crash. Once we started getting a little busy, with 5 or so people logging in the application stopped responding. I worked with the devs in the server room, where we saw the new OS/2 server hung. We rebooted it, thinking something went wrong.

    This happened again.

    And again, and again.

    We ended up seeing either the entire server hung, or SQL Server not accepting connections. In the v4.2 version, we didn’t have a Dedicated Administrator Connection, when the server was hung, we could only see external, OS-level counters. Our network staff (4 of us) stayed until around 4am, at which point my boss sent a few people home. They had families, so he and I remained behind. We alternately caught naps and were on call to reboot the server. Sometime during the morning the plant rolled most people back to paper, manually tracking workers and exposure on cards, one per worker. We kept one machine on to keep testing. That worked, but when a second client was added, the server kept hanging.

    My boss and I stayed, trying all our tricks, reading lots of OS/2 and SQL Server  docs, and trying to help developers figure things out. The plant wasn’t that busy on Jan 1, but busy enough that their system kept freezing up. Lots of arguments amongst our staff, our managers, and the software development group. I stayed overnight, rebooting servers, napping in the server room (a cold room) with a hat and coat. When the “normal” day started on Jan 2 and coworkers showed up, I went home.

    I returned late afternoon to relieve the other staff, working through the night again, mostly rebooting a server and trying to find a cause. I learned about the “probe” user, which was a backdoor account used for accessing data. I never found a reason why the application didn’t work as expected, other than the developers didn’t really test with multiple clients.

    In any case, it didn’t matter. I worked 100 hours that week, from Dec 31-Jan  6. Since I was being paid hourly, I was happy to do so. My partner, less happy.  I continued working over 80 hours every week that month. The developers stabilized things enough for the system to be used, but it still was rebooted every few hours. Since the plant runs 24×7, that meant the 5 or 6 of us needed to ensure someone was there all night long.

    A profitable month (and the next), but a hard one.And an unforgettable outage.

  • T-SQL Tuesday #200: When I Look at a Query …

    This month is a milestone for T-SQL Tuesday. It’s number 200, which doesn’t sound big, but this is a monthly party (started by Adam Machanic). We have 12 blog party events a year. 200 means this has been running for almost 17 years (16 years and 8 months).

    I don’t care who you are, that’s impressive. Think about where you were, what you were doing, and what was happening in 2009.

    I haven’t been running it that long, but I am glad I took it over from Adam and have kept it going. Thanks to everyone who writes and reads the posts and especially the hosts.

    I tried to get Adam to host, but he declined. Fortunately Brent Ozar stepped up with a great initiation. My response below.

    At First Glance

    There are two things that immediately stand out to me when I see a query and create concern.

    1. cross joins
    2. functions in the where/on clause

    While there are other things I might see, these two stand out and usually I can guess there will be issues.

    For cross joins, I don’t see this as much when people use SQL Prompt or some other helper because they tend to use inner/left outer/right outer explicitly, or cross join. If you explicitly use a cross join, I might ask why, but these clauses require an ON clause, which means you’re deciding to join tables.

    Where I see people using old style joins, like this:

    select *

from a, b

where a.id > 23 and b.saledate > current_date

or (a.id is null and b.saledate is null)

    I get worried. This happens in Oracle, and PostgreSQ, and it’s easy to forget to join a and b, especially when there are multiple tables. Usually cross joins happen with legacy join conditions.

    The other area is using functions in the WHERE clause. A common example is

    select *

from customer

where upper(customername) = ‘Steve’

    This function in the WHERE clause ruins the ability to see the data. The index is something like (‘Adam’, ‘bill’, ‘Steve’, ‘WILLIAM’). This can’t be used when the UPPER is applied. This often results in more reads, more scans than a system might otherwise take.

    There are plenty of other issues that can indicate performance issues, but these two are the ones I’ve often run into and the ones that would have helped 2004 Steve write and review better code.

  • Un-Migrating From the Cloud: T-SQL Tuesday #199

    This month we have a very interesting invitation from Koen Verbeeck. He has hosted once before, and agreed to help me out this month by tackling another topic. We’ve shared a few beers in the EU, though not in some time, so I hopefully will get the chance to buy him a pint and thank him for this month’s invite.

    He was inspired by another friend, Alexander Avidsson, who wrote about skills and the cloud, but with an interesting take. This month’s invite is about moving back on-premises from the cloud, which is something I’ve had a few customers do, or start doing. Nothing is quick when migrating systems, either to or from the cloud.

    Here’s my take.

    How Easy Is It To Un-Migrate?

    I decided on a fun title here, since so many people talk about migrating to the cloud. Is going back on-premises an un-migration? Or a re-migration? Just a migration? I won’t worry about the semantics.

    I work with a lot of different customers at Redgate Software. Whether they are discussing development topics (Prompt/Toolbelt), deployment issues (Flyway), production stuff (Monitor) or compliance (all of the above + TDM), I find that most of them are still rooted in an on-premises mindset. Even when they’ve migrated to the cloud, it’s often lift-and-shift, with VMs running in AWS/Azure/GCP. In that sense, they still tend to manage things in an on-premises way.

    They’re more comfortable with those skills and more confused by cloud auth systems, still. In 2026.

    I think that’s still the norm and it’s easy to think that most people work in the cloud all the time. Some do, and I think most people have some familiarity with one of the major cloud systems, but I would expect that lots of people would love to come back on-premises.

    Is it easy? No. It’s another migration, and while you might find it easy to re-provision hardware (whether purchased or rented from another provider like Rackspace) there are still major data movement challenges in the db world. How do I sync systems? Can I get log backups? What type of quiescing and cutover is there?

    The tech stuff, matching versions, etc. is likely easy. After all, most of us don’t use the latest and greatest functions from SQL Server, so moving back to on-premises likely just works. If you are in the IaaS world, this is simple, other than the latency of copying data down (and explaining the egress charges).

    I actually think customers might gain some efficiencies from moving systems with known workloads and costs back on-premises. Especially those that create lots of tickets for DBAs or developers. Any time spent moving back will come back in the skills that so many have with on-premises systems.

    The one downside I think might cause some issues is HA. It’s easy in the cloud, and hard on-premises. Those are skills that some people likely need to brush up on if they don’t have a significant HA footprint with VMs.