Press "Enter" to skip to content

Author: Kevin Feasel

Rolling Filtered Indexes in SQL Server

Published 2025-08-19 by Kevin Feasel

Aaron Bertrand only needs recent data:

I recently resolved an issue where a query pulling data from the last 30 days would time out due to the table’s size and the lack of a supporting index. Creating a supporting index is possible, but not ideal; it will be very large and may not be useful for most queries and use cases. I wonder how I could implement a filtered index that follows time and is always limited to the last n days.

Read on to see how Aaron did this. It’s a clever solution and Aaron does make clear the tricky part of filtered indexes: explicit conditions.

Comments closed

Troubleshooting Power BI Report Performance

Published 2025-08-19 by Kevin Feasel

Ben Richardson speeds up a report:

Slow Power BI reports waste time and frustrate users.

The slowdown often comes from hidden issues in the data model, DAX, visuals, or refresh settings.

This guide shows you how to find the cause, fix it, and keep reports running smoothly.

Read on to see some of the built-in tooling for performance optimization, as well as more information on these four common issues.

Comments closed

Comparing Memory-Optimized Table Variables to Temp Tables

Published 2025-08-19 by Kevin Feasel

Mehdi Ghapanvari runs a test:

SQL Server memory optimized table variable and a temporary table: Which is faster to temporarily load and store data?

Dear readers, I want to share a true story. In 2018, when I was using SQL Server 2016 in production, I had a complex stored procedure where I needed to store data temporarily. I can’t remember exactly, but I needed to store, say, 50,000 rows temporarily. At that time, I thought memory-optimized table variables were extremely fast for storing temporary data. So, I used them. There was not a lot of concurrency on that stored procedure. However, after a while, I realized that it was not fast enough. I investigated to find the root of the issue and found that loading data into the Memory-Optimized table variable was causing the problem.

Here is where I say that the specific circumstances are going to play a large role in this. If the number of rows is smaller, that can change the outcome. If the action you are performing with the temporary object is more complex, that can change the outcome (generally in favor of temp tables). If you have very high concurrency, with hundreds of users trying to access variants of this object at all times throughout the day, that can change the outcome (generally in favor of the memory-optimized table type).

If you try to pin me down on specific advice, I’d say to start with temp tables and only switch to memory-optimized table types after you have proof of table creation (or re-use) contention in tempdb tied to that specific object. But unlike most situations with In-Memory OLTP, it’s reasonably straightforward to find a great use case for memory-optimized table types.

Comments closed

All-Caps and Technical Writing

Published 2025-08-18 by Kevin Feasel

Mike Robbins argues against over-capitalization:

In technical writing, letter case affects more than style. It influences tone, readability, and accessibility. Typing in ALL CAPITAL LETTERS might seem like an easy way to add emphasis or style, but it often does more harm than good, affecting how quickly readers process information and how your message is perceived.

If you want your message to be clear, approachable, and accessible, avoiding all caps is usually the best approach.

Read on for the reasons why.

Comments closed

Business Rule Automation in SQL Server Triggers

Published 2025-08-18 by Kevin Feasel

Kenneth Omorodion uses triggers:

The advantage of using triggers is that the same processing can occur regardless of where or how the data has been inserted, updated or deleted. In this article, we look at several examples of where and why triggers could be useful along with an example use case.

I’m not a huge fan of using triggers for handling business rules for a few reasons. The biggest one is that triggers get lost in the mix too easily—it’s not easy to see that a trigger exists and that it is the thing modifying data. This is probably one of the better setups I’ve seen for triggers, as it does include an audit log table and the triggers assume multiple rows rather than expecting a single row will come in.

Comments closed

The Value of a No-Op Startup Step in a SQL Agent Job

Published 2025-08-18 by Kevin Feasel

Steve Stedman lays out the reasoning:

When managing SQL Server, SQL Server Agent Jobs are indispensable for automating tasks like backups, index maintenance, and data imports. However, monitoring these jobs can sometimes be tricky, especially when trying to quickly assess their status. One simple yet effective trick to improve visibility is adding a no-op (no operation) or logging step as the first step in your SQL Server Agent Jobs. This blog post explains why this small addition can make a big difference in monitoring and managing your jobs.

Click through for Steve’s argument.

Comments closed

The Importance of Running DBCC CHECKDB

Published 2025-08-18 by Kevin Feasel

Kevin Hill has some advice:

You just ran DBCC CHECKDB for the first time in a while (or maybe ever) and saw something you didn’t expect: the word corruption.

Take a breath.

Don’t detach the database.
Don’t run REPAIR_ALLOW_DATA_LOSS.
Don’t reboot the server or start restoring things just yet.

There’s a lot of bad advice floating around from old blogs, well-meaning forum posts, and even some popular current LinkedIn threads. Some of it might’ve been okay 15 years ago. Some of it is dangerous.

Let’s dig in.

Click through to dig in.

Comments closed

An Introduction to Query Folding in Power BI

Published 2025-08-18 by Kevin Feasel

Alex Powers takes us through one major performance optimization technique in Power BI:

One of the most powerful capabilities of Power Query and the M Language is Query Folding (also referred to as query delegation, and predicate push-down). Query Folding allows the Power Query Mashup Engine to push the transformations expressed in an M (mashup) query to the data source, in the data source’s query language, resulting in more efficient data processing.

For inexperienced database technology users this ability to leverage the graphical user interface of Power Query to dynamically generate a query written in the data source’s query language unlocks enormous opportunities to find insights with any data, at any scale.

Click through to see how you can know if query folding is enabled, as well as some hints around when and to what extent query folding will work.

Comments closed