A Fine Slice Of SQL Server
I thought about doing this in the normal Curated SQL style, where I grab a graf from each post. But instead, you get the second-best Curated SQL format: the hodge-podge bulleted list, but still in present tense and with a touch of commentary and the occasional rant.
Really, it should be “I did a thing” but I can’t go having that past tense nonsense here, now can I?
Thank you again to everyone who contributed. It was a lot of fun reading through all of these.
Comments closedRob Farley ponders a pair of potential performance improvements and their effects on durability:
Durability in SQL is handled by making sure that data changes are written to disk before the calling application is informed that the transaction is complete. We don’t walk out of a shop with our goods before the cashier has confirmed that the credit card payment has worked. If we did, and the payment bounced, the cashier would be calling us back pretty quickly. In SQL this is about confirming that the transaction log entry has been written, and it’s why you shouldn’t use disks with write-cache for databases
And yet, in-memory features of SQL, commonly called “Hekaton” handles transactions without writing to disk. The durability is delayed. This month, Todd Kleinhans invites us to write about Hekaton.
In-Memory OLTP is one of those features that I wish worked better for most use cases or didn’t have as many limitations around only working within the context of a single database. In practice, instead of using In-Memory OLTP for most tables, you’re usually better off just jamming more RAM on the box and limiting how many scans of large tables flush your buffer pool.
Comments closedBrent Ozar wraps up this month’s T-SQL Tuesday:
So when I’m meeting a new team and learning what they do, I’ve found it helpful to ask, “What specifically was the last issue you closed?” Note that I don’t ask, “What are you working on now?” because that tends to lead to long-term projects that people want to do, but not necessarily what they’re paid to do. If you ask them about the last specific task they checked off, that’s usually related to something the company demands that they do because it’s urgent. It leads to fun discoveries about what people think they do, versus why managers really keep them around on the payroll.
Click through for this month’s list of respondents.
Comments closedAaron Bertrand has an admission:
Before looking at the code path, the query, or the execution plan, I didn’t even believe the application would regularly perform a hard delete. Teams typically soft delete “expensive” things that are ever-growing (e.g., change an
IsActivecolumn from1to0). Deleting a user is bound to be expensive, because there are usually many inbound foreign keys that have to be validated for the delete to succeed. Also, every index has to be updated as part of the operation. On top of that, there are often triggers that fire on delete.While I know that we do sometimes soft delete users, the engineer assured me that the application does, in some cases, hard delete users.
Click through for the full story and a minor bout of self-petard-hosting. I’m as guilty as anyone else of jumping to conclusions, and this is a good reminder to go through the process even when you think you know the answer.
Comments closedShane O’Neill applies the Sgt. Schultz defense:
I don’t like posting about issues unless I fundamentally understand the root cause. That’s not the case here. A lot of the explanation here will be hand-waving while spouting “here be dragons, and giants, and three-headed dogs”, but I know enough to give you the gist of the issue.
Click through for a fun story about Kerberos and behavioral changes after absolutely nothing happened.
Comments closedMichael J. Swart does some modeling:
How do we predict whether it’s safe to put workloads from two servers onto one?
We use Availability Groups to create readable secondary replicas (which I’ll call mirrors). The mirrors are used to offload reporting workloads. The mirrors are mostly bound by IOPS and the primaries are mostly bound by CPU, so I wondered “Is there any wiggle room that lets us consolidate these servers?”
Can we point the reporting workloads (queries) at the primary replica safely?
Read on for the answers to these questions.
Comments closedLucas Kartawidjaja goes on a quest:
The vulnerabilities are affecting Microsoft ODBC Driver 17 and 18, as well as OLE DB Driver 18 and 19. For more information and also download location for the security update/ hotfix can be found on the following page: Update: Hotfixes released for ODBC and OLE DB drivers for SQL Server
We do an automated security scanning tool that would flag the systems (servers, desktops, latptops, etc.) that haven’t been patched. So we can quickly identify the systems that need to be patch and patched those systems quickly.
For this post, I was wondering if there is a quick way to identify Microsoft ODBC and OLE DB drivers that are being installed on the systems.
Click through to see what Lucas came up with.
Comments closedKevin Chant talks about a recent issue:
Recently I have been helping others get up to speed with Microsoft Fabric. Which includes going through some Power BI topics.
One issue that came up was how to show them the DAX used for a measure within a Power BI report that had been published to Microsoft Fabric. To link working with measures in Power BI Desktop with working in Microsoft Fabric.
Kevin shows the normal way of doing this, as well as an alternative using the SemPy library.
Comments closedMikey Bronowski fixed a problem:
Regular execution of DBCC CHECKDB is a cornerstone practice for DBAs, ensuring that databases are free from corruption. However, this routine maintenance can sometimes feel more like a Herculean task, especially when DBCC CHECKDB runs slower than a snail in molasses, or worse, gets terminated because it runs too slow.
Read on to see what Mikey did to fix the issue. This is a good reminder that sometimes, there is no single silver bullet, but a whole magazine of lead can still get you to the same location.
Comments closedIn fact, I just fixed a stored procedure that had its performance change due to an upgrade to SQL Server 2022 last week. We were doing internal testing in our test environment and one proc suddenly took significantly longer than it should have. But it was also a proc that had not changed in months so it was very clear that the reason it became a problem was due to the upgrade.
Click through for some detail on how Deborah figured it out.
Comments closed