Press "Enter" to skip to content

Month: December 2023

Saving Money on Azure SQL Database

Published 2023-12-15 by Kevin Feasel

Andy Leonard’s speaking my language:

Regular readers of this blog know I am all about saving money. I’ve blogged about ways to set up inexpensive instances of Azure SQL DB. I recently learned a couple new ways to save even more money working with Azure SQL. Both methods begin the same:

Read on for a pair of tips. Serverless mode does work really well for dev environments and cases where you don’t need it up that much (and can wait the 3-5 minutes for the service to spin up the first time you use it that day). But if you’re using it 12+ hours a day, it’s actually more expensive than using the standard model.

Comments closed

What’s Missing in Azure SQL Managed Instance?

Published 2023-12-15 by Kevin Feasel

Kendra Little gives us the lowdown:

Spoiler: a large amount of features from SQL Server 2022 are missing from Azure SQL Managed Instance. Some major features are missing that were introduced in SQL Server 2019– and here we are just a few weeks away from 2024.

But Microsoft’s top-line marketing claims about Azure SQL Managed Instance remain that “it’s always up to date with the latest SQL features and functionality.”

Let’s dig into some of the documented highlights on missing features, so you can decide for yourself what to think of that statement.

Read on for Kendra’s take. I agree with the overall argument: Managed Instance is an expensive service intended to serve as a SQL Server replacement. But there’s still a lot missing from the product and it still feels like it’s smarter simply to build a VM running SQL Server rather than play the Managed Instance game. Azure SQL DB I extend a bit more grace toward, as its intent is a bit different from the SQL Server box product or Managed Instance.

Comments closed

Trying out Azure Load Testing

Published 2023-12-14 by Kevin Feasel

Dieu Phan takes us through the Azure Load Testing service:

Azure Load Testing is a fully managed load-testing service that enables you to generate high-scale loads. The service simulates traffic for your applications, regardless of where they’re hosted so I would like to share a walkthrough Azure Load Testing in this post.

Okay, this post isn’t very data platform-centric, but I do like the Load Testing service and think more companies and people should use it.

Comments closed

Data Modeling for Sankey Charts in Power BI

Published 2023-12-14 by Kevin Feasel

Marco Russo and Alberto Ferrari explain when Sankey charts can actually make sense:

Picture this: you manage a company that sells subscription services on the web, and you want to track the evolution of your customers by analyzing different events to understand how many customers start a trial before they purchase or how many renew or cancel their subscriptions.

The report should look like this: the darker flow indicates the number of customers who skipped the trial and went directly from a phone call to purchasing a subscription.

Read on for that sales funnel example and how you can prepare the data to make best use of Power BI’s Sankey chart visual.

Comments closed

Batch Changes in T-SQL

Published 2023-12-14 by Kevin Feasel

Erik Darling doesn’t update all the rows at once like some barbarian:

The goal of batching modifications is to accomplish large numbers of row modifications with exacerbating locking problems, and being kinder to your server’s transaction log.

There is generally an appreciable difference in transaction time in modifying 1,000 rows and modifying 10,000,000 rows. Go try it, if you don’t believe me.

Click through for more information on good ways to set up batching, including the use of the highly underrated OUTPUT ... INTO ... clause on DML statements.

Comments closed

Advent of Code Day 4

Published 2023-12-14 by Kevin Feasel

Kevin Wilkie continues a Quixotic quest. Part 1 sets up the story:

Now we get to work with a set of numbers that our elf played and a set of winning numbers for each card. This sounds like something that SQL is meant for!

As always, we load the fun input data provided by the AoC group into our database. I actually loaded it in as 2 columns – Card and String. I’m just not a fan of throwing all of our data into one column of a table and letting it all get sorted out later.

Part 2 wraps it up:

With part 1, we just had to figure out how many times each of the winners showed up. With part 2 though, we have to jump through several hoops since we now have to determine how many cards we will end up with if we win the next series of cards for every match.

I slimmed down the table that I’m working with to only 2 columns – yes, in the real world, I would have used a view, but today was not that day.

By the way, as soon as I saw OverlyLongNamesThatNoOneCanTypeWithoutUsingIntellisense I wondered when Kevin got access to my code base. I have, on a few occasions, created punishment names, names so long that they punished the people who had to type or track them. In fairness, it wasn’t just a fit of pique, though pique was a common factor in all of those situations.

Comments closed

Fun with WAITFOR

Published 2023-12-14 by Kevin Feasel

Aaron Bertrand plays red light, green light:

WAITFOR is a very useful command to prevent further operations from occurring until a certain amount of time has passed (WAITFOR DELAY) or until the clock strikes a specific time (WAITFOR TIME). These commands are covered quite well (including pros and cons) in a previous tip, “SQL WAITFOR Command to Delay SQL Code Execution.”

WAITFOR DELAY is also commonly used to “let SQL Server breathe.” For example, when performing some operations in a loop, on a resource-constrained system, an artificial delay can potentially allow for more concurrency, even if it makes the intensive operation take longer.

But these commands can also be used to synchronize multiple query windows for local testing and debugging, and I thought I would share a recent example.

Click through for some of the ways you can use WAITFOR in your scripts.

Comments closed

Backup and Recovery Options for Relational Databases

Published 2023-12-14 by Kevin Feasel

Adron Hall keeps a copy:

In the realm of data management, ensuring the safety and recoverability of data is paramount. Relational databases, being at the core of many business operations, require robust backup and recovery strategies. This article delves into the general concepts of backup and recovery in relational databases, followed by specific strategies for SQL Server, Oracle, MariaDB/MySQL, and PostgreSQL.

Click through for a high-level overview of general database backup concepts and a variety of options available in the major relational database platforms.

Comments closed