Voice of the DBA

Tag: T-SQL Tuesday

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

  • T-SQL Tuesday #198–Change Detection

    This month we have a new host, Meagan Longoria, who graciously agreed to help me this month. I’ve known Meagan for a number of years and she’s been a person whom I’ve asked questions about data visualization and analysis in the past. I was slightly surprised by the topic this month, but only slightly. I’ll write my answer below, but if you want to try blogging and host a future month, let me know.

    Change Detection

    The topic this month is change detection, which is important for efficient ETL work, but also for other areas, such as auditing. I haven’t tended to work in high volume systems where we did a lot of ETL and needed to very efficiently detect changes. Most of the time I’ve had ETL pipelines they were busy, but not excessively so.

    As a result, in the past, I’ve often used a roll-your-own approach in the SQL 6.5->2008 era. As I’ve worked on those systems, we’ve usually used a simple update or modified date in the table that tracked when something was altered. By knowing the last time a pipeline ran, we could gather all data from that point forward and extract it.

    We used a similar approach to send a lot of emails from SQL Server Central years ago. That can work well, and as long as you track the last execution of your pipeline process, whatever that is, you minimize the data being transferred.

    A Modern Approach

    I ran into dbt a few years ago and did a one day class on how it works. It was interesting to me, and I could see the appeal. Recently I had a conversation with John Miner, who’s used it in his Fabric Modern Data Platform series. I would be very tempted to use dbt, in conjunction with a modified date as described above, to ETL data around today.

    However.

    I have been seeing that Change Data Capture (CDC) is being used by lots of products these days. It’s behind the Fabric mirroring, Oracle has used it for years, when you look to move data into Databricks, CDC is common, it seems like it’s everywhere. 

    If I were going to be regularly moving data in 2026, CDC seems like something I’d experiment with and test, since it’s a known technology that works across many platforms and there is a lot of knowledge out there on how it works. AI can certainly help with experiments, and with understanding the overhead on your system, because there is some overhead.

    I haven’t looked at the new Change Event Streaming, though I’m always wary of anything that limits me to one cloud. The more generic CDC, with AI assisted configuration and maintenance, seems like a better approach.