A Fine Slice Of SQL Server
Andy Mallon troubleshoots an issue:
In January, one of our Staff Engineers sent the following message to the DBRE help channel in Slack:
Morning folks, we had a pretty significant wait spike on the [database]. Circuit breakers closed and reopened quickly. Is anyone immediately aware of a reason why this could’ve happened?
Read on for Andy’s quick analysis and then the root cause and solution.
Comments closedEitan Blumin can’t reach quorum:
The incident started with a late-night phone call from one of our customers (it’s always a late-night phone call, isn’t it?).
They reported that during a DR exercise on their production environment (Chaos Engineering, anyone?) their entire cluster failed and they weren’t able to bring any of the replicas back online.
Click through for the full story, including what happened, why it happened, and what you can do to prevent similar problems in the future.
Comments closedReitse Eskens writes up a roundup about abandoned projects:
When I thought of this subject last year, I was really on the fence if it would work or not. Part of me was convinced it would elicit some response from the community, part of me was convinced people would be looking for a ‘happy’ start of the year and might not want to think or write about past learnings.
Part of me was right, but I never expected SO MANY of you to jump in and write so many wonderful blogs. It feels a bit unfair to summarise all your hard hard work, so please click the links to read the full stories. Well worth your time!
Click through for plenty of stories on the topic of lessons learned from abandoned projects.
Comments closedAndy Brownsword shares a warning:
A point of sale system being rolled out across hundreds of physical locations. Transaction data collected each night to be batch processed into a warehouse for usual types of analysis. Our integration preference was SSIS internally. A solution was deployed in preparation.
Rolling out of the new system started with a handful of locations which steadily increased as confidence grew. On the back of this the data hitting our solution was increasing too. With a trickle of data early on there were no issues as expected. A small volume of data from a small number of stores. The process flew. We left it doing it’s thing.
Read on to see the story take a darker turn and the importance of planning for scale.
Comments closedI didn’t get much of a chance to check out the posts as I was at the PASS Data Community Summit, but I came home and started to work through them.
This was the 8th one I’ve hosted, which makes sense as I’ve taken over managing the party from Adam Machanic and there have been a few places I’ve had to fill in for missing hosts. In any case, here’s the roundup. I’m going in order of the comments as I see them on the blog.
Click through for this month’s list of entrants.
Comments closedRob Farley eliminates a self-join:
Sometimes query tuning involves taking a different approach to a problem. And given that other tuning options might be creating index(es) or redesigning tables – both of which are much more permanent changes to an environment – rewriting a query can often be just right.
Window functions seem to pop up quite often when rewriting queries, and an example around this would be appropriate for this month’s T-SQL Tuesday, hosted by Steve Jones (@way0utwest at X/Twitter).
Read on for the all-too-common scenario and how Rob improves an existing query.
Comments closedBarney Lawrence shows off a nice enhancement to T-SQL in SQL Server 2022:
With SQL Server 2022 came a much requested additional feature added from the SQL standards – IGNORE_NULLS. You can probably guess what it does. Drop in IGNORE_NULLS after your function and you can blur the non null values over those gaps giving us results like this:
Read on for the pre-2022 version of the query and what it does, versus the version with IGNORE_NULLS specified. This small flag is extremely helpful in time series statistical analysis and I’m glad it’s in SQL Server now.
Chad Callihan gets the lead out:
Imagine we want to find gaps in post history for users. If a user is posting every day, we’re happy and can assume they’re happy. If they’re only posting once every few weeks, we want to investigate why that is.
Read on to see how you can do this with the LEAD() function.
In his invitation, Steve specifically asks for examples where Window functions provided a neat solution to a real world problem. Well, sorry Steve, but I am not going to do that. But your invitation did inspire to me write about the execution plans for these window functions. And there is, in fact, so much to write about it, that this is just the first part.
So that makes this post not only a T-SQL Tuesday contribution, but also part 23 in my ongoing plansplaining series: blog posts where I take an in depth look at execution plans to explain how exactly they work, and point out often overlooked details. In this post, I will look at the basics of window functions, as they have existed for over 10 years now. I will point out a few interesting performance gotchas. And there are some links to feedback items that you can upvote (if you are so inclined) to pressure Microsoft to make some changes to the optimizer and the execution engine, to get some small but still welcome performance gains.
Read on to get an in-depth understanding of how those window functions actually operate once the database engine gets ahold of your query.
Comments closedDeborah Melkin builds a report query:
I’ve been doing a lot of work recently where I have long running processes where I need to have visibility on where we are during the process as well as being able to provide a report we can use to find out if there were any errors, how long each step took, and other useful information that we can use later. My solution was to create an audit log table where I simply added a row before and after each step, recording the information I had available. When the processes are done, I create a query to run over the table returning the values I want to see.
Deb also throws in the only ordered set function in SQL Server, STRING_AGG().