SQL Server Performance Blog where milliseconds matter

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 ...