This month’s T-SQL Tuesday asks us to explain a concept like the audience like they are 5 years old. I’ve decided to literally do that.
Do you like jelly beans? I like jelly beans. Imagine if you had a big, big bowl of jelly beans.
How many jelly beans do you have? Can you tell by looking? I think you would have to count and it would take a very long time. Well, computers are good at counting! We could teach a computer to count our jelly beans and tell us how many Jelly beans we have. Using a program called Power BI, the computer can tell us we have 800 jelly beans! That’s a lot of jelly beans.
Do you have a favorite flavor of jelly beans? I really like the pink ones that taste like bubble gum.
What if we could tell the computer to count by flavor? We could then see how many we have total and how many by flavor.
Wait a minute! We have a bunch of cinnamon jelly beans, ew. I can’t stand cinnamon jelly beans. I will probably give them to my mom. I want to hide the cinnamon jelly beans, to see how many tasty ones I have. We can add a filter to look at specific flavors.
We can even add categories of things, so we don’t have to remember which flavors are good and which flavors are bad.
Oh good, we have 700 tasty flavors and 100 gross flavors. Okay, I want you to imagine one more thing. Imagine instead of one bowl of jelly beans, you had 100 jelly bean dispensers and you owned a whole jelly bean store!
Well now we *really* need a computer to count everything. We want to know when a dispenser is empty. We want to know which flavors are most popular. Some people actually like cinnamon jelly beans. Gross! But if you own a store, you have to buy and sell the flavors people want.
Power BI is a program that helps business owners count everything going on in their stores. It helps them count everything but also let’s them look at little details like a single flavor or a single dispenser. Power BI helps store owners make more money and sell people’s favorite candy!
When I first heard about SQL on Linux back in the beta days, I really didn’t see the point. Was this an attempt to steal customers away from Oracle? Was this intended to appeal to Postgres and MySQL users who were used to $0 licensing? Were that many organizations that were fully Linux except for the one SQL server?
And then as I learned more about devops and containers, it started to click for me. And while I’m still fumbling with devops, I’ve found one really good use for Linux and containers: demos.
Specifically, making a presentation on SQL Server 2019 is what convinced me. I presented on it back when it was still called vNext. Then 5 months later I was presenting it again. But now I had to uninstall it and reinstall the latest version. And I have to find whatever virtual machine I had installed it on in the first place. I had a virtual machine because I didn’t want to contaminate my host laptop.
But now, I can run 5 lines of code or less and suddenly I have the latest CTP ready to present against. All in the time it takes to down the update plus a minute or two. I couldn’t do this without SQL on Linux and Linux containers. So, at least for demos, I’m never going back.
This week’s T-SQL Tuesday invitation is all about puzzles. I’ve got an accidental puzzle that I’ve never quite solved, from one of my demos. I’m sure the answer will be a “Well duh!” moment.
I give presentations on SQL Server execution plans. As part of that, I like to show that if you pull a single row from a heap, it has to read everything. As part of that demo, I try to push everything out of memory by disabling readahead reads, taking a checkpoint, and dropping clean buffers. But for some reason… it never quite works!
IF NOT EXISTS ( SELECT *
FROM sys.schemas
WHERE name = N'Demo' )
EXEC('CREATE SCHEMA [Demo] AUTHORIZATION [dbo]');
GO
IF OBJECT_ID('Adventureworks2014.Demo.Person', 'U') IS NOT NULL
BEGIN
DROP TABLE AdventureWorks2014.Demo.Person;
END
SET STATISTICS IO ON
SELECT *
INTO [AdventureWorks2014].[Demo].[Person]
FROM [AdventureWorks2014].[Person].[Person]
DBCC TRACEON (652,-1);
CHECKPOINT
DBCC DROPCLEANBUFFERS
SELECT * FROM [AdventureWorks2014].[Demo].[Person]
WHERE BusinessEntityID = 25
You can see here that it shows 3,808 logical reads, but 5 physical reads.
I’m sure there is some simple way to force it to do all of the physical reads, but I have yet to figure it out. Or it may be that I’m misunderstanding something and physical reads are the only pages used. But when I look at the execution plan, it says it read all of the rows.
I’d love to get an answer to this puzzle. I’m sure it’s something simple.
Update: Andy G. asked if maybe the issue is I’m not using all the rows. Here I tried a heap of a single row, and I get 1 logical read and 0 physical reads.
This week’s T-SQL Tuesday is about where you use databases in your personal life. And I have a database I don’t use any more that’s a little happy and a little sad. For the first year of my marriage, I would track every time we played a board game together.
Of course, some may question if it was really a database. We kept the data in Google Sheets. It was ugly data; if we played multiple games in a day, I didn’t always put in all the dates. I didn’t always spell games the same way. I had different entries for which configuration of Star Realms we played, even though it was the same game.
One thing that was really useful was seeing which games Annie kicked my butt at. Or to see which games we played the most. Magic the gathering is in there twice because I did’t always spell it the same way.
After a while, we didn’t play quite as frequently and all the data entry started to wear on me. I even played around with making a PowerApp to make it easier.
In the end though, I stopped keeping track. Maybe at some point I’ll start again. I find it strangely satisfying to have this bizarre log of the first year of my marriage. During that time, we played about 90 distinct games about 220 times. We had a lot of fun and still dedicate 9 PM to 10 PM as our date hour, but will watch movies or play video games too now. And ultimately, we started what has been the best decision of my life, which was getting married.
For this T-SQL Tuesday, Andy Leonard asks us, “What is your why?”, why do you do what you do?
Many of the answers from others are heartfelt and admirable. Mine are not. Most fall under the category of either dumb or dumb luck.
Starcraft and Scholastic
My very first taste of programming was back in first grade or so with LOGO. I thought it was a lot of fun but never did any significant after that. I first got my second taste of programming with Starcraft custom maps. The year was 1999. I know this because my username was eugene11. I’ll let you do the math. Starcraft had a scripting logic that was basically a simple if/then system.
But the day I became a true programmer was when looking in the back of a scholastic catalog, I found Interplay’s Learn to Program BASIC! The description promised to teach me how to make video games.
It was pretty easy to follow along with the lessons, although I didn’t truly understand what I was doing. I could make small modifications to the games they provided but didn’t understand half of the logic therein.
In high school I would make video games on my graphing calculator, because I was bored. I also participated in a game jam called PyWeek.
Brothers and ex-girlfriends
When I went to college, I figured I’d either be a programmer or a professor. I went to Penn State beaver because it was close, cheap and my brother graduated there, so I already knew one of the professors.
In my senior year, I needed to take an elective in my major. I took Business intelligence, because the other option didn’t work with my girlfriend’s schedule. Remember when I said some of the reasons were dumb?
Later on I asked the professor for a job. He worked full time as the head of Business Intelligence at Bayer Material Science. He hired me and I worked there for a year. Ultimately, though, it wasn’t a good fit.
My next job was labeled .net/SQL developer. But after I was accepted I realized it was 90% SQL and suddenly I was a DBA and the new BI department. This part falls under the dumb luck, and it was lucky. I was in a role that I could grow into and over the years ended up working as a BI consultant within the company.
While much of the path to BI was an accident, I’ve stayed with it in part because of the strong community and the friendships I’ve made. I really wonder if I’d find a community as strong and giving as #sqlfamily in a different field. But also, if I’m being honest, because the opportunity cost is high and this is a well-paying profession.
Mom and my new adventures
My career for past 7 years has largely been an accident. I’ve always loved computers and thinking, but I can hardly say I chose this field. But back in September, I quit my job to work for myself. I did this to work on my physical health, my mental health, and have more control over my career.
And honestly, why not? Worst case I have a gap year on my resume, and I go back to get a regular job, right?
I’ve written about the lessons learned so far and more recently, I’ve discovered another why. Working for myself allows me the flexibility to take care of my mom. And so why not becomes much more of a why.
The theme for biggest failure of my career, I wrote that I would have automated things more. What was that automation? Nightly ETL with stored procedures, maybe SSIS + BIML if I was smarter. If I was doing the project all over, it’d wouldn’t be called automation it would be called best practices!
So, what project would I definitely call automation that I have done? Well I can only think of one project, and it’s pretty silly. 4 years ago, I was using the Raspberry Pi as my main computer. Over the past few years I’ve tinkered with the Raspberry Pi and I’ve built some dumb BASH scripts to install and configure a new install of the OS.
It’s not fancy or impressive, but it makes my life easier and I’m happy with it. In the future I’d like to do something similar with windows so I can spin up a whole Hyper-v lab without any work.
This month’s T-SQL Tuesday invitation is to write about a project that went horribly wrong. And hoo-boy, do I have a project that comes to mind.
My biggest project was my biggest failure
The biggest project I ever worked on, that I ever led, was also my biggest failure. In many ways it has defined the early trajectory of my career and shaped what I value in my new career.
About 8 months into my previous job, my boss wanted us to move to a different ERP system. This would allow us to consolidate 5-6 different pieces of software into a single piece of software. The theoretical business benefits would be quite significant. No longer would we need to have this awkward patchwork of integrations between different line-of-business applications. And while we eventually did it, I consider it the biggest personal failure of my career. So what went wrong?
The software was built in-house
So the first issue was that the software was built in-house by another company in the same industry. Imagine, for example, if a large bakery had created an ERP system and another large bakery wanted to move to that system. Sounds great, right? Well, you run into two issues in that scenario.
First, a bakery is not an independent software vendor. Programming, by definition, is not their core competency. Which means that you may run into fragility or issues that you wouldn’t run into with a commercial piece of software. It also means that there isn’t going to be any documentation on migrating to the software or implementing it. Why would there be. If you built software for one company, why would you create scaffolding to move other companies onto it?
Second, not every business is the same. A lot of the fundamentals are the same, but you will run into many edge cases. We do invoices this way. They do workorders this way. We handle purchase orders this way. They handle inventory that way.
The way that I think about it is like a sea shell. It’s this intricate curve that’s grown over time, organically, to fit that creature. If you just try to fit a different snail or mollusk in that shell, it may not work out.
I was not qualified
This was my second real job ever and I had been working less than 2 years in the industry. I always tell people the jobs said .net / SQL developer, and it turned out to be a lot more SQL and a lot more DBA.
When I started that job, I didn’t know what a stored procedure was. I didn’t know what a view was. 8 months later, when this project started, I had never done any large scale integrations or migrations. At that point I had done some small integrations between pairs of systems, but nothing nearly at this scale.
Add on top of that the need to staff up and add someone to the team. So, about year in to my new job I was also now a manager.
The software stack was older and different
Because this software had started way back in the Apple II days and grown over time, much of the technology stack was quite old. The application was built on VB6, MySQL, Classic ASP, Crystal Reports 9 and Adobe Flex. This presented challenges in getting it up and running, as well migrating to it.
Migrating data from SQL server is a giant pain. MySQL workbench has an import wizard, which works decently well, but it can be a bit of tedious process. Later I was able to set up linked servers, but that involved looking up strange property settings and forcing char padding on MySQL.
The software made a lot of assumptions
Because it was homegrown, the software made a lot of assumptions about the data. Project numbers under 1000 were reserved for certain pay codes. Half of the columns would crash the software if they had null values. A lot of the tables had to be populated for the application to even run.
So, so, so much of the process was modify a view, migrate the data, see what blows up, repeat. This is a big part of what made the weekly data load so painful. So much of the migration work was sheer trial and error.
Migrating ERP systems requires understanding 2 businesses
Migrating from one ERP system to another in this case require understanding the business that created the software and the business intending to use the software. This is a lot of learning and a lot of weird edge cases. I think I deeply underestimated the sheer complexity of running a business and all the departments therein.
When we deployed, there was a whole chunk of functionality we hadn’t implemented because “certificate of work” meant one thing to use and something totally different to the other company. They whole system updated maintenance cycles based on these certificates, whereas we had assumed they we merely customer deliverables and thus optional.
What would I have done differently?
Ultimately the project took 13 months instead of the estimated 8, and even then that was only because I decided for us to have a hard cutoff at the calendar year. So, if I could travel back in time, what would I have told my younger self?
Tighten the feedback loop. Migrating the data from one system to another was a largely manual process and quite painful, so we only did it maybe a week. Near the end I had started automating the process. In retrospect, I should have made it a nightly process.
Learn SSIS and BIML. So much of the pain came from the slow turnaround cycle. I suspect that if I had learned SSIS, I could have made things into a daily or even hourly migration process and saved so much time.
Read Rapid Development. I wish I had a better idea of what I needed to know from the outset. Rapid Development was a revolutionary book for me and had so much good advice in it.
Identify a measurable list of use cases. When we made the cut-over, a number of basic things did not work because we hadn’t tested them. What would have been smart would be to have a checklist that we could run through before hand to test the use cases.
Don’t take it personally. I was young and I treated things outside of my control as my responsibility. I was sore for a long time about how things went, and I thought too much on how that reflected on me and my abilities.
So now what?
Even to this day, I’ve got a certain amount of skittishness around the idea of large projects. There is a whole suite of soft-skills, of methodologies involved in making sure an IT project is successful, and I’ve learned the hard way what happens when you don’t have those skills.
For now, I’m excited to focus on course authoring and projects where I just need to create content.
Finally, I’ll leave you with a favorite quote of mine:
Good judgement comes from experience. Experience comes from bad judgement.
I once had to some auditing for a customer and it was a complicated, multi-stage process. We had to be able to demonstrate who had admin access and what kind of activity was going on, on the server. But before we could do any of that, we first had to identify who was actually logging on.
Triggers to the rescue
So what are the different options for telling who is logging on to a a SQL server? 5 options come to mind:
Configuring login auditing really isn’t a good solution. What you are doing is changing the base settings to log successful logins in addition to failed logins. The problem is that these events are written to the SQL Server event log, which isn’t convenient to parse.
Well what about using a trace? Well I’ve always been told that traces are expensive in terms of performance so I shied away from using one of those. In retrospect, I doubt it would have been too expensive since it’s only tracing logins. If anyone knows, let me know!
The next option is to use Extended Events, which often have better performance. Unfortunately, this server was SQL Server 2008 R2 and there was no GUI support for extended events. So that wasn’t ideal.
What about SQL Audit? Underneath the hood, SQL Audit is just Extended Events. That being said, there is at least some GUI component to it. For 2008R2, it required Extended Edition. while that wasn’t an issue for us, it seemed like overkill.
So what’s the last option? Creating a server level trigger. This was simple to implement and easy to dump the data into a SQL table for reporting purposes.
Proceed with caution
So, what’s the downside. Wellllllll. What happens if you have an error in your code? If you hit an error, then you can’t login. At all. Anyone.
There are ways to resolve this issue, but it requires shutting down the SQL Server and taking an outage to fix it. Suffice it to say, I spent a looooot of time testing before I pushed this out to production.
Overall, triggers provided a simple solution to a simple problem. But the solution required a good dose of caution.
If you are going to change jobs every two years, then that’s how long you have to learn before it’s on to the next thing.
I’ve had my freakout about the data platform constantly broadening. Mindy Curnutt has a great podcast episode about imposter syndrome. We are all worried about the pace of change.
And I think Meidinger’s law is our saving grace in some sort of way. Things keep changing, we have to keep learning. But how much we actually have to learn is constrained by that 22 year old drinking red-bull with no family obligations.
Throwing Darts
Oh yeah, we were supposed to be predicting the future. Well I think the fact that we are all going to be replaced by that 22 year old some day gives us a hint at the future.
I think more and more things are going to be abstracted away. We’ve seen it with virtualization. We’ve seen it with the cloud. These abstractions mean the new guy can learn new, more important things. He doesn’t have to be intimately familiar with RAID 5. He doesn’t have to have the OSI model memorized.
I think we are seeing it now with data science and machine learning. So much of those areas require a Phd and years and years of study. But things like Azure machine learning and Azure cognitive services are going to get easier and easier. So easy that even the new guy can do it.
For the 99th T-SQL Tuesday, I’m going to talk about something I do that’s completely unrelated to work. Something I’ve never told any of you about.
What I don’t want you to know
There are 3 things in my life that could cost me a job, things that I fear an employer ever finding out about. I suffer from 3 big problems in life: depression, diabetes, and LARP. That’s right, I’m… a LARPer.
I’m not kidding about the fear. People get weirded out easily, and it’s easier to say no to an applicant than to say yes. I really worry about someone finding this post and turning me down.
I once heard a story about a potential staffed employee. Everything was looking good until the client looked up the employee’s Facebook and found out that they were a furry. After that point, the client was no longer interested in staffing that employee.
It’s unfortunate that harmless personal hobbies present such a risk, but here we are.
How it started
When I first heard about it, I was skeptical. In my head, all I knew was the stereotype of the uber-nerd shouting MAGIC MISSILE!
My then fiancée and a couple friends of ours had started going, and she wanted me to come along. I told her quite clearly, “Listen, LARP is so dorky that if I go with YOU, you’ll end up breaking up with ME. No way.”
Well it turned out that my excuse had a limited shelf-life. A couple days after we are married, my new wife reminds me that I’m very much against us getting divorced. So now I have no excuse not to go, because we aren’t breaking up any time soon. We go to LARP a week after we are married.
What is it?
I could tell you it’s Dungeons and Dragons in the woods, but that would be doing it a disservice. Instead, let me tell you a couple stories from Memorial Day weekend, 2016. It was 8 months later, and the start of our honeymoon.
That time I tried to commandeer a flying ship
So my character is a diplomat / minstrel / healer. Which means, if possible, I’d prefer to solve things with words instead of violence. And one of my abilities is to use my charisma to persuade people, convince them we are friends, etc.
So, me and my group are informed by a Non-Player Character that some bandits have taken over his grounded airship, and he needs us to get it back.
We approach the ship, and as we get on the ramp the bandits warn us to stay back. I tell my group, “Let me handle this.”
So I walk up the ramp, alone and in mild danger. I tell the bandits, “Hey, I’m here to help. That guy you stole this ship from, he’s trying to find adventurers to kill you.” “We didn’t steal it!”, they reply. “Fine, the former owner is looking for you.”, I say as I keep walking up the ramp. ”Stay back!” they shout. Then I throw two spell packets at them. Spell packets are basically birdseed wrapped in cloth. This allows for ranged attacks without hurting us or the wild life.
Now the two bandits at the front are charmed and best friends with me. They are trying to convince the rest of the bandits that I’m legit. That they know me from…somewhere?
“Now, here’s how I’m going to help you.” I say as I lean forward, at the top of the ramp. As my foot hits the ground, I hear a loud squeak.
Crap.
“Hoooooold!”, we shout. We have to pause the game because something important just happened. I stepped on a trap. An obvious trap. A trap so obvious that the person setting them up expected everyone to see it. Nope, not me. I’m too busy with my silver tongue.
“10 fire damage in a 10 foot radius. That counts as an aggressive action and breaks charisma.”
Crap. I guess we aren’t BFFs anymore.
I try to re-charisma the one bandit and convince her that she set off the trap. She apologizes profusely, but the rest of the bandits are not convinced and a melee ensues.
Well, at least I tried. Now we have to kill them all. This is what happens when you travel with murderhobos.
Thankfully, all of the dead bandits are very forgiving.
That time my hat was a cauldron, and it saved the swamp
“Keshan, do you know how to featherfall?” I’m asked. “Sure, I just learned it.” I reply. “Okay, we need you to climb this tree [metaphorically] with these magic flowers, and then jump out of the tree.”, Angus says. “No problem.”
I jump and do a twirl and pretend I’m falling out of a tree. Now they can make some magic potion that’s going to cure the swamp of some ancient curse.
Only there is a problem. “Where is the cauldron?” says the game marshal, “You can’t make a potion without a cauldron.”
Mind you, we are preparing for a town fight, a battle between all of the players and all of the NPCs. As far as our characters are concerned, we could be attacked at any moment.
So I shout, “There’s no time! Zanrick, turn my hat into a cauldron.” Zanrick here is a gnome, and they are a crafty folk. And they have an ability called improvise, which means they can make something into something else if they B.S. well enough.
More shouts go out, “We need an amour patch!”. We end up with 3 of them. Technically they are mason jar lids, but in game they are used to repair your armor after a fight. And suddenly, my hat is a cauldron brewing a magic potion to save the swamp. Pretty cool, right?
The best part of all of it? The game marshal was so delighted by my silliness, that my hat now counts as plate armor in-game. She was just expecting one of us to go to the kitchen and grab a pot. Well, that simply wouldn’t do.
To hell with what everyone thinks
There was a recent conversation on Twitter about how when you are 40, you learn to worry less about what other people think, and you learn to do what you love. I’m not quite 30, and I worry what people will think of me, but damnit I love LARP.
I mentioned depression at the beginning of this post. I usually don’t like to talk about it, because I don’t want to seem like I’m fishing for sympathy. But it’s worth mentioning that one of the causes of depression is ruminating or obsessing over negative thoughts.
In my life there have been only 4 things that have truly gotten me out of my own head and allowed me to stop worrying about things:
Video game programming competitions
Board games
First-person shooters
LARP
That’s it. Those are the only things that allow me to escape for a little while.
But even more than that, LARP is an excuse to turn off my cell phone. It’s an excuse to go outside and walk around. It’s a situation where I’m forced to be hyper-social and meet new friends. It’s this beautiful mathematical dual to everything I do in IT.
I do it to get away. I do it get a break. I do it to make friends.
As I get older, all of those things get harder and harder to do. I may take my work home with me, but there’s at least one place in this world where my work can’t follow me.
One last thing
One last thing, that I should probably mention. So, I might have published a book, in-game. Because why the hell not.
The Mockingbird is a project I did for fun, collecting stories and lore from other players.
And so I’ll end with the introduction from my book. And if you ever come LARP with me, I’ll sell you a copy, for only two and a half gold.
