Press "Enter" to skip to content

Curated SQL Posts

Invoke External REST Endpoints from Azure SQL DB

Published 2022-11-17 by Kevin Feasel

Rob Farley is impressed:

This internal procedure is new in Azure SQL DB in 2022. I think it presents a significant change to the way we do things in the world of SQL, and makes some other tools a whole lot more useful as well.

sp_invoke_external_rest_endpoint lets me send data to a REST API from within a stored procedure. Invoking an HTTP REST endpoint – as simple as that. And while I know you’re probably thinking, “But I can send data to a REST API from anywhere – why do I need to do it from within a stored procedure?”, I want to describe a few scenarios to you.

I like having the functionality, though would want to control how frequently my teams would use it. The reason is that this potentially makes your database the a domain boundary (when thinking in domain-driven design concepts).

Comments closed

Query Cost Normalizing

Published 2022-11-17 by Kevin Feasel

Tibor Karaszi does the math:

There are plenty of articles out there recommending you to up the “cost threshold for parallelism” configuration option. Most of you already know this, if not fire up your favorite search engine and spend a few minutes reading about it.

My aim here is to do a totally non-scientific test if I can translate these fictitious seconds to what they correspond to on a reasonably modern hardware – which is my laptop from 2020.

This is one interesting approach to back into a cost threshold for parallelism.

Comments closed

Continuous Backup for Cosmos DB

Published 2022-11-17 by Kevin Feasel

Manvendra Singh wants a backup:

This article will explore Continuous backup and steps to configure it for a new Azure Cosmos DB account or an existing Cosmos DB account. Azure Cosmos DB is a fully managed and highly secure, NoSQL database service on the Azure cloud that is designed for modern-day application development. It automatically runs backup for its databases on separate Azure blob storage at regular intervals without affecting the performance, availability, and provisioned resource units (RUs) to ensure data protection from a data recovery standpoint which can be needed in case of data corruption, deletion, or wrongly data updates.

Click through for the process and some limitations.

Comments closed

Full-Text Search in Postgres

Published 2022-11-16 by Kevin Feasel

Adam Zegelin takes us through full-text search options in PostgreSQL:

Full-text Search is a PostgreSQL® feature that facilitates the indexing of natural language text documents, and in the identification of indexed documents that match a given query. Matching documents can be sorted based on their relevance to the query, and document excerpts can be generated with the matching terms highlighted. A set of SQL data types, operators, and functions are provided to assist with the indexing, querying, and ranking of documents.

PostgreSQL uses the term document to mean any fragment of natural language text— essentially, strings containing human-readable words separated by whitespace and punctuation. Documents are often stored as text columns but can also be generated dynamically—such as by concatenating multiple columns together (even from multiple tables).

Click through for the tutorial.

Comments closed

Increased Response Rate and Request Time in Kafka

Published 2022-11-16 by Kevin Feasel

Danica Fine and Nikoleta Verbeck troubleshoot another common Apache Kafka issue:

It can be easy to go about life without thinking about them, but requests are an important part of Kafka; they form the basis of how clients (both producers and consumers) interact with data as it moves into and out of Kafka topics, and, in certain cases, too many requests can have a negative impact on your brokers. To understand how requests can affect the brokers, it’s important to be familiar with what happens under the hood when a request is made. 

Read on to see how the process works under the covers, what kinds of metrics you can use to determine how well things are going, and what might be going wrong if you see certain symptoms.

Comments closed

Removing Indexes by Name from Multiple SQL Servers

Published 2022-11-16 by Kevin Feasel

Jana Sattainathan doesn’t have time to click-and-repeat:

This is something I had to do today. In this blog post, I am going to show you how you can disable or remove indexes matching certain name criteria or other condition(s) across multiple servers and databases in various tables. Usually, this is a very painful process if done by hand but with the fantastic work done by the dbatools team, it becomes quite easy.

Read on for the Powershell script.

Comments closed

Defining “Legacy”

Published 2022-11-16 by Kevin Feasel

Brendan Tierney takes apart a phrase:

In the IT industry we hear the term “legacy” being using, but that does it mean? It can mean a lot of different things and it really depends on the person who is saying it, their context, what they want to portray and their intended meaning. In a lot of cases people seem to use it without knowing the meaning or the impact it can have. This can result in negative impact and not in the way the person intended.

Before looking at some (and there can be lots) possible meanings, lets have a look at what one person said recently.

Read on for a thoughtful reply to some marketing madness.

Comments closed

Defragmenting Power BI Fact Tables

Published 2022-11-16 by Kevin Feasel

Chris Webb reminds us that Power BI incremental refresh can lead to some fragmentation:

A lot of people are building large datasets in Power BI Premium nowadays, and if you’re building a large dataset you’re very likely to be using Power BI’s incremental refresh functionality with your fact tables or manually creating and refreshing partitions in them. If so, you should occasionally do a special kind of refresh to shrink the dictionaries on your fact tables.

Read on for a test of the most extreme scenario, though even less extreme versions can be bad.

Comments closed