Home Uncategorized The Guru (T-SQL Tuesday #089)

    The Guru (T-SQL Tuesday #089)

    2205
    20

    T-SQL TuesdayI became a consultant a bit earlier in my career than was probably wise. The going was rough at first, with periods of feast and periods of famine. Although I had several clients, I didn’t understand how to sell services or schedule my workload, and so I’d wind up either doing huge amounts of work for a few weeks, or absolutely nothing some other weeks. The topic of this month’s T-SQL Tuesday is “Database WTFs” and it was during one of my periods of relative inactivity that I met Jonathan at a local user group meeting, which resulted in one of the more memorable WTF situations of my career.

    Jonathan was a few years older than I was. Most of the user group attendees wore jeans and sneakers. Jonathan had on a nice looking suit and shiny shoes. He had a smooth demeanor and introduced himself as “a consultant and SQL guru.” Literally, with that exact phrasing. We struck up a conversation and he told me about all of the amazing work he was doing with various customers. I was extremely impressed. Jonathan represented the consultant I thought I wanted to be, a bit further down the road.

    I told Jonathan about my struggles with finding steady work, and he immediately offered to introduce me to a company he’d spoken with, CorpX, Inc. He explained that they were a growing company and needed lots of performance tuning help. He was just too busy on other things to give them a hand, but maybe I could. I eagerly agreed, and he did an e-mail introduction the next day.

    I don’t remember the initial conversation with CorpX, nor whether there was any kind of interview, but we signed a three month agreement and I told my customers I wouldn’t be available for a while. Before I knew it I was nervously walking in to a strange office for my first day on the job. Once in the company’s space, the receptionist told me she wasn’t expecting me, but that I could sit down and wait while she sorted things out. She offered me a magazine, but only after 20 minutes of waiting did I actually pick it up and start flipping through the pages. The receptionist told me that she thought I was supposed to meet with the CTO, but he was busy, so I should keep waiting. An hour later I’d read the magazine cover-to-cover, so I picked up a second one.

    At some point, a few magazines in, a guy stopped by to chat with the receptionist. He was a some sort of IT person, and she asked him if he knew when the CTO would be available. “Oh, he went for coffee a while ago.” However, the guy had heard that I was supposed to be there that day, and was able to lead me to my space: A cubicle in a dark corner of the basement. Just like Milton’s. He left me there to stew, but it didn’t last long. I had no login credentials, nothing to do, and was tired of wasting my day. So I tracked him down again and asked him to introduce me to the DBA.

    The DBA was a red-faced guy named Dave, and he seemed to be in a state of panic. He spoke quickly and told me he didn’t have time to deal with me; he was in the middle of something way too important. His large office was perfectly clean and ordered, and I clearly recall the three huge monitors set up on his desk (well, huge for the time). A complex ER diagram covered all available screen real estate. I told Dave that I could possibly help out with his SQL Server problem, and he replied that there was nothing for me to do. He deeply involved in designing the next version of the database. He couldn’t possibly think about anything else. He asked me to leave his office.

    Vintage Baseball BatsIt was around noon, so I left for a while to get lunch. When I returned to the office, this time I was marched directly to the CTO. “Where have you been? I’ve been waiting for you.” The CTO was a middle-aged guy with a beer gut and, apparently, an extreme baseball fetish. His office was filled with baseballs, helmets, jerseys, and bats. He absentmindedly played with a bat as he talked to me, lifting it and bringing it down on his desk when he wanted to emphasize a point. “We hired you as a pinch hitter. We retired our SQL Server team a few weeks ago, because I finally came to the realization that SQL Server can’t meet our performance demands. It’s like the database farm team, you know? We’re moving to Oracle in six months. That’s a real database! Until then, though, we need to play hard and score one for our customers, and that’s where you come in. You’ll turn our screwballs into fast balls. Well, as fast as that crappy thing can go, am I right?”

    I asked him if he was sure that SQL Server was at fault. Even though I was still fairly young, I had already worked on some pretty big projects. And I had a number of very experienced friends, including some well known MVPs who talked about their work on gigantic systems. “Yes, we’re sure. We had Jonathan in here, and he is a SQL guru. He told us that SQL Server simply does not scale.”

    Part of me wanted to get up and run, but I was intrigued. I decided to treat it as a challenge. I would prove him wrong and save the company millions on its Oracle transition. Maybe I’d score a steady long-term client in the process. I asked for access to a test system and to the source control repository, and the CTO gave me a blank look. “You’re here to performance tune our production database.” There was no need for a test environment, he told me, and he wasn’t aware of any database code in source control. He handed me a printout of a network login and password and told me that I had plenty of access to “work some magic.” I asked him which part of the application to focus on, and he told me to work on any piece I felt like; they were all equally slow. He concluded our discourse by saying that he was a very busy person and as a consultant I was expected to figure things out for myself and not bother him or his staff with details. I was to come back and talk to him when I had made some progress, and not before then.

    Back in my basement hideout, I spent the next couple of hours exploring the network and figuring out which server to connect to. The CTO was right; I did have enough access. I was sysadmin on the production SQL Server and had full admin access to the app server. I logged in to the app and with the help of a Profiler trace managed to figure out one of the main slow stored procedure calls that occurred any time someone saved a change via the user interface.

    Pasting the procedure call into SSMS, I turned on Actual Execution Plan, hit F5, and got ready to see indications of a few missing indexes. I was ready to walk back upstairs, gloat to the CTO, and ask for a better workspace so I could continue to help. What I didn’t expect was what actually came back: Not one execution plan, or two, or three, but hundreds and hundreds. The scroll bar become progressively smaller as time clicked by and the elapsed counter did not stop running. All I’d done in the application was change the name of a single field. What was going on?

    RecursionI opened the stored procedure and it was a simple update against a single table. But after poking around in Object Explorer I discovered that the table had four different insert and update triggers. Each of the triggers created a cursor over the inserted table, and for each row therein called one or two stored procedures. Each of these stored procedures was seemingly more complex than the last, with increasing amounts of dynamic SQL and nested cursors. And each did updates or inserts to at least one, and usually several tables. As I chased down those tables I saw the same pattern again and again: More triggers on more tables, with more cursors and more stored procedure calls, with more dynamic SQL and more cursors and more stored procedure calls. I began recursively searching for the bottom of it all, but gave up once I hit 9 or 10 levels. The tables were named with some sort of scheme involving various abbreviations, but with no documentation or support, I had no clue what was going on.

    Getting concerned about my ability to unwind this mess, I looked around the database and discovered that the same pattern was used on every single table. I ran some counts and found around 400 tables, 1200 triggers, and several thousand stored procedures and user-defined functions. Not to mention a massive number of views, all extraordinarily nested.

    The best part? Every single module in the database had a dated copyright notice, containing my friend Jonathan’s name. He had apparently built the entire thing himself over the prior four years, no doubt billing some truly obscene number of hours. And then he had gone ahead and signed his work. To this day I still wonder about the legal implications of that copyright notice. What does it mean to copyright a work for hire that contains logic specific to someone else’s business? And why did CorpX allow this in its code? Over all of that time did no one else ever look at the database?

    I wandered upstairs to ask the CTO what he expected me to do with such a massive mess, but the office was empty. Sitting in the basement, I’d lost track of time, and had spent four or five hours just researching that one initial bit of code path. It was late in the evening, I’d fixed nothing, and I knew that I wouldn’t be able to work any magic. I realized that the guru had already tricked us all, and I decided to cut my losses. I packed up my things, logged out, and left the building. I emailed the CTO that night and politely told him that the engagement wouldn’t work out. I never billed for my day, and he never replied to my note. I looked up Dave, the DBA, on LinkedIn a few months later. He was now “database architect” for a different company. Years later I met someone else who worked for CorpX and found out that they were still, miraculously, in business. Perhaps in the end they did find a magician; I was told that they were still running SQL Server.

    Copyright JonathanI ran into Jonathan one other time, and he asked me why I never thanked him for getting me into such a sweet gig. I merely shook my head.

    (Yes, this is a true story. Well, mostly. I changed some names and details to obscure things a bit. But this is pretty much how things happened. I learned a lot that day!)

    Previous articleSolving the Net Changes Problem with Temporal Tables (T-SQL Tuesday #087)
    Next articleThe SQL Hall of Shame
    Adam Machanic helps companies get the most out of their SQL Server databases. He creates solid architectural foundations for high performance databases and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to numerous books on SQL Server development. A long-time Microsoft MVP for SQL Server, he speaks and trains at IT conferences across North America and Europe.

    20 COMMENTS

    1. Great story, until 80% of the article somewhere in me was hoping for a happy ending "Adam played the magic he is known for"
      Thx for sharing…

    2. @Prakash
      If only I knew a trace flag to fix such a crazy design, I would make a lot of money applying it for customers 🙂

    3. One Of the most enjoyable stories i’ve read in a while!  Jonathan, perhaps like Kaiser Soze, had this all planned out just for you….

    4. Hahaha, I thought the same thing as Prakash. I was waiting for the line that said, "so I fixed this one line of code, and…"

    5. Interesting story!  Out of curiosity, do you think if you came across that sort of situation today that you would be more inclined to persevere and try to sort through it all, or would it just be a case of it not being worth the amount of time and effort required?
      I enjoy a challenge and all, but that does sound almost insurmountable.

    6. @Paul
      Good question. From both a communication and confidence perspective I am today much better equipped to handle such difficult customers than I was when this situation took place.
      As for actually doing the work? I’m not sure. Getting that thing into shape would have been a truly massive undertaking. Basically a complete re-write, and I think it would have taken 2+ years of concentrated effort, both for me and the company’s developer team.
      I am happy to help customers who need that kind of work, but success in such an endeavor requires buy-in and good will on both sides. Were I treated like garbage from day one like that, no way would I stick around to do it.
      Looking back I feel like I absolutely made the right decision to run.
      –Adam

    7. I think I’ve met some of their subsidiaries ;/
      Just when you think you’ve heard every crappy client plus SQL Server story!
      Thanks Adam!

    8. That might be the WORST database design story of all time.
      It’s at times like that when I wonder what it was about "Jonathan" that convinced the CTO he was so skilled? I’m not certain, but most people I’ve met with even rudimentary certifications know better than to use that design approach…
      Thanks for sharing!
      -Kev

    9. @Kevin
      “Jonathan” is an incredibly smooth talker. Very skilled at selling himself. There is a LOT to be learned from him… just not technology 🙂
      A friend of mine read this post over the weekend and knew right away who I was talking about. He emailed me and told me that he worked with Jonathan a bit more recently and had a similar situation. Jonathan is the kind of guy who wants to use every possible feature in every possible way and that’s why he comes up with these crazy and convoluted designs. My friend told me that in his case it was a huge SSIS package that used every kind of lookup, transform, etc, available. Luckily it was just a smaller component of a larger project so no major harm was done when it buckled under its own weight. My friend told me that by simplifying it and ripping out all of the complexity they made the entire process several orders of magnitude faster. Great lessons to be learned there as well, about the nastiness of over-architecture and the fact that quite often less equals more … it’s just unfortunate that some people will never learn.
      It’s funny you mention rudimentary certifications because I think that many of the Microsoft tests are geared for exactly what Jonathan does: Cram in as many features as possible. Maybe certifications are, in fact, the problem! 🙂
      –Adam

    10. Nice one,Thanks for sharing.particularly this comment is good..
      "am happy to help customers who need that kind of work, but success in such an endeavor requires buy-in and good will on both sides. Were I treated like garbage from day one like that, no way would I stick around to do it."

    11. Adam,
      thanks for sharing this experience. I think the greatest take away I got was that it’s OK to say no and not feel bad about it.
      A lot of times I feel like saying no means I am not as competent as I want to be. But it sounds like saying no can also make one much more competent rather than fighting with a broken process or system.
      Especially if one is being treated like garbage. That would not have been good as a confidence builder. This would have been counter productive in building confidence.
      Good call and thanks again for sharing.

    12. Jonathan is not a particularly common name in my neighbourhood, so I thought first you were talking about Jonathan Kehayias from SQLSkills. But it’s probably some other Jonathan 🙂

    13. @Andrej
      LOL! No, not him at all! I wish I would have had him to work with instead of the real guy. Would have been an entirely different experience.
      –Adam

    14. "Re-sverved" is a term concerning car traffic and accidents, right? Similar to skidding and sliding? And what happens when "All Rights" hit a wall due to excessive sverving?
      (Is that screenshot actually real or…?)

    15. Yeah, why didn’t you thank him? You got great article for the blog and a great experience that you will never forget.

    Comments are closed.