Press "Enter" to skip to content

Month: August 2024

“General Failure” and Query Store Compilation Times

Published 2024-08-14 by Kevin Feasel

Kendra Little warns us not to let a person named General Failure run your military:

This post demonstrates two related bugs with plan forcing in Query Store which increase the likelihood of slower query execution and application timeouts in SQL Server environments.

These bugs are most likely to impact you if:

  • You use the Automatic Plan Correction feature in SQL Server, which automatically forces query plans.
  • Anyone manually forces query plans with Query Store.
  • You have slow storage, which can increase your likelihood of having longer compilation times.

The General Purpose tier of Azure SQL Managed Instance and Azure SQL Database feature both slow storage and Automatic Plan Correction enabled by default. So, weirdly enough, your risks of suffering from this problem are high if you are an Azure SQL customer.

In the words of the great John Madden, that’s a heckuva bug.

Comments closed

Building a Microsoft Fabric AI Skill to Generate Data

Published 2024-08-13 by Kevin Feasel

Sandeep Pawar tries out AI Skills in Microsoft Fabric:

In the last blog I wrote, I showed how to call the AI Skills endpoint in a Fabric notebook. Being able to call the endpoint programmatically creates many opportunities to integrate AI Skills in different applications. One that I thought of was using AI Skills as a function. Function Calling or Tools is a specific use case in Gen AI to create structured output based on a function or behavior instructed by the user. I am not referring to that as AI Skills can only return a table. Instead, what if you created a number of these AI Skills that are grounded in your data with specific functions built to get the intended output? You could serve/share these with end users who can call these functions to generate data/results. Think of these as macros in Excel.

Click through for an example of how it works.

Comments closed

Concurrent Index Creation in Postgres

Published 2024-08-13 by Kevin Feasel

Shayon Mukherjee recommends against a particular technique:

As a developer, you might have encountered situations where creating an index in PostgreSQL fails due to lock timeouts. In such scenarios, it’s tempting to use the IF NOT EXISTS as a quick fix and move on. However, this approach can lead to subtle and hard-to-debug issues in production environments.

Click through to learn more about how concurrent index creation works in Postgres and why the use of IF NOT EXISTS might not work the way you want.

Comments closed

Highlighting a Single Data Point in Power BI

Published 2024-08-13 by Kevin Feasel

Kurt Buhler points something out:

Effective visualizations provide context so that you can interpret the numbers and what they mean to you. Is this number bad or is it good? This is particularly important for visuals that aim to provide a quick, 3-second overview, like cards, KPIs, and simple trendlines. You can provide context by comparing to a target, but if no target is available, you can also compare to a measure of central tendency, like the average or median. However, instead of comparing to an aggregate, you might also want to compare to other categories.

Consider the following example, which shows the desired end result for this article: a plot which highlights a selected value so that the user can compare it to all others. This example uses some DAX and formatting with a line chart and scatterplot to achieve the result of a joint plot atop a jitter plot. If you want to learn more about what a joint plot or a jitter plot is, we gave an overview of these and similar chart types in a previous article.

This is something I find frustratingly difficult with Power BI. Kurt does a great job of showing how to get there, but it seems like it should be a lot easier to do.

Comments closed

Migrating an Azure Data Studio Connection List

Published 2024-08-13 by Kevin Feasel

Josephine Bush packs up and moves down the road:

I wanted to migrate some connections without recreating them and wondered how to do that in Azure Data Studio. It turns out to be easy. I post about whatever I’m working on, and here’s a short one for you today.

Read on to see how to do this on Windows and MacOS. But that leaves the most critical question: what about on Linux?

Linux follows the same pattern as Windows: File –> Preferences –> Settings, and then Data –> Connection.

Comments closed

Thoughts on Start-DbaMigration in dbatools

Published 2024-08-13 by Kevin Feasel

Chad Callihan shares an experience:

It’s time to follow up on a post from a few months ago where I said I’d give my thoughts on Start-DbaMigration from dbatools after picking up more experience. Along with Start-DbaMigration I picked up some experience with Copy-DbaDatabase to migrate databases from one server to another. Below are some thoughts on the good and bad.

Read for Chad’s scenario and some of the good and the bad of Start-DbaMigration.

Comments closed

Cosmos DB HTAP into Azure Synapse Analytics and Microsoft Fabric

Published 2024-08-13 by Kevin Feasel

Paul Hernandez doesn’t want to write ETL jobs:

In the ever-evolving landscape of data management and analytics, choosing the right tools and approaches is crucial for optimizing performance and achieving business goals. Two prominent solutions that have gained traction are Azure Synapse Link for Azure Cosmos DB and Mirroring in Microsoft Fabric. Both offer unique benefits and cater to different needs, making it essential to understand their differences and use cases.

Read on to see how each of these works, as well as a quick demonstration of efficacy.

Comments closed

Service Broker Blocking on DisableQ

Published 2024-08-13 by Kevin Feasel

Tom Zika is angling to become the Service Broker Baron:

I was paged about a blocking chain where the blocked resource was a Service Broker queue, the lead blocked transaction was called CSbRollbackHandlerTask::DisableQ, the lock mode was SCH-M and even lock partitioning was involved. I won’t repro fully this time, but I’ve covered lock partitioning repro in my post Async-stats-update-causing-blocking. The goal was to capture an instance of transaction CSbRollbackHandlerTask::DisableQ with the same lock mode.

Click through for a demo and how to troubleshoot the issue.

Comments closed

Counting Character Occurrences in R

Published 2024-08-12 by Kevin Feasel

Steven Sanderson counts the ways:

Counting the occurrences of a specific character within a string is a common task in data processing and text manipulation. Whether you’re working with base R or leveraging the power of packages like stringr or stringi, R provides efficient ways to accomplish this. In this post, we’ll explore how to do this using three different methods.

Read on for three separate examples.

Comments closed

An Overview of Cross-Validation

Published 2024-08-12 by Kevin Feasel

Vinod Chugani explains the benefit of cross-validation in a data science project:

Many beginners will initially rely on the train-test method to evaluate their models. This method is straightforward and seems to give a clear indication of how well a model performs on unseen data. However, this approach can often lead to an incomplete understanding of a model’s capabilities. In this blog, we’ll discuss why it’s important to go beyond the basic train-test split and how cross-validation can offer a more thorough evaluation of model performance. Join us as we guide you through the essential steps to achieve a deeper and more accurate assessment of your machine learning models.

Click through for the full article.

Comments closed