A Fine Slice Of SQL Server
Mala Mahadevan gives us the low-down on database code management:
I was privileged to host yet another T-SQL Tuesday, for the month of August, 2024. My topic was on Managing database code. I was worried about getting responses, given that most of the community is no longer on X/Twitter and my invite didn’t seem to get much attention. I was wrong. People seem to keep track of this by other means and the response was excellent. Summary as below.
Read on for 11 takes on the topic.
Comments closedLouis Davidson asks for advice:
There was a lot of great advice, and I really wonder how much of this I would have listened to myself back in the day. In no order other than the order I found them in my comments and then XTwitter.
Click through for a bevy of responses.
Comments closedIt’s time to celebrate and confess.
I was honored to host the June 2024 edition of T-SQL Tuesday – #175! That’s the celebration.
Confession: I had GoDaddy add a firewall back in February and it worked well. Too well, in fact! A friend reached out to let me know comments on the blog post – titled T-SQL Tuesday #175: Old Tech, New Tech, Bold Tech, Blue Tech – was returning a nasty ACCESS DENIED message:
It was a bit of a short month in terms of turnout, but click through for Andy’s summary.
Comments closedI 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 closed