Press "Enter" to skip to content

Author: Kevin Feasel

Error Messages during Change Tracking Cleanup

Published 2021-11-23 by Kevin Feasel

Lee Markum troubleshoots some error messages:

You’re a data professional and you’re trying to keep up with patching a wide range of SQL Server versions and editions. How do you know what’s in the CU and whether you should apply it or not? My favorite way to read up on CUs is to go to SQLServerUpdates and click around there.  It will take you to the latest CU pages as well as give you a way to see previous CUs that are available.

While doing this recently, I discovered this for CU 26 on SQL Server 2017.

These sorts of regressions do slip in, so keep an eye on them before (and after) upgrading. Lee gives us a concreate example of one in a recent CU of SQL Server 2017.

Comments closed

Offset Window Functions

Published 2021-11-23 by Kevin Feasel

I continue a series on window functions:

Offset functions are another class of window function in SQL Server thanks to being part of the ANSI SQL standard. Think of a window of data, stretching over some number of rows. What we want to do is take the current row and then look back (or “lag” the data) or forward (“lead”).

There are four interesting offset window functions: LAG(), LEAD(), FIRST_VALUE(), and LAST_VALUE(). All four of these offset functions require an ORDER BY clause because of the nature of these functions. LAG() and LEAD() take two parameters, one of which is required. We need to know the value to lag/lead, so that’s a mandatory parameter. In addition, we have an optional parameter which indicates how many steps forward or backward we want to look. Let’s see this in action with a query:

Click through for that query, as well as a few more and plenty of explanation.

Comments closed

Sargability and Dates

Published 2021-11-23 by Kevin Feasel

Chad Callihan makes me want to change the title to “Getting Sarge a Date”:

We’ll use the StackOverflow2013 database for this example. Let’s say we want to return the users created in 2013. One way to return this data is to use the YEAR() function to pull out the desired year for our query:

For the reference, check out Chad’s prior post. My expectation is that about 90% of people in the US who are aware of the term pronounce it “Sarge-able” instead of “Sar-guhble” and therefore immediately think of Sergeants.

1 Comment

Intent Shared Locks in RCSI

Published 2021-11-23 by Kevin Feasel

Tomas Zika troubleshoots a deadlocking problem:

I was analyzing a deadlock graph and there was a mystery lock of type IS (Intent Shared). That was weird by itself because the database has Read Committed Snapshot Isolation (RCSI) enabled, which is the Optimistic Concurrency model that shouldn’t take shared locks. All the statements were contained in this database. Also, the locked table was seemingly unrelated to anything that has been going on in the deadlock report.

Click through for an image, a repro script, and an explanation as to what exactly is going on.

Comments closed

Managed Instance Failover Groups

Published 2021-11-23 by Kevin Feasel

Arun Sirpal takes us through Azure SQL Managed Instance failover groups:

If you have been following me for a while you will know that I really like the Fail over groups within Azure SQL DB and it is no different to when applying it to Managed Instances. If you want a rock-solid DR plan, this is the way forward.

Remember it’s an abstraction layer on top of the active geo-replication feature, before this we had to do a lot of manual one to one database setups but now this feature simplifies deployment and management of geo-replicated databases at scale. You can initiate failover manually or automatically if there is a massive failure (researching this topic this could mean things from memory leaks to wrong network cables cut during routine hardware decommissioning – you never know, it could happen so plan for it)

Click through to see how to set this up and what failover looks like.

Comments closed

Using Enums in Powershell

Published 2021-11-22 by Kevin Feasel

Robert Cain quietly tells us that Powershell is a real programming language, sysadmins who claim to hate programming:

This post begins a series on using Classes in PowerShell. As a first step, we will cover the use of an Enum, as enums are frequently used in combination with classes.

An Enum is a way to provide a set of predetermined values to the end user. This allows the user to pick from a finite list, and assure a value being passed into a function or class will be valid.

Click through to learn more about enums and how they work in Powershell.

Comments closed

Decision-Making with Bayes’s Theorem

Published 2021-11-22 by Kevin Feasel

Bill Schmarzo lays out a framework to classify decision-making:

In my blog “Making Informed Decisions in Imperfect Situations”, I discussed the importance of properly and objectively framing the decision that we seek to make and how that impacts the data that we gather (and ignore) in an effort to make an informed decision. That is:

Are you trying to gather data to determine the right decisions or are you gathering data to support the decision that you have already made? 

In that blog, I introduced two tools that can help us make informed decisions using the best available data, even when that data might be incomplete, conflicting, and/or distorted by others. 

Read the whole thing.

Comments closed

Data Types Matter, Even in the Serverless SQL Pool

Published 2021-11-22 by Kevin Feasel

Jovan Popovic has a public service announcement for us:

The serverless SQL pool is a distributed computing system that executes concurrent queries on a set of distributed compute nodes. Multiple compute nodes are running the parts of a distributed query plan that read the underlying files, join the data sets, group, and aggregate results. Different queries might try to use the same compute nodes to execute the parts of the queries.

The oversized column types like VARCHAR(MAX) might trick the compute node to allocate more resources than is needed. However, the allocation is based on the estimate, but these over-allocated resources will not be used in actual execution because they are not needed. If a compute node needs 100MB to sort the results it will use these 100MB although the query optimizer allocated 4GB of memory for the task on the compute node.

Read the whole thing.

Comments closed

Ranking Window Functions

Published 2021-11-22 by Kevin Feasel

I continue a series on window functions in SQL Server:

The whole concept of ranking window functions is to assign some numeric ordering to a dataset. There are four ranking functions in SQL Server. Three of them are very similar to one another: ROW_NUMBER(), RANK(), DENSE_RANK(). The fourth one, NTILE(), is the odd cousin of the family.

Unlike aggregate window functions, all ranking window functions must have at least an ORDER BY clause in the OVER() operator. The reason is that you are attempting to bring order to the chaos of your data by assigning a number based on the order in which you join.

Watch me ramble on about monotonicity and quietly admit that I learned what it was from economics, where the naming feels utterly backward (“strongly monotonic” is the “greater than or equal to” of monotonicity, whereas “weakly monotonic” is the “greater than” of monotonicity). Also, I structured this entire post so that I could get that video from The Prisoner (the good one, not the garbage one) in it.

Comments closed