Press "Enter" to skip to content

Curated SQL Posts

An Overview on Spinlocks in SQL Server

Published 2025-01-20 by Kevin Feasel

Stephen Planck talks spinlocks:

High concurrency can expose subtle performance bottlenecks in SQL Server, particularly those stemming from spinlocks and latch contention. Both mechanisms exist to synchronize access to shared data structures, yet they operate differently and require distinct troubleshooting approaches. By recognizing how they work and knowing what causes them to overload a system, DBAs can reduce CPU spikes, timeouts, and overall application slowdowns.

Read on to more about spinlocks and latch contention. My experiential bias is that spinlocks are the actual problem approximately 5% of the number of times that DBAs believe spinlocks are the actual problem.

Comments closed

CPU and Memory Configuration for MySQL

Published 2025-01-20 by Kevin Feasel

Chisom Kanu continues a series on MySQL:

In the first part of the series article, we introduced MySQL Shell as a tool for managing and optimizing MySQL configurations. We discussed how to install the Shell, connect to the MySQL server, and modify basic configuration parameters. Now, we’re into performance optimization, focusing specifically on memory and CPU configurations. These two components are important because they directly impact how efficiently your database processes queries, handles connections, and stores data in memory. In this article, we will look at techniques using MySQL Shell to help you optimize both memory and CPU usage to ensure smooth and fast database performance.

Read on to learn more.

Comments closed

Understanding the SQL Server Version Store

Published 2025-01-20 by Kevin Feasel

Haripriya Naidu digs into tempdb:

This article discusses when version store starts generating rows, when cleanup takes place, and how TempDB space gets occupied due to this activity.

I’ve hardly used PerfMon much in the past, nor was I a fan of it, just like many of you. But, when I used it a few times to determine how many data files needed to be added to TempDB while resolving contention, it helped me make a clear decision on the number of files needed. Since then, I’ve become a fan of PerfMon.
Although I don’t use it daily, I don’t hesitate to turn to it when I think it might help me understand things better.

Coming back to our topic of version store in this article, I resorted to PerfMon counters because visually watching the lines go up and down when an update is performed on a table helped me better understand how the version generation and cleanup works.

Click through for a demonstration of the process, including relevant PerfMon counters and the type of information you can get directly from SQL Server.

Comments closed

Porting an R Shiny App to Observable Framework

Published 2025-01-17 by Kevin Feasel

Tim Brock makes a change:

If you’re interested in interactive data visualisation you’ve probably heard of the d3 JavaScript library, even if you’ve never used it or even know any JavaScript. Mike Bostock, the creator of d3, and colleagues followed this up with d3.express, which was quickly renamed to Observable.

Read on to see how you can build a simple Observable Framework app without spending a lot of time troubleshooting JavaScript code.

Comments closed

Thoughts on Separating Power BI and Microsoft Fabric

Published 2025-01-17 by Kevin Feasel

Eugene Meidinger calls out my favorite law of headlines:

If you know Betteridge’s Law of Headlines, then you know the answer is no. But let’s get into it anyway.

Recently there was LinkedIn post that made a bunch of great and valid points but ended on an odd one.

Number one change would be removing Power BI from Fabric completely and doubling down on making it even easier for the average business user, as I have previously covered in some posts.

It’s hard for me to take this as a serious proposal instead of wishful thinking, but I think the author is being serious, so let’s treat it as such.

Read on for a bit of history and why Eugene thinks this is a very bad idea.

Comments closed

SELECT Queries Triggering INSERT/UPDATE/DELETE Audits in SQL Server

Published 2025-01-17 by Kevin Feasel

Sean Gallardy digs into an interesting problem:

Quite the interesting problem was brought to me by a friend, in that they had a database level audit setup and were having `select` queries trigger audits that the object had updates run on them. The question became, how is this possible as a select query doesn’t update anything. Let’s start with a quick setup and original output.

Read on for the solution, as well as a much more common place in which you’d find this issue.

Comments closed

What’s Ahead for SQL Server in 2025

Published 2025-01-17 by Kevin Feasel

Bob Ward lays out the plan:

As we begin a new year in 2025, many of you are looking at new projects, new applications, trying to determine how to integrate AI into your business, modernizing your data estate, or considering an upgrade or a cloud migration. As you consider your options, let’s look at the state of the union in 2025 of Microsoft new releases and capabilities for SQL Server, Azure SQL, SQL database in Fabric, Copilots, tools, and developer experiences.

There’s a lot on the list, so check it out.

Comments closed

Point-in-Time Restoration for Azure SQL Managed Instances

Published 2025-01-17 by Kevin Feasel

Andy Brownsword points and clicks:

One of the benefits which comes with a Managed Instance is having backups taken care of for you. That also includes restores. Particularly useful is a one-click (ish) restore for a specific point in time.

Restoring a database is as easy as creating a new database, as its part of the same workflow.

Read on to see how it works, as well as one limitation around existing databases.

Comments closed

Near Real-Time Data Plotting in Python

Published 2025-01-16 by Kevin Feasel

Hristo Hristov wants to know where the International Space Station is:

Gathering data on events as they occur in real-time is a powerful and popular technique in scientific and industrial computing. If we can query an online REST API representing the position of the International Space Station’s (ISS), how can we visualize these data in real time? How do you plot the data points as soon as they arrive and observe changes in the station’s position immediately? Let’s look at using Python for a real time plot of data.

Click through for the solution and plenty of explanation along the way.

Comments closed

Speed Differences with Separating Data and Log Files

Published 2025-01-16 by Kevin Feasel

Brent Ozar performs a test:

I’ve already explained that no, it doesn’t make your database server more reliable – and in fact, it’s the exact opposite. But what about performance?

The answer is going to depend on your hardware and workload, but let’s work through an example. I’ll take the first lab workload from the Mastering Server Tuning class and set it up on an AWS i3en.2xlarge VM, which has 8 cores, 64GB RAM, and two 2.5TB NVMe SSDs. (This was one of the cheapest SQL-friendly VM types with two SSDs, but of course there are any number of ways you could run a test like this, including EBS volumes.)

I would expect cloud versus on-premises answers to be quite different, because cloud services tend to throttle you hard on how much storage throughput you’re allowed to have. For that reason, the results make perfect sense in AWS (or Azure or GCP for that matter), but unless your on-prem solution has hard throttles on IOPS or throughput because your sysadmins are monsters, the limits of performance would be in how hard you can push the drives or your storage controllers.

Ultimately, the most appropriate answer is to test your systems and not rely on expectations, especially if you’re shifting from on-premises to a cloud (or vice versa).

Comments closed