Press "Enter" to skip to content

Curated SQL Posts

Efficient Polling of Remote Data Sources

Ed Pollack looks at ways of optimizing linked server connections to remote SQL Server and Postgres instances:

Data rarely resides in one place. Oftentimes, there is a need to collect data from many different data sources and combine them locally into meaningful reports, analytics, or tables. The process for accessing, collecting, validating, and using data from remote data sources requires the same level of design and architectural considerations as building data structures for a software application.

In this article, methods of accessing remote data will be introduced with the goal of presenting best practices and ways to optimize load processes. This will lead into a discussion of performance and how to avoid the latency often associated with loading data from remote locations.

Click through for the article.

Comments closed

Refresh a Dataset when Dataflow Refresh Completes

Matthew Roche points out an API update:

Back in August I highlighted the new dataflows PowerShell script repo on GitHub. These scripts provide an accelerated starting point to working with the dataflows REST APIs and to automate common dataflows tasks.

This week the dataflows team has released two new REST APIs for managing dataflows transactions (think “refresh”) and a new parameterized PowerShell script to make using the APIs easier.

Read on to see how you can use these new APIs to trigger a dataset refresh once a dataflow refresh has completed.

Comments closed

Color Combination Testing in Powershell

Jeffrey Hicks has a script to test out color combinations in Powershell:

A lot of my PowerShell work lately has involved color. I find myself using ANSI escape sequences quite often. I’m also playing with different color schemes in Windows Terminal. And I still on occasion find myself using Write-Host to display colorized messages. What has gotten trickier is that Windows Terminal schemes can redefine colors. What I am used to as Green may not actually be Green. I polished up a simple script, to display all possible combinations for the console colors.

Read on for the script, as well as some oddities with Windows Terminal.

Comments closed

What Makes for a Good Estimator?

Jasmine Nettiksimmons and Molly Davies explain what estimators are:

What makes a good estimator? What is an estimator? Why should I care? There is an entire branch of statistics called Estimation Theory that concerns itself with these questions and we have no intention of doing it justice in a single blog post. However, modern effect estimation has come a long way in recent years and we’re excited to share some of the methods we’ve been using in an upcoming post. This will serve as a gentle introduction to the topic and a foundation for understanding what makes some of these modern estimators so exciting.

Read on for a very nice introduction to the topic.

Comments closed

Azure Arc Deployment Options for SQL Server

Sasha Nosov takes us through Azure Arc deployment options for SQL Server:

As you can see, both on-Azure and off-Azure options offer you a choice between IaaS and PaaS. The IaaS category targets the applications that cannot be changed because of the SQL version dependency, ISV certification or simply because the lack of in-house expertise to modernize. The PaaS category targets the applications that will benefit from modernization by leveraging the latest SQL features, gaining a better SLA and reducing the management complexity.

Click through for a graphic, as well as further clarification on each item.

Comments closed

Find and Modify File Growth Settings Across Databases

Lori Brown has a challenge:

To some of you out there, that many databases might not be a big deal.  To me, it is a big deal.  That many databases can cause many admin tasks to take a very long time.  One such task is to review all databases to make sure that they are not growing their files by 1MB or 10 Percent, especially the transaction log files.  I find those default settings to not be a good thing generally, and have learned to set files to grow by specified amounts if auto growth needs to occur.  I also try to manually grow data files during maintenance periods but we all know that auto growth happens.

So…how do we find out if our databases have 1MB or 10 Percent file growth set up?

Click through for a script which finds default-sized databases, as well as a script to fix them.

Comments closed

Learn Powershell with PSKoans

Mikey Bronowski shows off PSKoans:

Recently I have got a question about resources to learn PowerShell. There is plenty out there in the wild, but I came across an interesting module I would like to write today – PSKoans.

I’m a big fan of the koan strategy of learning. It ramps you up slowly and gives you plenty of code to help understand syntax and flow. The F# koans are fantastic, as are Python’s.

Comments closed

Understanding MERGE Execution Plans

Hugo Kornelis walks us through the most interesting operator:

But first a word of warning. The MERGE statement, introduced in SQL Server 2008 as an easier alternative for “delete / update / insert” logic, turned out to have issues when it was released. And now, in 2020, many of those issues still exist. So I’ll just point you to Aaron Bertrand’s excellent overview, and leave you with the recommendation to be extremely wary before using MERGE in production code.

But here, we are not going to use MERGE in production. We are merely going to set up a simple test and look at how the elements in the execution plan cooperate to produce the expected results. This is interesting even if you never use MERGE, because many of the details explained below can also occur in other execution plans.

Read the whole thing, even if you avoid MERGE like the plague.

Comments closed

The SQL Server and Containers Guide

Andrew Pruski has a guide:

I’ve been blogging about running SQL Server in Docker containers for a while now and, to be honest, my blogs are scattered over a few years and some need to be archived as they’re out of date.

So what I wanted to do was have one place where I could collate all the blogs I’ve written about running SQL Server in a container. This would make it easy for people to access information and make it easy for me to keep it all up-to-date as well.

So introducing, The SQL Server and Containers Guide!

Go check it out.

Comments closed