Blog

IRL #6: Availability Group Performance

October 1, 2019 by Kevin3NF 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/

The Ironic DBA—Back to Basics

September 24, 2019 by SQLandMTB 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

Follow @Dallas_DBAs

T-SQL Tuesday: Your Fantasy SQL Feature

September 10, 2019 by SQLDork 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

T-SQL Tuesday #118: Fantasy SQL Feature

September 10, 2019 by Kevin3NF 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), SQL dude and fellow cyclist! Kevin wants to know what our “Fantasy SQL Feature” is.

I asked my LinkedIn connections a very similar question in my “Question of the Week” there. Hit the link to see a lot of different responses.

One of the recurring themes and possibly the most common was:

Load Balanced Writes. Unless I’ve completely missed something, there is nothing in SQL Server natively that will allow you to write to different copies of the database (leaving out Merge and Bi-Directional Transactional Replication, because they suck and don’t scale).

Don’t get me wrong…I love read-only replicas in an Availability Group, replication for reporting, or even delayed Log Shipping. They are wonderful for taking the read traffic off an OLTP box, but if you are dying under heavy load in a very optimized setup…oh well.

Bonus Fantasy: Give me a button in the Log Shipping Setup GUI (Database Properties>>Log Shipping) that just says “Re-initialize”. Sometimes LS just falls apart and its easier to tear it down and start over. Even easier is to just back up the db, restore over the Secondary db and go. But, for the small business using LS for poor-man’s DR and no DBA on staff…give me a button. Ideally that button calls sp_ReInit_Log_Shipping @DBName = ‘MyDB’. But someone will have to write that first. I am not that someone. This may already be in the DBATools.io set of toys, but I haven’t looked, and again…small customers need a button in a GUI.

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

T-SQL Tuesday: Fantasy SQL Feature

September 10, 2019 by SQLandMTB 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). This month’s edition is hosted by Kevin Chant (b|t), who has invited us to share our fantasy SQL feature.

From Jeff, The Ironic DBA (Dallas DBAs Apprentice):

I haven’t been poking around in production servers very long, and so far my main responsibility is customer server reviews. I’ve blogged a couple of times about tweaking existing scripts to massage the date output from the Error Logs and Job History for readability.

One of the obstacles I’ve identified working with SQL Server is the sheer depth of the product. There’s so much to learn it’s hard to get up to speed quickly. With Microsoft’s recent advances in AI, I see a potential area where they could add a nice little feature inside SSMS.

Take a look at this video from @Patrick Leblanc (b|t) (one-half of Guy in a Cube) where he demonstrates a Power BI feature called “Column from Examples”—specifically where he plays around with datetime formatting at about 2:15.

Essentially, Power BI uses PowerQuery to let you provide examples for column formatting and then uses the underlying PowerQuery AI to figure out what you want. Then, it writes the appropriate M Language formula for you.

A similar, but relevant, implementation within SSMS would be a pretty cool time saver. SSMS already writes T-SQL scripts for you anytime you use a wizard, so having it write T-SQL commands that output a desired style of formatting isn’t too far outside the realm of possibility.

What are some other ways you could see PowerQuery implemented within SSMS?

The Ironic DBA—Failure Is Always an Option

September 5, 2019 by SQLandMTB 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.

In the last episode I showed how I tweaked some in-house scripts to provide more user-friendly output. This time around I want to revisit those scripts and give my fellow beginner DBAs some insight on some very basic troubleshooting.

Failure is Always An Option

I’m a big Mythbusters fan, and was saddened when the show eventually went off the air. There’s so much I learned about how the world around me works by watching the antics of Adam Savage, Jamie Hyneman, the rest of their crew. I still follow both of them on Twitter (links above), and also regularly watch Adam Savage’s Tested on YouTube.

Several pithy phrases were said over the many seasons of Mythbusters episodes, including gems like, “I reject your reality and substitute my own,” and “Jamie likes big boom.” My favorite line from the show is “Failure is always an option.”

My family has been rewatching some of the show’s episodes, and in one of their final shows Adam mentions how they wouldn’t have been able to accomplish that particular episode’s goals without their previous years of experience. If you were to go and watch every episode, you’d quickly realize that the Mythbusters failed more often than succeeded. It’s through repeated trial and error that they learned the most beneficial lessons.

Learning about SQL Server’s capabilities has been a similar sort of journey. It’s still early days for me, but I’m sure that most Senior DBA’s out there will tell you that the knowledge they’ve gained over the years has been full of “that didn’t work” moments. For fun, check out this video from Bert Wagner (b|t) about SQL Fails.

No matter how much I try to remember all of this, I’m still the sort of person who gets that flip-flopping stomach feeling when I mess up or can’t figure something out right away. It’s in moments like these that I have to take a breath and remind myself that I’m still learning. SQL Server is a very complex piece of software—so complex that I doubt there is any one person who knows EVERYTHING about it, not even the people who’ve worked to develop it over the years.

Failure is always an option. As a SQL Server DBA, you’ll soon learn that your client’s servers will fail—no matter how good you are at your job. How will you identify those failures? Here’s one way.

There’s Your Problem

SQL Server has a bunch of built-in tools and resources that help identify failures and errors. Having said that, there’s a learning curve involved that’s sort of like baking a loaf of bread. The components are all there at your fingertips, but you need someone to show you how to use them in the correct properly.

One of the scripts I run daily, the Read Errorlog script, has a bit of code that looks like this:

--Dump all the things into the table
	insert into #Errorlog
	EXEC sys.xp_readerrorlog 
	0 -- Current ERRORLOG
	,1 -- SQL ERRORLOG (not Agent)

What’s relevant here is understanding WHAT is being read when this script is run. I’m not all that concerned today with showing how we massage the output, just where the information is coming from.

The line EXEC sys.xp_readerrorlog is executing a widely-known but undocumented Extended Stored Procedure. This is why you see “xp” in the scriptlet. If a regular Stored Procedure were being executed you’d see “sp” instead.

NOTE: You’ll see the following message at the top of the MS Docs related to Extended Stored Procedures: “This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use CLR Integration instead.” We will probably need to rewrite our in-house scripts some time in the future to stay current.

Error logs are not stored in the database, but rather in text files on the host server. So, this Extended Stored Procedure looks outside of SQL Server to where the error log text files are stored within the hardware environment.

What is the procedure reading? xp_readerrorlog is pulling information from the files you can find in the Object Explorer under Management–>SQL Server Logs (highlighted in green). The Extended Stored Procedure helps make our lives as DBAs just a little bit more efficient by pulling the relevant information from the text files for us rather than forcing us to view each individual log file and scroll through hundreds of lines of results.

What’s also important to note here is what our version of the Read Errorlog script is NOT reading. Notice the Error Logs folder highlighted in red in the Object Explorer. You can find it under SQL Server Agent–>Error Logs. We don’t care about those error logs for this particular task.

xp_readerrorlogs accepts several parameters. The two we use most often are the Log Number and Log Type parameters.

The Log Number parameter we pass is “0”, which tells SSMS to read the current log. The Log Type parameter we pass is “1”, which tells SSMS to read from SQL Server Logs (green) and NOT from the SQL Server Agent Error Logs (red).

Beyond this, we are then able to use our script to tell SSMS what data we’d like displayed from the logs, rather than having it output every single line. For instance, if we’re specifically looking for deadlocks, our SELECT statement can be written to only look for LogText like ‘%deadlock encountered%’.

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

Follow @Dallas_DBAs