Press "Enter" to skip to content

Curated SQL Posts

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

A Primer on ACID Properties and Transactions

Published 2024-10-30 by Kevin Feasel

Joe Celko covers four useful properties of database transactions:

ACID is a cute acronym for Atomicity, Consistency, Isolation, and Durability. This is a set of properties we want in transactions in a database. The original work was done by the late Jim Gray, but this acronym is due to Andreas Reuter and Theo Härder at IBM. These four properties as defined by Reuter and Härder are as follows:

Click through for a description of each property, as well as a brief overview of isolation levels.

Comments closed

Sending E-Mail from SQL Server and Snowflake

Published 2024-10-30 by Kevin Feasel

Kevin Wilkie says you’ve got mail:

For the many other parameters you can use with sp_sendmail, please refer to the Microsoft Documentation found here.

After running this stored procedure, SQL Server queues the email to be sent. From there, you should check often to see if it was actually sent. (And yes, it can get stuck there forever.)

With, Snowflake, it’s actually fairly close to the same basic principle as SQL Server.

Click through for the syntax for both systems.

Comments closed

KQLMagic in Fabric Runtime 1.3

Published 2024-10-30 by Kevin Feasel

Sandeep Pawar spreads the news:

I wrote a blog last year on the usefulness of KQLMagic command in Fabric notebook and made a suggestion that it should be part of the default runtime. Well, guess what – it’s now in the Fabric Runtime 1.3. No installation necessary and authentication is handled automatically.

Read on to learn more about how you can use KQLMagic in a Microsoft Fabric notebook to read from an Eventhouse.

Comments closed