DallasDBAs.com

SQL Server Database Consulting

Kevin3NF

PASS Summit 2019 thoughts and wrapup

by Leave a Comment

Holy smokes my brain hurts!  And at the same time it is feeling the joy of learning things I can actually use!

If you are a Dallas DBAs client…you will soon very likely be reaping the benefits of my trip to Seattle for the 2019 PASS Summit.

Its rare, but when I’m really excited I pop for the in-flight WiFi, which is where I sit now typing this up. If you look to my right you can see…clouds.

Stream of consciousness in near chronological order, accompanied by notes where possible:

Day 1

What’s new in SQL Server Tools – Vicky Harp (t) and Udeesha Gautam

Most of this was Azure Data Studio (watch me embarrass myself here), which was clear in the abstract. Very cool tool mostly for devs, but the Notebooks aspect has lots of ways I can send something useful to my clients to review, in one format.  Has a little One Note with code and results feel to it.  Great demo. SSMS is not going away!

Improving Availability with Accelerated Data recovery and Resumable Operations – Pam Lahoud (b|t)

Ever tried to roll back a runaway query that took 4 hours to notice? Reboot the server trying to kill it and that didn’t help?

This is the fix to that nonsense! Off by default, rewrite of the SQL Server Recovery process.  Bang…instant recovery.  Of course there’s some overhead. SQL 2019 and Azure only feature.

Also, “Resumable Index Operations” are now a thing. A cool thing.

After the KeyNote and these 2 sessions, I managed to score a massive headache so no more sessions.

Game night! – sponsored by PASS (Thanks Joe!)

Day 2

DBA Tools – Jack Corbett (b|t)

I’ve already thanked Jack in person twice and online at least that much for this class. If I can only tell you one thing from 3 days of classes its this: “YOU DONT NEED TO KNOW POWERSHELL TO USE THE DBATOOLS COMMAND!!!”  I paid extra for the caps and 2 bonus exclamation points, just for you. That sentence alone has never sunk in to my head.  A whole lot of goodness will flow from that to you.  Thanks Jack!

If you are a junior DBA, you still need to learn the concepts and how to do things in the SSMS GUI/T-SQL, but these tools are a great step for some of the more complex tasks.  Best session of the week for me.

SQL Server in Containers – Grant Fritchey (b|t) – Red Gate vendor session

Awesome – Grant told us early and often that the 1st 2/3 of the session would be vendor agnostic, then move to RG tools.  Many thanks for that clarity!

I tried out Docker 2 years ago in test/dev and had no use case for it. Now I do and saw that some things have gotten easier. I spin up Azure VMs to play with stuff all the time. Now I can accomplish the tasks locally and faster and cheaper! I also learned the difference between images, layers, containers and VMs,  Greatness in a confusing tech stack.

Azure DR strategy – John Morehouse (b|t), with Denny Cherry in the back for additional flavor when needed.

I’ve known conceptually about much of what John presented for a while…but now I have a lot more clarity and insight from a true pro. Plus, some of what I learned at a DeepPockets™ client 2 years ago is out of date or enhanced in Azure

Exhibitor Hall and Beanbag sessions. I got to meet one of my freelancers for lunch so that was cool. Thanks Brendan.

HA/DR – Too many choices – Mike Walsh (b|t)

I go to sessions I know nothing about and sessions I do…this one I knew, and I still learned stuff. Sometimes I go just to see friends speak.  This was that as well as learning. Mike is a great guy running a great company. If you need SQL help, call me first.  If Dallas DBAs isn’t the right fit, Straight Path is.

Game night again!

Day 3 – Friday

Batch Execution Mode on Rowstore – Niko Neugebauer (b|t)

Learned a ton, saw great demos, super fun slides and WOW! Niko is a brilliant and highly entertaining presenter. If you present, go find something he’s done and watch his style.  Its crazy fun.  And the technical content is very solid.

Best Practices for Branching Database Code in Git – Kendra Little and the Corgi Crew (b|t)

I don’t do much in the Git/OSS world and was hoping this would help me pick some of that up along the way, even though this was not a “learn Git” presentation.  It did, and I now feel good about bringing some DallasDBAs internal scripts into a Git/GitHub type repository so the whole team can contribute to them. Maybe then I’ll start contributing to other open source projects.

Great lunch with a good friend I only see at conferences. We solved a few world problems.  Again.

More bean bag time.

Upgrading SQL Server – Mike Walsh again!

I’ve done more than a few upgrades, but I knew Mike and team at StraightPath have a more formal and streamlined process, so I wanted to steal info from a competitor. Is it stealing if he’s up front giving it away and I’m hiding in the front row?

Tap house for dinner, then back to the Sheraton to pack and chill.

I’m over Colorado now✈ .  If this was readable and no spelling errors, that happened at my desk in Dallas.

Look out clients…your stuff is going to get better.  And YES, YOU NEED TO MOVE TO AZURE OR SQL SERVER 2019. There, I said it.  “Mr. Wait and See” is pushing a product 6 days into formal release!

Thanks for reading!

Kevin3NF

The Value of Training- Summit 2019

by Leave a Comment

I sit here writing this on a Sunday morning with coffee nearby, eagerly anticipating this week’s annual PASS Summit. This is my 5th Summit, and 4th in a row. And yes, I pay my own way happily to this conference every year, to the tune about $4000. That’s for everything – conference fee, travel, lodging, food, airport parking. And yes, I bump for better seats on the plane, to avoid being cramped and miserable for 4 hours each way.

But why do I do this? As a self-employed DBA, that’s a lot of money!

Because training.

I’m 50+ (just added the “+” last month) years old. Things in the SQL world are changing faster than ever. In 1999 with SQL 7 there was no cloud. There was a 5 year gap between SQL 2000 and 2005 releases. And to be quite honest, I never had a mentor guiding me. My entire workday was reactive to whatever ticket came along and Yahoo (before Google) or Books Online to figure things out.

That all changed when I got laid off from Verizon Managed Hosting (Now IBM) in 2015. I re-entered the job market with 15 years of DBA history, but severely lacking current skills, and under developed in other areas. RDX confirmed this by not hiring me after some interview rounds.

I had a conversation with myself and decided I needed to get a LOT more structured, develop a plan to get back on track and be as current as I can. I had been on Twitter, MSDN Forums, and other sites long enough to know who some of the more accomplished folks on the DBA side of the world are – specifically I chose to look at what Brent Ozar (b|t), Paul Randal (b|t) and Pinal Dave (b|t) were doing. I had already met (very briefly) the first two and now call Pinal a friend.

I had always shied away from the deeper performance tuning issues, aside from throwing indexes at things and maybe running sp_updatestats (which worked more often than not). Slow performance is one of the most common problems SQL Server customers have. And Availability Groups. I knew nothing about them at the time. So I took a position as a team lead with a small project team in a deep pockets type company and started learning. I read a lot of things. I watched a few webinars. I went to a performance tuning class from Brent that changed everything (I do best in-person, no distractions) for me.

All of this created a new habit in me – seeking out new knowledge and going deeper than the surface level “just get it back online and close the ticket” work I had been doing for years.

Training is critical!

If the folks you report to aren’t allowing you to get training on the job, I submit that either they are desperately starved for cash, or they just really don’t care about your future nearly as much as they should. So, its up to you to train yourself. Read blogs. Help out on forums. Set up a test box in Azure so you can break things and fix them. If you have a local user group go to it, regardless of the topic. Attend a SQL Saturday. Become a member (for free) of PASS to take advantage of webinars and tons of free video content! If you have a few dollars a month, get a Pluralsight subscription. Take charge of your training and your career!

So here I sit. Looking forward to Summit and possibly my last visit to Seattle (next year is in Houston).

While retirement is still 10+ years off I owe it myself, my employees, and my clients to be as knowledgeable as I can be. 3 days of full immersion in Database Administration, Azure and SQL 2019 features. 3 days of getting my steps in by noon and scanning the Community Zone for an empty bean bag. 3 days of seeing friends I only see once a year. 3 days of networking, talking to vendors (Come sponsor SQL Saturday Dallas, yo!), and having a good time. 3 days of #SQLFamily.

Worth. Every. Penny.

Thanks for reading!

Kevin3NF

Stop Saying “It Depends”

by Leave a Comment

 

Dear fellow DBAs…something popped into my head the other day and its been brewing ever since.

We joke about saying “It Depends” all the time, when people ask us for something:

  • How long will that restore take?
  • Why is the database slow?
  • Do we need more RAM?

All of these are legitimate questions, and NONE of them have enough info. So, we typically reply with “It Depends.”  Now, you probably do the right thing and ask the appropriate questions to move the conversation along to where it needs to be.

But some other DBAs stop right there with a smirk and a smug satisfaction level that comes from blocking someone from the other team.

Don’t be THAT DBA

After all, don’t people do the same to you?:

  • How much to fix my car? (It Depends on what the diagnostics say)
  • How long until the doctor sees me? (It depends on what’s going on with the previous patient)
  • How much to paint my house? (It Depends on how many rooms, paint type and inside or outside)

Instead, I am challenging every DBA to skip right on past the It Depends and move to the probing questions:

  • How big is the database you want restored?
  • Can I reproduce the slow query?
  • Have we done a health check/index analysis on our current memory?

When your first response to a question from a non-DBA is “It Depends”, its a blocker for them and sets up a possible confrontation.

When you ask them a question (which you were going to ask anyway), you come across as interested in helping them achieve their goal.

They very often don’t know what they don’t know…so help them.  If its a recurring request like restores, or installs…give them a checklist/build sheet so they can fill in the blanks.

Everyone will be happier and the DBA team will look fantastic!

Thanks for reading!

Kevin3NF

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:
Select
	datediff(ms,last_redone_time,last_hardened_time) as [MilliSeconds behind]
	,Cast((datediff(ss,last_redone_time,last_hardened_time)) as decimal(18,2)) as [Seconds behind]
	,Cast((datediff(mi,last_redone_time,last_hardened_time)) as decimal(18,2)) as [Minutes behind]
	,redo_queue_size as [Redo Queue size in KB]
	,redo_rate as [Redo Rate KB/second]
	, getdate() as [datetime]
From sys.dm_hadr_database_replica_states
Where 1=1
	and is_primary_replica = 0

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/

Home Blog About Privacy Policy