T-SQL Tuesday #146 Round-up! Upending Preconceived Notions

Hello everyone!

First of all, I want to thank everyone who took some time to participate in this month’s T-SQL Tuesday: Upending Preconceived Notions. There’s a fantastic line-up of contributions! I was tickled that many of the topics chosen were things I once held one viewpoint on, later learned more, and evolved that viewpoint.


That was a lot of fun. And I’m happy to say that I learned some stuff.

Thank you again to everyone who took some time to contribute a blog post!

Dear readers, I hope you take the time to read each and every contribution, and come away learning something new too!

T-SQL Tuesday #146: Disk Storage – More Than Meets The Eye

Welcome to another edition of T-SQL Tuesday. This month, I’m the host and I’ve asked participants to blog about “Upending Preconceived Notions.”

What’s Your Preconceived Notion Andy?

Just over 3 months ago, I joined Pure Storage, which is fundamentally a storage infrastructure company. Confession: I’ve never been an infrastructure OR storage guru. I’ve spent the vast majority of my career focused on slinging code, and not the underlying hardware. But like many things in life, these things can be taught and I’ve been learning a LOT these past few months!

What I Used To Think About Disk Storage

For the vast majority of my career, I just thought of storage as a place to read and write your data from. Storage was merely a receptacle… a place for your data to live. Of course, as data professionals, we want to move tons of data quickly, so things like latency, throughput, and capacity still mattered to me. But beyond that, disk was disk.

So What Changed?

Frankly, I learned about how Pure Storage’s FlashArray worked underneath the covers. (DISCLAIMER: I may slightly butcher what I explain next, as I’m still getting to the point of being able to eloquently articulate the stuff I’ve been learning.) When writing data blocks, FlashArray essentially maintains “metadata” about what data tidbits live where in the storage array. I’m going to over-simplify and say let’s just think of this metadata as a glorified index or table of contents, okay?

So why is that special? FlashArray uses this metadata not only to find where your data lives but to MANAGE it as well. We do things like make copies of our data and I know we can ALL relate to staring at progress meters moving at a glacier’s pace. Well what if all we had to do is make an adjustment to the metadata instead? I’d rather just copy a small little “table of contents” instead of an entire book of information, and it’s a heck of a lot FASTER!

If you’re curious to learn more about this, here’s a cool blog about ODX and FlashArray. https://blog.purestorage.com/purely-technical/odx-flasharray-engineers-perspective/

Why does all of this matter?

As data professionals, we have to move data around a lot. And it is often a hassle. Refresh dev/QA/staging with a copy of Prod. Reseed an Availability Group. What has blown my mind is that because of clever decisions that Pure has made, regarding how data is stored in FlashArray, now enables data professionals to attack those copy/data movement headaches in ways they could not before! I’m amazed at some of the things that can be done almost instaneously, that would take hours in another vendor’s storage array. And that is extremely exciting to me!

Revelations

As I’m writing this very blog post, I just had a personal revelation.

Years ago, I attended SQLskills’ IE1 class. One of the first topics was SQL Server storage engine internals and it was another one of those eye opening moments in my career journey. And I just realized that my new understanding of FlashArray and how it works underneath the covers, has really opened by eyes and changed how I view storage SOLUTIONS! This bit is totally just a personal notation, but I figured I’d write and share it here anyway!

Think Back

Anyway, I hope you all enjoyed this story. Sorry if it came across as a Pure Storage commercial – it wasn’t meant to be, but damn, they’re doing some really wicked awesome stuff underneath the covers!

Regardless, if you’re reading now, think back over the course of your career and ask yourself if there’s ever been a time when you’ve learned something that’s totally changed your outlook or view on something bigger.

Thanks for reading!

T-SQL Tuesday #146: Upending Preconceived Notions

Welcome back to another edition of T-SQL Tuesday! I’m honored to be your host once again!

Theme to Kick off 2022

This month, I’d like to ask everyone to think about something you’ve learned, that subsequently changed your opinion/viewpoint/etc. on something. Maybe you’ve had a certain opinion, belief, or bias? Perhaps you’ve always thought something worked a certain way? Or you’ve always thought that a certain something (called “X”) was only good for “A”, only to later learn that it can help with “B”, and “C” as well. Regardless, you learned something and it totally upended and changed that preconceived notion you held previously.

When has this happened to you Andy?

Let me share an example. In my past as a T-SQL developer, I remember when I first learned about CTEs. I thought the world of them and started using them everywhere! However, there was one slight problem. I was under the mistaken impression that they pre-materialize each sub-query. And they do… in OTHER RDBMS’s. Whoops! After a few years, I learned that they don’t behave that way in SQL Server. Instead the query optimizer inlines the query in the CTE, making them functionally no different that a subquery. And well, let’s just say that that made me regret some of the coding decisions I’d made during my “CTEs-are-awesome” phase.

Rule Refresher

To remind you of the rules, please write a blog post corresponding to this month’s theme, include the T-SQL Tuesday logo, link back to this announcement post for the trackback, and leave a comment here with a link back to your contribution. Deadline is end of day Jan 11, 2022 (don’t worry about time zones, I’m not picky about late entries).

The other thing to remember is, your blog is your blog. If you want to gently twist the theme, to write a blog that better suits you, by all means do so! Just want to encourage you all to participate!

Go Forth and Blog!

So take some time to think about something you’ve learned that subsequently upended a preconceived notion you held before! I look forward to reading your contributions.

Happy Blogging!

The Future Is Orange

A month ago, I blogged about my last day with SentryOne.

And today, I’m thrilled to share that I’ve had my first day at Pure Storage! I’m joining Marsha Pierce’s team of Field Solutions Architects, where I’ll be doing all sorts of awesome stuff with SQL Server and Pure Storage technology. I know I have a lot to learn but am extremely excited to grow and learn once again.

Burnout and stagnation sucks. It’s okay to take time off and it’s okay to shake things up, move out of your comfort zone, and challenge one’s self again.

T-SQL Tuesday #143 – Random Fun

This month’s T-SQL Tuesday is hosted by John McCormack, who asked participants to blog about Short Code Examples.

A few years ago, I wrote a similar blog about T-SQL Tools that I could not live without. Those were my snippets files, which I maintain in Google Drive. Not much has changed since I’d spent the last 4 years working for SentryOne, but I still maintain the entire toolset and did use it periodically.

For today’s, I’m going to dive into my 1_Scripts.sql file and share some of my favorites all involving “random” values!

Random Fun

First, there are times where you need multiple random numbers. Simply using multiple iterations of RAND() in a single statement won’t cut it, as you need to vary the seed. So I keep this snippet handy for when I need a bunch of random values in a single statement:

/* Random Number between 0-99 */
SELECT
   CAST((RAND(CAST(NEWID() AS VARBINARY)) * 100) AS INT),
   CAST((RAND(CAST(NEWID() AS VARBINARY)) * 100) AS INT);
SELECT
   RAND(CHECKSUM(NEWID())),
   RAND(CAST(NEWID() AS VARBINARY));

Wait For Me Randomly

During my time at SentryOne, I found that I had to create mock workload on a regular basis. I wanted to introduce some unpredictability to make demos more interesting, so wrote this chunk of code to introduce random WAITFOR DELAY intervals.

/* Shell Agent Job with randomized runtime */

DECLARE
    @DelayMin INT = 5,
    @DelayLength CHAR(8) = '00:00:00'; -- hh:mm:ss
SELECT
    -- Create randomized 5-15 MINUTE WAITFOR DELAY
   @DelayLength = '00:'
   + RIGHT('0' + CAST((@DelayMin + CAST((RAND(CAST(NEWID() AS VARBINARY)) * 100) AS INT) % 15) AS VARCHAR(2)), 2)
   + ':00';
   -- Create randomized 5-15 SECOND WAITFOR DELAY
   --@DelayLength = '00:00:'
   --+ RIGHT('0' + CAST((@DelayMin + CAST((RAND(CAST(NEWID() AS VARBINARY)) * 100) AS INT) % 15) AS VARCHAR(2)), 2)

SELECT @DelayLength AS '@DelayLength';

PRINT 'START - Do Something';

-- DO SOMETHING HERE;
WAITFOR DELAY @DelayLength;

PRINT 'END - All Done With Something';

For My Final Trick, Random Strings

In much of my AutoDealershipDemo database, I have random strings for various things, so needed code to do that. I’m using a Tally Table approach, that then combines CHAR() with modulus operators to only general alpha-numeric values. But this can easily be adapted for other values available in CHAR().

; WITH Ten_CTE (N) AS (
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), Thousand_CTE (N) AS (
   SELECT 1 FROM Ten_CTE a CROSS JOIN Ten_CTE b CROSS JOIN Ten_CTE c
), Tally_CTE (N) AS (
   -- This provides the "base" CTE and limits the number of rows right up front
   -- for both a performance gain and prevention of accidental "overruns"
   SELECT
      ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
   FROM Thousand_CTE
   -- CROSS APPLY Thousand_CTE t2 -- UNCOMMENT IF YOU WANT 1,000,000 results instead of 1,000
)
SELECT
   ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RecID,
   -- Increase concat below to increase length of string
   CHAR(ABS(CHECKSUM(NEWID())) % 26 + 97)
   + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 97)
   + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 97)
   + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 97)
   + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 97)
   + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 97)
   AS RandomString
FROM Tally_CTE;


Hope you’ve enjoyed reading. These will most likely not be useful to the vast majority of you. But if you do ever have to write some mock workload, demo scripts, etc., then hopefully you’ll remember this blog post and make use of the above!

Standing Desk: V3

It seems crazy to me that it’s been FIVE YEARS since I last blogged about my Standing Desk setup, and boy a LOT has changed!! During those years, I moved several times, but my Ikea standing desk stuck with me.

Out With The Old

This month marks a big career change for me so I decided it was finally time to retire my Ikea setup. It is sturdy and solid enough but its biggest drawback is that it is not really convertible between sitting & standing. Technically you could change it up with the pegs and trestle legs, but that’s very impractical given how much heavy hardware “permanently” lives on the desk to begin with.

My old standing desk built from Ikea parts

So What Did You Want?

My new office space is a smaller converted attic room, which presented some limiting factors – the primary being desk width because of where I can place a standing desk with my sloped ceilings. This limited to me to setups around 40 inches wide. Because of another storage dresser, I had to overlap my window but I don’t mind. I also wanted to treat myself to a real wood desktop vs the cheap corrugated Ikea top I’ve had since the beginning.

What’d You Wind Up Buying?

After a ton of research, I wound up selecting Uplift. Went with a walnut butcher block top, C-frame, and decided to be a bit different with their “industrial” finish. I enjoyed having a monitor shelf, so added that option as well. The biggest drawback to my old setup was that I lost desktop space to my keyboard & mouse, so I also opted for an under-desk keyboard tray.

Uplift desk pre-assembly photo
C-frame almost done!
Had to stop and actually do some work, so used a TV tray! 😀
All stood up!

And here’s the final product! I kept my Vivo laptop stand, added some cheap LED under-cabinet kitchen lighting that we had leftover from our last place, and picked up a set of Cyber Acoustics CA-SP34BT 2.1 speakers for music.

Seated position w. my Steelcase Gesture
Standing position

What’s That Underneath Your Desk?

But it’s what’s underneath my desk that’s noteworthy. I’d been using an Ergodriven Topo mat for years and think it’s great. But I wanted something more. A few years ago, I’d learned of under-desk elipticals for sitting desks and began to wonder if there were now specialized treadmills that’d fit under a standing desk.

A quick bit of research yielded a number of results that seemed like fairly cheap Chinese treadmills. If I wanted solid quality, I’d be looking at spending at least $800 if not more. One key detail about an under-desk treadmill is that its not meant for hardcore running. I also wanted something “portable” enough to move out of the way, which is why I did not look for a traditional running treadmill.

Uplift + GoYouth under-desk treadmill

In the end, I opted to go with a lower-end treadmill. I’m optimistic that I can get into a habit of using it regularly, especially while on webcasts or doing lesser intensive tasks like reading, e-mail “chores,” etc. And if it winds up like other home exercise equipment often does (as a clothes hanger), then I won’t feel as bad if I had burned cash for a higher end model.

Was It Worth It?

My setup has been operational for a few days now and I’m pleased to share that I’ve already gotten good use out of the treadmill. Ran it for during a few meetings in my last workweek, and I after a while, I didn’t notice the treadmill at all. I’m only walking between 0.8MPH and 1.4MPH at this point, to get used to walking while doing stuff. Don’t expect to get much faster than that but I’m okay with that. As long as this thing keeps me moving for an extended period of time AND I can ingrain its use as a daily habit, I’ll call it a win.

End of Another Chapter

TL;DR

Today is my last day with SentryOne. And burnout has been kicking my ass HARD. But I've mostly defeated it and am looking forward to what's coming next.

A Retrospective

Just over four years ago, I had the fortune to join SentryOne. And today, that journey comes to a close. I am forever grateful for this fantastic chapter of my career and wanted to blog a bit about it (mostly for me, but if you're here, hope you enjoy the story too).

Never Dreamed I'd Be In Sales... and LIKE IT

When I was a very young child, I was asked what I wanted to be when I grew up and said a "salesman." My father was in sales, which is most likely why I gave such an answer. But then I discovered video games, which drove me to computer science, and while eventually lead to databases and SQL Server. In 2013, I started speaking and found that I was good at it but really loved it. Through speaking and events, I met many wonderful people including Scott Fallen, who recruited me into SentryOne, as a Solutions Engineer (aka a pre-sales engineer). When I started as an SE, I never knew such a role even existed. And in my first few months, I thought I made the worst mistake of my life! At that point, I knew how to present, teach, train, and consult. But there's a different nuance with a pre-sales role. You do all of those things yet you don't do any of them fully. Eventually it clicked in my brain, that I needed to not teach and explain, but rather to showcase, highlight, and most importantly, listen. Once I figured that out, I grew as a pre-sales engineer and found that it was one of the most rewarding roles I've ever had. It's true... I came to love pre-sales.

Change Is Inevitable - 2020

We all know how everything changed in March of 2020. What followed for me through the remainder of 2020, was a great deal of personal, community, and professional changes.

Personal

Am not going to blog details publicly, but 2020 brought about many lows but some very wonderful highs too. And at the end of 2020, I moved to Boston to start a new chapter of my life there.

Professional

In the course of my career, I'd been a part of SEVEN acquisitions and/or mergers. I dodged two more, as those were coincidentally announced after I had given notice but before I'd left the respective company. The craziest one was when Lehman Brothers shuttered. My company was a wholy-owned subsidiary of Lehman Brothers. I distinctly remember our CEO calling an all-hands Monday morning, telling us straight up that we had two weeks of money to pay everyone, and that he'd be spending every waking minute trying to find us a new home. He was successful and we had new ownership four days later. Another job of mine seemed like a fantastic home for me. That company was a small start-up that had just been acquired. I was the first hire under the new parent company. Unfortunately, conflicts arose with another key employee and the environment became toxic, so I left after seven months. That worked out for the best because the next year, the parent company terminated almost everyone who had been acquired! In October 2019, SentryOne was acquired, making it acquisition number eight for my career journey. It's frankly annoying how I frequently find myself at companies that get acquired! In some acquisitions, nothing really changed except the company name, e-mail addresses, and HR stuff. But other cases, well, a hell of lot changes. And not all change is for the better either.

Community

As a speaker, I continued to be extremely active as we shifted fully online. I wound up presenting MORE in 2020 after March, than in all of 2019, thanks to everything being virtual. As a user group leader, I embraced the virtual path and started hosting meetings twice a month. But when the end of the year rolled around, I came to a powerful realization - I was BURNED OUT HARD.

Fighting Burnout - 2021

2021 rolled on in, bringing about many new changes. Deborah and I bought a new place together in Boston. As awesome as that is, moving and "new home to-do's" are still a significant stressor. And I was still feeling burnout and stressed. The good thing is that I was aware and conscious of it, so tried to mitigate it as best I could. The hardest and easiest mitigation was electing to cut back on my community involvement. I relinquished my leadership role in the Chicago Suburban User Group. I opted to not speak at all during Q1 2021. And I reduced my speaking engagements dramatically the remainder of 2021. Finally I drastically reduced my social media presence and consumption. There were a number of controversies in the community, especially earlier this year, where I opted to remain silent. Frankly, I got to the point where I might only open Twitter once a day or only check DMs and that's it. It's been good for my mental health, but I do miss interacting with #SQLFamily.

What Brings You Joy?

When my first 2021 speaking engagement rolled around, I'll confess that I found myself dreading it. I did not have the energy or the desire to do it. I wondered if this was finally the sign that I should stop speaking entirely. But a commitment is a commitment and I'm glad I went through with it. I found that when "the lights went on and the curtains opened," I rediscovered the joy of speaking and presenting. I recognized that it was my burnout that made me dread it. But in the end, I did miss it and still loved presenting.

Mourning the Past

Aside from speaking, I did my best to combat my burnout and stress. The summer of 2021 was particularly rough, with a few key setbacks that hampered my attempts to shake off my burnout. But more importantly, I realized that I had been in a cycle of mourning as well, not wanting to accept professional changes. In the end, I stopped resisting, came to terms with the passing of something I loved, and moved on. Once I did, I could finally look forward again and a weight was slowly lifted from my shoulders.

Looking Forward to What's Next

I always say that I believe in "counting one's blessings." And a few weeks ago, one of those blessings came in the form of a new opportunity. As excited as I am, I'm going to wait a bit before I share where I'm going next. I'm doing this because I want to focus the next few weeks not on what's next, but on the now. I'll be taking the next month to fully recharge and eliminate any remaining burnout of 2020/2021. I'm done with burnout bullshit and am eager to move forward again.

T-SQL Tuesday #135: Tools That Have Helped Me the Most

Welcome back to another edition of T-SQL Tuesday. This month’s edition is hosted by Mikey Bronowski (b|t) who asks us to write about a tool that has helped you the most over the years.

I’m a query tuner at heart, so for me the answer is an easy one: SentryOne Plan Explorer. Disclaimer: Yes, I work for SentryOne now, but I’ve been a devoted Plan Explorer’s user for most of its 10 year history, having discovered it back in its early days.

This blog isn’t going to tell you all about how awesome Plan Explorer is. There’s tons of existing resources out there to help you get up to speed (like this Beginners Resources PDF). Instead, I’m going to share a more esoteric tidbit that I only recently learned.

.pesession Files

In Plan Explorer, you can use it as a query performance tuning environment (rather than a query development environment), iterate through multiple changes, and go back in history to compare and contrast. Then if you want, you can save your working session out as a .pesession file. This allows you to share everything you’ve done, give it to a colleague, or whatever.

For this blog post, I’m going to be using the .pesession file from my Advanced Plan Explorer Usage presentation. First, here’s the history from the master file and you can see that I have 8 iterations.

So What’s The Big Reveal?

So here’s the tidbit that I only recently just learned, that frankly kind of blew my mind but didn’t surprise me at the same time. 

.pesession files are really zip files. 

This means you can extract the contents!

Once you’ve extracted everything, you can open up individual queryanalysis files inside Plan Explorer.


In the above example, now you see that I have the 1st, 4th, and 7th entries open from my original .pesession file.

How Is This Useful?

Honestly? I’m not so sure that this is really that useful to the everyday query tuner.

Best use case I can think of is if you’ve been working on something, have like 30 iterations, but only want to send the 23rd one over to your colleague, because that’s the one that worked best in the end. Like I said earlier, this is certainly an esoteric tidbit. But hopefully was entertaining enough.

Thanks for reading!

T-SQL Tuesday #134: Breaks for Mental Health

Welcome back for another edition of T-SQL Tuesday. This month’s edition is hosted by James McGillivray (b|t), who asks us to write a post about how we take breaks for our mental health. I’m going to share about two things I try to do.

Looking Inward

Throughout my life, I’ve dabbled with meditation but like exercise, never quite got fully into it. But there are lessons that I have learned, that I leverage regularly. These include basic breathing techniques to help calm myself. And I’ve found the ability to quiet my mind to also be invaluable (though it doesn’t always work for me). But what I’ve come to appreciate most is learning how to be mindful of and focus on my surroundings.

If you’re curious about meditation, the two smartphone apps that I found helpful are Headspace and Insight Timer. I find guided meditations useful. But alas, like daily exercise, I just sucked at getting into a routine and sticking with it. But if you can succeed where I’ve failed, then you too can reap the benefits of regular meditation.

Making Music for Money? Making Music for Me

The above is the title of an old, obscure Jimmy Buffett song. I’ve always enjoyed listening to music, but hated the piano lessons that my mother forced me to take in my early years. Funny how things have come full circle and I’ve picked up an instrument again.

Back in the fall of 2020, I gifted Deborah Melkin (b|t) with a new Martin 000-16GT. As a result, I decided to start dabbling with her Epiphone PR-4E, which served her well for many years as her starter guitar. I was particularly motivated to learn guitar by various articles I’ve read, which talk about learning new skills to get more fulfillment out of life. Given that we’re stuck in quarantine hell and I no longer have my dogs, why not?

L to R: Epiphone PR-4E (old) & a Martin 000-16GT (new)


Justin Guitar’s beginner series has been invaluable in my guitar learning journey. It’s funny to me that like exercising, the first few weeks were THE WORST! It was when I was most excited and wanted to devote lots of time to learning, but couldn’t thanks to finger pain and lack of callouses. But I’m finally past that hurdle now. Like meditation and exercise, I don’t practice daily, but the desire is still there and I get back to it in fits and spurts, with aspirations to continue my learning.

It Just Takes a Few Minutes

Heck, when I decided to write this blog, I pulled the guitar out and started practicing a new chord, a finger stretch exercise, and practiced a basic strumming pattern. And it FELT GOOD! Just a 10-15 minute tangent… but you know what? It was better for my mental health than doomscrolling or some of the other mindless habits that I’ve gotten into to pass the time, that do nothing for my mental health.

And sometimes, even just a few minutes of peace and quiet enough to soothe the soul.

2020 Community Recap: By the Numbers

Looking back over 2020, I’m blessed to have had many opportunities to remain very active in the SQL Server community. Don’t feel like writing a lot so here’s the breakdown by the numbers:

Presenting

Event Type # of Presentations # of Events
Conferences 10 8
SQL Saturdays 10 9
User Groups 8 8
Webinars 4 4
     
Total 32 29

* Excludes SentryOne vendor activities

Other Stuff

Blog Posts = 3 (not including this one)
Chicago Suburban User Group meetings = 11
Event Moderator = 1

Another Chapter Ends…

Finally, the most bittersweet number.

2309 days since Sept 4, 2014 to today, Dec 30, 2020 = User Group Leader

Effective today, I’ve stepped down as co-Chapter Leader of the Chicago Suburban User Group.

At least the reason for this change is a good one; I’ve relocated to Boston!

Until Next Time…

Here’s to hoping for a better 2021!

Cheers