Press "Enter" to skip to content

Category: Administration

Finding Bad (Worse?) NOLOCK Statements across Instances

Aaron Bertrand powers up for about six episodes straight, but the results are amazing:

In Part 1 of this series, I showed how to use a Visitor pattern to walk through one or more T-SQL statements to identify a problematic pattern where NOLOCK hints are applied to the target of an update or delete. The method in my initial examples was very manual, though, and won’t scale if this problem might be widespread. We need to be able to automate collecting a potentially large number of statements across an entire environment, and then try to eliminate false positives without manual intervention.

Read on to see how you can take what Aaron wrote last time and make it scalable.

Comments closed

Estimating Nonclustered Index Sizes with Powershell

Alex Stuart performs some calculations:

I recently encountered a requirement to estimate the size of (a lot of) nonclustered indexes on some very large tables due to not having a test box to create them on or the time to build one. I couldn’t find a script to do this, and as any programmer knows, laziness is the real mother of invention, so I wrote one.
This post summarises how we can calculate this and provides a Powershell function to do so.

I used Microsoft’s documentation as a basis on how to do it, but as it’s a wall of text that’s a little tricky to follow, I’ll go over the basics here. I’m only covering the leaf levels and non-MAX columns that would create LOB pages – I’ll explain why later.

Click through for the article and to see how Alex’s calculations play out.

Comments closed

Configuring Classifiers for Workload Management

Reiss McSporran continues a series on workload management in Azure Synapse Analytics:

So in part 1 we found out what Workload Management was and why we wanted to use it. (If you haven’t read that part, please click here and read that post before you carry on, as we’re jumping in at the deep end!) In this part we’ll be learning how to configure it.

Continuing from the end of the previous post, you have two main options to configure this. First is to assign a user or role to one of the existing system defined workload groups, second is to create a custom workload group and assign a user or role to this instead.

Read on to learn how to create classifiers, what the rules look like for them, and their importance.

Comments closed

Putting a SQL Server Database into Recovery Pending

John Morehouse causes harm to a perfectly good database:

You might be asking why on earth would you want to get a database into an undesirable state, more specifically into a Recovery Pending state.  Well, in my case, I had a client database that had entered into this state due to a failure of storage.  Thankfully, this database was not being used for production, so I had some time to determine the best way to fix the issue.

A phrase that was often used during my time in the fire service, was “Try Before You Pry”.  Does the front door of the house need to be kicked in?  It may be unlocked and by trying before prying (with my boot) I can prevent damage to the door.  In these types of scenarios, this philosophy holds true.  Try things out on non-critical databases will help prevent any further damage.

In this instance, I want to try it before forcing something that might be damaging.  This meant I had to get a test database into a recovering state.  Once it is in the state I need, then I can attempt different methods to recover the database properly.  Once a successful solution is determined, I can then confidently pry against the damaged database in production knowing that I am using a validated solution.

Read on to learn one way to put a database into recovery pending state, but definitely pay attention to the disclaimer. And its twin.

Comments closed

Compilations per Second in SQL Server

Fabiano Amorim clarifies a metric’s definition:

As you can see, the number of SQL Compilations/Sec is very high. It’s important to step back and remember the general description and guideline for this counter and understand what I mean by “high”:

Official Description: “Number of SQL compilations per second. Indicates the number of times the compile code path is entered.”

Read on for a dive into ad hoc SQL statements parameterization, how an instance can have a high compilations/sec value relative to batch requests/sec, and how that can affect performance in the long run.

Comments closed

Issue with SQL Server and High Availability in Kubernetes

Andrew Pruski has a problem:

I’ve been running a proof of concept for SQL Server on Kubernetes over the last year or so (ok, probably longer than that…hey, I’m a busy guy 🙂 ) and have come across an issue that has been sort of a show stopper.

There are currently no HA solutions for SQL Server running on plain K8s (not discussing Azure Arc here) so my tests have been relying on the in-built HA that Kubernetes provides but there’s a problem.

Be sure to check it out because it is a doozy.

Comments closed

Minimizing PAGELATCH_EX Waits in SQL Server 2019

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

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

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