Press "Enter" to skip to content

Month: April 2021

Read-Ahead Reads

Published 2021-04-26 by Kevin Feasel

Chad Callihan provides some helpful tips around read-ahead:

What are read-ahead reads and how do they impact SQL Server performance? Read-ahead reads allow SQL Server to think ahead to pull pages into the buffer cache before they are actually requested for a query. Up to 64 contiguous pages from a file can be read and the ability to read-ahead can be used for both data pages and index pages. Once data is in the buffer cache, it will not need to be pulled in for future queries unless it has been pushed out by other SQL Server tasks.

Click through to see what they are and how you could disable them if you really need to.

Comments closed

Service Endpoints in Azure SQL Database

Published 2021-04-26 by Kevin Feasel

Mike Wood takes us through service endpoints in Azure:

In previous installments of my “Securing Azure SQL Database” series, I covered Azure SQL Database firewall rules and private endpoints—the first of which is a way to help reduce the public exposure of your database endpoint and the second being a means to remove all public access if necessary. Each option has unique benefits, and some scenarios might call for a mix of the two options.

In this blog post, I’ll cover a third option for securing Azure SQL Database—service endpoints. This option is similar to private endpoints in that you restrict public access and only grant access to the database through your Virtual Network (VNet).

Read on to learn more.

Comments closed

T-SQL Tuesday 137 Round-Up

Published 2021-04-26 by Kevin Feasel

Steve Jones wraps up the latest T-SQL Tuesday:

I hosted the blog party this month, with the invite to write about notebooks. These are a neat technology, and I’ve written about them at SQLServerCentral.

This post is a wrap-up of the various responses to my invitation. First, quite a few people give credit to either Aaron Nelson or Rob Sewell for their writings and work with notebooks, so check out their blogs.

Click through for the list of respondents.

Comments closed

Using Calendar Tables

Published 2021-04-26 by Kevin Feasel

Aaron Bertrand has a post up on using a calendar table:

A while back, I wrote an article called Creating a date dimension or calendar table in SQL Server. I have used this pattern repeatedly and, based on the questions I get from the community, many of you are using it, too. Some of the questions I get are along the lines of “how do I actually use this table in my queries?” and “what are the performance characteristics compared to other approaches?” So, I thought I would put together a collection of use cases and analysis, starting with business day problems.

I’m a big fan of calendar tables as well. They’re quite useful for a variety of business problems and make date math problems really easy, especially when dealing with non-standard calendars (e.g., work weeks, fiscal years, figuring out what day Easter is).

Comments closed

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