Press "Enter" to skip to content

Curated SQL Posts

ADF Data Flows and Joins Failing During Debugging

Mark Kromer clears up some issues around debugging in Azure Data Factory:

One of the important features built into ADF is the ability to quickly preview your data while designing your data flows and to execute the finished product against a sampling of data prior to finalizing and operationalizing your pipelines.

However, there are a few fundamentals relative to working with Joins that you should keep in mind and a few details below are important to understand at design time and while debugging / testing.

The answer makes sense but it would not have been the first thing to come to mind for me.

Comments closed

Using Perspectives in Power BI

Mark Lelijveld walks us through something new in Power BI Desktop’s August 2020 update:

If you work or used to work with Analysis Services, you might know the perspectives functionality. It is a feature inside tabular modelling that allows you to define viewable subsets of a data model.

Each tabular model can include multiple perspectives, where each perspective can include a subset of tables, columns a measures. Especially with large enterprise models, perspectives can be very useful.

With perspectives, you can define specific perspectives to be defines for a specific target audience. For example, the author can create logical subsets of the model for each audience of the dataset. (e.g. Sales, Finance, Marketing, etc.) One thing must clear, perspectives are not object level security or any other kind of security! It is just a better way to view it.

Read on to see how you can create and work with these in Power BI Desktop.

Comments closed

When Altering a Table Blocks the Log Reader

Aaron Bertrand walks us through a painful scenario:

We recently performed a DDL operation against a SQL Server table – simply increasing the size of a varchar column – which should have been instantaneous. Instead, we killed the SQL Server process after observing 20 minutes of HARD_SYNC_COMMIT waits and a blocked replication log reader. Could this issue have been avoided? What went wrong?

I spotted the issue pretty quickly, but it’s easy to miss in a code review. Read the whole thing.

Comments closed

Using the Ring Buffer for Monitoring

Eitan Blumin uses Extended Events to track activity:

Extended events provides a solution similar to client side trace. It basically can capture all events that a trace can capture (and more), and it also supports a wider choice of target types. And that, is where its true power lies.

It just so happens that Extended Events has a target type called “ring buffer“, and it gives us exactly what we need.

The ring buffer is easy to set up and if you don’t need permanence, works great.

Comments closed

Fun with Benford’s Law

Nagdev Amruthnath covers a topic which brings me joy:

Benford’s Law is one of the most underrated and widely used techniques that are commonly used in various applications. United States IRS neither confirms nor denies their use of Benford’s law to detect any number of manipulations in income tax filing. Across the Atlantic, the EU is very open and proudly claims its use of Benford’s law. Today, this is widely used in accounting to detect any fraud. Nigrini, a professor at the University of Cape Town, also used this law to identify financial discrepancies in Enron’s financial statement. In another case, Jennifer Golbeck, a professor at the University of Maryland, was able to identify bot accounts on twitter using Benford’s law. Xiaoyu Wang from the University of Winnipeg even published a report on how to use Benford’s law on images. In the rest of this article, we will take about Benford’s law and how it can be applied using R.

The applications to images and music were new to me. Very cool. H/T R-Bloggers

Comments closed

The Row Count Spool Operator

Hugo Kornelis dives into another operator:

The Row Count Spool operator is one of the four spool operators that SQL Server supports. It counts the number of rows in its input data, and can then later return that same amount of rows, without having to call its child operators to produce the input again.

The Row Count Spool can be viewed as similar to Table Spool, but optimized for cases where the amount of rows is relevant but their content is not. Because the content of the rows is not relevant, the operator does not need to use tempdb to store its input in a worktable; it only has to keep a running count as it reads the input. The other two spool operators have different use cases: Index Spool is used to enable the spool operator to return specific subsets of the input multiple times, and the Window Spool operator is used to support the ROWS and RANGE specifications of windowing functions.

Read on to see where this might be useful and when it may appear.

Comments closed

Managing User Input and Creating Menus in Powershell

Mark Wilkinson wants a bit of user interaction:

Fully automated hands-off PowerShell scripts can be extermely useful for the DBA or System Administrator, but what if you need to get input from the user, or maybe you want to implement a menu system? Like most things related to PowerShell, you have a few options:

Read-Host
[Console] object methods

Most use cases are covered by Read-Host, but if you need something a little more flexible, the [Console] methods might be the way to go.

Read on to see how these work, as well as one way to create a menu.

Comments closed

Statistics Management with Azure SQL DB Serverless

Joey D’Antony takes us through stats management with the serverless tier of Azure SQL Database:

One of the only things platform as a service databases like Azure SQL Database do not do for you is actively manage column and index statistics. While backups, patches, and even integrity checks are built into the platform services, managing your metadata is not. Since Azure SQL Database lacks a SQL Sever Agent for scheduling, you have to use an alternative for job scheduling. 

Read on to learn about techniques as well as a few gotchas.

Comments closed

Decoding Helm Secrets with a kubectl Plugin

Andrew Pruski didn’t want to type that much:

The post goes through deploying a Helm Chart to Kubernetes and then running the following to decode the secrets that Helm creates in order for it to be able to rollback a release: –

kubectl get secret sh.helm.release.v1.testchart.v1 -o jsonpath="{ .data.release }" | base64 -d | base64 -d | gunzip -c | jq '.chart.templates[].data' | tr -d '"' | base64 -d

But that’s a bit long winded eh? I don’t really fancy typing that every time I want to have a look at those secrets. So I’ve created a kubectl plugin that’ll do it for us!

Click through to see the code, how you install the plugin, and how you use it.

Comments closed