Press "Enter" to skip to content

Curated SQL Posts

Common Query Plan Patterns for Joins

Published 2021-04-14 by Kevin Feasel

Erik Darling has a new mini-series. First up is degrees of parallelism and bitmaps:

But wait! This query runs at DOP 4. You can tell by looking at the number of executions.

Who runs queries at DOP 4?

Fools.

This DOP goes up to 11.

Next, Erik versus equality predicates:

But not every query has an available equality join predicate. For various reasons, like poor normalization choices, or just the requirements of the query, you may run into things like I’m about to show you.

If there’s a headline point to this post, it’s that joins that don’t have an equality predicate in them only have one join choice: Nested Loops. You cannot have a Hash or Merge join with this type of query. Even with the best possible indexes, some of these query patterns just never seem to pan out performance-wise.

Read on for the implications of this, as well as techniques to improve performance.

1 Comment

Importing Data from ADLS Gen2 into Power BI

Published 2021-04-14 by Kevin Feasel

Chris Webb summarizes a significant amount of work:

Over the last few months I’ve written a series of posts looking at different aspects of one question: what is the best way to import data from ADLSgen2 storage into a Power BI dataset? For example, is Parquet really better than CSV? Should you use Azure Synapse Serverless? In this post I’m going to summarise my findings and offer some recommendations – although, as always, I need to stress that these are the conclusions I can draw from my test results and not the absolute, incontrovertible “Microsoft-says” truth so please do your own testing too.

Read on and check it out for yourself.

Comments closed

Using Notebooks in Azure Machine Learning Studio

Published 2021-04-14 by Kevin Feasel

Lina Kovacheva takes us through the process of working with notebooks in Azure Machine Learning Studio:

I discovered Jupiter notebooks not that long ago, but the more I use them the more I see how powerful they could be. For those of you who are not familiar whit Jupiter Notebook: It is an open-source web application where you can combine code, output, visualizations and explanatory text all in one document allowing you to write a code that tells a story. Now that you have an idea of what Jupiter notebook is I will walk you through how you can use it in Azure Machine Learning Studio.

Click through for the process. One advantage to notebooks in an environment like Azure ML over Azure Data Studio is that you have a much wider variety of languages, although Azure Data Studio has a SQL Server kernel, which other platforms currently do not have.

Comments closed

Lessons from using Notebooks

Published 2021-04-14 by Kevin Feasel

Glenn Berry takes us through some of the past (and sometimes present) challenges of running notebooks in Azure Data Studio:

I have to admit that I do not use Jupyter notebooks or Azure Data Studio (ADS) everyday. Last August, I made separate Jupyter notebook versions of my SQL Server Diagnostic Information Queries. There was a separate version for SQL Server 2012 through SQL Server 2019, along with one for Azure SQL Database. This was after a number of requests from people in the community.

Creating these notebooks was a pretty decent amount of work. Luckily, this was right around the time that Azure Data Studio was making it much easier to edit and format markdown for the text blocks. Since then, Azure Data Studio is even easier to use for editing and formatting. Even more fortuitous was the fact that Julie Koesmarno (@MsSQLGirl) volunteered to greatly improve my formatting!

Unfortunately, there has not been as much interest in my Jupyter notebooks as I hoped for. There are probably a number of reasons for this.

Read on for Glenn’s notes.

Comments closed

Power BI Cross-Report Drill-Through

Published 2021-04-14 by Kevin Feasel

Marc Lelijveld takes us through the benefits and challenges of drilling through to a different report in Power BI:

It is very common to have multiple reports for different audiences, while there is also one (group of) user(s) that requires to have an overview over all these different insights. The main challenge you will face, is having cross-report interactivity and find related insights.

Let’s take an example of three different roles, where we have a customer account manager, reseller manager and a regional manager. Of course, they should have the same single source of truth, but there is one thing you want to avoid as report creator! You do not want to create three different report for the three mentioned audiences. But as they have different roles and responsibilities, you do not want them to see each other’s data and keep it clean and simple! In this blog I will describe how you can setup cross-report drill through to jump from one report to another, while respecting applied filters and avoiding building three separate reports!

Click through for the process, as well as potential issues you may hit along the way.

Comments closed

Running a Docker Container as a WSL2 Distribution

Published 2021-04-14 by Kevin Feasel

Andrew Pruski has a wacky idea that just might work:

I’ve been playing around a bit with WSL2 and noticed that you can import TAR files into it to create your own custom distributions.

This means that we can export docker containers and run them as WSL distros!

So, let’s build a custom SQL Server 2019 docker image, run a container, and then import that container into WSL2…so that we have a custom distro running SQL Server 2019.

Read on to see how.

Comments closed

SCD2 Dimensions on Spark with Apache Hudi

Published 2021-04-13 by Kevin Feasel

David Greenshtein shows how we can build type-2 slowly changing dimensions using Apache Hudi:

Implementing SCD2 in a data lake without using an additional framework like Apache Hudi introduces the challenge of updating data stored on immutable Amazon S3 storage, and as a result requires the implementor to create multiple copies of intermediate results. This situation may lead to a significant maintenance effort and potential data loss or data inconsistency.

Apache Hudi is an open-source data management framework used to simplify incremental data processing and data pipeline development. Hudi enables you to manage data at the record level in Amazon S3 and helps to handle data privacy use cases requiring record-level updates and deletes. Hudi is supported by Amazon EMR starting from version 5.28 and is automatically installed when you choose Spark, Hive, or Presto when deploying your EMR cluster.

Click through for the process.

Comments closed

Star Schemas and Power BI Go Together

Published 2021-04-13 by Kevin Feasel

Marco Russo and Alberto Ferrari explain why star schemas make so much sense for Power BI:

Why should I have products, sales, date and customers as separate tables? Wouldn’t it be better to store everything in a single table named Sales that contains all the information? After all, every query I will ever run will always start from Sales. By storing everything in a single table, I avoid paying the price of relationships at query time, therefore my model will be faster.

There are multiple reasons why a single, large table is not better than a star schema. Here anyway, the focus is strictly on performance. Is it true that a single table is faster than a star schema? After all, we all know that joining two tables is an expensive operation. So it seems reasonable to think that removing the problem of joins ends up in the model being faster. Besides, with the advent of NOSQL and big data, there are so many so-called data lakes holding information within one single table… Isn’t it tempting to use those data sources without any transformation?

Read on to see why this is not the case.

Comments closed

Running Jupyter Notebooks from Powershell

Published 2021-04-13 by Kevin Feasel

Rob Farley has a change of heart:

The concept is that if I have a notebook with a bunch of queries in it, I can easily call that using Invoke-SqlNotebook, and get the results of the queries to be stored in an easily-viewable file. But I can also just call Invoke-SqlCmd and get the results stored. Or I can create an RDL to create something that will email me based on a subscription. And I wasn’t sure I needed another method for running something.

Read on to see what changed Rob’s mind.

Comments closed