A Fine Slice Of SQL Server
Grant Fritchey learns a thing:
I live for questions and this was an interesting one. Can you see Table Valued Parameters that have been passed in to Extended Events? I literally have no idea. I’m sure we’ll see something, I just don’t know what. Time to find out.
Click through for the answer.
Comments closedHaripriya Naidu wants to know what object this is:
You run a query to check for locking or blocking in SSMS and find a wait resource in the format (8:1:3610). To identify the object name, you would typically run multiple queries, first find database name, turn on trace flag 3604, then find object id from DBCC PAGE and then find object name from sys.objects.
However, with a new function “sys.dm_db_page_info” introduced in SQL Server 2019, you no longer need to go through these steps. Instead, you can run a single query to get the object name directly.
Read on to see how it all works. This is definitely a lot easier than in the olden days.
Comments closedAndy Brownsword stores some context:
Package configuration allows us to retrieve variable values from a variety of sources. We recently looked at storing the values in a SQL database.
We can elevate the database configuration a step further by using system level Environment Variables. In this post we’ll walk through that process.
Click through to see how.
Comments closedKendra Little takes a peek at a view:
Views let you do dumb things by accident in SQL Server. Then they make you have to think way too hard to fix them.
Most of the time when people create views, they start by refining a
SELECTquery, then turn it into a view. People also often create multiple views that pull different slices of data and UNION the results together.Combined, these two things easily lead to undeclared datatypes in views with problematic implicit conversions.
Read on for an example of this problem in action. Kendra’s example involved a view and a separate table, but you can also see this kind of thing pop up in a view that itself contains set operators like UNION.
Comments closedErik Darling answers a set of user questions:
You have said that table variables, CTEs, Change Tracking, and Azure Managed Instances all suck. Do you have a full list of “features” to avoid?
Click through for a video of Erik answering questions around deadlocks, terrible things, UTF-8, and more. And I like the nuance behind Erik’s answer of this particular question. It’s easy to say “this thing is awful” and be done with it, but often times, the answer is more of “In this particular circumstance, don’t use this thing because of reasons X, Y, and Z; instead, use this thing.” That’s a rather different answer.
Comments closedJosephine Bush wants an extra copy of the backup:
This may or may not be helpful in the long term, but since I’m doing it to be super cautious, I figured I would blog about it. We migrated to Flex last week, and to be abundantly cautious, we’re putting the last single server backup into cold storage. You could also use this same process to offload Flex if you were going to delete a server and want to save a final backup or have some use case for saving backups to storage longer term.
Read on for the process. It’s not as simple as running a command or two, but Josephine does take us through the process.
Comments closedShayon Mukherjee discusses trade-offs:
I was recently looking into some workloads that generate a lot of I/O and CPU contention on some very high-write code paths and came across
synchronous_commit(https://www.postgresql.org/docs/current/wal-async-commit.html). It can be very tempting to turn this off globally because the performance gains in terms of I/O, CPU, and TPS (transactions per second) are very hard to overlook. I noticed I/O completely gone, CPU down 20% (at peak), and a 30% increase in TPS. However, this comes with important trade-offs that are worthwhile keeping in mind.
Click through for more information. This sounds a lot like the delayed durability feature in SQL Server, though you have more fine-grained control in PostgreSQL versus it being a database-level setting in SQL Server.
Comments closedRick Dobson signs a procedure:
This tip introduces the basics of digital signatures based on self-signed certificates for selected T-SQL objects (stored procedures, functions, and triggers) with T-SQL samples. Learn how to detect what happens to a digital signature when a stored procedure is accidentally altered by a database administrator or intentionally modified by a bad actor.
Click through for the process.
Comments closedStaff writers in the Confluent writing mines perform a deletion:
Can you delete a topic in Apache Kafka®? The answer is yes—but the process depends on your Kafka configuration and the environment in which you are working (i.e., if it is self-managed, hosted in the cloud, or a fully managed Kafka service like Confluent Cloud).
Read on to see how you can do so, as well as some recommendations around deletion and topic management.
Comments closedHamza Boubou builds a dynamic report:
There was a requirement from my client that seemed simple at first but turned out to be a Trojan horse after deeper investigation. The goal was to create a Power BI Time Comparisons Top N Analysis page, giving users complete control over the Top/Bottom N products based on multiple metrics. Users needed to define the period, compare it with other periods, and adjust the N parameter dynamically.
Read on to see how Hamza was able to solve this customer request.
Comments closed