Watch me embarrass myself by completely failing to find things in my first look at ADS:
Comments? Put them on the video…but be nice 🙂
Thanks for watching!
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.
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.
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:
From here, I surmised that the issue was in this process:
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.
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
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.
Other posts in the IRL – In Real Life series:
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.
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.
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.
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:
So now i have to:
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.
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!
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?