DallasDBAs.com

Explaining SQL Server in plain english

Header Right

SQL

The Ironic DBA—Don’t Be Afraid of the Climb

by Leave a Comment

Welcome back to The Ironic DBA Files, a series where the newbie DBA on staff at Dallas DBAs chronicles the ups and downs of fast-tracking a new career as a DBA.

Yes, my blogging pace has slowed somewhat, but I’m back with something to share. Last time I wrote about getting back to basics and reinforcing my knowledge of introductory SQL Server concepts. This week I want to go in the opposite direction and share why I think you should regularly push yourself out of your comfort zone.

The view from the top is worth the climb.

Climbing Sucks

I’ve mentioned before that I love mountain biking—I’m not great at it, but I love getting out on the trails. Unfortunately, I also have very low heat tolerance, and so I was almost completely off my bike for the majority of the Texas summer—about 2.5 months. I did sneak one ride in before the worst heat abated about 3.5 weeks ago, but it didn’t end well.

I’ve been able to get back out and do three or four trail rides a week for the last three weeks. The trail I’ve frequented is one of the closest to my house, though it’s not a favorite. There’s not a lot of elevation at this trail, but after so long off the bike even gentle slopes were tough to climb at first. To make matters worse, I’m overweight, have some significant balance issues, and absolutely suck at climbing.

Fast forward to just last night where I went out and rode a different trail—the same one that hadn’t gone well 3.5 weeks ago. This trail is not the longest or hardest in the area, nor does it have the most climbing, but it’s been my nemesis since the first time I put down tires on its dirt. I have a less than 50% completion record at this particular trail, so I’m determined to conquer it.

Last night’s ride was pretty good. I completed the entire trail for the first time in months, and set some personal records on a few segments along the way. But here’s the coolest thing about the ride. At one point I made it to the top of climb that’s typically pretty tough for me and took a quick water break. I immediately noticed that the climb had felt easier than expected, but I figured that was due to intentionally choosing easier gears than usual. I was pleasantly surprised to look down at my gears and realize that I was 9th gear when I thought I was probably in 6th or 7th. (For those who don’t ride bikes, the lower the gear number the “easier” the gear.)

The time I’ve been spending getting back to trail riding regularly is already beginning to pay off. I’m back to feeling comfortable on the bike, my fitness level is increasing again, and my ability to ride harder, longer, steeper trails is gradually improving.

Climbing is Awesome

Tim Chapman presenting “Troubleshoot SQL Server Like a Microsoft Engineer”

“That’s great,” you say. “Good for you. But what does this have to do with SQL Server stuff?” I’m glad you asked!

I recently attended a fantastic one-day workshop that was made possible by my local PASS group, the North Texas SQL Server User Group. After the huge success of this year’s SQL Saturday Dallas, and in particular the response attendees had from Brent Ozar’s (b|t) performance tuning pre-con, the group decided to bring in Tim Chapman (t) in for a one-day workshop entitled “Troubleshoot SQL Server Like a Microsoft Engineer.”

I was looking forward to attending even though I knew the vast majority of the class was probably going to be over my head. I’ve advanced fairly rapidly in my SQL Server knowledge since beginning this journey a little over four months ago, but the reality is I’ve only got four months of experience. That’s actually far too little time and experience to make the most of a class at this level. So why bother attending?

Learning is a climb, sometimes a very arduous climb. Just like some of my trail rides, there are moments when I can’t reach my learning goals and have to take a step back and start over. Some days the learning is smooth and the climbing is easy, boosting my confidence and my progress. Some days, the climb is hard, but not as hard as expected due to the work I’ve put in previously.

It’s for this very reason that I wanted to attend this workshop, and the same reason I’m currently working my way through Brent Ozar’s Senior DBA Class. Both are way over my head and deal with issues I won’t have to worry about being directly responsible for any time soon. But the classes make me climb and reach for a goal. Such classes make me dig deep and recall everything I’ve learned so far and apply it as best I can. Such classes expose holes in my current knowledge set and help flesh out a trail map for what I need to learn moving forward to become the best DBA I can be.

Sometimes the climb sucks, but getting to the top is awesome. Onward to the next peak!

Troubleshoot SQL Server Like a Microsoft Engineer—A Review

As for Tim Chapman’s class itself, let me give you a quick review and share my thoughts and takeaways from the day.

First up, I didn’t know what to expect walking into the class. Still being somewhat new to all this, I sort of expected the class to rapidly accelerate to light speed and beyond to leave me grasping for whatever small tidbits I could glean. That never happened.

Tim is an excellent presenter, but beyond that he’s an excellent teacher. So many technical people who are very smart, know their stuff, and have years of experience often don’t realize just how far beyond the average newbie they really are. But not once did I feel like the class was moving too fast for me to keep up, and a great deal of this is due to how Tim broke everything down logically, built concepts upon one another, and didn’t make assumptions that everyone in the room knew more than they really did.

Having said that, there definitely was a certain level of foundational knowledge required in order to make the most of the class, and thankfully my studies thus far proved to be enough. There were a few concepts and pieces in SQL Server that I’d never heard of before (namely triggers and forced query plans), but by and large I was familiar enough with SQL Server, relational databases, nomenclature, and SSMS to never get completely lost.

In a nutshell, here’s what Tim covered in roughly seven-hours-worth of sessions:

  • An Overview of Troubleshooting
  • Queries and Waits
  • Extended Events
  • Query Store
  • Most Common Problems

One of the most encouraging things Tim shared as a new DBA was this concept: “Often, there are 5 or fewer things to look at for a server to solve problems. Like the Pareto Principal: 20% causes 80% of the issues. Today’s Goal: Identify and master the 20%.”

This is why so much of the class was understandable and relate-able. The truth is, to know how you fix the other 80% of problems possible in SQL Server means you’ve either gained years and years of experience, know how to ask Google and StackOverflow the right questions, or both. Stay in the DBA field long enough and you’re sure to run into something rare, an edge-case that very few people have seen. But beyond those edge cases, the vast majority of the problems we see are repeated regularly. Getting a grasp on these underlying issues that regularly rear their head will help me be a solid DBA while giving me the foundation to troubleshoot those rare issues.

Big thanks to Tim for helping me put together a more direct path to follow on my self-guided learning. If any of you reading this ever have the chance to take this class from Tim in the future, I highly recommend it. I’d like to sit through it again myself sometime in the future when I’ve got more knowledge and experience in my tool kit.

Also thanks to NTSSUG for investing in your members and bringing Tim in to teach us. I’m looking forward to more opportunities in the future.

That’s all for this post. Join me next time for the next episode in The Ironic DBA Files.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.

The Ironic DBA Files



TSQL Tuesday: Changing your mind

by Leave a Comment

 

T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Alex Yates(b|t), who has asked us to write about “Changing Your Mind“

My best technical example of this is something I wrote about in January 2017, so I invite you to read the original post:

PowerShell for DBAs – why I changed my mind.

In addition to the 2 main reasons I gave then, I am adding a 3rd:

dbatools.io has become a very solid and almost all-encompassing way to manage SQL Server and will be a required skillset in 3-5 years at any large SQL shop.

That’s it for this month!

Thanks for reading!

Kevin3NF

IRL #6: Availability Group Performance

by Leave a Comment

Problem:

My client came to me with their biggest company-wide issue. They are a retailer with many independent sales reps. These reps tend to enter a majority of their orders into the system during the last 4 hours of each month, and enter just enough to hit the next commission level. The data-entry part is fine, but the commission reports get way behind on showing the orders just entered.

Background:

2 node SQL 2016 Availability Group.  Automatic Failover, Synchronous commit.  One readable secondary.  The reports traffic has to be pointed to the Primary if it gets more than 10 minutes out of sync.

Investigation:

I’ve only done the basics of Availability Group work at this point – Install, add/remove databases, etc. so I knew I needed to brush up on the various phases of the data movement process, especially since I was under the impression that if data was entered successfully on the Primary replica, it HAD to be entered and visible on the Secondary replica.  This impression did not mesh with what the client was telling me.  Also, we were only 5 days away from EOM (end-of-month).

The best link I found to learn about these phases was from Microsoft’s CSS SQL Escalation Services team:

Troubleshooting data movement latency between synchronous-commit AlwaysOn Availability Groups

From here, I surmised that the issue was in this process:

“logBlock receive->LogCache->LogFlush->HardenToLDF->AckPrimary”

This is followed directly by the “redo” phase, which makes the data visible to requesting connections.

Alongside this, I was running sp_whoisactive to try to catch any waits or queries that might need some tuning or investigation.  I found this one when the system would back up a bit: DIRTY_PAGE_TABLE_LOCK

If you are not aware already, the best resource on the web (In my opinion) for wait types is SQLSkills.com.  THIS article for the above wait type gave me this golden nugget:

“This wait type can become prevalent on a replica where there is a lot of read activity on the readable secondary and there is a heavy update workload on the primary replica.”

This matched my scenario perfectly.  The wait is specific to parallel redo threads, which are the default in SQL 2016+

Paul links over to this MS Tiger Team post for a further discussion of Parallel vs. Serial redo.  Go read it.  If you already know this, share with a co-worker.

Findings:

  • Checkpoint (attempted as anecdotal possible solution by Paul) may give relief
  • Trace Flag 3459 disables parallel redo without a SQL restart (version/CU specific)
  • To re-enable parallel redo, disable the trace flag and restart SQL.

Actions Taken:

  • Checkpoint gave some relief
  • Enabled TF 3459 globally on the Secondary replica
  • Continued to monitor Secondary replica using:
Results:

Trace Flag 3459 completely fixed the issue, and the reporting side of the EOM process worked flawlessly for the first time in many months. As I write this, we have gone through 2 separate EOM periods without issues.

Conclusion:

  • Understanding the phases of the AG process, or any other offloading of reporting processes is critical to troubleshooting
  • “Synchronous commit” does not mean “Immediately readable”.  It means the transaction is hardened to the log file on all synchronous replicas. Many presenters/bloggers gloss over this distinction.
  • Know your troubleshooting tools (sp_whoisactive, AG Dashboard) and where to go to learn (MS, SQL Skills, Ozar, etc)

Other posts in the IRL – In Real Life series:
https://dallasdbas.com/category/sql/irl/

The Ironic DBA—Back to Basics

by Leave a Comment

Welcome back to The Ironic DBA Files, a series where the newbie DBA on staff at Dallas DBAs chronicles the ups and downs of fast-tracking a new career as a DBA.

It’s been a few weeks since I added anything to this series—though I did contribute my first-ever T-SQL Tuesday post a couple of weeks ago. The reasons for my silence are actually pretty simple. I’ve been busy.

Is Your Isolation Concurrent?

My main daily task since coming on board here at Dallas DBAs has been immersive self-study. I spend the vast majority of my time reading blog posts, books, and watching videos about all things SQL Server. I recently enrolled in Brent Ozar’s training classes and have been learning a great deal. I typically watch one or two videos a day there, and spend a lot of time aftewards doing follow-up reading in an attempt to reinforce what I’ve just consumed.

There’s so much to learn!

Before enrolling in those classes, I spent a few weeks sort of ambling all over the place without any specific step-by-step process as to what I should be studying. I had sort of gotten to the point where I had learned enough that it was getting hard to determine exactly what I should learn next, so my focus was rather fuzzy.

Along the way, I spent a few days going down the rabbit hole of concurrency and isolation levels, which is really useful stuff to know if you’re serious about being a top-notch DBA. It’s good stuff, and I’m glad I read up on it, but 90% of what I had read was cart-before-the-horse type stuff. I needed to keep it simple and go back to basics.

Build that Muscle Memory

I wrote in my last Ironic DBA post about the basics of finding and reading error logs. Nested within that simple write up was a truth I needed to remind myself about and keep coming back to: Keep learning about how navigate and use SQL Server Management Studio (SSMS).

Case in point: I currently have three clients whose servers I review daily, and one client who receives a weekly review. I’ll be picking up one or two more clients in the near future. I’m almost daily presented with an “I’ve never seen this before” moment, which is a learning opportunity. It’s not uncommon for me find a new-to-me error and spend a bunch of time Googling and checking reliable sources in an attempt to figure out what’s going on.

More often than not, my difficulties in figuring out what’s going wrong are equal parts not knowing where to look in SSMS and not knowing about the error itself. I can learn from mentoring or reading what is causing an error, but knowing how to troubleshoot it is largely knowing how to navigate SSMS effectively.

Seriously, I think the best piece of advice I can give my fellow newbie DBAs is do everything you can to learn about using SSMS. Learning how SQL Server works under the hood, how relational databases work, how to write and troubleshoot queries, and things like indexing, statistics, and monitoring are all critical to your career as a DBA. But none of that matters if you don’t get familiar with the tool you will use most often.

SSMS is the tool that will make everything else you learn make more sense because it is where you can see all the magic happen—or not happen in the case of job failures, deadlocks, and other nasty stuff. Let’s be honest, the tool is not intuitive, and in 2019 it feels very long in the tooth—like using legacy software because there’s nothing else better. As a graphic designer and lifelong Mac user I find the software clunky and confusing, and constantly think about ways the GUI could be vastly improved.

The problem with that is it would blow the mind of every long-term DBA out there. Can you imagine how lost the majority of career DBAs would be if Microsoft suddenly released a whole new interface to SQL Server? Even if they created a GUI that was objectively better, many DBAs would feel like they’re starting over and it’s a frustration they just don’t need to deal with to get their jobs done. So, I agree that the best course of action is to identify simple ways to tweak the current GUI to improve the tool without blowing it up and starting from scratch.

So embrace SSMS for what it is and what it does. Despite it’s weaknesses, it’s the most powerful tool in your DBA tool kit.

That’s all for this week. Join me next time for the next episode in The Ironic DBA Files.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.

The Ironic DBA Files



T-SQL Tuesday: Your Fantasy SQL Feature

by Leave a Comment

T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Kevin Chant (b|t) who has asked us to describe “Your Fantasy SQL Feature“. (This is also the first time I’ve made a blog post for T-SQL Tuesday.)

The Problem: As a junior DBA, part of my job involves checking job failures for 2-3 clients each day, which we have a script in-house for. Every once in a while, a job actually fails! So like any DBA who knows that you can go to the Job Activity Monitor under the SQL Agent to view details on job history, i do exactly that only to find it’s a job that ran an SSIS package and gave me this very informative error message:

That’s super helpful, thanks!

So now i have to:

  • Check the step properties
  • Look at the path for the SSIS package
  • Drill down into the integration services catalogue
  • clickity, clickity
  • Wait which folder was it in?
  • Go check the path again
  • clickity clickity
  • There’s 12 different packages with very similar names:

  • Resize the window or scroll over to see which one i care about
  • Finally, open the All Executions Report to get a very helpful error message:

Gee darnit, i’m out of coffee.

The Solution: Just put a button in the failure message/job history that does all that for you.  Or a hyperlink. Whatever.

Kevin Hill (b|t) has made a script that reads the SSISDB tables, but often takes a while to run with only the existing Microsoft indexes. Especially if you are keeping crazy amounts of history…like the MS default 365 days, on a busy system.

/*Please work*/,

SQLDork