SQL Server Performance Blog where milliseconds matter

SQL Server Performance Weekly Recap – What You Might Have Missed This Week! 👌 Hi SQL SERVER Guys, This week we explored some of the most important SQL Server performance topics. Some of them challenge common habits developers have used for years. Others reveal optimization techniques that can dramatically improve query performance. If you missed some of the posts, here is a quick recap of the posts of the week: SQL SERVER! SARGability: the One Concept You Absolutely Must Understand! If there is one concept every SQL Server developer must understand, it is SARGability . A non-SARGable predicate can silently destroy performance by preventing SQL Server from using indexes efficiently. In this article we explored why SARGability matters and how a small rewrite can completely change the execution plan. The Most Dangerous SQL Server Query Pattern Nobody Talks About There is a query pattern that appears perfectly innocent… but can cause huge performan...

Hi SQL SERVER Guys, Welcome back to this series about the most important concepts about performance! If you care about SQL Server performance, you cannot miss this post becuase there is one concept you absolutely must understand: SARGability . In a previous post I showed how dangerous some SQL queries can be for performance: The Most Dangerous SQL Server Query Today we look at one of the most important techniques to write faster queries: the SARGable way . What does SARGable mean? SARGable stands for Search ARGument ABLE . It means SQL Server can use an index efficiently to find rows. When a predicate is SARGable SQL Server can perform an: Index Seek (fast) When it is not SARGable SQL Server often performs an: Index Scan (slow) A classic NON-SARGable query SELECT * FROM Orders WHERE YEAR(OrderDate) = 2024; This query looks simple, but it hides a performance problem. Because the column is wrapped in the function YEAR() , SQL Serve...

The Most Dangerous SQL Server Query Pattern Nobody Talks About ... SQL Server Performance Series – Hidden Performance Killers Hi SQL SERVER Guys, Today we talk about a query pattern that silently destroys performance in many SQL Server systems. If you lost my last post, just click here to enjoy it...  previous post It does not look dangerous since: It compiles fine. It returns the correct results. It passes code review. But under the hood  it forces SQL Server to ignore indexes and scan massive amounts of data . And the worst part? Many developers do not even realize they are doing it. Let's look at the pattern. The Pattern A function applied directly on a column inside the WHERE clause. SELECT * FROM Orders WHERE YEAR(OrderDate) = 2025; Looks harmless. But this single line forces SQL Server to evaluate the function for every row. That means the index on OrderDate becomes useless. Why This Is Dangerous When SQL Server s...

Why Most Developers Should Stop Using XML in SQL Server SQL Server Performance Series – Understanding the Cost of Modern Data Formats Previous article: JSON vs XML Indexing – Which One Really Performs Better?   Hi SQL SERVER guys, today we talk about something that may sound controversial. XML in SQL Server. For years it was considered the "enterprise" way to store structured documents. And it was true but modern workloads are changing. And due to this reason in many cases XML is now the wrong choice. Of course, this does not mean XML is useless. But it does mean that most developers still use it without understanding the real performance cost. So let's break it down. Why Developers Loved XML When SQL Server introduced native XML support, it solved many real problems. Hierarchical data representation Schema validation Powerful XQuery language Integration with enterprise systems For complex documents it was a big step forward...

JSON vs XML Indexing in SQL Server – The Ultimate Performance Showdown (Real Benchmarks Inside) SQL Server Performance Series – Advanced Indexing Deep Dive Related article in this series: SQL Server Performance Series – Engine-Level Optimization Deep Dive Hi SQL SERVER guys, today we will answer to a question that keeps coming back in modern SQL Server workloads: Should you index XML… or JSON? Which one performs better? Which one scales? Which one burns your CPU? As you usually do let’s benchmark it properly. Why This Matters Modern applications store semi-structured data everywhere: Microservices payloads Audit logs Dynamic attributes External API responses SQL Server supports both XML and JSON standards, but indexing strategies are completely different. How XML Indexing Works XML supports the following types of indexes: Primary XML Index Secondary PATH index Secondary VALUE index Secondary PROPERTY index It shreds XML into inte...

SQL Server XML Indexes – When They Help, When They Hurt, and What Nobody Tells You SQL Server Performance Series – Understanding the Engine Beyond Traditional Indexes Previous article: I Removed 32 Seconds From This SQL Server Query Without Adding an Index Hi guys, In the previous post we pushed the SQL Server engine to remove 32 seconds from a query — without adding a single index. Today we go somewhere different. Not rowstore. Not columnstore. Not filtered indexes. XML Indexes. Powerful. Expensive. Often misunderstood. Let’s break them down properly. The Problem You store semi-structured data inside an XML column. Then someone writes a query like this: SELECT * FROM Orders WHERE OrderData.value('(/Order/CustomerID)[1]', 'INT') = 42; It works. But performance is terrible. Full table scan. XML parsing per row. CPU explosion. Why XML Is Expensive Without an XML index, SQL Server must: Shred XML at runtime Parse the...

I Removed 32 Seconds From This SQL Server Query Without Adding an Index SQL Server Performance Series – No New Index. No Schema Change. Just pure engine optimization. Hi guys, This is not theory. This is not lab-only tuning. This happened on a real workload. Original execution time: 42 seconds. After optimization: 9.8 seconds. No new index. No schema change. No hardware upgrade. Just understanding how the engine works. Happy reading — let’s make SQL Server fly. The Problem A reporting query running on a table with ~5 million rows. Users were complaining about slow dashboard refresh. CPU spikes appeared during heavy aggregation. Environment: SQL Server 2019 Compatibility Level 150 Rowstore table No columnstore index The Query SELECT CustomerID, SUM(Amount) AS TotalAmount FROM SalesBig GROUP BY CustomerID; Simple. Clean. Nothing exotic. Yet painfully slow. Metrics Before SET STATISTICS IO, TIME ON; Elapsed Time: ...

Still on Batch Mode… – Part 6: Compatibility & Query Settings That Influence Execution Back to the series: Part 5 – How to Force Batch Mode on Rowstore Hi guys, We’re still on Batch Mode, because understanding when it works is just as important as knowing how to force it. Today we go deeper: we will see compatibility levels, memory grants, cardinality estimation, and trace flags. Happy reading and may your CPU stay cool under heavy aggregation! Compatibility Requirements Batch Mode on Rowstore requires both: ✔ SQL Server 2019 or newer ✔ Database compatibility level ≥ 150 So, if your database runs at compatibility 140 or lower then Batch Mode on Rowstore simply cannot activate. Check Compatibility SELECT name, compatibility_level FROM sys.databases WHERE name = DB_NAME(); Change Compatibility ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150; GO Always test before upgrading — optimizer behavior changes across compatibility levels. De...

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