Press "Enter" to skip to content

Day: May 23, 2024

The Joy of the Common Table Expression

Published 2024-05-23 by Kevin Feasel

Kevin Wilkie talks common table expressions:

Most of our coding these days has Common Table Expressions, also known as CTEs. If not, you’re either working on an older version of SQL Server or you haven’t been introduced to this piece of goodness.

CTEs can make reading SQL queries a lot easier if the logic is convoluted. For example, let’s use the following in a CTE.

I’ll admit that I probably over-use common table expressions, but I like them more than sub-queries—I find them easier to read, and if they’re going to perform as well (or poorly) as sub-queries, I’d might as well use the form that makes more intuitive sense to me.

Comments closed

Tracking Transaction Log Space in SQL Server

Published 2024-05-23 by Kevin Feasel

Greg Robidoux keeps track of available space:

Keeping track of your transaction log usage is key to understanding how your database transaction logs are being used as well as keeping track of how much space the transaction logs are using. Depending on the recovery model that is set within your database and also the backup jobs that you run, the size of your transaction log can vary quite a bit. So what is the best way to keep track of the transaction log usage?

Read on to see how. This is almost assuredly a part of any monitoring system but if you’re working to roll your own or find yourself in an environment without such a system, it’s a good thing to track.

Comments closed

Orchestration Controllers in Azure Data Factory

Published 2024-05-23 by Kevin Feasel

Martin Schoombee gets to the top of the pyramid:

Controllers are pipelines that initiate the execution of a single process or task in a specific order and with constraints. Whereas everything else in this framework is pretty automated, this part is entirely manual.

Why? Well, when I started thinking about the design of this framework I knew I needed something at the “highest level” that would execute an entire daily ETL process, or a modified ETL process that only loads specific data during the day. I wanted to maximize the flexibility of the framework, and that either meant adding another level to the metadata structure or creating this layer of pipelines that sit at the top. I opted for the second, because I did not feel it was worth the complexity of adding another layer into the metadata structure. That being said, it doesn’t mean it cannot or shouldn’t be done…it was a personal choice I made to keep things as simple as I could.

Read on to learn more about what the controller should look like and how the other pieces fit in.

Comments closed

Trying Power BI Copilot to Describe DAX Measures

Published 2024-05-23 by Kevin Feasel

Marc Lelijveld tries out a feature:

It has been roughly two years since the last update on the Power BI Model Documenter, an external tool that I developed to auto-document Power BI Semantic Models. Back then, we still called it datasets though. Looking at the stats of my website, the model documenter and related posts/pages are still most read on my website – every month again.

As the technology has kept on improving, there are new options that will help you to kick-start generating documentation about your semantic model. In this post, I will elaborate on how you can use AI in Power BI to enrich your Model Documenter output, by helping you describing the measures you’ve added to your semantic model.

Read on for a demonstration, as well as what the auto-generated results get right and wrong.

Comments closed

OPTIMIZE FOR vs Forced Plans in SQL Server

Published 2024-05-23 by Kevin Feasel

Erik Darling makes a comparison:

I often see clients using forced plans or plan guides (yes, even still, to this day) to deal with various SQL Server performance problems with plans changing.

There’s usually an execution plan or two floating around that seems to be a good general idea for a given query, and a couple weird high-end and low-end outliers for very specific populations of values.

Read the whole thing, of course.

In defense of plan guides, the company I used to work for had a few—maybe three or four in total—because of really weird data skew problems on database 106 out of 700 (or so)—because there’s always one customer that makes wildly different use of the system than everyone else. And so a query that worked perfectly fine for 699 databases (or so) flops like a fish out of water for this one database with this one customer’s data in it. So the plan guide was a nicer expediency than optimizing for mediocre on all 700 (or so) databases.

1 Comment

Open Questions on Fabric Administration

Published 2024-05-23 by Kevin Feasel

Paul Andrew asks some great questions:

Microsoft Fabric is a big product with lots of different data handling capabilities. From a data engineering perspective creating and innovating with Fabric as a unified tool is a great experience, ultimately delivering data insights for the business and adding value, nice! However, as with all new developments, the creativity is the fun part. The governance and movement of code into production is less fun and can become the hard/ugly part if the change management, platform and governance aren’t mature enough.

Paul doesn’t have answers for us, though I do think many of these will eventually have answers most people find reasonable.

Comments closed

T-SQL Tuesday 174 Round-Up

Published 2024-05-23 by Kevin Feasel

I do a thing:

I thought about doing this in the normal Curated SQL style, where I grab a graf from each post. But instead, you get the second-best Curated SQL format: the hodge-podge bulleted list, but still in present tense and with a touch of commentary and the occasional rant.

Really, it should be “I did a thing” but I can’t go having that past tense nonsense here, now can I?

Thank you again to everyone who contributed. It was a lot of fun reading through all of these.

Comments closed

Using Project Configuration for SSIS Package Deployment

Published 2024-05-23 by Kevin Feasel

Andy Brownsword shows us the brand new (well, okay, 12 years old) deployment model for Integration Services:

Last week we looked at configuring SSIS packages using package configuration. This week we’ll look at another approach for configuration: Project Configuration

Project Configuration is the standard way to configure projects. This took over from the legacy approach which we looked at last week. This method allows us to share common parameters and connections across an entire project to help managing a number of packages more efficient.

Read on to see how it works.

Comments closed