Let me Try this… in Prod

A more light-hearted look at things today. There are certain phrases or ideas you hear that should give you pause. There’s the classic, “here, hold my beer and watch this.”

And of course what I heard yesterday while on a call with a client and their developer said, “well let me try this, what’s the worst that could happen?”

Just the other day, fellow #SQLFamily member David Klee tweeted:

A software vendor just told my client to restart their SQL Server after every backup. I am beyond speechless.

I read that tweet and literally sat there slack-jawed for half a minute. I swear I felt a disturbance in the Force as a million DBAs cried out in terror.

But this got me thinking of other bad advice I’ve seen over the years, such as “we reboot our SQL Server nightly because it has a memory leak and uses all the memory on our server. Oh and by the way, can you tell us why our server is so slow in the morning?” (Ok the 2nd sentence is partly made up, but I’ve had clients complain about performance issues which were due in part to them restarting their SQL Server.)

Or, “don’t index, keep everything as a heap.”  Yes, I saw that someplace, I’m still not quite sure the reason they had for that.

“Oh, we had a problem with this stored procedure sometimes running really slowly, so we hardcoded WITH RECOMPILE in. Now it runs consistently.” Fortunately by the time I had arrived they had stopped this particular process, instead they just had  scheduled task that recompiled it at least once a day. This one was interesting. After determining a couple of performance issues with this sproc, including parameter sniffing and using SQL Server 2005 XML parsing in it, I developed a far better solution that eliminated the parameter sniffing and in most cases eliminated the need to parse the XML at all. The client didn’t adopt it. A year later, a DBA brought in for another project took a stab at this sproc and came up with a similar solution (though he didn’t reduce the XML parsing like I did.). They didn’t adopt it. Finally, over 2 years after my initial recommendation, I was able to convince them to implement it.

“Oh, I have covering indexes for every column and include all the other columns!” Ok, I haven’t seen it quite this bad, but I’ve seen indexing that approached this level.

“We use NOLOCK, because it’s faster.” This is a common one. Now, I’ll admit years ago on a platform we built we did use NOLOCK because it was faster, BUT we also actually understood what it could do and honestly didn’t care about inconsistent results (we were serving up classified ads, so if you saw a different set on a page refresh, it was actually a useful side effect.)

In general, I find bad advice or bad ideas fascinating. I love to understand how they came into being. In some cases, like my employer’s use of NOLOCK, it was actually a conscious choice and we understood the drawbacks and accepted them. I wouldn’t necessary call that a bad idea once all the particulars were known. But on the face of it, it certainly looked like a bad idea, and that was SQL 7.0 and later SQL 2000. With more modern versions of SQL Server, I would argue there are betters solutions now.

In the case of my former client doing the RECOMPILE, that’s more subtle. Yes, their solution worked, but, it was clearly the wrong solution because they didn’t understand what the problem was, or how to fix it properly. So I’d argue this was a bad idea.

But when it comes to restarting SQL Server after a backup, I really still have no words. It’s not clear to me at all what problem the vendor thought they were solving or why this would solve it. This truly is a bad idea all around.

Fortunately, in the particular case of my client and their developer, the worst was, we’d resend 25,000 rows of data to Salesforce to be updated. That would take 2 minutes and not break anything. He knew this and was joking, but it was funny to hear.

So my question to my readers is: what’s the worst idea or advice you’ve heard and did it in retrospect have enough context to perhaps at lease explain why someone came up with it, or was it simply so bad you’re still shaking your head? This doesn’t have to be SQL related.

P.S. – my next Redgate article should be published soon. Keep your eyes open for it!

Covid Challenges

There’s no doubt that Covid-19 has had a huge impact on our lives. Professionally for example it means a change to a virtual SQL Saturday and a Virtual PASS Summit. It means some of my fellow #SQLFamily have gone radio silent for various periods of time as they’ve dealt with this crisis in their own fashion.

I know personally there are days I just want to go outside and scream. There’s so much disruption in the world and in my life. I miss being able to travel freely, to see as many friends in person as I’d like and so much more. I mourn the loss of schooling as we know it for my kids and everyone else’s kids.

20200313_183236

Early on during the pandemic – bare shelves where toilet paper and paper towels should be

But, I’ve also been very fortunate. I’ve had a few friends who have contracted Covid-19, but all have survived (though as we’re learning, surviving may include long-term impacts such as irregular heart rhythms, changes to mental status and drug uptakes and more). I know of one former co-worker who succumbed to the disease when it was in the middle of the NYC spike. Other than that, fortunately, the only deaths I’m aware of have been friends of friends. This doesn’t make the disease any less tragic, but just a bit more remote for me.

But, the above could be said of most of us and rather than focus on the negatives, I wanted to talk about some of the changes in my life this year and how I’ve tried to rise to the challenge. Before I do so, I want to be clear, that how one rises to the challenge is different for everyone and this is not meant to be a brag sheet as much as a statement of what I see as some positive things in my life. I’d love to hear some positivity from YOUR life in my comments. We all know how bad this year has been, let’s talk about some good stuff for a change.

Backpacking

One of my goals for decades has been to hike the Appalachian Trail. No, I can’t say this was the year. But, I had section hiked a portion from my Dad’s house in CT up through Dalton MA while in college and then a year or two later from Bennington VT up through Manchester VT (I’m still grateful to the poor soul who picked up my friend and I have 2 days of sweaty exertion without clean clothes!) But this meant there has been a gap between Dalton MA and Bennington VT.  Last month I was able to FINALLY put a backpack on and close part of that gap.  I’d love to post photos, but silly me left my cell phone in my car! So here’s one after I got back to my car.

Do I look tired?

After hiking 17 miles during a heat wave

I hope to get in the final 20 mile stretch in the coming weeks. This will mean that I can check Massachusetts off my list of states to hike for the AT.

Sourdough

Ok, show of hands, who here has dabbled in sourdough during this pandemic? I know I have. I kept a starter going for about 4 months before taking a break from it over the summer. I made a number of loafs of bread as well as some sourdough waffles (and I’ll admit sacrificed the first batch more than once to the Waffle-Iron gods.) I even added a bit to some homemade pizzas. Tasty stuff and to be honest, I’ll probably do another starter again come fall. I’ve always loved baking so this was just an extension of it.

20200505_120934

Roast Beef Sandwich with homemade sourdough bread!

20200725_190323

Two sourdough pizzas with home grown herbs

More Time with Family

Of course making that much pizza and waffles means I need someone to help eat it. Fortunately we have a full house this summer (and will this fall). So an upside has been more time with the family. Among other things, this meant, especially early on during the pandemic, more family walks in the area.

20200411_145101

Family walk near the house with leaden skies

Bicycling

Of course all that delicious food I’ve been making needs to be burned off. I’ve literally biked more this year than the last few years combined. As of today, that means over 850 miles for the year. This includes a 55+ mile ride this past weekend. I’ve really been enjoying it. I’ve been a bicyclist my entire life but have missed riding this much. I’m hoping next year to upgrade my road bike (open to suggestions) to replace my 30 year old Trek 520.

20200809_134643

You mean I still have to bike to the top of THAT? 20 miles in and about 7 miles to go, but that’s where all the altitude gain is!

20200809_142741

“It looks like we made it!” – after the climb

And I’m still hoping to complete my first century ride in over 3 decades (that makes me feel old!)

Speaking

On one hand I’ve done far less speaking for SQL events than most years. I believe SQL Saturday Albany was my first SQL event this year. But I’ve been asked to present virtually at 3 NSS Grotto meetings on “So this is your first rescue.” I’ve also been selected to speak at PASS Summit for the first time, so even though it won’t be in person, I’m excited! I’ve also volunteered to speak at the Denver User Group meeting on September 17th.

Webinars

I’ve taken advantage of the fact that so much is now virtual and attended some Red-Gate Live webinars, a few SQL WIT webinars and others. One piece of advice I’ll give here, if you can, attend what you can. You no longer have to be physically present for most SQL User Group meetings, I know several #SQLFamily members who have attended 2-3 User Group presentations in the same week! It’s one advantage of everything going virtual!

Virtual Get-togethers

I, and in some cases the rest of my family, have Zoomed with my mom, my aunt and others and almost weekly, with members of my #SQLFamily. It’s been a great uplift to see so many folks.

What’s Next?

I’ll admit, it’s been a different year. We had to postpone our NCRC National Weeklong Training Seminar until 2021. But, I just got approval to host a Modular Level 1 this fall. I’m still not sure we can pull it off, but if we can, it’ll be great.

I’ve really missed seeing a lot of folks in person.

Covid still looms large in my planning of travel and events. I don’t know what the next 6-9 months will bring, but I know I’ll try to make the best of it!

What about you?

What’s something positive you’ve been able to accomplish during this pandemic? I want to hear it!

And Remember

20200809_161758

We could all use a little support!

The Year So Far

Today happens to be the last day of the month and the last day of the quarter. And according to my calendar, it’s the 4th Blursberyday of the month of Holiecouw.

I decided to take a look back at my first post of the year: 2020 in Preview. Wow, a lot has changed in a scant three months. I mentioned I was reading Station Eleven. It’s set in a post-apocalyptic world after a world-wide flu pandemic. Little did I know at the time I’d be living that reality a scant 3 months later. Ok, this is not nearly as bad as in the book, but it does give on pause to think. We are living in a time of upheaval and it will be interesting to see how this current pandemic changes social structures for coming years.

I wanted to speak at SQL Saturdays. Well, almost every one I’ve put in for or was planning on putting in for has been cancelled or delayed. So much for that goal. On the other hand, members of the #SQLFamily have been holding Friday afternoon (and other times) Zoom hangouts as sort of a morale boost. So I’ve actually gotten to know a number of my fellow DBAs and fellow speakers, so that’s better.

Fortunately, I’m still working. As a consultant, you realize every meal may be your last meal, so you keep working at it and hoping more meals are coming your way. So far my biggest client shows no sign of slowing down, nor does my second largest client. I’ve been fortunate, I know a number of folks across many industries who have been hit with a temporary or even permanent job loss. This is going to be hard for many.

But, I’ve also been taking the time to do more webinars. Last week I sat in on a Redgate webinar on the state of DevOps that was quite informative. The next day, Kendra Little (also of Redgate) gave the WIT webinar and also talked about DevOps. Both were quite informative and I learned a lot. I look forward to the upcoming Redgate Streamed event.

I’ve been using git more and more. I started using it integrated with Visual Studio about two years ago I think. But, after seeing my son working on a project where he was using it at the command line, I decided it was time to start to do that and now for one client that’s my de facto way of checking in and out changes I’ve been making to the PowerShell scripts I write for them. Next up, more version control for the SQL Scripts. I’ve already written a small deploy script I use to deploy scripts and changes and more importantly to log them. So while that client hasn’t really adopted DevOps, I’m doing my part for my small corner of work.

My next goal is probably starting to learn how to use Docker more. Cathrine Wilhemson’s blog post on that has convinced me it’s time.

And I finally finished binge-watching Haven.

So, the last few weeks haven’t been exactly what I planned for, and the upcoming months won’t be what I planned on either, but it hasn’t been a terrible time. What about you?

P.S. While out biking the other day, a thought dawned on me. Many post-apocalyptic books (such as Station Eleven) have characters using cars, but more like carts, either pulling them themselves or with horses because once the gas runs out, you can’t make more. But I got wondering how having a large number of electric vehicles would play out in such a world. Yes, much of the infrastructure would be gone, but even if you had to carry panels with you (much like Mark Watney in The Martian) you could probably be far more mobile. Hmm…

It’s Easier the Other Way!

This is what someone said to me while biking up a hill the other day.

I have a certain bike route I do that includes stopping at the supermarket along the way. It’s just over 5 miles. Obviously over the course of the entire route I return to the same place I started, but the topography varies along the way. But the simple fact of the matter is that the supermarket is at a lower altitude than the house, and about 2/3rds of the way along the route in the direction I go.

So when I tell people about biking this route, I point out that it’s sort of a double-whammy. When I get to the lower point in the route, I go shopping. To finish my loop, my bike is often heavier with groceries and I have all the altitude to gain back (which admittedly isn’t much, a bit over a 100′) in a short distance (maybe 2000′).  It was along this section that my erstwhile adviser offered his advice.  My reply of course was that “true, but home is that way!”, I said pointing up the hill.

Unfortunately, sometimes one can’t do it the easier way. One has to do it the hard way.

But, this leads into something else I wrote on Quora.com: Do Bad Programmers Know hey write bad code? I only partly addressed the question, but to sum it up, I think the worst don’t, but the best programmers do, and sometimes intentionally.

The truth is, we probably should always be writing the best code we can. It should handle error trapping and handling. It should validate inputs. It should fail gracefully.

But often, we need a one-off script. Something that gets the job done here and now.

I recently did a 5-minute lightning round for my SQL User Group on the benefits of using PowerShell. I took to quick and dirty scripts I had written and rewrote them a bit for the presentation.  Afterwards, one of the attendees asked me a few questions about my stylistic choices in the code.  He was right in general, but I pointed out what my goal was. My goal was more to show what PowerShell could do than to actually show how to write good code in PowerShell.  That said, I probably should have written slightly better code, but this got the job done. It definitely didn’t need error handling and the like. It was good enough.

And ironically, this post is sort of like that. (I love it when I can get meta on my own posts). I have about a dozen drafts I have saved in WordPress. Most have just a title and a quick set of notes on what I think I should write about. This post was mostly written and just needed a bit more to flesh it out.  It was easier this way than trying to come up with a new topic for this week. Hope you enjoyed it. (and to keep things even easier, I’m going to let WordPress use a random photo for it!)

Snow Daze

I’ll be honest upfront. I have a love/hate relationship with snow. I love the sounds of a snowstorm. I don’t mean the wind if it’s there, I mean the quiet hush that can settle over the land, a muffled calm. It’s peaceful.

I love the starkness it can leave behind, everything white with a gray or black background. The lack of color is beautiful in its own way.

I love how it slows down life for a bit. You can’t go fast. You have to stop and take a measure of the moment.

And, I hate shoveling it. Sure, it’s a good workout, if you’re fit enough and not at a risk for a heart attack. But, it can be a pain. I drive a Subaru, so often I only shovel the end of my driveway where the snowplows leave their pile of detritus as they go by since once that freezes in place it can become an implacable wall. But this snowstorm, I did the entire driveway. It was deep enough I thought it prudent.  And it’s not just the weight of the snow that’s the issue. It’s moving it to a place that’s out of the way.

I’m a person who actually loves the seasons. I love how they mark the inexorable passage of time.

“One Christmas was so much like the other, in those years around the sea-town corner now, out of all sound except the distant speaking of the voices I sometimes hear a moment before sleep, that I can never remember whether it snowed for six days and six nights when I was twelve, or whether it snowed for twelve days and twelve nights when I was six.” – Dylan Thomas A Child’s Christmas in Wales

I’ve been alive for over a half-century now and at times it seems the years fly by faster. The snippet from Dylan Thomas reminds me so much of Falls Village CT, the town I grew up in. In some ways it never lost a certain Victorian charm.  We were part of the modern world, but separate enough that we were ensconced in a tiny bubble. In the winter, I’d lay awake on a snowy morning hoping to hear the snow plow go by.  We’d build forts in the snow and have epic snowball fights. We’d come in sopping wet and warm ourselves by the wood stove and drink hot chocolate and share epic tales amongst ourselves.

When it got a bit closer to Christmas we’d go to the nursery and buy a 14′ tall tree, because we could.  We’d decorate the first 8′ and then above that, the best we could.

It’s not Christmas yet here, but at GMS Headquarters it’s slowly arriving:

20191203_094342

Christmas Lights

And soon I’ll start listening to Christmas music.

And I’ll try not to think about the next round of shoveling I have to do.

I’d love to sit around all day sipping hot chocolate and doing nothing, but there’s work to do and eventually more snow to shovel.

And I can hear the occasional plow go by, getting the roads fully clear.

All is well here at GMS Headquarters.

 

Sleep, Perchance to Dream

I’ve skipped two weeks of blogging, which is unusual for me. The first time I’ll admit I was simply too tired after flying back from SQL Summit in Seattle. That, combined with catching up on work for my largest client meant I simply didn’t have time. And last week, well Monday night I was sure I was coming down with the flu and had a terrible night’s sleep and was in a brain fog all day Tuesday.  Now I have no idea if it was the flu (I tend to doubt it) but by Wednesday or Thursday I was feeling a lot better.

And, then last night, I barely slept either. So suffice to say, I’ve had sleep on my brain a lot lately.

I find sleep and dreaming to be fascinating aspects of evolution. When you stop to think about it, unless you’re an apex predator, sleeping would appear to be an evolutionary poor choice in many cases. Depending on the animal, it can spend anywhere from 2-3 hours asleep (perhaps broken up over the course of the day) to 20 hours.

And in fact, predators often tend to sleep more, which conserves energy, while prey tend to sleep less (so they can more easily flee said predators).

Some animals in fact are capable of unihemispheric sleep, i.e. only half of their brain appears to go to sleep. In fact dolphins in pods appear to sleep such that if they’re on the outside of the pod, the side of the brain that goes to sleep is opposite of the eye on the outside of the pod. They literally sleep with “one eye open” looking for danger.

Yet, despite the risks to prey, they still sleep. It seems pretty universal and something that as far as I know, all vertebrates do to some point. So it seems pretty necessary. And we’re learning at least in humans that chronic lack of sleep can lead to issues such as dementia later in life or even a shortened lifespan.  It seems the phrase, “you can sleep when you’re dead” tends to mean that your death may come even sooner if you chronically undersleep.

And when we sleep, we don’t just basically stop interacting with the outside world, we create a fantasy world inside our heads. I’m a person who can often remember his dreams and they tend to  be vivid and rich in detail. When dreams incorporate elements of places I’ve been or seen I’m not surprised. But then occasionally I will dream of places I know I’ve never been, houses I’ve never been in, landscapes I’ve never seen in such detail. It amazes me that my brain can, to use a computer term, render such rich detail in what appears to be real time.

In any event, right now, sleep is on my brain, but work calls.

Kids, get off my lawn!

Change can be hard. But sometimes it’s necessary. And a lot has happened this week.  First, I want to congratulate my fellow #SQLFamily member Cathrine Wihlemsen on one more orbit of the Sun. Apparently, in her honor Microsoft decided to release SQL Server 2019 on her birthday! I’ve been using SQL Server since the 4.21a days. Every version has had new features and required learning something new. As I said recently, it’s easy to fall into the trap of being an old dog and not learning new tricks. This is something we have to avoid. Being trapped in the past can be limiting.

Besides SQL Server 2019 dropping this week, I recently upgraded my phone. I had been using a Windows Phone for about 5 years now. I loved it. Especially when it first came out, it was top of the line and had a bright future. I eagerly downloaded apps and it became part of my life. But alas, we know how well Microsoft did in the Windows Phone market. But I doggedly held on, even as features were deprecated. I couldn’t use the Weather App. The Amtrak App went away. Eventually several features of Cortana stopped work as Microsoft stopped supporting them. Slowly my phone was becoming a brick. I kept debating do I upgrade to one more Windows Phone knowing it’s the end of the line, or what? I kept putting off the decision. After the mapping function failed me on my recent trip to the Hampton Roads User Group Meeting I decided it was time to finally time to replace it with an Android phone. Choosing from the plethora out there was not fun. It was very tempting to go with one of the top of the line models, but spending $1000 or so wasn’t really a fun idea.  I eventually ended up choosing a Samsung A50.

I’m mostly happy with it. Right now I’m struggling with what parts of it are “get off my lawn” because I don’t like change, and what parts are “what the hell is the UI doing now?”  Fortunately, my son has mentioned some of his dislike of certain UI functionalities, so I think not all of it is me simply being an old curmudgeon (are there young ones?) I will say what I’m most happy with is that Microsoft has a number of tools including the Windows Launcher and the Phone Companion, as well as the obvious apps like Outlook and other parts office.

A word about the Phone Companion. This alone has made the upgrade a win. One of the features is that when I’m working at home (I have not yet enabled it on my Surface Pro) is that things like text messages pop-up on my desktop screen. This actually makes life a LOT easier, since I can simply type a reply from a full-size keyboard or copy the numerous soft-tokens I get to log into various client sites without having to pick up my phone. It’s a small detail, but a wonderful one!

The Launcher helps me retain some of the features that I liked about my Windows Phone. Overall, it’s a win.

But the changes in my life aren’t complete. As I mentioned last week I’m at PASS Summit again this week in Seattle. But alas, this is the last year that PASS Summit will be in Seattle. Next year it will be held in Houston. Just as I’ve figured out where the cheapest and most convenient parking for me is, where some decent food places are, and I’m feeling, if not at home in Seattle, at least comfortable, next year is a big change. I won’t be able to stay with my college friends or do our annual Thai pot luck with a bunch of ROC Alumns.

But, I’ll get to explore another city. I’ve been to Houston only once, literally decades ago, to do SQL Server install at Exxon. The server was literally the only Intel computer in a room full of mainframe equipment. I suspect that has changed since then.  That was one of my early experiences installing SQL Server (4.21a for the record).

So, this old dog is still learning and looking forward to new experiences: plus ça change, plus c’est la même chose.

 

Small Victories

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

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!

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!