Press "Enter" to skip to content

Category: Administration

Minimizing PAGELATCH_EX Waits in SQL Server 2019

Published 2021-07-27 by Kevin Feasel

Aaron Bertrand has some advice for us:

There is a common problem in SQL Server where contention when writing to a single page can lead to excessive waiting. This waiting comes in the form of the wait type PAGELATCH_EX, and often happens when the clustering key is a monotonically increasing value – like an IDENTITY or date/time column. Pedro Lopes has highlighted some non-trivial workarounds in his post, “PAGELATCH_EX waits and heavy inserts,” and there is some official guidance in the Microsoft Docs article, “Resolve last-page insert PAGELATCH_EX contention in SQL Server.” But are there any ways to address this issue without intrusive changes?

Read on to see what Aaron has in mind.

Comments closed

The Benefits of Instant File Initialization

Published 2021-07-27 by Kevin Feasel

Greg Larsen explains how instant file initialization works:

Instant file initialization is a SQL Server setup option that keeps SQL Server from writing binary zeroes to data files when a database is first created, expanded, or restored. By avoiding the writing of binary zeroes, there is a lower performance impact when disk space is allocated for several database operations.

By default, when SQL Server creates a database, increases the size of a database, or restores a database, it needs to initialize the disk space prior to allocating the space. This initialization process writes binary zeroes (“0”) across all the bits and bytes of space being allocated. By writing binary zeroes across the disk space, the SQL Server engine makes sure that data previously stored in the unused disk space is completely overwritten before the disk space is allocated to a database.

Read on for more details, as well as why this can be worth enabling.

Comments closed

Setting Resource Constraints on Containers

Published 2021-07-26 by Kevin Feasel

Anthony Nocentino won’t let this container run amok:

Docker gives you the ability to control a container’s access to CPU, Memory, and network and disk IO using resource constraints, sometimes called Limits. You define limits as parameters when creating containers. In its default configuration, a container will have no resource constraints for accessing resources of the host operating system. This post will look at how to configure resource constraints in Docker and look at how SQL Server sees the resources when CPU and Memory resource constraints are in place.

In this post, we will focus on using CPU and Memory Limits using the parameters --cpus <value> and --memory <value>. These provide the essential resource controls for access to CPU and Memory available on the host. If you want to dive deep into the various types of resource constraints available in Docker, check out this page https://docs.docker.com/config/containers/resource_constraints/. The goal of this post is to introduce the SQL Server DBA into resource constraints in containers.

Read on to learn how you can use CPU and memory limits to control resource allocation for SQL Server containers—including at runtime.

Comments closed

Unlocking a Login the Bad Way

Published 2021-07-23 by Kevin Feasel

Kenneth Fisher needs bad things done fast:

We recently had an application login (SQL Server authenticated) in one of our training environments start locking out on a regular basis. I won’t go into why other than to say we did resolve it eventually. This was a major problem that escalated rather quickly up our management chain. We needed to solve it ASAP. And because of that we needed not only a long term solution but a short term one as well. The short term solution involved creating a script that unlocked the login (if it’s currently locked) and sticking it in a job that runs every 5 minutes.

Note: This is not something you should be doing in production! This creates a major security hole.

The process itself isn’t, strictly speaking, a bad idea—for example, maybe you’re testing out some integration and accidentally lock the account. The bad idea is more the script to keep doing this every few minutes.

Comments closed

Remote Powershell Management via the ISE

Published 2021-07-23 by Kevin Feasel

Jeffrey Hicks has a use for the Powershell ISE:

Way back before the days of PowerShell Core, or even VS Code for that matter, the PowerShell ISE was the center of my PowerShell world. I spent a lot of time finding ways to make it easier for me to use and to push it to its limits. Naturally, the PowerShell ISE doesn’t play much of a role for me these days. But that may not be true for you. In fact, one area where the ISE can be useful is with remote sessions. 

Read on to see how.

Comments closed

The Benefits of Kubernetes for App Hosting

Published 2021-07-21 by Kevin Feasel

Joy George Kunjikkur enumerates reasons why you might want to use Kubernetes to host applications:

I started writing this post 2-3 years back. Mainly when Apache Spark 2.3 started supporting Kubernetes (K8s) in 2018. It was obvious that Kubernetes is taking over app hosting space the same way virtual machines took over physical machines. All are expected to understand where the industry is moving and adopt. Hence I paused this post as there is nothing I need to endorse. But it’s time to resume this post and publish it.

Click through for a slew of thoughts on the topic.

Comments closed

Log Shipping Configuration with Powershell

Published 2021-07-20 by Kevin Feasel

Lee Markum needs to do log shipping in bulk:

I recently needed to configure log shipping for multiple databases at once as part of a migration project. I turned to PowerShell to do this.

But before we get to that part, this post assumes that you’ve done the upfront work to create shares for the backups to write to and for the backups to be copied to. This will involve providing the right permissions for the SQL Server service accounts involved in Log Shipping. If you are not familiar with this, that’s perfectly fine. Check out this article in MS Docs first.

Click through for the next steps in the process, including a well-timed Get-Help call.

Comments closed

SQL Server Baselines with the TIG Stack

Published 2021-07-15 by Kevin Feasel

Mark Wilkinson combines Telegraf, InfluxDB, and Grafana:

Lots of folks wonder why I would go through the trouble of building out a system when so many vendors have already solved the problem of collecting baseline metrics. The answer at the time was simple: cost. With my setup I could monitor close to 600 instances (including dev) for $3,000 USD per year. That includes data retention of ~2 years! Are there some administration costs as far as my time is concerned? Of course. In the begining things were a little rough as I learned more about InfluxDB, but once things were configured correctly the most work I’ve had to do is to expand the size of the data drive as we started collecting more metrics.

Click through for more info and check out the GitHub repo.

Comments closed