Stack Exchange
log in

Aaron Bertrand

 The Heap™ – Consultancy ©®

General on- and off-site discussion for dba.stackexchange.com....
6
3
Aaron Bertrand
Sep 18, 2024 19:33
@ErikReasonableRatesDarling lol, I wish
Aaron Bertrand
Sep 18, 2024 19:09
One of the comments on the post suggested that some of the holes have been fixed in or before SQL Server 2017. I only saw that comment today, so I haven't gone back to investigate. I've basically ignored the feature since I wrote the articles.
Aaron Bertrand
Sep 18, 2024 19:08
Similar to "don't use stored procedures, just put the query in the app, then it's easier to update the query in the app" unless you run the same query from 14 different apps and sometimes want to run it in SSMS - then a stored procedure makes more sense.
Aaron Bertrand
Sep 18, 2024 19:07
Well, I think part of the intention is to not force people to repeat that control in 14 different applications, along with SSMS, ADS, etc.
Aaron Bertrand
Sep 18, 2024 19:05
@ErikReasonableRatesDarling Neither. Just had some unexpected waitfor this week.
Aaron Bertrand
Sep 18, 2024 19:03
@PaulWhite Do you have a handy list of optimizations that are skipped in tempdb? I know you've alluded to it a few times but, other than fk join elimination, I haven't found any other concrete examples. (Context: I have been working on a post about why to not do any real testing in tempdb.)
Aaron Bertrand
Sep 18, 2024 19:00
@Yano_of_Queenscastle I came to a similar conclusion: mssqltips.com/sqlservertip/4002/…
Aaron Bertrand
Sep 18, 2024 18:59
hai
 

 The Edge of Propinquity

This may or may not be a room for AI-generated text, images, a...
Aaron Bertrand
Apr 7, 2024 23:40
I manually fixed this. As we look to resolve this more permanently, please try to resist trying any more 4+ million-character experiments.
4
 

 Discussion on answer by Ray Butterwor…

Imported from a comment discussion on christianity.stackexchan...
Aaron Bertrand
Feb 2, 2023 17:45
^ ~610 characters
Aaron Bertrand
Feb 2, 2023 17:44
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
 

 Room for Aaron Bertrand and Makyen

Aaron Bertrand
Dec 10, 2022 00:43
It doesn't surprise me that there were other not-realized-at-the-time victims.
Aaron Bertrand
Dec 10, 2022 00:43
We did some further tuning of those routes and shuffled schedules around so fewer were happening exactly on the hour or all at a specific UTC hour.
Aaron Bertrand
Dec 10, 2022 00:42
We did in the meantime discover a few self-inflicted scheduled route kind of things that did, in fact, pinch us pretty hard at predictable times each day. Sometimes the pinch was lost in other details (DDoS attacks would sometimes last for much longer periods) so the pattern was sometimes hidden in the noise.
Aaron Bertrand
Dec 10, 2022 00:40
Just "there's more load now" doesn't seem consistent with the issues being, primarily, at almost exactly the same times of day, to the minute, over a period of months. If it was just that there's more load or the result of DDoS attacks, I'd expect the pattern of errors to be significantly different, much more spread out throughout the day or concentrated into larger blocks of time. For DDoS attacks, I'd expect a substantially higher number during the periods when you've been under attack, which has appeared to be for hours or days, not just for a few minutes at the same time on many days. — Makyen Sep 21 at 14:48
Aaron Bertrand
Dec 10, 2022 00:40
Was reading through some of the other posts about SmokeDetector (I don't know how I have been completely unaware of this until today), and wanted to comment on this one:
Aaron Bertrand
Dec 10, 2022 00:16
Makes sense, thank you for the details
Aaron Bertrand
Dec 9, 2022 23:26
Also important to note that SmokeDetector is currently passing min=0 instead of min=<6 minutes ago>, which means it is asking for all data since 1970, and asking for a count that it doesn't need, so when these issues are fixed they will also help stability. — Aaron Bertrand 2 mins ago
Aaron Bertrand
Dec 9, 2022 23:25
(Or correct me if any of that should be stated differently.)
Aaron Bertrand
Dec 9, 2022 23:25
Great, thanks! I've posted a comment here, feel free to add on:
Aaron Bertrand
Dec 9, 2022 22:31
It's still asking for a count, too, just to be clear (I'm not sure which changes you're testing).
Aaron Bertrand
Dec 9, 2022 22:17
I'm still seeing min=0 at the end of the URL?
Aaron Bertrand
Dec 9, 2022 22:10
Cool, thanks, let me know if I can help, lots of eyes on our dashboards so if anything goes awry we should know :-)
Aaron Bertrand
Dec 9, 2022 21:55
Hopefully it is easy to change that to pass in either the last time you ran a successful check, or 12 hours ago or something, whichever is older.
Aaron Bertrand
Dec 9, 2022 21:54
I think that trailing min=0 is what's being interpreted as epoch.
Aaron Bertrand
Dec 9, 2022 21:53
Seems the new URL is
https://api.stackexchange.com/2.4/questions?key=IAkbitmze4B8KpacUfLqkw((&filter=!7bj2kejr9-Tmw-wWkT)JQ1T3qUUB9KLZAB0TT-dWOwUFyqxVII1y.BH6Ji(.pwih1odhF-wr29R*Jbti&state=PublishedAndStagingGround&site=stackoverflow.com&pagesize=50&min=0
Aaron Bertrand
Dec 9, 2022 21:52
Right, but if they do, I'd expect them to pass in something recent, e.g. the last time they checked.
Aaron Bertrand
Dec 9, 2022 21:49
Maybe a parameter isn't getting passed along properly.
Aaron Bertrand
Dec 9, 2022 21:49
Well, I think there is a bug somewhere because if you look at the screenshot, it seems 1970-01-01 was passed in. That means all of the data.
Aaron Bertrand
Dec 9, 2022 21:45
Yeah it must be, but feels obtuse at a high level.
Aaron Bertrand
Dec 9, 2022 21:44
That's the actual SQL query that was timing out (so not a query that pulls post data). Maybe that's just something the API does first, but I will bring it up with my team as something that seems... questionable.
Aaron Bertrand
Dec 9, 2022 21:43
user image
Aaron Bertrand
Dec 9, 2022 21:43
(We have a filtered index for {published=true} but not one for {published=true OR any of these staging ground states}.)
Aaron Bertrand
Dec 9, 2022 21:41
But I'm curious what purpose just a count serves, what do you do with that? The count can go up and down as posts are published, but if you check and it's 23,468,922, and then two posts get deleted and two new posts get published, then check again, the count is still 23,468,922.
Aaron Bertrand
Dec 9, 2022 21:40
So this is why the performance changed - there isn't a great index to support that specific query.
Aaron Bertrand
Dec 9, 2022 21:39
Got it. The query that timed out is one that just gets a count of posts that have been published (like 23 million something). In the latest version of the API, it was changed to also include posts that were in various states of staging ground.
Aaron Bertrand
Dec 9, 2022 21:37
(Sorry if I come across as badgering. Just trying to piece things together.)
Aaron Bertrand
Dec 9, 2022 21:36
Ok. And did it change recently to use a newer version of the API?
Aaron Bertrand
Dec 9, 2022 21:35
Yes, this report was that a very specific API call to /2.4/questions/ was causing some backoff error message, because the underlying SQL query timed out. Why was this API call different than all of the others?
Aaron Bertrand
Dec 9, 2022 21:34
What is charcoal, what is its purpose, what is it monitoring?
Aaron Bertrand
Dec 9, 2022 21:34
The report said usually this kind of error happens 47 times a year, which seems crazy to me because we have about 30 timeouts an hour
Aaron Bertrand
Dec 9, 2022 21:33
We often troubleshoot timeout expired for all kinds of bad API calls and this is the first time I've heard of it impacting charcoal, so I'm just trying to round out my knowledge and understand
Aaron Bertrand
Dec 9, 2022 21:32
Hi there, I'm a DBRE @ Stack and would love a quick explanation of charcoal, which I heard of for the first time, oh, an hour ago
 

 Discussion on answer by J.D.: What is…

Imported from a comment discussion on dba.stackexchange.com/qu...
Aaron Bertrand
Feb 3, 2022 17:54
Point-in-time recovery isn't something you need to use very often, but when you need it, it sure feels good to have the option. I think it's more important that being in full opens up so many more (supported) options.
Aaron Bertrand
Feb 3, 2022 17:53
Haha, true. I have had enough experience with replication that it wouldn't even be on my menu. And yeah, for staging databases or whatever, simple / minimal logging makes sense. Just remember that if you flash that source from a backup and you're using it as a repl publisher, you also have to re-init all subscribers.
Aaron Bertrand
Feb 3, 2022 17:15
My thinking was simply that (minimal logging aside) if your log is effectively operating like full except those brief periods between checkpoint and next write, the only impact of being in simple is you don't have to manage log backups. But for a small incremental cost in the grand scheme of things, you get point in time recovery instead of whenever I felt like taking a full backup last.
Aaron Bertrand
Feb 3, 2022 17:13
Heh, for the corner case of people who are choosing between simple and full and know what minimal logging is, sure. :-)
Aaron Bertrand
Feb 3, 2022 16:01
@BrendanMcCaffrey So I still can't envision a scenario where I'd choose mirroring over an AG. "We're not good at clustering" doesn't mean you can't use clustering, it means you have a new skill to learn. It's like "we can't go to the cloud because we're not cloud experts." Guess how you become a cloud expert? Not by ignoring it. :-)
Aaron Bertrand
Feb 3, 2022 16:01
@BrendanMcCaffrey Well, I mean, a cluster isn't even strictly necessary if you just want read-scale secondaries and don't need automatic failover capability. Some materials here, here, here. Heck, Windows isn't even necessary, nor is a common domain, but that's scope creep for sure.
Aaron Bertrand
Feb 3, 2022 16:01
@J.D. No I was saying mirroring, log shipping, and AGs all require full (there is a hack to make mirroring work without it, but it is not supported and I have never tried it). Replication does not require full, but it affects the ability to truncate/reuse the log without adding the safety of point-in-time recovery, so at that point you may as well be in full IMHO (and the log reader doesn't do any less work in either case).