Bits are cheap

And, as unfortunately as a recent incident in our #SQLFamily community illustrated, apparently at times so is respect.  Bear with me as I relate these two ideas and another incident.

Let me start with a statement that should make more sense by the end of this post: My name is Gregory, but I prefer that you call me Greg. My pronouns are he/him/his.

But first a trip down memory lane. Many of us recall the Y2K issue. This was a direct result of programmers decades ago trying to save bytes in storage (and to a lesser extent memory and CPU cycles) because storage was expensive. By storing dates as just the last two digits of the year, they could cut the storage for years in half. This was important back then because it saved money. But, as many of us recall, as the year 2000 approached, this started to cause more and more problems. (As a point aside, the first example I’m aware of was brought to my attention by a programmer who worked for a bank in 1970. Seems as if they suddenly had issues handling 30 year mortgages!)

Since then of course the cost of storage has dropped and as an industry we’ve moved to storing years as a 4-digit year. No one in today’s day and age would normally question this decision.

But enough of ancient history, let me get to the point of this article: respecting others.

As many readers know, those of us on Twitter will often use the hashtag #SQLFamily.  In the past week I’ve seen two incidents that have illustrated the worst and the best of this family.

In the first case, a member of the community, a woman I had never met, said she was leaving the family, she no longer felt welcome. At an event she had been misgendered not once, but multiple times. For those who aren’t sure what that means, I will, without going into background or details (because they’re not important) say she is a trans-woman. Several people at the event took it upon themselves to refer to her using by male pronouns.

In the most recent case, a fellow speaker, Cathrine Wilhemsen tweeted about how she had been addressed as Cathi and Kathi twice in the previous 24 hours. She says this hasn’t been the only time, but just the most recent and recent enough for her to comment on.

In both cases, part of the problem is that strangers addressed the person in question in a manner that did not respect them; in the first case by not using the proper pronouns and in the second by not using her provided name.

But that’s one part of the problem.  So let’s address that: we have members of the #sqlfamily who don’t respect other members. But, we have another issue, and one that I think is important to address: those who minimize the issue. In the first case, apparently no one called out the folks misgendering the woman.  In a situation like this, a show of support can be as simple as saying something like, “Umm, I think you mean she, not he.”  You can also support the use of pronouns on nametags at events or in the bio descriptions for events.

Remember though, today, bits are cheap. So we can do more. Don’t design your database with a bit field for gender. Make it a table. These are relational databases after all. Have a table for possible gender identifications. Allow for a method to add rows to this table. Have a table for pronouns.  There’s more than you might think and people are often crafting additional ones. While the singular they/them is becoming more popular, it’s NOT the only alternative to he/him, she/hers.

We are data professionals after all. We absolutely should not lock our data into a single view of the world if that worldview is changing. (Note, the world is not changing, there have been multiple genders throughout recorded history.  We’re simply becoming more cognizant of it now.)

In the case of Cathrine being called by another name, keep it simple. Use the name provided, be it in an introduction, on the nametag or other method. Respect the person’s wishes. And do not, as some did on Twitter respond by “well they probably didn’t mean anything” or “eh, just roll with it.” It’s not YOUR name. It’s not YOUR identity. Sure, you might not care if someone calls you Richard, Rick, Ricky or Dick. But another person might. Their name is part of their identity, respect their wishes.  I will add one more note that Cathrine shared with me and that other women have shared with me, it is almost always men that will use nicknames or cute names or similar without prompting.  Yes, fellow men, I’m calling you out. We may not think about it. In fact I would argue we often don’t think about it. It’s something that privilege allows us. But be aware that your attempt to be friendly or familiar is actually often coming off as diminishing and condescending.

Now, despite the failure of some members of #SQLFamily, I want to celebrate the great people in the community. These two incidents have created a lot of responses. I’ve seen at least two great posts, one from Jen McCown and another from Kellyn Gorman. I’m sure there are others. I also have written in the past about being an ally. But in addition, while I’ve seen one or two tweets that have dismissed Cathrine’s tweet, I’ve seen many members rally to the defense of the women in both incidents. And, also very importantly, I’ve seen several tweets from people asking, “how can I help?” or “how can I improve my behavior?” I love that last one. I’m constantly trying to unlearn some of the behaviors I was taught and to be more conscious of what being a white, straight cis-het male brings to the table. We can always learn to do better.

Yes, our #SQLFamily has some members who could and need to do better. That saddens me. Fortunately as I’ve seen, it also has a lot of members actively striving to do better and help others do better. That gladdens me. Let’s all be the latter.

Respect and disk space don’t cost us much. Let’s learn to be respectful of people and to design databases that can also respect the world around us.

P.S. I want to note, I was purposely vague about the first incident because the specifics weren’t important and I did not want to draw more attention to a specific person without their permission. In Cathrine’s case, I made a point of respecting her and exchanged messages with her first to make sure she was ok with me bringing more attention to the incident.

How Much We Know

Last night I had the privilege of introducing Grant Fritchey  as our speaker to our local user group. He works for Redgate who was a sponsor. The topic was on 10 Steps Towards Global Data Compliance.  Between that and a discussion I had with several members during the informal food portion of our meeting I was reminded me of something that’s been on my mind for awhile.

As I’ve mentioned in the past, I’ve worked with SQL Server since the 4.21a days. In other words, I’ve worked with SQL Server for a very long time. As a result, I recall when SQL Server was just a database engine. There was a lot to it, but I think it was safe to say that one could justifiably consider themselves an expert in it with a sufficient amount of effort. And as a DBA, our jobs were fairly simple: tune a query here, setup an index update job there, do a restore from backups once in awhile. It wasn’t hard but there was definitely enough to keep a DBA busy.

But, things have changed.  Yes, I still get called upon to tune a query now and then. Perhaps I making sure stats are updated instead of rerunning an index rebuild, and I still get called upon to restore a database now and then. But, now my job includes so much more. Yesterday I was writing a PowerShell script for a client. This script calls an SFTP server, downloads a file, unzips it and then calls a DTSX package to load it into the database.  So now I’m expected to know enough PowerShell to get around. I need to know enough SSIS to write some simple ETL packages. And the reason I was rewriting the PowerShell script was to make it more robust and easier to deploy so that when I build out the DR box for this client, I can more easily drop it in place and maintain it going forward.  Oh, did I mention that we’re looking at setting up an Availability Group using an asynchronous replica in a different data center? And I should mention before we even build that out, I need to consult with the VMWare team to get a couple of quick and dirty VMs setup so I can do some testing.

And that was just Monday.  Today with another client I need to check out the latest build of their application, deploy a new stored procedure, and go over testing it with their main user. Oh, and call another potential client about some possible work with them. And tomorrow, I’ll be putting the finishing touches on another PowerShell article.

So what does this have to do with last night’s meeting on Global Data Compliance? Grant made a point that in a sense Data Compliance (global or otherwise) is a business problem. But guess who will get charged with solving it, or at least portions of it?  Us DBAs.

As I started out saying, years ago it was relatively easy to be an expert in SQL Server. It was basically a single product and the lines tended to be fairly distinct and well drawn between it and other work. Today though, it’s no longer just a database engine. Microsoft correctly calls it a data platform.  Even PASS has gone from being an acronym for Professional Association of SQL Server to simply PASS.

Oh, there are still definitely experts in specific areas of of the Microsoft Data Platform, but I’d say they’re probably more rare now than before.  Many of us are generalists.

I mentioned above too that I’d probably be more likely to update stats than an index these days.  And while I still deal with backups, even just the change to having compression has made that less onerous as I worry less about disk space, network speed and the like. In many ways, the more mundane tasks of SQL Server have become automated or at least simpler and take up less of my time. But that’s not a problem for me, I’m busier than ever.

So, long gone are the days where knowing how to install SQL Server and run a few queries is sufficient. If one wants to work in the data platform, one has to up their game. And personally, I think that’s a good thing. What do you think? How has your job changed over the past decade or more. I’d love to hear your input.

2020 in Preview

Ok, time for the obligatory dad joke: I can’t see what’s coming in the next year, I genuinely do not have 20/20 vision!

But I suppose my vision looking back was better. So I will try to prognosticate for the coming year and set some goals. I said last year I’m not a fan of New Year’s Resolutions, but I suppose I may have to reassess that claim as this is the second year in a row I’ve gone out on a limb and set goals, and what are goals if not a form of a resolution?

  • I’m going to continue to blog at least once a week. While I hope my readers get something out of it, I also blog for my own personal reasons: it helps me keep my writing and creative juices flowing. If years ago you told me I would have written a book and was blogging I’d have laughed and not believed it. I also would have wondered what blogging was!
  • Related to that, I will continue to writing for Red-Gate. This is a bit different from my blogging. It’s far more technical in nature which requires more effort. Since I’ve set aside an hour a week (and in fact my calendar just reminded me it was time for that hour) I’ve found I’ve been more productive. It’s in part why I wrote 5 articles last year and got 4 published. All so far have been on PowerShell. Generally my approach as been either, “here is a problem I had at a client and how I solved it with PowerShell” or lately it’s been a bit more of “hey, here’s a challenge, let’s see how to do it in PowerShell.” The best example of this last year was my article on using PowerShell to create a countdown timer with a GUI. It’s perhaps not the most productive way to do it, I think other languages and approaches would be easier, but it was a fun challenge and I learned a lot.
  • Extended Events! Or as Grant Fritchey would say #TeamExEvents! I’m a proud member and my goal is to learn more about them and to write more about them this year. It’s just a question of how much. But I’m a convert and a definite fan!
  • Read more blogs on a regular basis. I sporadically read Grant’s and also Monica Rathbun’s and would recommend both. I also sometimes read Cathrine Wilhemsen’s and she’s recently been on a tear with her guide to Azure Data Factories. I’ll admit I haven’t worked with it, but 25 posts in 25 days is an incredible feat and she’s great and knowledgeable on the topic, so I can highly recommend it in any event. I also want to add a few non-technical blogs to the mix. We’ll see.
  • Keep speaking at SQL Saturdays. I have yet to put in for any, but I will. Perhaps I’ll be visiting a city near you!
  • Create a couple of new topics to speak on. I’ve suggested a collaboration with someone and now I have to get off my butt and put together notes and see if they’re still willing to speak with lil’ ol’ me.
  • Speak at SQL Summit. This is an ongoing goal. Someday I’ll achieve it.
  • Have a successful NCRC Weeklong Cave Rescue Seminar here in NY. I’m the site coordinator for it this  year. I’ve got a great team backing me up, but as they say, the “Buck Stops Here”.  Registration is looking great, but until I get hit my goals, I’ll be stressing.
  • Read more! – I received several books for the holidays, including:
    • The Power Broker, I biography of Robert Moses
    • Station Eleven, a fiction  book (and if you’re the one that recommended it to me, please remind me who you are so I can thank you.)
    • Headstrong, 52 Women Who Changed Science and the World

And finally some rather generic goals

  • Love more!
  • Cave more!
  • Hike more!
  • Bike more!
  • Travel!
  • Vote the bastard out!
  • Have fun!

And I’ll conclude with one more dad joke because… that’s the way I roll!

When does a joke become a dad joke?

When it becomes a-parent.

Hey, don’t blame me if you groaned. I warned you it was coming!

Have a great New Year!

2019 in Review

Last year I did a review of 2018 and then the next day I did a post of plans for 2019. I figured I would take time to look back on 2019 and see how well I did on some of my goals and then perhaps tomorrow set goals for 2020.

One of my first goals always is to make one more revolution around the Sun. I can safely say I successfully achieved that.

But what else? I vowed to blog once a week. I did miss a few this year, but pretty much succeeded on that one. But, perhaps those misses where why I failed to break 2000 page views for 2019. That said, I don’t feel too bad. In 2018, I had one particular post in 2018 that sort of went viral, and that alone really accounts for the higher number in 2018. So if I ignore that outlier, I did as well or better for 2019. That said, I think I’ll set a goal of 2020 page views for 2020. It’s a nice symmetry.

I’ve continued to speak at SQL Saturdays in 2019 and will do so in 2020. Still working on additional topics and may hint at one tomorrow.

But I again failed to get selected to speak at SQL Summit itself. That said, I was proud to again speak at the User Group Leadership meeting this year. My topic was on moving the needle and challenging user group leaders to bring more diversity to their selection of speakers (with a focus on more women, but that shouldn’t be the only focus).  It was mostly well received, but I could tell at least a few folks weren’t comfortable with the topic. I was ok with that.

I set a goal of at least 3  more articles for Redgate’s Simple Talk.  I’m pleased to say I not only succeeded, but exceeded that with 4 articles published. It would have been 5, but time conspired against that. That said, I should have another article coming out next month.

I never did take time to learn more about containers.

I continue to teach cave rescue.

I think I caved more.

I didn’t hike more, alas.

And there were a few personal goals I not only met, but I exceeded. And one or two I failed it.

But, I definitely succeeded at my last goal, having fun. 2019 was a great year in many ways and I spent much of it surrounded with friends and family. For reasons I can’t quite put my finger on, I think I enjoyed SQL Summit this year far more than previous years. It really was like spending time with family.

I’ve been blessed with great friends and family and 2019 just reminded me of that more than ever.  Thank you to everyone who brought positive contributions to my life in 2019. I appreciate it.

 

‘Tis Better to Give than Receive

My family complains that I’m hard to buy gifts for, and I have to admit, I suppose they’re right. Things I want, I’m likely to buy for myself. And honestly, I’d rather give than receive.  But sometimes, it’s two way street:

CASSUG

This is the local Capital Area SQL Server User Group I head up. I haven’t added up the number hours a year I spend on this, but I wouldn’t be surprised if it’s in the triple digits. And I don’t get paid. It’s all volunteer.  Now that’s not to say I don’t get something tangible out of it, I do get to attend PASS Summit every year at no cost. But that’s not the only reason I do it. I do it for #sqlfamily.  I’ve mentioned them before, but let’s just say, that the help and advice I’ve received from them is amazing. It’s made me a far better DBA.  So I give a lot, but get a lot more in return. Thanks #sqlfamily.

NCRC

If I give a lot of time to CASSUG, I give even more time to the National Cave Rescue Commission. In a normal year, I will teach at least one 2-day OCR and a Weeklong. To be clear, a “week-long” for instructors typically means arriving sometime on a Thursday and working 14-15 hours days until the following Saturday. I’m planning the 2020 Weeklong, which means I will spend far more hours than usual doing work for the NCRC. I also am a Regional Coordinator, which means meetings with my fellow coordinators as well as working with local resources.  Now I’ll admit, there’s an additional reason I do this. I figure if I ever get stuck, I want some trained folks out there.

RPI Outing Club

I still work with the RPI Outing Club, mostly on caving, because it gave me so much I want to give back. That and being around young people does make me feel younger.

Blood (and more)

This and the holiday tomorrow is what prompted this post. I give blood pretty much as often as I can.  It literally is the gift of life. I figure I’ve got plenty and I can make more. I’m partly inspired by a childhood friend who had a rare platelet diseases and needed multiple transfusions. I was too young to give then, but I figure I’ve more than made up for it since then.

I’m also a registered bone-marrow recipient and a certain friend knows, if the time comes and I’m a match, she’s got dibs on one of my kidneys.

My Family

I’ll admit, I thought twice about putting this down. Not because I don’t love giving them things, but because I figure it’s sort of my job. But I’ll admit, I take enormous satisfaction at times at sitting back and seeing the smiles on their faces and knowing that I had a role in that. And ultimately, they’re the most important to me. And for everything I’ve given them, they’ve given back to me 10x.

What do I want?

Now, I know I’m not on the gift list of most of my readers. So I don’t expect anything, but I’ll say what I want. Be kind. Give time. Give your skills to another. To quote Whitman:

Oh me! Oh life! of the questions of these recurring,
Of the endless trains of the faithless, of cities fill’d with the foolish,
Of myself forever reproaching myself, (for who more foolish than I, and who more faithless?)
Of eyes that vainly crave the light, of the objects mean, of the struggle ever renew’d,
Of the poor results of all, of the plodding and sordid crowds I see around me,
Of the empty and useless years of the rest, with the rest me intertwined,
The question, O me! so sad, recurring—What good amid these, O me, O life?

Answer.
That you are here—that life exists and identity,
That the powerful play goes on, and you may contribute a verse.

What will your verse be in this holiday season?

 

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.