Blogs

One feature I’ve not appreciated enough in the past in SQL Server is Resource Governor.

Resource Governor allows you to fix problems with Memory Grants in a simple way, as Erik Darling recommends. It also lets you classify sessions into groups and limit the maximum number of simultaneous requests, and /or limit the degree of parallelism if you need more CPU for other workloads. While this will slow down the queries you classify into that group, this can be super useful, especially if you’re already using something like Snapshot isolation to prevent blocking or you are using a read-only replica.

In Azure SQL Managed Instance, you get to use Resource Governor, even in the General Purpose tier. This is awesome.

Just make sure you execute commands in the context of the master database, or you’ll get error 40510: Statement 'ALTER RESOURCE GOVERNOR' is not supported in this version of SQL Server.

It is supported! Just USE master; before running your commands.

How Many Features are Missing from Azure SQL Managed Instance?

By Kendra Little in SQL Server

December 8, 2023

Spoiler: a large amount of features from SQL Server 2022 are missing from Azure SQL Managed Instance. Some major features are missing that were introduced in SQL Server 2019– and here we are just a few weeks away from 2024.

But Microsoft’s top-line marketing claims about Azure SQL Managed Instance remain that “it’s always up to date with the latest SQL features and functionality.”

Let’s dig into some of the documented highlights on missing features, so you can decide for yourself what to think of that statement.

Lost Updates under Read Committed Snapshot Isolation (RCSI)

By Kendra Little in Isolation Levels

October 4, 2023

I shared an image on social media this week that describes how I feel about isolation levels in SQL Server (and its various flavors): the more concurrent sessions you have in a database reading and writing data at the same time, the more attractive it is to use version-based optimistic locking for scalability reasons.

There are two isolation levels in SQL Server that use optimistic locking for disk-based tables:

Read Committed Snapshot Isolation (RCSI), which changes the implementation of the default Read Committed Isolation level and enables statement-based consistency.
Snapshot Isolation, which provides high consistency for transactions (which often contain multiple statements). Snapshot Isolation also provides support for identifying update conflicts.

Many folks get pretty nervous about RCSI when they learn that certain timing effects can happen with data modifications that don’t happen under Read Committed. The irony is that RCSI does solve many OTHER timing risks in Read Committed, and overall is more consistent, so sticking with the pessimistic implementation of Read Committed is not a great solution, either.

Automated Deployments and the Art of the Database with Database Michael J Swart

By Kendra Little in Database DevOps

September 26, 2023

In this episode, Michael J Swart joins the Dear SQL DBA podcast to talk about databases, automation, and how he’s come to illustrate some of the coolest blog posts ever to be written about data.

Check out Michael’s art and blog posts at michaeljswart.com, and explore his posts by illustration at https://michaeljswart.com/browse-articles-by-illustration/.

PAGELATCH, PAGEIOLATCH, and LATCH waits in SQL Server

By Kendra Little in SQL Server, Wait Stats

September 12, 2023

I’ve long found it tricky to remember and explain the differences between three similar-sounding waits in SQL Server that all have “LATCH” in the name: PAGELATCH, LATCH, and PAGEIOLATCH waits.

Here’s an illustration that explains these waits, along with wait subtypes.

Jer and Kendar Explore Optimized Locking

By Kendra Little in SQL Server

September 4, 2023

SQL Server has a new feature that’s currently only available in Azure SQL Database: Optimized Locking.

Jeremiah Peschka joins Kendra (aka Kendar) to talk through the docs and nerd out on locks, blocks, and how to pronounce the acronym “LAQ”.

Prefer to explore optimized locking with a diagram? I’ve also got a little sketchnote for ya.

Dear SQL DBA - Advice for Technical Leaders with Alex Robson

By Kendra Little in Leadership, Dear SQL DBA

August 31, 2023

Ever wondered what it’s like to be a VP or Director of Engineering? I chatted with Alex Robson about leadership in technology, what you can get out of coaching or an MBA program (should you be interested), and what makes a high performing team.

Alex’s site and blog: https://robsonconsulting.services

Msg 195: STRING_SPLIT is not a recognized built-in function name

By Kendra Little in Transact-SQL, Functions

August 25, 2023

Yesterday, I was writing some Transact SQL to dust off the cobwebs. I got confused when I was playing around with the STRING_SPLIT function, and kept getting the error:

Msg 195, Level 15, State 10, Line 2 ‘STRING_SPLIT’ is not a recognized built-in function name.

I checked my database compatibility level, which must be 130 or higher for the function to be available. My database compatibility level was 160, so that was fine.

The issue? STRING_SPLIT is a table valued function. If your syntax uses it as a scalar function, you will still see a message that it doesn’t exist – but really the issue is that the function exists, but needs to be called with a different syntax.

Let’s look at some examples.

Dear SQL DBA - Performance Tuning with Erik Darling

By Kendra Little in Performance Tuning, Dear SQL DBA

August 18, 2023

SQL Server performance tuning expert Erik Darling joins the podcast today to chat about how good queries can go bad and how bad queries can get better.

He also answers the question on everyone’s mind: if he was a database, what database would he be?