Press "Enter" to skip to content

Category: Administration

Halloween Problem and Inserts

Published 2020-07-30 by Kevin Feasel

Jared Poche continues a dive into the Halloween Problem:

I would have expected us to scan the temp table, then have a LEFT JOIN to the base table. The Table Spool is the red flag that we have an issue with the plan, and is frequently seen with Halloween protections.

The index scan on the base table seems to be overkill since we’re joining on the primary key columns (the key lookup isn’t much of a concern). But we’re likely doing the scan because of the spool; it’s SQL Server’s way of getting all relevant records in one place at one time, breaking the normal flow of row mode operation, to make sure we don’t look up the same record multiple times.

Read on to see the execution plan as well as Jared’s fix.

Comments closed

Working with SQL Server Configuration Files

Published 2020-07-29 by Kevin Feasel

Jamie Wick takes us through an underrated part of the SQL Server installer:

The ability to use a parameter file (configurationfile.ini), for automating the installation of SQL Server, has been around for many years. However, each release of SQL Server has had different parameters that could be included in the file. Here are some directions on how to find or create a parameter file, along with the parameter values that are supported by each version of SQL Server.

I appreciate the fact that every installation of SQL Server generates one of these and even points it out to you as you go through the installer wizard. And Jamie has gone a step further by giving us an Excel spreadsheet with all of the available settings and their defaults.

Comments closed

Enforcing Database Practices with Policy-Based Management

Published 2020-07-27 by Kevin Feasel

Nisarg Upadhyay hits on one of my favorite features Microsoft doesn’t care about anymore:

In this article, I am going to explain how we can enforce the SQL database best practices using Policy-Based Management. The policy-based management feature of SQL Server was introduced in SQL Server 2005. This feature was useful because it helps database administrators to define and enforce the database policies based on the organizations’ requirements.

Back when I was a DBA, I really appreciated Policy-Based Management. Unfortunately, although my job has changed a few times since then, Policy-Based Management hasn’t.

Comments closed

Finding and Downloading SQL Server Updates

Published 2020-07-22 by Kevin Feasel

Andy Levy combines dbatools and KBUpdate:

Another of Chrissy LeMaire’s (blog | twitter) projects is KBUpdate. Compared to dbatools it’s a pretty compact module, but it’s incredibly useful – it’ll seek out information about KB updates and even download them for you! She’s also rolled these functions into dbatools for convenience, so we don’t need to install or import that module separately.

Read on to see how Andy ties it all together.

Comments closed

Latching in SQL Server

Published 2020-07-22 by Kevin Feasel

Dan Jackson explains the concept of latching in SQL Server:

To start with, a basic definition: ‘Latches are lightweight synchronization objects, that are used by the storage engine of SQL Server to protect the internal memory structures’. Compare this with locks in SQL server, which are a transaction level construct to manage concurrency, latches work at the thread level to maintain data integrity within the internal memory structures. They are not exposed outside of the SQL Server Operating System (SQLOS). They are only managed by SQL Server itself, not by users (unlike locks that can be overridden via lock hints or changing isolation level). It is useful to keep in mind that a single transaction can use multiple threads at the same time.

Latching is a funny concept to me, in that I think people say “This must be a latching problem” far more than there actually is, but when there is a proper latching problem, it usually winds up being a pretty big deal.

Comments closed

Missing Indexes Don’t Tell the Whole Story

Published 2020-07-21 by Kevin Feasel

Erik Darling explains some of the shortcomings of the missing indexes DMV:

The problem with relying on any data point is that when it’s not there, it can look like there’s nothing to see.

Missing indexes requests are one of those data points. Even though there are many reasons why they might not be there, sometimes it’s not terribly clear why one might not surface.

That can be annoying if you’re trying to do a general round of tuning on a server, because you can miss some easy opportunities to make improvements.

Read on for a few examples of where the results can betray you.

Comments closed

Auto-Shutdown an Azure VM and Notify You on Slack

Published 2020-07-21 by Kevin Feasel

Daniel Hutmacher has a fun assignment:

Virtual machines cost money when they’re powered on. Most servers obviously need to be on 24 hours a day. Others, like development machines, only have to be on when you’re using them. And if you forget to turn them off, they’ll empty out your Azure credits (or your credit card) before you know it.

Today, I’ll show you how to set an Auto-shutdown time to turn a VM off if you forget, as well as have Azure notify you on Slack 30 minutes ahead of time, so you have the option to postpone or cancel the shutdown.

There are a few steps to the process, but everything is straightforward.

Comments closed

Azure SQL Database Business Continuity Options

Published 2020-07-21 by Kevin Feasel

James Serra covers business continuity scenarios with Azure SQL Database:

I have wrote a number of blogs on the topic of business continuity in SQL Database before (HA/DR for Azure SQL Database, Azure SQL Database high availability, Azure SQL Database disaster recovery) but with a number of new features I felt it was time for a new blog on the subject, focusing on disaster recovery and not high availability.

Business continuity in Azure SQL Database and SQL Managed Instance refers to the mechanisms, policies, and procedures that enable your business to continue operating in the face of disruption, particularly to its computing infrastructure. In the most of the cases, SQL Database and SQL Managed Instance will handle the disruptive events that might happen in the cloud environment and keep your applications and business processes running.

James takes us through options available for Azure SQL Database as well as managed instances.

Comments closed

The Tuple Mover in SQL Server 2019

Published 2020-07-21 by Kevin Feasel

Taryn Pratt gives us closure on an issue from a few months back:

I suggest reading my other post first, it’ll only take a few minutes. I’ll wait…

However, if you really don’t want to read it, here’s a quick recap on the initial issue.

In early February 2020, a lot of data was deleted from some clustered columnstore indexes in our PRIZM database. Some of the tables were rebuilt, but 11 tables weren’t since we don’t have maintenance windows, and that would involve downtime. The rebuilds would happen once we upgraded to SQL Server 2019, to take advantage of the ability to rebuild those columnstore indexes online.

Taryn now has the full story and I recommend giving it a read.

Comments closed