Press "Enter" to skip to content

Curated SQL Posts

Getting Good Feedback

Published 2021-04-23 by Kevin Feasel

Cole Nussbaumer Knaflic explains how to get feedback:

We recently kicked off a new 10-week course, which has been really fun to develop, because it’s both longer than our typical workshops and spread out over a greater amount time. Combining these aspects means that we get to cover more topics related to data storytelling and go into greater depth on each. We kicked things off with a focus on feedback, due to the important role this will play throughout the course, and the critical role it plays in our skill development and efforts to communicate effectively with data in general.

There’s some good advice in here.

Comments closed

Deploying from One Source to Multiple SQL Servers with GitHub Actions

Published 2021-04-23 by Kevin Feasel

Kevin Chant demystifies GitHub Actions:

In this post I want to share how to deploy from one source to multiple SQL Server database types using GitHub Actions. Because I did a demo of it at Data Saturday Redmond last weekend.

By the end of this post, you will know more about how to do this using GitHub Actions. If you are used to Azure DevOps, you will find this an interesting comparison.

Previously I did a post about how you can do this using Azure DevOps. You can read that post in detail here. Later in this post I also mention an older post here a couple of times so it’s worth keeping that open.

Read on to learn how.

Comments closed

Shrinking an Azure SQL Database

Published 2021-04-23 by Kevin Feasel

Joey D’Antoni wants to take it down a notch:

You will note that I didn’t mention that “your log file grew because of a large index rebuild”. That’s because that is probably roughly (this is a really rough rule of thumb) how big your transaction log needs to be. But, anyway, we’re talking about Azure SQL Database, so you don’t need to worry about your transaction log file. Microsoft takes care of that for you: ‘Unlike data files, Azure SQL Database automatically shrinks log files since that operation does not impact database performance.’

Read on for the twist at the end.

Comments closed

Kubernetes Alone is Insufficient

Published 2021-04-23 by Kevin Feasel

Chris Adkin does some explaining:

Someone I know had worked at an organization that needed to scale out their OpenShift clusters/footprint, they were constrained by the speed of their procurement department and were wondering if they could get by with vanilla Kubernetes. Following on from this I posted a thread on twitter as to why Kubernetes on its own is not enough, much to my pleasant surprise it generated a lot of interest, as such I wanted to do this subject justice in the form of a blog post.

Read on for the full argument as well as some objection-handling.

Comments closed

Another Batch of Common Query Plan Patterns

Published 2021-04-23 by Kevin Feasel

Erik Darling continues pulling one-star query plan patterns. First up is common subexpression spools:

The first time I heard the term “common subexpression spool” my brain went numb for a week.

It’s not a particularly mellifluous phrase, but it is helpful to understand what it is.

One easy way to think about it is a temporary cache for the result of a query, like a temp table inside your execution plan:

Then we look at optimizer choices around sorting:

Sometimes these Sorts are harmless, and sometimes they’re not. There are many situational things about the queries, indexes, available resources, and query plan appropriateness that will lead you to treating things differently.

Parameter sniffing, cardinality estimate accuracy, query concurrency, and physical memory are all potential reasons for these choices going great or going grog.

Read both of the whole things.

Comments closed

Working on Multiple Repos with Azure Data Studio

Published 2021-04-23 by Kevin Feasel

Deborah Melkin shows off a feature of Azure Data Studio:

If you read my T-SQL Tuesday post from this month, I mentioned that I’ve been using Azure Data Studio on daily basis. One of the things that I find I use it for the most is for Source Control with Git. I’m incredibly surprised by this. Maybe it comes from years of using Management Studio and not being able to check in code from the tool that I’m using to write it. (Or maybe I’ve been able to do that all this time and no one told me…?)

As I’m using it, I found two things that have helped me out. So naturally, I thought I’d share.

Click through for information on how to use multiple repos, as well as a bonus item.

Comments closed

Installing Kubernetes on EC2

Published 2021-04-22 by Kevin Feasel

Praveen Sripati eschews EKS:

There are tons of ways of setting up K8S on AWS. Today we will see one of the easiest way to get started with K8S on AWS. The good thing is that we would be using t2.micro instance type, which falls under the AWS free tier. This configuration is good enough to get started with K8S and not for production setup. It’s with the assumption that the reader is familiar with the basic concepts of AWS.

Click through for the process.

Comments closed

A Primer on Columnstore Indexes

Published 2021-04-22 by Kevin Feasel

Gail Shaw gives us an introduction to columnstore indexes:

Columnstores are… different.

The first, and I would say most important thing to realise about columnstore indexes is that they don’t have keys. These are not seekable indexes. Any access to a columnstore index is going to be a scan.

Instead of storing the rows together on a page, a columnstore index instead stores column values together. The rows in the table are divided into chunks of max a million rows, called a row group, and the columns are then stored separately, in what are called segments. A segment will only ever contain one column’s values.

Read the whole thing.

Comments closed

Altering Columns in Temporal Tables

Published 2021-04-22 by Kevin Feasel

Meagan Longoria explains the process around table alteration when it’s a temporal table:

System-versioned temporal tables were introduced in SQL Server 2016. They provide information about data stored in the table at any point in time by storing an effective dated version of each row rather than only the data that is correct at the current time

You can alter a temporal table to add or change columns, but you must first turn off system versioning. Let’s look at an example.

The example here relates to a computed column, so a bit more work has to happen due to the way you define computed columns.

Comments closed