Press "Enter" to skip to content

Day: November 4, 2021

Troubleshooting with sp_HumanEvents

Published 2021-11-04 by Kevin Feasel

Erik Darling shows off sp_HumanEvents:

With shorter procs you can probably just collect actual execution plans and slam F5 like a tall glass of gin at 6am.

But you don’t wanna do that with the larger procs, for a few practical reasons:

– Lots of little queries run quickly, and we don’t care about those

– Navigating through lots of plans in SSMS is tedious

– There’s no differentiation when other procedures, etc. are invoked

– You introduce a lot of overhead retrieving and rendering all those plans

– The full query text might not be captured, which is a limitation in many places

Let’s save the day with sp_HumanEvents, my stored procedure that makes using Extended Events really easy.

Read on to see how this all works.

Comments closed

Calculating the Adaptive Join Threshold

Published 2021-11-04 by Kevin Feasel

Paul White breaks out the math books:

One thing I want you to bear in mind throughout this piece is an adaptive join always starts executing as a batch mode hash join. This is true even if the execution plan indicates the adaptive join expects to run as a row mode apply.

Like any hash join, an adaptive join reads all rows available on its build input and copies the required data into a hash table. The batch mode flavour of hash join stores these rows in an optimized format, and partitions them using one or more hash functions. Once the build input has been consumed, the hash table is fully populated and partitioned, ready for the hash join to start checking probe-side rows for matches.

This is the point where an adaptive join makes the decision to proceed with the batch mode hash join or to transition to a row mode apply. If the number of rows in the hash table is less than the threshold value, the join switches to an apply; otherwise, the join continues as a hash join by starting to read rows from the probe input.

Read the whole thing and learn more about the cutoffs for adaptive joins.

Comments closed

Wildcards and Data Type Precedence

Published 2021-11-04 by Kevin Feasel

Daniel Hutmacher has fun with implicit conversion:

Implicit conversions in SQL Server follow a specific, predictable order, called data type precedence. This means that if you compare or add/concatenate two values, a and b, with different data types, you can predict which one will be implicitly converted to the data type of the other one in order to be able to complete the operation.

I stumbled on an interesting exception to this rule the other day.

Click through for an example of date searching with LIKE.

Comments closed

Ignite Announcements

Published 2021-11-04 by Kevin Feasel

James Serra has a round-up of Ignite 2022 announcements:

 Azure Managed Instance for Apache Cassandra: Is now GA. Cassandra is an open source, column family store NoSQL database. The Azure Cassandra service includes an automatic synchronization feature that can sync data between with customers’ own Cassandra instances, on-premises and elsewhere. More info

Wolfgang Strasser has some thoughts as well on what Ignite has shown us so far:

As you might have noticed, Azure Purview is one of my newest friends in Azure Data town. During Ignite, the support for Amazon RDS (Relational Database Service), the Data Lake Data Asset Access Governance, and Microsoft Defender for Cloud Integration with Azure Purview was announced.

What I really look forward to test is the Data Asset Access Governance for Data Lake storages. Imagine a world that allows you to define permission on a central place and those permissions are brought to a storage account / system of your choice..

Read both of them for two different perspectives.

Comments closed