Describing a SQL Server Wait – Like a Date

I wanted to blog about SQL Server Wait Stats today, but from a bit of a different angle than your normal Wait Stat blog.

I often find myself being shown TOP X wait stats output and being told “OMG, these are horrible, we need to fix these!!!” And I stop them, counter with a “no, not necessarily, let’s dig deeper. Are these waits abnormal?” “Abnormal? SQL Server has waits, that’s always bad, right?”

If you’re already familiar with SQL Server Waits, you will know that SQL Server is always waiting on something. And that’s perfectly okay. A few months ago, I was driving home from SQL Sat Cleveland, and came up with a fantastic analogy that I thought would describe these waits rather well.

Pretend two people, Vanessa and Wade, are on a date and having a conversation. Vanessa says something, then Wade responds, and their conversation continues back and forth. But between each person’s statement, there is a very brief moment of silence. That can be thought of as a “wait” – the other individual is processing the prior message and formulating a response. And in the context of this conversation, these waits are perfectly normal.

Now, let’s pretend that Wade says something really, really stupid. It actually shocks Vanessa, who is speechless. Instead of responding immediately, she waivers and remains silent. A long period of time passes before she responds to Wade. This particular wait is abnormal! And so goes a SQL Server wait.

Okay Andy, does that mean that if my SQL Server waits all have long timeperiods, that those are all of the bad ones? No, not necessarily either. In the example of Vanessa and Wade, yes, because their normal cadence of conversation is very rapid with only brief waits. But let’s look at a second example.

Pretend you have two grumpy old men, Jack and Walter, fishing out on a lake in a boat. It’s quiet and they’re grumpy, so they’re not saying much. Walter might make a comment about the fish not biting, and Jack may respond after a lengthy period of time with a brief response. After a long period of silence, Jack may comment about his beer being empty, and he asks Walter for another. Walter fails to respond, because he’s dozed off. And only after several minutes does Jack actually turn around to see that Walter has dozed off, and gets a beer for himself.

If we take a step back, think of each couple as a server’s workload. What does your workload look like normally? It is difficult to know if a given SQL Server Wait time period is anormal if we don’t know what our times look like under normal circumstances. This is why many stress the importance of baselining your SQL Servers.

So the next time you find yourself explaining SQL Server Waits to someone new, feel free to use this analogy to help illustrate why waits in of themselves are perfectly normal, and what a good wait vs a bad wait may look like.

Advertisement

T-SQL Tuesday #88: Surprise – Those Aren’t Test Accounts!

Welcome back to another edition of T-SQL Tuesday. This month’s host is Kennie Pontoppidan (b|t), who has invited participants to share a WTF story!

A few jobs back, I worked for a software firm. Our system had a complex system of “accounts” which were tied to client companies and users.  And our users could have multiple different accounts through which data would get routed to “make widgets.”  Without going into details, these accounts were simply like an individual’s savings account – they were fairly complex entities which were simply labelled as “accounts.”

My team had been wanting to clean up obsolete account data stored in our account manager database for a terribly long time. Aside from just raw storage, having obsolete accounts lingering in our system had numerous other consequences which made reporting and other things terribly irritating. But our business was always extremely afraid to ever delete anything – so we had account data for everyone we ever had, etc.

As a “pilot,” they agreed to let us finally delete several hundred internal-only accounts. These accounts were obsolete, only used for demo, and were not associated to any client companies, just our company.  As the point person for making this change, I put together a complex T-SQL script to manually remove all of these accounts from the various tables of our database system. But what I also did was put together a backout/restore T-SQL script that restored all of those accounts. I did this out of paranoia and lessons learned from prior experiences. This way one could run my delete script & my restore script repeatedly. The QA team tested my scripts for 1-2 weeks and we were finally given the go-ahead to clean out the stale records.

Monday morning came along and as scheduled, our Production DBAs executed my script before start of business. 30 minutes later, the frantic calls started to reach us. Seems some of our clients could no longer “make widgets!” Accounts that they needed to route data were gone! My manager and I looked at one another in horror – we were only deleting internal accounts!!! We didn’t hesitate and immediately had our Prod DBAs back out the change with my backout script, before the rest of the United Stated started business. The backout was executed immediately and all was back to normal, but business folks were pissed and wanted to know what happened.

As we dug through things, we came to discover that some of our implementations folks had jury-rigged some of their implementations. To meet customer requests for unsupported features, they utilized our company’s internal demo accounts! WTF!!! We in development were furious. Their use of the internal accounts was not documented anywhere, but the data trail didn’t lie.  And these clients weren’t in QA, which is why they never picked it up either!

Moral of the story? Have a robust restore plan. Sure, our systems were fully logged and we could have executed a point-in-time restore. But for a scenario like this, having an immutable data restore script, that was fully tested and ready to be executed, was far faster. It allowed our business to get back online almost instantaneously & saved my bacon.

Microsoft Data Platform MVP Award

So I wrote a blog post yesterday, giving a quick update of what I’ve been up to.

And early this morning, I got a huge surprise in my e-mail.

 

Aaaand this was me for the rest of the day:

So yeah, I am honored & humbled to share that Microsoft has recognized me with a Data Platform MVP Award.

REFLECTION

I remember years ago, when I was just beginning my career, when searching the Internet for information, I’d occasionally come across forum posts by people who were Microsoft MVPs. Back then, I wondered who these people were. They must be REALLY smart, like walking encyclopedias, to be recognized by Microsoft in that way!!!

Flash forward a number of years. I’ve just attended my first SQLskills Immersion Event in Chicago, met some fantastic people, and am slowly learning about the existence of the SQL Server community. I thought it was awesome to just have the opportunity to meet some of these folks and even forge friendships!

Then I started volunteering at events… and then help run SQL Saturday Chicago… and then started speaking (and occasionally blogging)… and here we are.

I’ve never “wanted” the MVP Award. I do what I do in the community, for many reasons. I’ve gotten a great deal out of the community, and want to give back in turn. I’ve built many wonderful friendships through this community. I’ve discovered that I really enjoy teaching & presenting. I do what I do, because it is definitely my passion.

THANKS TO ALL OF YOU

Nonetheless, thank you all, who sent congratulatory messages to me. I am overwhelmed by your love and support and friendship. You are all a testament to why I do what I do! Thank you!!