Voice of the DBA

Tag: T-SQL Tuesday

  • Reading Blogs: T-SQL Tuesday #204

    This is an interesting invitation this month. Jeff Ianucci is a new host, and also has agreed to start taking over some of the T-SQL Tuesday management. Mike Walsh and StraightPath Solutions will steward this, but Jeff will likely do a lot of the day-to-day (or month-to-month).

    In any case, I hope you support him and more of you participate, even if it’s a post on LinkedIn.

    This month Jeff asks about blogs, which is kind of like a circular reference. Blogging about blogs. Reminds me of GNU.

    In any case.

    The Blogs I Read

    Jeff asks is blogs are still relevant and I’d say yes. They contain information lost in the AI summaries, so I encourage you to read them.

    I run SQL Server Central, and we syndicate blogs here (ping the webmaster if you want to join). I add some of those to the newsletter, and read most of them. I especially love seeing Brian Kelley’s posts.

    The others I read, with a little why, are:

    • Brent Ozar Pro blog – He always has interesting stuff in here, often things I didn’t think about.
    • Ozar.me – makes me think about life. Brent’s personal blog
    • Erik Darling – I learn about performance here. I don’t use a lot, but to me, this is good background to keep in mind as you look at other code, or think about patterns in development.
    • SQL Authority – I see Pinal on X, and when I do, he sometimes a post catches my eye, so I’ll go look.
    • SQL Server blog – I catch up on stuff here
    • StraightPath Solutions – One of the few we don’t syndicate that still updates

    For most of these, I don’t subscribe or read every day. Mostly I catch posts on social media, or while compiling the Database Weekly newsletter, but I find this content more helpful for me to be interested in tech and grow than AI stuff or many random posts.

    Keep blogging, and keep reading blogs. It’s good exercise for your mind, and it’s a great way to improve your communication skills.

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