Monthly Archives: October 2019

Small Victories

Posted on by
3

Ask most DBAs and they’ll probably tell you they’re not a huge fan of triggers.  They can be useful, but hard to debug.  Events last week reminded me of that. Fortunately a little debugging made a huge difference.

Let me set the scene, but unfortunately since this was work for a client, I can’t really use many screenshots. (or rather to do so would take far too long to sanitize them in the time I allocate to write my weekly blog posts.)

The gist is, my client is working on a process to take data from one system and insert it into their Salesforce system.  To do so, we’re using a 3rd party tool called Pentaho. It’s similar to SSIS in some ways, but based on Java.

Anyway, the process I was debugging was fairly simple. Take account information from the source and upsert it into Salesforce. If the account already existed in Salesforce, great, simply perform an update. If it’s new data, perform an insert.  At the end of the process Pentaho returns a record that contains the original account information and the Salesforce ID.

So far so good. Now, the original author of the system had setup a trigger so when these records are returned it can update the original source account record with the Salesforce ID if it didn’t exist previously. I should note that updating the accounts is just one of many possible transformations the entire process runs.

After working on the Pentaho ETL (extract, transform, load) for a bit and getting it stable, I decided to focus on performance. There appeared to be two main areas of slowness, the upsert to Salesforce and the handling of the returned records. Now, I had no insight into the Salesforce side of things, so I decided to focus on handling the returned records.

The problem of course was that Pentaho was sort of hiding what it was doing. I had to get some insight there. I knew it was doing an Insert into a master table of successful records and then a trigger to update the original account.

Now,  being a 21st Century DBA and taking into account Grant Fritchey’s blog post on Extended Events I had previously setup a Extended Events Session on this database. I had to tweak it a bit, but I got what I wanted in short order.

CREATE EVENT SESSION [Pentaho Trace SalesForceData] ON SERVER
ADD EVENT sqlserver.existing_connection(
    ACTION(sqlserver.session_id)
    WHERE ([sqlserver].[username]=N'TempPentaho')),
ADD EVENT sqlserver.login(SET collect_options_text=(1)
    ACTION(sqlserver.session_id)
    WHERE ([sqlserver].[username]=N'TempPentaho')),
ADD EVENT sqlserver.logout(
    ACTION(sqlserver.session_id)
    WHERE ([sqlserver].[username]=N'TempPentaho')),
ADD EVENT sqlserver.rpc_starting(
    ACTION(sqlserver.session_id)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[username]=N'TempPentaho')),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.session_id)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[username]=N'TempPentaho')),
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(sqlserver.session_id)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[username]=N'TempPentaho'))
ADD TARGET package0.ring_buffer(SET max_memory=(1024000))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

It’s not much, but it lets me watch incoming transactions.

I could then fire off the ETL in question and capture some live data. A typical returned result looked like

exec sp_execute 1,N'SourceData',N'GQF',N'Account',N'1962062',N'a6W4O00000064zbUAA','2019-10-11 13:07:22.8270000',N'neALaRggAlD/Y/T4ign0vOA==L',N'Upsert Success'

Now that’s not much, but I knew what the Insert statement looked like so I could build an insert statement wrapped with a begin tran/rollback around it so I could test the insert without actually changing my data.  I then tossed in some set statistics IO ON and enabled Include Actual Execution Plan so I could see what was happening.

“Wait, what’s this? What’s this 300K rows read? And why is it doing a clustered index scan on this table?”  This was a disconcerting. The field I was comparing was the clustered index, it should be a seek!

So I looked more closely at the trigger. There were two changes I ended up making.

       -- Link Accounts
       --MERGE INTO GQF_AccountMaster T
       --USING Inserted S
       --ON (CAST(T.ClientId AS VARCHAR(255)) = S.External_Id__c
       --AND S.Transformation in ('Account'))
       --WHEN MATCHED THEN UPDATE
       --SET T.SFID = S.Id
       --;
       
       if (select transformation from Inserted) ='Account'
       begin
              MERGE INTO GQF_AccountMaster T
              USING Inserted S
              ON T.ClientId  = S.External_Id__c
              WHEN MATCHED THEN UPDATE
              SET T.SFID = S.Id
       end

An astute DBA will notice that CAST in there.  Given the design, the Inserted table field External_Id__C is sort of a catch all for all sorts of various types of IDs and some in fact could be up to 255 characters. However, in the case of an Account it’s a varchar(10).

The original developer probably put the CAST in there since they didn’t want to blow up the Merge statement if it compared a transformation other than an Account. (From what I can tell, T-SQL does not guarantee short-circuit evaluation, if I’m wrong, please let me know and point me to definitive documentation.) However, the minute you cast that, you lose the ability to seek using the index, you have to use a scan.

So I rewrote the commented section into an IF to guarantee we were only dealing with Account transformations and then I stripped out the cast.

Then I reran and watched. My index scan of 300K rows was down to a seek of 3 rows. The trigger now performed in subsecond time. Not bad for an hour or so of work. That and some other improvements meant that now we could handle a few 1000 inserts and updates in the time it was previously taking to do 10 or so.  It’s one of those days where I like to think my client got their money’s worth out of me.

Slight note: Next week I will be at PASS Summit so not sure if/when I’ll be blogging. But follow me on Twitter @stridergdm.

Hampton Roads User Group Recap

Posted on by
1

I’ve talked about how I think it’s important to be part of the #sqlfamily community and how I enjoy talking and giving back. Last week was another example of this. Much earlier this year (it might have even been at Pass Summit last year) I convinced Monica Rathbun to do a quid pro quo. I’d speak at her user group in Virginia Beach if she’d come to upstate NY to speak at my user group. I’d seen Monica speak and knew she would be a great speaker for my group. Fortunately, despite seeing me speak, she apparently felt I’d be good enough for her group.  Seriously though it was a good deal.

My original plan had been to drive down Wednesday, address her group, stay at an AirBnB on the beach and then spend a few nights in the Washington DC area visiting with some friends.  Unfortunately, less than a week before I was ready to head down, my DC plans fell through. This radically changed my travel plans and I scrambled to make various plans to make the trip a practical one and one that wouldn’t break my budget. One of the unfortunate facts of being as consultant is that I don’t have an employer that can cover travel expenses. On the other hand, I often have a lot more flexibility in when and how I travel.

I ended up taking the train to Wilmington Delaware and getting a rental car from there. This allowed the most flexibility, was second in time to flying, and overall the least stressful. I love taking the train because I can sleep (which I did on the Albany to NYC segment) and get work done (which I did on the NYC-Wilmington segment, working on a future article for Redgate Simple-Talk and reviewing my talk) Unfortunately, due to a missed turn, some slow traffic due to the rain and then the rain in general, rather than showing up at 5:30 like I had hoped, I was in the door at 6:15 or so. This gave me time for a single chicken wing before I launched into my talk.

I had been monitoring the Meet-up page to see how many people were expected and at my last count it was 8. I was comfortable with that. I was hoping for more, but hey, I’ll take what I can get.  Imagine my surprise when I walked in and there were closer to 20 people there. Honestly, a great turnout! But, between running late, the usual hardware issues of getting my laptop and the monitor talking, and not being able to get one last run through of a 10 minute section of my talk, I’ve got to say I was a bit flustered.

I love to teach. But I would be lying if I said I don’t love it when I see or hear a student have what I call that “Aha moment!” This is that moment when you explain or demonstrate something and you can see the look in their eyes or the tone in their voice when something just clicks. It might be a small thing to you, but for them you’ve just rocked their world.

A number of years ago while teaching the Level 2 cave rescue class in Colorado, we were doing an instructor lead evolution. During these, the instructors take the lead and guide the students through the problem. It’s usually the first real new teaching experience of the week-long class, before that it’s mostly review. In this case I had a single student working with me and we were charged with setting up two lines to be used as a safety and for another purpose.  I told her to grab a single rope and a carabiner. She looked at me questioning because she knew we needed to have two lines rigged. I then showed her the tree I had selected and told her to basically double the rope, tie what’s known as a high strength tie-off using the middle of the rope, clip it in with the carabiner and toss both ends down. Then the aha moment, “wow, I’d never thought of that. That’s worth the price of the class right there.” I’ve got to say I was proud. My job was done, 2nd day of teaching. I could take the week off. Of course I didn’t.

This time around, I was talking about the Model Database and how most DBAs completely ignore it and overlook it. I was demonstrating how when you put objects in it or change various options in it (such as from Simple Logging to Full Logging) any new databases will pick up those objects or options (unless you override the options using a script.)  As I was bending over the keyboard to type the next demo I heard it, someone in the middle of the classroom suddenly said, “Woah…” and you could tell their world had just been expanded. That alone made the entire 36 hours (including travel time, sleeping etc) of the trip worth it. I knew someone had learned something. I live for those moments.

Don’t get me wrong, I enjoy getting paid as a consultant, but honestly, I speak on SQL Server topics and teach cave rescue for those aha moments, for knowing that I’ve just expanded someone else’s world a bit.

Oh that, and in this case, the free wings!

ehb5jp6w4ae8-at.jpg

Tasty wings at Hampton Roads SQL Server User Group

Just a reminder, I will be at the 2019 PASS Summit in Seattle and look forward to meeting with anyone who wants. My Twitter handle is @stridergdm and I often hang out with the folks at MinionWare (they’ve got a comfortable couch) and will be attending the Birds of the Feather luncheon (undecided where I’ll be sitting) and the Women in Technology Luncheon.

And I’m hoping for my nest article on PowerShell for Redgate’s Simple-Talk to be submitted before then!

 

He’s dying Jim!

Posted on by
4

Less than a minute after the mountain biker blew by me on the trail I heard the wail. It was scary. I raced forward with my hiking partner and very quickly came upon the accident scene. Thomas was laying in a crumpled mess, his $1500 mountain bike further down the trail with one more bend in the frame than the manufacturer had created it with.  This didn’t look good.

The hillside was steep and Thomas was on his side. I dropped my pack and went to backside of Thomas. Quickly my training kicked in. I introduced myself and asked him his name. His response re-assured me. He was breathing and had a pulse. And in medical terms, he appeared to be alert and oriented times three.

I put on my gloves (yes, I do carry nitrile or latex or material gloves pretty much anyplace I go, you should too!)  I then moved to his backside and palpated his head. So far, so good. No blood or cerebral-spinal fluid.  Since he was on his side, it was an ideal (if one could call his situation that) position for me to check his spine. Working down, so far so good until I got to the top of his lumbar portion. There I felt something very wrong.  Ok, shit just got real.  Upper torso, so far so good. Lower torso, right side, a reaction to pain. Barely noticeable, but definitely some mass internally. Again, not good. I continued down his legs. I got to his feet and normally I’d have saved this for the secondary survey, but since I already had a bad feeling and this wouldn’t take very long, I asked him to press his toes against my hands like he was pressing the gas. Nothing. I asked him to pull up his toes, again nothing.  This was not good. Same lack of reaction on the other side.  I could hear the panic in his voice, “why I can’t I move my legs?”

In all my past training they always taught us, “never lie to the patient. But also remember you’re not a doctor.” So I was honest. “Look, it could be a lot of things. I’m not a doctor so I don’t want to speculate. We’ll leave that to the professionals.” But deep down I knew. Bad tumble off a bike, bad position of a lumbar vertebra, and lack of sensation distal to that all added up to some sort of spinal injury. Would it be permanent, I had no idea.

I also checked his right arm since it was immediately available and this time came up with blood and point pain over the radius/ulna.  This matched what the accident most likely was.  He had hit a rock or something and wrapped his right side around a tree, breaking his arm, damaging his spine and most likely causing internal bleeding.

But I still had the left side to check.

With my partner’s help, we got a ground pad behind him and then rolled him very gently onto it. It’s always a risk moving a patient with apparent trauma like this but we needed to get him isolated from the could ground which was stealing his body heat and I needed to check for injuries on the left side.

Fortunately, this was the only bright news. A thorough check of his left side showed no apparent trauma. But, his shivering was getting worse and his mental state was decomposing. Whereas he was had previously been alert and oriented to who he was, where he was and approximately the time, now he kept asking the time.  Taking a set of vitals, things were not what one would like to get.

My partner was writing down all this information and giving me gear as needed. We had gotten the groundpad under him and clothing on top, but we needed to do more.  At this point, since we confirmed he wasn’t about to bleed out, we worked on splinting his arm. Providing traction in-line proved to be a bit painful at first, but ultimately gave him some pain relief and temporarily solved that particular issue. We did as much as we could in the back-country.

I took another set of vitals, and the numbers were a wee bit worse. In addition, Thomas was now wondering where he was. I repalpated his lower right abdomen and got an increased pain response and the firm area was larger. This was a very worrying sign.

While doing this, my partner ran down the trail with a copy of his notes until he got cell service and called 911. He gave them the details and then came back.  At this point, with his help we decided to get Thomas into a bivy sack to help keep him warm.  This took some effort since Thomas was bigger than either of us, fairly muscular and we were doing out best to protect his spine. But eventually we got it around him. Between this and some liquid “squirt” we were able to give him, his pending hypothermia appeared to stabilize and eventually improve.

But his vitals continued to degrade and the pain and mass in lower right his abdomen continued to get worse. He was dying and there was nothing I could do about it.

Well there was one thing I could do. I looked at Thomas and said, “well I think that’s it. Did I miss anything or do you think we got this exercise covered?”

He looked up and smiled, “Nope, I think you got it.  By the way, the biv sack really did help a lot. I was actually starting to get cold for real.” We removed the biv sack and he remarked, “Wow, you really did have a lot of warm stuff on me.”

Now, fortunately, all this had been an exercise, part of a SOLO Wilderness First Aid class I was taking over the weekend in order to renew my certificate.  The scenario was completely made up. But, I have to say, the feeling of helplessness was real.

Strangely though I’d say that was a good thing. For any skill we want to maintain competency in, we need to practice. Fortunately, I haven’t come across a crumpled mountain biker and most if not all back-country medical emergencies I’ve encountered have basically been fairly simple (an abrasion here, a blister there, or most commonly, mild hypothermia). But, continual practice does help. When arriving at the staged scene, I knew what I wanted to do and I knew how I wanted to do it and how to do it. The years of training and practice came back very easily. I knew how to do a primary survey and what I wanted to look for. I knew what vitals I needed and what trends I wanted.  There wasn’t much searching for knowledge, it bubbled up as needed. Practice really does in a sense “make perfect.”

And, even knowing that my mock patient most likely had internal bleeding that was leading to hypovolemic shock was good to know. Knowing that there was very little I could do if this was a for-real in the back-country was scary, but also strangely reassuring. I was confident that I had done all that I could reasonably do. And sometimes that may have to be enough.

I’ve talked previously about training as one fights. This should be true in any situation you may find yourself in: caving, the back-country, or even something as mundane as being a DBA. When’s the last time you practiced restoring a backup or doing a failover test?

Practice may or may not make perfect, but it does provide confidence.

P.S. if you’re in the Hampton Roads area tomorrow night (October 16th) come check me out speaking on System Databases at the Hampton Roads SQL Server User Group. Rumor has it, they’re serving wings!

NY ComicCon

Posted on by
1

Last week I talked about Kids These Days. This past weekend I went with my daughter to NY ComicCon. It was a late 8th grade graduation present she had requested. Due to me messing things up last year, we missed our chance to go, so I made up for it this year. And it was well worth it, for a couple of reasons. I want to focus on two, one topical and one personal. The topical first.

The topic is in the above photograph.  I apologize for it being blurry, “I’m a DBA Jim, not a photographer.”  But I took it for a reason. This was the panel for a talk titled: Join the Resistance! (Journey to Star Wars: The Rise of Skywalker). It was an interesting panel that talked about the books they wrote that cover the time between The Last Jedi to this December’s The Rise of Skywalker. But partway through listening, something dawned on me about the panel.  Can you figure out what I realized?

It’s there in the picture, but if not, let me list the panelists: authors Rebecca Roanhorse, Justina Ireland, Kevin Shinick, Ethan Sacks, Delilah S. Dawson, audiobook narrator Marc Thompson and moderator Ashley Eckstein.

What strikes you about that list of names? Now compare that to the panels you see at a number of tech events such as various SQL events. Note what it’s not. This is NOT a MANEL!

Science Fiction has for far too long been treated as the domain of boys and then later men. Marketing for decades often focused on boys. It was assumed that every boy wanted to be Han Solo or Luke Skywalker or Captain Kirk.  Women in shows and books were often only there as props for the male characters to react to. Granted, this statement isn’t 100% true, even Princess Leia had some meat to her character in the original Star Wars (back before it was episode IV or A New Hope.) Even then though, she served the role set out in much of mythology as the princess in distress to be rescued. Fortunately her role and the role of women in Star Wars was greatly expanded over the series, to the point now where Rey is our hero.

Ahsoka Tano in triplicate!

Ahsoka Tano in triplicate!

And this panel shows exactly how equitable the Star Wars universe has become. The moderator was Ahsley Eckstein, who voices the character Ahsoka Tano in various animated Star Wars series. Three of the authors on the panel were women. In other words, women were well represented.

Think about this when planning your tech event such as SQL Saturday. Do you have equal representation? “But wait Greg, there’s just not that women doing SQL! I only had 3 women apply to talk and 30 men!” I’m going to give you some advice. Ask for more women. Talk to those three, see if they know anyone who might want to speak, but was too nervous to put in a submission. Talk to Kathi Kellenberger and Rie Irish of the PASS Virtual Group Women in Technology.  Yes, there may not be as many women in tech as men, but I can guarantee that there’s more than you think and that it won’t change without encouragement and representation. If you as a guy get invited to speak on a panel, make sure there’s diversity. Turn down opportunities if it looks like it’s going to be a manel. Call out your fellow community members if they’re engaging in sexist behavior. It’s not always comfortable,especially if it’s a friend or a co-worker, but it needs to be done. Do your part.

If ComicCon can have an equitable panel in regards to Star Wars, you can do the same in regards to SQL or other tech panels.

Now for the personal:

Live Long and Prospoer

Autograph and picture with two amazing women, Nichelle Nichols and my daughter Rebecca.

Two amazing women: Nichelle Nichols is an amazing woman and helped represent African Americans on television in the 1960s and helped inspire people like Whoopi Goldberg and Mae Jemison. And as for my daughter, her future and journey is in front of her.  I will admit to basically being speechless in front of such an icon and here I am, still three days later grinning ear to ear thinking “I was in the presence of Uhura!”

(BTW, for those who recognize it, that’s a 1st edition Star Fleet Technical Manual with her signature. It also contains the signature of George Takei and James Doohan.)

 

Kids These Days

Posted on by
1

“The children now love luxury; they have bad manners, contempt for authority; they show disrespect for elders and love chatter in place of exercise. Children are now tyrants, not the servants of their households. They no longer rise when elders enter the room. They contradict their parents, chatter before company, gobble up dainties at the table, cross their legs, and tyrannize their teachers.” – Socrates 

We’ve probably all seen that quote in some form or other over the years. If not, it’s usually presented as a modern day rant (though given the use of the words chatter and dainties, I suspect the translation is a few decades old) and then is revealed to be close to 2500 years old.

But, it still holds true. Well, not so much the part about how terrible kids are, but how poorly many adults think of kids. How often have you heard someone say, “Kids these days, they always have their head down in their phones!” “Kids these days, they don’t read like they used to?” “The music kids listen to these days….”

The truth is, perhaps things have changed more in the past few decades than in centuries past simply because of the advances in technology. But, the truth is, in some ways, things are still. This is not to say that phones and tablets are 100% harmless (there’s a lot of evidence that for very young children they’re probably not a good idea). But, they can also be a huge impact. I have a friend whose autistic son can pretty much only communicate because of the use of a tablet. Without it, he’d be locked into a world of little communication. Kids these days…. can communicate in ways they previously couldn’t.

When I was starting college I discovered that through the local mainframe and then later the Internet (long before it was truly publicly available) I could communicate with people miles or 100s or even 1000s of miles away. I made deep, lasting friendships that way. I learned a lot. These is even more true. I know kids who have friends across timezones and countries and these are deep and meaningful friendships. It’s much like having a penpal, but basically in real-time. This has helped them develop cross-cultural understandings and learn more about the world.  Kids these days, can communicate in ways we never could have hoped for.

Just last night, on a chat system run out of RPI that I’ve mentioned, Lily, we were having a discussion about the advances in computing languages and the discussion included people from across the country, including my own son who could add his perspective from several hundred miles from away.  Kids these days, can interact with adults with decades of experience, and can provide their own perspective.

My daughter recently started a new seasonal job at a local haunted hay-ride. Granted, I’m on the hook (as is her mom) for doing a bunch of driving, but it was her initiative and work that helped her find the job and get the job. This is in addition to the school work she does, the sports and planning on helping with the school play down the road.  Kids these days, are not lazy and have their own initiative.

People talk about the state of today’s music. I’m sorry, but any generation that enjoys a song where half the words are basically “I want to ride my bicycle” or variations on that has no place criticizing the simplicity of lyrics today. For every Bohemian Rhapsody there’s a Bicycle. That was true then and is true today.  Kids these days, actually listen to as wide a range of music as we do, and some of it is actually pretty good.

Kids these days, sail across the ocean, alone, to make a plea for their future. And in return, people mock her and call her names. She’s literally asking us to consider her future and too many simply want to insult her. Adults these days, can be as cruel as always.

Kids these days simply want to inherit as good a world as we did. They’re working hard to make it a better place, but adults seem hell bent on denying them that. They’d rather smugly look down their noses at kids these days.  Don’t be those adults.

 

 

IIS FTP 530 error

Posted on by
Reply

To my usual readers, you can ignore this. This is simply my way of making sure that the next time I have to google an issue, or someone else days, there’s a better chance of finding a solution. Note, there are other places posting the same solution.

But simply put, over the weekend client’s IT group rebooted a server and an FTP process started to fail. It took a lot of digging to solve it.

It appears that two things happened:

The IUSR to the FTP root directory got munged (it’s not 100% clear and this may not be a necessary step. But, things did NOT work for me until I did this, but then they continued to work when I removed the IUSR to see if I could recreate the problem.)

The part that was 100% necessary was this: https://manage.accuwebhosting.com/knowledgebase/941/FTP-Error-530-User-cannot-log-in-home-directory-inaccessible.html go to Step 8 and add all users. Why this was removed, I don’t know. But adding this after re-adding the IUSR step seems to have solved the issue.

That said, before anyone asks, “why in the world are you using FTP in the 21st century?” I won’t disagree other than to say this is purely an internal process that simply moves some non-PII data to a 2nd server.  Not a huge justification, but there it is.