Press "Enter" to skip to content

Day: September 20, 2021

Latches to Know

Paul Randal wraps up a series on latches with a few miscellaneous entries:

When either a heap or an index is being accessed, internally there’s an object called a HeapDataSetSession or IndexDataSetSession, respectively. When a parallel scan is being performed, the threads doing the actual work of the scan each have a “child” dataset (another instance of the two objects I just described), and the main dataset, which is really controlling the scan, is called the “parent.”

When one of the scan worker threads has exhausted the set of rows it’s supposed to scan, it needs to get a new range by accessing the parent dataset, which means acquiring the ACCESS_METHODS_DATASET_PARENT latch in exclusive mode. While this can seem like a bottleneck, it’s not really, and there’s nothing you can do to stop the threads performing a parallel scan from occasionally showing a LATCH_EX wait for this latch.

Click through to read the whole thing.

Comments closed

Concatenating in SQL Server

Lee Markup takes us through a pair of very useful functions in SQL Server:

SQL Server concatenation methods have been enhanced in modern versions of SQL Server. SQL Server 2012 introduced the CONCAT() function. In SQL Server 2017 we get CONCAT_WS().

A common usage of concatenation, or joining column values together in a string, is combining a FirstName and LastName column into a FullName column.  Another common usage might be for creating an address column that pulls together building number, street, city and zip code.

Read on to learn more. CONCAT() and CONCAT_WS() are also extremely helpful for change detection in ETL processes. For example, you might have a queue table to process and only want to update records in which relevant source fields changed, ignoring the ones which don’t exist in your destination. A combination of HASHBYTES() and CONCAT_WS() will do the trick quite nicely.

Comments closed

The Reason for Tail Log Backups

Chad Callihan explains why we need tail log backups:

When you are migrating a database from one server to another, how can you be sure to backup all transactions? Sure, you can notify the client and let them know “there will be a short outage at 8AM so please stay out of the application at that time.” Can you really trust that? Of course not. Let’s demonstrate the steps needed to include all transactions with the tail-log backup.

Protip: if you build your application such that nobody wants to use it, you can migrate the database much more easily. Assuming you don’t want to follow that outstanding advice, Chad has you covered.

Comments closed

Software Development Estimation is Hard

Andy Leonard makes a point:

I recently blogged about punching developers in the brain when a software deliverable deadline is slipping. The title of the post is The Question Unasked. In this post, I would like to address software development estimation.

In the past, I have stated “Either all software developers are pathological liars or software development is inherently inestimable.”

Read on for Andy’s take. For mine, the overly short version is that incentives are set up for software estimation to fail due to perverse incentives and a lack of requirements. Ask the question, “How long will it take to build a skyscraper?” and you might get some answer, but it certainly won’t be the answer to the pertinent question, “How long will it take to build this skyscraper?” That’s because we haven’t talked at all about location, building plans, blueprints, regulatory requirements, financing, or any of the hundreds of other things which organizations talk about and do before they start slapping mortar on bricks. With software development, we’re expected to wing it and solve all of those sorts of problems along the way. Making it even better, more often than not, the skyscraper “pivots” to being a suspension bridge.

Comments closed