Press "Enter" to skip to content

Curated SQL Posts

A Primer on Outlier Detection

Published 2024-10-31 by Kevin Feasel

Jayita Gulati provides an overview:

Anomaly detection means finding patterns in data that are different from normal. These unusual patterns are called anomalies or outliers. In large datasets, finding anomalies is harder. The data is big, and patterns can be complex. Regular methods may not work well because there is so much data to look through. Special techniques are needed to find these rare patterns quickly and easily. These methods help in many areas, like banking, healthcare, and security.

Let’s have a concise look at anomaly detection techniques for use on large scale datasets. This will be no-frills, and be straight to the point in order for you to follow up with additional materials where you see fit.

Outlier detection is a large an interesting space. I suppose I should shill for myself a little bit and note that I wrote a book on the topic. This post provides some quick guidance around outlier detection techniques and applications, and serves as a fine starting point for digging in further.

Comments closed

Updates in .NET 9

Published 2024-10-31 by Kevin Feasel

Ajay Jajoo tells us what’s new:

One of the standout features of .NET 9 is its focus on performance. With numerous optimizations across the runtime and libraries, applications can expect faster execution times and reduced memory usage. This is particularly beneficial for high-load applications, making .NET 9 an ideal choice for cloud-based solutions.

.NET 9 brings various performance optimizations, including improvements in garbage collection and just-in-time (JIT) compilation.

If you work at all with C#, you’ll see some quality of life improvements in .NET 9. But given Microsoft’s policy around short-term and long-term releases, you might wait until .NET 10 in many corporate environments to see them.

Comments closed

Query Processor Ran out of Internal Resources

Published 2024-10-31 by Kevin Feasel

David Fowler explains an error:

Recently I received a cry for help over Teams. The issue was that an application was throwing up the following SQL error,

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

I’ll be honest, that’s not one that I had seen before but it seemed pretty self explanatory. the query was just too complex for SQL to cope with. I asked what the query was, the answer was something similar to the snippet below,

Read on to learn what the problem was, as well as David’s answer. David had a simple rewrite retaining the IN clause, though you could also rewrite this with an INNER JOIN or even an EXISTS. One of those two alternative approaches might have a better performance profile, though there are no guarantees.

1 Comment

T-SQL Notebooks in Microsoft Fabric

Published 2024-10-31 by Kevin Feasel

Dennes Torres tries out T-SQL notebooks:

T-SQL Notebooks is one of the new features announced during FabCon Europe.

The most distracted could miss the fact this is a new feature at all. Yes, it is. Notebooks were capable to support Spark SQL, but T-SQL is something new.

The main examples being announced are built with data warehouses, but let me confirm and highlight this:

T-SQL Notebooks support lakehouses as well.

There is at least one limitation: DML is not supported with lakehouses.

Saving my rant about lakehouses vs warehouses in Fabric, do read what Dennes has to say about T-SQL notebooks as they exist today.

Comments closed

Timeouts Attempting to Open Connections from High-Thread Applications

Published 2024-10-31 by Kevin Feasel

Jose Manuel Jurado Diaz works through a customer issue:

Recently, I worked on a service request that a customer application reported the following error connecting to the database: “Timeout attempting to open the connection. The time period elapsed prior to attempting to open the connection has been exceeded. This may have occurred because of too many simultaneous non-pooled connection attempts.“. 

Following, I would like to share the experience learned here.

The issue isn’t extremely common, but it does happen, especially when applications don’t use connection pooling.

Comments closed

Dynamically Running Notebooks across Fabric Lakehouse Environments

Published 2024-10-30 by Kevin Feasel

Ayman El-Ghazali solves a problem:

A few months ago, an ISV customer approached with a request to have notebooks run across Microsoft Fabric Lakehouse environments dynamically.  Initially the first request was to allow pipelines in Fabric to pass parameters for file paths to help with data ingestion.  This would allow the customer to use the same notebook across Lakehouse environments for the customers that they are serving. After resolving this, the scope increased to include the notebook execution. The notebooks should be able to run across workspace environments and not have to be attached to a Lakehouse at the time of execution.  The solution presented below allows for the customer to run notebooks across environments but also allows them to run SQL queries against existing Lakehouse tables; additionally it allows for access to tables created during the notebook execution run without the notebook being attached to the Lakehouse. 

Read on to learn how.

Comments closed

Backing up SQL Server via PowerShell

Published 2024-10-30 by Kevin Feasel

I have a new video:

In this video, I show how to perform a variety of database backup operations via PowerShell, using the dbatools PowerShell module. I also show how easy it is to test a database backup using dbatools.

I finally have a video shorter than 10 minutes long. Don’t worry, the next one will blow right past that figure.

Comments closed