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.

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.

e-mail

 

Aaaand this was me for the rest of the day:

deadpool-omg

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!!

It’s 2017 – Where Have You Been Andy?

Hello everyone and Happy 2017!

Yes, it’s Feb 27th – is it too late to still say that? 🙂

I admit that I’ve been extremely quiet in this blog over these past few months. But that’s because I’ve been extremely busy elsewhere! Doing what you may ask? Here’s a brief recap!

* December – Presented at UP (Yooper) PASS User Group: I presented virtually as their debut speaker!
* January – Presented at SQL Saturday Nashville: a great SQL Saturday to kick off 2017!
* February – Presented at SQL Saturday Cleveland: another fantastic event that I was thrilled to be a part of!
* February – Idera Twitter #SQLChat: Idera invited me to host this monthly Twitter event. Was a fast & furious flurry of fun Twitter chatter (say that 3 times fast).
* February – Saturday Night SQL Virtual Chapter: This VC approached me back at PASS Summit and I had a blast presenting. Hope to return again!

So I’d think that that’s a decent amount of community activity, right? But it doesn’t stop there!

SQL Saturday Chicago

In less than two weeks, SQL Saturday Chicago will be upon us. I’ve taken on a new role, running pre-cons this year, in addition to helping keep the lights on with the rest of Team SQL Saturday Chicago.

One thing that makes SQL Saturday Chicago special is that it is the 600th SQL Saturday! In addition to that, ours is the first SQL Saturday to feature the new PASS branding! In fact, our first piece of attendee swag showed up earlier today, which is what prompted me to finally sit down and write this blog post.

Oh, you want to see it?

20170227_171549

We’re pretty jazzed! Our event hit the waitlist TWO MONTHS IN ADVANCE! Can you believe it? We couldn’t! No pressure here, right? 🙂

SQLBits

Yes, you read that header right. I’ll be flying to the U.K. to attend AND present at SQLBits – the largest SQL Server conference in Europe!

Speaking at PASS Summit was the first pinnacle of my speaking career, which I attained last year. My next goal was to speak internationally. I just never expected this opportunity to happen so quickly, but the dates for SQLBits 2017 were favorable, so I submitted and was accepted to present not just one, but TWO sessions! Afterwards, I will be spending the week following SQLBits in London with my wife. Never having been before, we are both looking forward to exploring the magnificant city!

On The Horizon

Beyond this, I have a few SQL Saturday’s on my radar that I’m eyeballing. PASS Summit 2017 session submissions will be forthcoming as well. And I still have a new session that I will be developing for 2017, focused around T-SQL tips & tricks, so keep a lookout for that!
Here’s to a productive 2017 of learning, sharing, and SQL Server fun!

SQL Server on Linux – It’s Really Happening!

When I first began my career, I became a Linux junkie. Slackware was my distro of choice. As the years went on, I shifted my career into SQL Server, leaving my needs for Linux behind.

Last night, I installed Ubuntu on a VMWare VM, followed by SQL Server vNext.

Microsoft’s Steve Balmer infamously labelled Linux a cancer. Today’s Microsoft embraces it. As a technologist, I am thrilled to pieces.

Fanboy gushing aside, I spent more time with things today and thought I’d just write a quick blog about things I learned/gotchas.  What follows is some randomness that I encountered today.

My VM lab on my laptop is setup with static IP addresses.  This meant I had to teach myself how to make network changes in Ubuntu, vs what I remembered from Slackware.  Just amounted to making appropriate changes in /etc/network/interfaces, /etc/hosts, and /etc/hostname.  Once that was squared away, I was able to stop using the direct interface and fire up PuTTY.

Oh yeah, I had to install sshd too.  Man, Ubuntu makes this stuff easy compared to what I remember!  ‘sudo apt get sshd’ and I was done!  Man you kids have it easy these days! 😉

Silly me struggled with getting SSMS 2016 to connect happily to vNext CTP1… until I discovered that I needed to grab a different SSMS RC1 release! Doh! Installed that side-by-side without issue and boom, I was in!

Next interesting challenge was enabling VMWare Workstation shared folders, so I could easily pull over my demo database backup files.  To accomplish this, I had to mount /dev/cdrom, copy over the VMware Tools GZ file, extract it, then I could install everything.  Once that was done, /mnt/hgfs/ had my VM shared folder, so I was able to easily pull over and restore my EveryByteCounts & AutoDealershipDemo databases!

I cracked open the solution files from my three presentations and ran through all of my demo scripts.  All worked as expected, even the DBCC PAGE & other internals related scripts (as they should).  I may do my next SQL Server presentation using my Linux VM, just because I can… and not tell my audience until the end, and see if that blows their minds!  🙂

At some point, I am going to throw all of this out & create a fresh Ubuntu VM without SQL Server installed, so I can use that as a linked clone.  Hopefully that’ll make spinning up and installing new CTPs easier to manage.  I am also curious to experiment with SQL Server installation options and explore the configuration files.  I only followed the basic instructions which installed everything.

I’m pleasantly surprised how fun I am having, tinkering with SQL Server on Linux.  This is genuinely exciting to me, since it takes me back to my Linux roots.  Look forward to what comes next!

T-SQL Tuesday #84: Growing New Speakers Round-Up

TSQL2sDay150x150Welcome to this month’s T-SQL Tuesday Round-Up! A few weeks ago, I sent out a call for bloggers and must say that I’m utterly blown away by the response. A whopping FORTY bloggers responded last week with contributions for Growing New Speakers!  Four – zero!  You people are all amazing!!!

I’ve decided to split the list of contributors into three groups.

  • New Speakers: Those who have never presented before!
  • Novice Speakers: Those who have presented just once, just a few times, or perhaps long ago in a galaxy far, far away. Generally if you’ve only spoken a handful of times, been speaking for less than a year, and/or self-identified as a new-ish speaker, you were grouped here.
  • Experienced Speakers: Those who are seasoned speakers.

NEW SPEAKERS

  1. Andrew Pruski-First Foray Into Presenting: @dbafromthecold
    Shares story of first presentation
  2. Angela Tidwell: @angelatidwell
    Writes about things learned at PASS Summit about presenting
  3. Arun Sirpal-SQL Server – Reconfigure: @blobeater1
    Explores RECONFIGURE in a technical blog for a first presentation
  4. Chris Voss: @ceedubvee
    Writes about upcoming first presentations and muses how to progress further
  5. Constantine Kokkinos-Exploring DBA Tools or: How I Learned to Stop Worrying and Love PowerShell: @mobileck
    Blogs about DBATools & Powershell for first presentation
  6. Deb Melkin-If I Were a Speaker…: @dgmelkin
    Explores three ideas for a first presentation
  7. Devon Leann Ramirez: @restinbeachface
    Writes an introduction for first presentation
  8. Jo Douglass: @jodouglass
    Writes about 1st presentation topic idea: surrogate keys as a DW/ETL anti-pattern
  9. Michelle Haarhues-On the Doorsteps: @mhaarhues
    Explores Women In Technology and first presentation about women presenters
  10. Robert Preseau: @robertpreseau
    Writes about overcoming mental obstacles to speaking
  11. Scott Millard-Taking the next step – becoming a speaker: @leftseatsql
    Accepts the challenge to speak
  12. Tywan Terrell: @tyawnterrell
    Writes about T-SQL fundamentals as first presentation topic

NOVICE SPEAKERS

  1. Anthony Nocentino-Public Speaking – The First Time: @nocentino
    Shares an updated recap blog from first presentation
  2. Bjoern Peters-Become a Speaker at a PASS Event or other Meetups – share your knowledge: @sql_aus_hh
    Writes about elements that make for a good presentation
  3. Chris Lumnah: @lumnah
    Reflects on lessons learned after first presentation
  4. Chris Sommer: @cjsommer
    Shares things to think about when writing a new presentation
  5. Kenneth Fisher-Zip to Speaker: @sqlstudent144
    Recaps prior blogs about speaking & offers encouragement
  6. Kevin Hill-Speaking & Presenting: @kevin3nf
    Explores DBA Basics for Non-DBAs as a “first” presentation idea
  7. Matt Gordon: @sqlatspeed
    Shares two key lessons learned
  8. Mike Kane: @tcp1433
    Explores target audience, topic, and format
  9. Monica Rathbun-Helping New Speakers: @sqlespresso
    Writes how to get started
  10. Wylie Blanchard-Use Video as a Tool to Enhance Speaking Skill and Create Content: @wylieblanchard1
    Discusses value & benefits of videotaping one’s self

EXPERIENCED SPEAKERS

  1. Alexander Arvidsson: @arcticdba
    Writes about body language
  2. Andy Yun-Building Your Slidedeck: @sqlbek
    Shares slidedeck building tips
  3. Brent Ozar-The Three Parts of Every Presentation: @brento
    Explores types of presentations
  4. Derek Hammer-Building an Hour of Content: @sqlhammer
    Shares advice in building your first hour long presentation
  5. Doug Lane-Conquer Your Fear of Presenting with a Gift-Giver’s Mentality: @thedouglane
    Presents a video blog about the Gift Giver’s Mentality
  6. Erin Stellato: @erinstellato
    Recaps 4 prior blogs about speaking & addresses topic of comfort
  7. Jes Borland-Dealing with Failure: @grrl_geek
    Discusses how to handle failure on the fly.
  8. John Deardurff-Speaking about SQL: @john_deardurff
    Shares tips about learning itself
  9. Kathi Kellenberger-New Speakers: @auntkathi
    Writes about how to field questions
  10. Lori Edwards-So You Want to Present: @loriedwards
    Builds on prior blog post about speaking, sharing advice learned since
  11. Michael Swart-I’m Trying To Kick My “Undo Button” Habit: @mjswart
    Writes about speaking at PASS Summit for the first time
  12. Mike Fal-Getting Ready for your Presentation: @mike_fal
    Shares how to prepare the day of your presentation.
  13. Mike Lawell-Speak in Public? What? Me?: @sqldiver
    Shares journey about starting speaking.
  14. Riley Major-No One Wants to Eat You: @rileymajor
    Shares his personal story of speaking, followed by a huge list of tips, tricks, and resources
  15. Rob Farley-How I Prepare for a Presentation: @rob_farley
    Writes about deeply exploring your topic
  16. Rob Sewell-Speaking? You? Go on.: @sqldbawithbeard
    Shares advice about getting into speaking
  17. Steff Locke-A note to (potential) new speakers: It’s ok not to be perfect!: @stefflocke
    Reinforces that perfection is not necessary.
  18. Steve Jones: @way0utwest
    Encourages readers to speak.

WHAT COMES NEXT?

As I pledged in the first blog post, I will now be reaching out to all New Speakers, to offer personalized feedback.  A number of Experienced Speakers also volunteered to help, so I will be working out those logistics.  Additionally, if any of the Novice Speakers would like the same kind of help, please contact me – I will add you to the list too!

There are numerous opportunities to present: internally to coworkers, Toastmasters, User Groups, SQL Saturdays, & Virtual Chapters.  And all of them are always looking for new speakers!  When will you speak next?

T-SQL Tuesday #84: Building Your Slidedeck

TSQL2sDay150x150

Welcome to another edition of T-SQL Tuesday. I am this month’s host, and the topic is Growing New Speakers. My contribution to this month’s T-SQL Tuesday was inspired via a tweet by Jonathan Cox (b|t).

the-questionWhen I gave my very first presentation, I had next to no experience with PowerPoint. In fact, I found it a bit intimidating. Where to start? How should I make it look? Do I have to pick a color scheme?

Not to worry! When you first open PowerPoint, there are numerous basic templates already loaded! My advice is to select a template that is simple. Don’t select one that has a background with very cluttered graphics. Visual noise is distracting and is the last thing you want to worry about.

avoid-examples

For your first presentation slidedeck, there’s no need to get fancy. Keep things simple like the templates below.

clean-examples

Readability is very important. What may look fantastic on your 1920×1200 laptop screen that’s 2 feet from you, will look drastically different when it’s been downsized by a projector to 1024×768 and is projected to an entire room.

Font size is the first key factor in Readability. Karen Lopez (b|t) tweeted an excellent tip, that I used when I was working on my PASS Summit slidedeck.

readableI readjusted a handful of my slides to pass this test (thanks Karen!).

Color contrast is important. Different rooms have different lighting conditions, so be sure to use a simple, high-contrast color combination so everyone in your room can read your slides. A lightly colored or white background with dark or black text works best. Stick with the basics – they work.

When it comes to amount of content, don’t write huge walls of text on slides. Put only your main talking points. Your speaking will fill in the corresponding details.  And if you do have a lot of slide content, don’t hesitate to split it into two slides.

Images and clip art. Some like art, I generally shy away from it – it’s all personal preference. If you do use an image from somewhere, you must give credit. Generally a footnote is acceptable. I prefer to rely on Creative Commons 0 images. Makes life hassle free. Check out Kendra Little’s (b|t) blog post all about Easy Free-Use Images.

I hope this collection of tips helps you on your way to building your first slidedeck!  And if you still find yourself a little stumped, Google/Bing is your friend!  There are many out there, who have written numerous articles & blogs on how to build effective slidedecks.  Leverage their collective knowledge.

Happy Powerpointing!