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?

 

Crossing the Threshold…

So it’s the usual story. You need to upgrade a machine, the IT group says, “no problem, we can virtualize it, it’ll be better! Don’t worry, yes, it’ll be fewer CPUs, but they’ll be much faster!”

So, you move forward with the upgrade. Twenty-three meetings later, 3 late nights, one OS upgrade, and two new machines forming one new cluster, you’re good. Things go live.  And then Monday happens. Monday of course is the first full day of business and just so happens to be the busiest day of the week.

Users are complaining. You look at the CPU and it’s hitting 100% routinely. Things are NOT distinctly better.

You look at the CPUs and you notices something striking:

cpu not being used

CPU 8 is showing a problem

4 of the CPUs (several are missing on this graphic) are showing virtually no utilization while the other  8 are going like gang-busters.  Then it hits you, the way the IT group setup the virtual CPUs was not what you needed.  They setup 6 sockets with 2 cores each for a total of 12 cores. This shouldn’t be a problem except that SQL Server Standard Edition uses the lower of either 4 sockets or 24 cores. Because your VM has 6 sockets, SQL Server refuses to use two of them.

You confirm the problem by running the following query:

SELECT scheduler_id, cpu_id, status, is_online FROM sys.dm_os_schedulers

This shows only 8 of your 12 CPUs are marked visible_online.

This is fortunately an easy fix.  A quick outage and your VM is reconfigured to 2 sockets with 6 cores a piece. Your CPU graphs now look like:

better CPU

A better CPU distribution

This is closer to what you want to see, but of course since you’re doing your work at night, you’re not seeing a full load. But you’re happier.

Then Monday happens again.  Things are better, but you’re still not happy. The CPUs are running on average at about 80% utilization. This is definitely better than 100%. But your client’s product manager knows they’ll need more processing power in coming months and running at 80% doesn’t give you much growth potential. The product manager would rather not have to buy more licenses.

So, you go to work. And since I’m tired of writing in the 2nd person, I’ll start writing in 1st person moving forward.

There’s a lot of ways to approach a problem like this, but often when I see heavy CPU usage, I want to see what sort of wait stats I’m dealing with. It may not always give me the best answer, but I find them useful.

Here’s the results of one quick query.

Fortunately, this being a new box, it was running SQL Server 2016 with the latest version service pack and CU.  This mean that I had some more useful data.

CXPackets

CXPackets and CXConsumer telling the tale

Note one of the suggestions: Changing the default Cost Threshold for Parallelism based on observed query cost for your entire workload.

Given the load I had observed, I guessed the Cost Threshold was way too low. It was in fact set to 10.  With that during testing I saw a CPU graph that looked like this:

43 percent CPU

43.5% at Cost Threshold of 10

I decided to change the Cost Threshold to 100 and the graph quickly became:

25 percent CPU

25% at Cost Threshold of 100

Dropping from 43.5% to 25.6%. That’s a savings you can take to the bank!

Of course that could have been a fluke, so I ran several 5 minute snapshots where I would set the threshold to 10, collect some data and then to 100 for 5 minutes and collect data.

CXPacket_10      CXPacket_10_Waittime_MS
635533 5611743
684578 4093190
674500 4428671
CXConsumer_10              CXConsumer_10_Waittime_MS
563830 3551016
595943 2661527
588635 2853673
CXPacket_100   CXPacket_100_Waittime_MS
0 0
41 22
1159 8156
CXConsumer_100            CXConsumer_100_Waittime_MS
0 0
13 29443
847 4328

You can see that over 3 runs the difference between having a threshold of 10 versus 100 made a dramatic difference in the total time spent waiting in the 5 minute window.

The other setting that can play a role in how parallelization can impact performance is MAXDOP. In this case testing didn’t show any real performance differences with changing that value.

At the end of the day though, I call this a good day. A few hours of my consulting time saved the client $1,000s of going down the wrong and expensive road of adding more CPUs and SQL licenses. There’s still room for improvement, but going from a box where only 8 of the 12 CPUs were being used and were running at 100% to a box where the average CPU usage is close to 25% is a good start.

What’s your tuning success story?

52

52 is an interesting number.  It’s the number of weeks in the year. It’s the number of cards in a deck. It’s a number of Earths in the DC Multiverse. It’s an untouchable number, something I just learned. It’s the atomic number of tellurium. In fact it has a number of interesting trivia associated with it according to Wikipedia: 52.

It also just happens to be the number of times I’ve been around the Sun, though strictly speaking that depends if you’re counting sidereal or the tropical year and the fact that I was born at night. But I think we’re close enough.

And it just so happens my birthday falls on the day I usually blog. So rather than something technical (though if I can get permission from a client, I may have something fun and technical soon) I thought I’d post some reflections and thoughts.

For me birthdays are both interesting and boring. I’m glad I’ve reached another milestone. But honestly, after age 25 when my car insurance rates went down, I haven’t given individual birthdays much thought. That’s not strictly true, I sometimes think about the fact that I’ve passed the point where statistically I’m looking at fewer days ahead of me than behind me.

I grew up in a small town, Falls Village CT, and parts of me never have left it. When I stop to daydream, my thoughts take me to the town green where we often played, or the woods behind my dad’s house, or the sand quarry behind the depot I grew up in. It was a safe and quiet life. I watched the world move from bell-bottoms to Reagan power ties.

While just a teenager, I and friends ran not one, but two Monopoly marathons to raise money for the Muscular Dystrophy Association. The first year we played for 100 hours (in teams) and the second 150 hours. I was and am still quite proud of the organization that took and the money we raised.

Since then I’ve done a lot.  I got thinking about that last night at the Capital Area SQL Server User Group meeting. I’m proud to lead this group.  I really enjoy, as I’ve noted before, giving back to the community that has helped me so much.

I’m proud to be a Regional Coordinator for the National Cave Rescue Commission. I can literally say the work the NCRC does saves lives. It’s an honor and humbling when folks come up to me and tell me how their training has made a difference in the lives.

I’m proud of much of what I’ve done in as my avocations and vocations, even if at times I’m often a victim of imposter syndrome. There’s still many times when someone will ask me a question and my first thought is, “why are they asking me, I’m just a kid and… oh wait… no I am the expert here and I’m far from being a kid.”  This is especially true when people I look up turn around and ask me for advice.  This happens a lot in the SQL world.

I’m very proud of my family, especially my son and daughter who are growing up to be wonderful adults, capable of critical and deep thinking. They will make an impact on the world and I don’t think as a parent I could want for anything more.

And of course proud of my wife, but I can’t take credit there, she’s a wonderful person in her own right. I just married well.

One common thread I’ve realized in my life that I enjoy is teaching and sharing my knowledge. I also, as anyone knows me, love a good debate.  Bring your facts to the table. Teach me something, change my mind, or be willing to have yours changed. I still recall a debate I had with someone once about a detail of the Constitution. She made one claim, I made another. We finally settled it by finding a copy of the text and realizing who was right. Afterwards there was no rancor or hurt. We both had appreciated the intellectual exercise and the correction of fact.  But even opinions can at times be changed. At Summit I had two pieces of white chocolate from New Zealand. They changed my mind about white chocolate! I enjoyed them.

People often ask me what I want for my birthday and the truth is, I rarely want material things. Honestly, unless it’s a new Tesla (and NOT the truck) I can and will probably buy it for myself.

But here goes:

  • Another 52 years – hey, why not? We’re making medical breakthroughs, and it’s possible I’m wrong and I’ve got more days ahead of me than behind me. Right now, I’d love that.
  • Learn something – challenge yourself in the next year to learn a new skill or a new topic. Don’t get stuck doing the same things all the time.  Earlier this year I finally took the time to start learning about Extended Events. Who knows what I’ll learn in this coming year.
  • Teach someone something – everyone one of you has a skill someone else doesn’t. Share.
  • Related to that: if you’re a caver, or have a friend or family member who is a caver, get them to take the 2020 National Cave Rescue Seminar.
  • Have a friendly debate with someone. Realize it’s not about winning or losing, but an exchange of ideas. Bring your facts to the table and recognize your opinions. Be open-minded.  Be prepared to say, “You know what, you’re right, I was wrong.” This is not losing a debate.  And be prepared to acknowledge someone saying the above to you. Accept the words graciously, don’t lord it over them. This is not about winning a debate.
  • Be kind.  If nothing else in the coming year, be kind.

And that’s it for turning 52.

P.S. Unrelated, but check out my latest article at Redgate’s Simple-talk: Building a Countdown Timer with PowerShell

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.