SQL Server Performance Blog where milliseconds matter

Part 5 – How to Force Batch Mode on Rowstore When SQL Server Doesn’t Choose It Previous article: Part 4 – No New Index, No Schema Change Hi guys, Welcome back to the series where we improve performance without creating new indexes, without changing the schema, and without redesigning the database. Just pure engine optimization. Grab your execution plans, turn on Actual Execution Plan, and let’s dive into Batch Mode on Rowstore again. Happy reading… and may your queries always run in parallel ⚡ Introduction In Part 4, we saw how Batch Mode on Rowstore can dramatically improve query performance without adding new indexes or changing the schema. SQL Server does not always choose Batch Mode automatically , even when the query looks like a perfect candidate. When SQL Server decides to use Batch Mode How to force it with USE HINT('ForceBatchMode') When Batch Mode can hurt performance Row Mode vs Batch Mode comparison Why This Top...

Hi guys, welcome to Part 4!! I hope you are enjoying this series. I truly believe it can be very useful for those who use SQL Server professionally as I have been doing for more than 20 years (how time flies!). Today we will talk about Batch Mode on Rowstore and how to make your Query run 10x Faster Without Adding Any Index Well...in Part 3 we introduced Columnstore indexes and saw how Batch Mode execution can dramatically improve analytical queries. If you missed the previous part, you can read it here: SQL Server Filtered Index vs Indexed View – Complete Performance Comparison But here’s the twist. Starting with SQL Server 2019, you don’t always need a Columnstore index to get Batch Mode performance. SQL Server can activate Batch Mode on Rowstore . And sometimes… as i told you at the start of this post ....your query becomes 5x or 10x faster without adding a single index. So i suggest you to continue to read... What Is Batch Mode? Traditionally, S...

Hi guys, I’m back again! 👋 In the previous articles we compared Filtered Indexes and Indexed Views , understanding when each one shines. If you missed Part 2, you can read it here: 👉 Filtered Index vs Indexed View – Complete Performance Comparison Today we add a new player to the game. Because when data volume starts growing… when millions of rows become tens or hundreds of millions… we gain a powerful new ally: Columnstore Indexes. And this changes everything. When Rowstore Is Not Enough Until now, we worked with traditional rowstore structures: Filtered Index → reduce I/O for selective predicates Indexed View → precompute aggregations Both are extremely powerful in OLTP scenarios. But when datasets increase significantly, row-by-row processing becomes the bottleneck. This is where Columnstore comes in. What Makes Columnstore Different? Traditional indexes store data row by row (B-Tree structure). Columnstore: Stores data by ...

Filtered Index vs Indexed View in SQL Server: Complete Performance Comparison with Real Examples Hi Guys, I am back! This article is the continuation of the previous deep dive on SQL Server performance tuning! If you missed Part 1, you can read it here: SQL Server Performance Tuning – How Filtered Indexes Drastically Improve Query Performance In the first article, we analyzed how Filtered Indexes can drastically reduce logical reads and optimize execution plans. Today we go deeper and compare Filtered Index vs Indexed View using practical, reproducible SQL scripts. You will find concrete scenarios that you can test in your own lab — because performance tuning is not theory, it’s experimentation. Enjoy the reading! Understanding the Core Difference Filtered Index Indexes only a subset of rows using a WHERE clause Does not pre-aggregate data Low maintenance overhead Ideal for selective filtering scenarios Indexed View Physically materializes query resu...

SQL Server Performance Tuning: How Filtered Indexes Drastically Improve Query Performance Hi Guys, I am back! Today we’re diving into a powerful SQL Server performance tuning technique that can drastically reduce logical reads, optimize execution plans, and significantly improve query performance in real-world production environments. If you are working with Microsoft SQL Server and struggling with slow queries, high logical reads, or inefficient execution plans, this advanced SQL Server performance tuning technique can dramatically improve query performance. In this article, we will analyze how Filtered Indexes in SQL Server can reduce I/O, optimize execution plans, and significantly boost OLTP performance. What Is a Filtered Index in SQL Server? A Filtered Index is a nonclustered index that includes only a subset of rows defined by a WHERE clause. Unlike traditional indexes that include all rows of a table, filtered indexes: Reduce index size Improve statistics ...

Introduction This post is an experiment . A first step, a proof of concept, and also a personal exploration. The idea is to start talking not only about SQL Server and Database , but also about Artificial Intelligence , constraint solving , and data-driven reasoning applied to real, concrete technical problems. In this case, the problem comes from an old but fascinating domain: the MOS 6502 / 6510 CPU . Project Overview and Motivation This code represents the first concrete step of a broader project whose goal is to build a code optimizer for the MOS 6502 / 6510 CPU using Constraint Logic Programming , specifically ECLiPSe CLP(FD) . The long-term vision of the project is to explore whether formal constraint-based reasoning can be used to automatically discover provably optimal instruction sequences for real hardware, instead of relying on hand-written heuristics, pattern matching, or empirical benchmarks. Rather than simulating execution or enumerating progra...

SQL Server 2025 Regex Support: Native T-SQL Expressions SQL Server 2025 && Regex = Less Code, Less Pain in your queries Hi Guys, Here’s some good news: after years of workarounds, hacks, and tricks, SQL Server 2025 finally ships with native regex support in T-SQL. And no, this isn’t just a nice-to-have. It’s a game changer for anyone who deals with messy data, phone numbers, emails, codes… you name it. For years we’ve had to rely on ugly patterns like LIKE '%...%' , CHARINDEX , SPLIT , or even external CLR functions. They worked, but the cost in complexity, performance, and bugs was too high. Finally: native regex in SQL Server 2025 That’s right. Now you get built-in functions such as: REGEXP_LIKE(...) REGEXP_REPLACE(...) REGEXP_SUBSTR(...) REGEXP_COUNT , REGEXP_SPLIT_TO_TABLE , REGEXP_INSTR , REGEXP_MATCHES (table-valued functions are rolling out too) All powered by the RE2 engine : fast, familiar, and compatible enough with ...

SQL Server 2025: Faster Backups with ZSTD Compression Hi Guys! One of the most practical improvements in SQL Server 2025 —and a very welcome one for DBAs constantly fighting backup windows—is the new support for Zstandard (ZSTD) compression. This modern compression algorithm delivers faster backups without significantly increasing CPU or sacrificing storage savings. What’s New Compared to Previous Versions Up to SQL Server 2022, backup compression relied on MS_XPRESS , with optional acceleration via Intel QAT (or software fallback using QATzip). But now with SQL Server 2025, Microsoft introduces ZSTD : an open-source, high-speed compression algorithm with flexible levels and excellent performance-to-compression ratios. How to Use It No new UI, no new tools. It’s all in T-SQL: BACKUP DATABASE YourDB TO DISK = 'YourPath\backup.bak' WITH COMPRESSION (ALGORITHM = ZSTD, LEVEL = LOW|MEDIUM|HIGH); LOW is the default and fastest MEDIUM ...

SQL Server 2025 – Currently in Preview under the Early Adoption Program Hey Guys, It’s been a while since we last caught up here on the blog, but don’t worry—I’ll tell you what I’ve been up to soon! For now, let’s dive into something exciting: the latest features of SQL Server 2025 . As always, I’ll break down the most important updates in a clear and concise way. Enjoy the read! 🚀 Enhanced Performance with Intelligent Query Processing (IQP) SQL Server 2025 introduces major enhancements to Intelligent Query Processing (IQP) , further optimizing query execution. Key improvements include: Adaptive Join Enhancements: SQL Server can now dynamically switch between Nested Loops, Hash, or Merge joins during query execution, reducing latency. Parameterized Plan Optimization: Helps mitigate parameter sniffing by generating multiple execution plans for different parameter values. Memory Grant Feedback: Now supports both row-mode and batch-mode execution, dynamically ...

Hi Guys, Welcome back! Today only a small post for a small thought. Today it is as if we were back to many years ago. I remember the first years when (at least in Italy) the connection was not absolutely flat! Every minute of internet browsing had a cost and it was not cheap. After some time things changed a bit and for Browsing internet we have to pay for the amount of data downloaded. Now with the cloud it’s a bit the same logic. If you have a database on the cloud, the more data you read, the more you pay. So that’s our "favorite weapon" to avoit to pay many money on the cloud?  Well, the tuning! it is still really useful. So in this case, we don’t just use tuning to make our queries faster and to eliminate or reduce locks and deadlocks. We can and must also use tuning to reduce the number of data to read from the database . Which is particularly welcome if our database is on the cloud. I will so a the simply example just to give to you a concept. Example Suppose...

Hi Guys,   Welcome back! You Start to be really many to read this blog! Just two words: thank you very much! Today we will talk again about the Accelerated Database Recovery (ADR) feature, we talked about the same topic already 4 years ago here  SQL Server 2019 and the Accelerated Database Recovery . Yeah, it’s been four years. In 2019 we described in detail what ADR is, now it’s time to talk about what improvements this feature has in SQL 2022. I want to remind you that SQL 2022 is the latest version of the famous RDBMS produced by Microsoft.   Accelerated Data Recovery: a short summary! As we have seen some time ago, the ADR has been introduced in SQL 2019 (15.x) with the obective of improving the database availability , especially in the presence of long running transactions.  To achieve this, the database engine recovery process has been redesigned. We do not have to scan the transaction log from and to the beginning of the oldest active transaction instead o...

Hi Guys!  Welcome back! Today we will talk about GROUPING SETS. As usual I will try to explain what we are talking about in the clearest possible way and we will always give examples . So what is a GROUPING SET? GROUPING SETS can be considered an extension of the GROUP BY clause in fact it gives you the possibility to combine multiple GROUP BY clauses . How many times do we have to show in video a series of data that maybe adding them up and regrouping them for more than one field? How many times have we had to use UNION clauses? Often many clauses union together make the code less readable and less maintainable. The solution is to use GROUPING SETS. Enjoy the reading!   GROUPING SETS  As said, a grouping set can be considered as an extension of the GROUP BY clause. Speaking in a technical way, SQL grouping sets are a way to group data in SQL queries by multiple dimensions. Now, in a typical SQL GROUP BY clause, data is grouped by one or mor...