Press "Enter" to skip to content

Curated SQL Posts

Tracking How Many Row Versions an RCSI Query Reads

Louis Tochon works with a new bit of information:

The Table line looks like a normal scan of 330 pages. The Object line says the read walked through 220,000 row versions, 22 per row, because another session had updated these rows 22 times in a transaction it had not committed yet. The same read took about 117 ms of CPU, against 3 ms when nothing was pending.

The Object line is new. It comes from the version store read metrics Dimitri Furman announced on the Azure SQL blog last week, which also appear in the actual plan XML returned by the server (SSMS 22.12.0 drops them, both in the graphical plan and in Show Execution Plan XML; copy the XML cell from SET STATISTICS XML ON to see them), in four extended events, in sys.dm_exec_query_stats and its procedure, trigger and function counterparts, and in sys.dm_exec_requests and sys.dm_exec_sessions (Microsoft documentation). For now they exist in Azure SQL Database and SQL database in Microsoft Fabric; Managed Instance and the next SQL Server version are announced to follow.

When I recommend RCSI, the usual worry is the open transaction someone forgot in SSMS. These metrics let me check what it actually costs the readers.

The article looks to be AI-generated, but it does show off a new bit of functionality that I’d be happy to see spread beyond Azure SQL Database.

Leave a Comment

Injecting a Hint with Query Store

Dualcore DBA gives us a hint:

In this post, we’ll look at a feature in SQL Server Query Store that was introduced in SQL Server 2022. I absolutely love Query Store, I use it so much I can barely remember what life was like without it.

One of the things that has been great about Query Store since its early days is the way you can force an execution plan for a query. This has saved me a number of times when a query’s performance has suddenly dropped off a cliff, the code hasn’t changed but the data distribution has and everyone is looking at me, the DBA to fix it. I can’t count the number of times I’ve forced the last good plan to resolve the issue. This is only a sticking plaster approach as with most approaches where forcing the optimizer is concerned – the query should then be added to the backlog for resolution but nonetheless this has got me out of a pinch on a number of occasions.

Click through to see how hints work. But just as with introducing normal hints into T-SQL code, you’ll want to make sure that you’re getting the behavior you expected, both now and in the future. As the underlying database details change, so too could the most appropriate hint choice.

Leave a Comment

Building Dashboards to User Expecatations

Ruben Van de Voorde provides some advice:

Data visualization has entered the era of “articulate it and let AI build it”. AI coding agents have cut the cost and effort required to create novel digital experiences, and the landscape of tools at our disposal to show data to our users keeps expanding. When each new dashboard can come with its own way of selecting, highlighting, and clearing selections, we run the risk of reinventing UX patterns in similar but different ways. A prompt such as “make an interactive dashboard of these sales” doesn’t say how UX should work, so every agent invents its own UX. Let’s relate it back to — what did you expect — semantic models to show what we mean.

To be blunt, very little of the specific advice has anything to do with AI agents. The same problems happen with dashboards humans create entirely, and the same good guidance applies.

Leave a Comment

Managing tempdb Activity

Aaron Bertrand has built a solution:

Managing tempdb growth tends to be a very reactive exercise. We size tempdb using a predictive ruleset based on expected workload, database size, concurrency, and other imperfect factors. Then someone does something silly and fills the T: drive. If we catch it in time, we may be able to kill the offending session or add space to prevent the workload from dying. Then we tell them not to do it again; but they will, of course, so we expand the drive again and hope this time it’s big enough. But it rarely is. It’s like a baseball game that’s tied in the 5,662nd inning. Each team keeps scoring a single run in every new inning; the game will never end.

Our existing “drive full” alarms start going bonkers as soon as a drive is more than 90% full. We have a dedicated drive for tempdb data, and we pre-allocate the data files to just under 90% of the available space. This prevents any growth events while they’re using tempdb within the bounds of its current size. But when we fill the space already allocated to the data files, they grow. After one or two growths, we have crossed the threshold for drive space alerts, and it probably won’t be long before the volume fills and the workload grinds to a halt.

Click through for the solution scripts and Aaron’s explanation of it all.

Leave a Comment

Full-Text Search with DAX

Marco Russo and Alberto Ferrari dig into a recent feature addition:

Searching text in a Power BI report usually means looking for a string inside the values of a column. For example, the filter pane translates a “contains” condition into a DAX expression based on CONTAINSSTRING or SEARCH, which look for a sequence of characters anywhere in the text; for example, a search for “phone” also returns “headphones” and “microphone”.

With a full-text index, search functions operate on words rather than characters. The text data is divided into words, also called tokens, and a following text-normalization technique called stemming reduces words to their root form. For example, “running” and “runs” both stem to “run”, whereas “runner” remains a different word. This way, a search for “phone” can return “phones”, but not “headphones”, because it has a different meaning. Also, full-text search can tolerate typos and rank results by relevance, which CONTAINSSTRING cannot do.

Click through to see how it works and what the current limitations are.

Leave a Comment

Making the Power BI Gantt Chart Useful

Valerie Junk shares some tips:

If you drag the new Power BI Gantt chart onto your report page and add a task with a start and end date, you get… well, a Gantt chart! But does it really tell you anything? A project plan is there to answer questions: where are we, what’s late, and who’s working on it? 

The good news: you don’t need anything fancy. With a few tweaks in the format pane, you can get a long way!

Click through for seven tips to turn this from a mess into a practical visual.

Leave a Comment

Report Bursting with Dataflows Gen2 and Action Sequences

Chris Webb follows up on a prior post:

A couple of weeks ago I wrote this post about how you can use the new Send Email action in Dataflows Gen2 to send out email-based reports to a list of recipients. I did it by creating a parameterised Dataflow – which worked but the end result was slow and therefore expensive. So I started thinking about other approaches and it turns out there is indeed another way of solving the problem, using a whole branch of M functions that is not properly documented and which I’ve never used before.

Read on to learn more about these Action Sequences.

Leave a Comment

Reducing SQL Server Container Image Sizes

Anthony Nocentino spreads the news:

This week I was at PASS Data Community Summit in Seattle, and I attended a session called “An Inside Look at How Microsoft Builds SQL Features, by Bob Ward, Naveen Prakash, and Travis Wright, all of whom are on the SQL Server team. In this session, Travis mentioned that they reduced the SQL Server container image size by a meaningful amount using AI agents that helped discover unused code in the image, but details on how much and when were lost when the conversation moved on to the next topic. So I set out to find out exactly which SQL Server build this came out in, and I also wanted to measure how much we saved. They also mentioned that startup times were better now.

I use SQL Server containers nearly every day, and I’ve written a ton about containers on this blog, so I got to work. So I pulled the CU images for SQL Server 2025 and SQL Server 2022, measured each one, and timed how long it took to get from docker run to a successful query.

Read on to see what Anthony was able to find with respect to image size and container startup time.

Leave a Comment