Press "Enter" to skip to content

Curated SQL Posts

SQL Server 2022 Query Store Hints

Published 2022-12-01 by Kevin Feasel

Matthew McGiffen takes a hint:

Another neat little feature in SQL Server 2022 is Query Store Hints. This is the ability to apply a query hint through Query Store rather than having to modify existing code or fiddle around with plan guides.


Query hints are a way to influence optimizer behaviour towards generating desired execution plans for a given query. The word “hint” is a bit of a misnomer as usually they mandate what you wish to happen.

Right. They’re ‘hints’ in the way that my wife ‘hints’ that I should take out the garbage.

Comments closed

Where Extended Events Go by Default

Published 2022-12-01 by Kevin Feasel

Tom Zika is curious:

Have you ever wondered where the .xel file is saved when you create a new Extended Event session and don’t specify the full path (just the file name)?

Like so: [image removed because you should go to Tom’s site and see it, ed.]

Well, so did I and here’s what I’ve found out.

Click through to learn where these files end up if you don’t specify anything.

Comments closed

Controlling Cosmos DB Time to Live

Published 2022-12-01 by Kevin Feasel

Rahul Mehta pulls out the stopwatch:

As Microsoft states, Azure Cosmos DB “is a fully managed NoSQL database service for building scalable, high-performance applications”. Cosmos DB is widely used for storing NoSQL data with options to create using different Core (SQL), MongoDB, Cassandra, Table, and using gremlin.

With wide usage, the content storage also increases, sometimes even in Gigabytes a day. With such content storage, retention and archival of data are one of the common ask from the customer. Today, we are going to talk about how to retain data and remove unnecessary data periodically from Azure Cosmos DB. Before we do that, we need to understand a storage concept called “Container”

Read on to learn about containers, as well as the built-in way to garbage collect data.

Comments closed

Azure Synapse Analytics Updates for November 2022

Published 2022-12-01 by Kevin Feasel

Ryan Majidimehr has a bundle of updates for us:

We are always working to improve Azure Analytics Spark performance. We are making significant changes that will increase Spark performance by up to 77%.  

Based on our testing using the 1TB TPC-H industry benchmark, you’re likely to see up to 77% increased performance. While your workload may perform differently than the TPC-H benchmark, everyone is expected to see improved performance. These Spark performance improvements come from moving to the latest Azure v5 VMs which have improved CPU performance, increased temporary SSD throughput, and lastly higher remote storage IOPS.  

Click through for a whole bunch of updates.

Comments closed

Migrating Azure Analysis Services to Power BI Premium

Published 2022-12-01 by Kevin Feasel

Gilbert Quevauvilliers dumps AAS:

I thought it would be a good idea to walk through the steps when looking to migrate AAS to PBI.

In the past when I had to do this for clients it was a lot of manual steps and a lot of small things to get just right. This process is now seamless and awesome!

Reviewing Gilbert’s step-by-step process, yeah, this is easy, though watch out for the pitfalls Gilbert found.

Comments closed

Redshift Query Editor v2

Published 2022-11-30 by Kevin Feasel

Anusha Challa, et al, announce a new version of a Redshift query editor:

Amazon Redshift is a fast, fully managed, petabyte-scale cloud data warehouse. You have the flexibility to choose from provisioned and serverless compute modes. You can start loading and querying large datasets conveniently in Amazon Redshift using Amazon Redshift Query Editor v2, a web-based SQL client application.

It’s worth a try if you’re a Redshift user, though I’d imagine that frequent Redshift users have already sorted out their IDEs of choice.

Comments closed

Percentiles in KQL

Published 2022-11-30 by Kevin Feasel

Robert Cain continues a series on KQL:

Often we want to get data that is relative to other data. For example, we want a list of computers that have free space that is greater than the free space of other computers. We need to set a threshold, for example we want to return results where the free space is greater than 95% of the free space on other computers.

To do this, Kusto provides the percentile operator, along with its variants percentiles and percentiles_array.

Read on to see how it works. I do like the way that KQL handles percentile operations.

Comments closed

Text Concatenation and Dataset Refresh Errors

Published 2022-11-30 by Kevin Feasel

Chris Webb follows up on a prior post:

One of the most popular topics I’ve ever written about here is the dataset refresh errors you get with dynamic data sources and the Web.Contents function in Power BI (see here and here for example). Recently I learned about an interesting new detail regarding this problem that I thought was worth blogging about, and which may help someone trying to troubleshoot it.

Read on for the scenario, as well as what you can do to avoid an issue.

Comments closed

Find and Take “Missed” Backups

Published 2022-11-30 by Kevin Feasel

Tracy Boggiano checks the lost-and-found:

I blogged a few years ago about my configuration tables for Ola Hallengren’s maintenance solution that allows me to pull the configuration parameters from tables so I don’t have to edit SQL Agent jobs and allows me to query tables to see how all my jobs are configured with Ola’s maintenance solution.  I’ve updated these as I’ve implemented them at my new job.  But I was talking to another DBA about another trick I have implemented that I didn’t originally publish and it’s how I make sure I get a full or differential backup for all databases even when things fail without a bunch of manual intervention.

With Ola’s script if you default to passing in USER_DATABASES for your @Databases parameter you can query the msdb for your time period, in my case a week (168 hours), all the databases that have been backed up and return those with a minus sign concatenated together so you can exclude those from being backed up, and append those to @Databases parameter where it has USER_DATABASES already using the below code with the magic of XML and the STUFF command and pass that back to the DatabaseBackup procedure:

Read on for the code.

Comments closed