SQL Server Performance Blog where milliseconds matter

How to Read a SQL Server Execution Plan in 10 Minutes! SQL Server Performance Series – Execution Plans Made Simple Hi SQL Server Guys, Execution plans represent the keys to understand SQL Server performance. Unfortunately many developers open an execution plan… look at the colorful icons… and close it immediately. I hear a lot of times these phrases: Too complicated. Too many operators. Too much information. But the truth is that you can learn how to read an execution plan surprisingly fast. In this post I will show you a simple approach that allows you to understand most SQL Server execution plans in about 10 minutes . Step 1 – Always Read the Plan From Right to Left This is the first thing that confuses many developers. Execution plans are read from right to left. The right side shows where the data is coming from. The left side shows the final result returned to the client. Think of the plan as a pipeline of operations that transform data s...

SQL Server Execution Plan ...which Operators Secretly Kill Performance? SQL Server Performance Series – Execution Plan Deep Dive Hi SQL Server Guys, When investigating slow SQL Server queries, many developers focus only on the query text. But the real story is almost always hidden somewhere else. Inside the execution plan . Execution plans is the right place where you must look to understand how SQL Server actually processes a query. And sometimes they reveal operators that silently destroy performance. Today we look at some of the most dangerous execution plan operators. Not because they are always bad, but because when they appear in the wrong context they can cause serious performance problems. 1. Table Spool The Table Spool operator stores intermediate results in a temporary structure. SQL Server does this to avoid recomputing data multiple times. But when the dataset is large, the spool can become extremely expensive. Typical causes: ...

SQL Server Performance Tuning – a SQL Server tuning story: Avoid That Damn Table Spool! SQL Server Performance Series – Execution Plan Deep Dive Hi SQL Server Guys,  Welcome to a SQL Server tuning story. This post is inspired by something that happens very often in real projects: a query that technically works… but performs terribly. During my work I spent a lot of time optimizing queries, sometimes by adding indexes, sometimes by rewriting them. And the interesting part is that sometimes a very small rewrite can completely change the execution plan. Today's example is exactly one of those situations. Enjoy the reading! 👍 PS: In case you missed my last post:  The SQL Server Query That Looks Fast ….Until It Hits Production. Why? The Problem: Table Spool A few days ago I analyzed a query whose execution time was considered too slow by a client. When looking at the execution plan, one operator immediately caught my attention: Table Spool ...

The SQL Server Query That Looks Fast… Until It Hits Production SQL Server Performance Series – Real World Query Pitfalls Hi SQL SERVER Guys,  Today we talk about a query pattern that every SQL Server developer has seen at least once. A query that runs perfectly in development. It returns results instantly. Execution plan looks simple. Everyone thinks the problem is solved. Then the code goes to production. and... ...And suddenly the query that ran in milliseconds now takes seconds… or minutes. What happened? The problem is usually not the query itself. The problem is the data. P.S. if you missed my last post:  SQL Server Performance Weekly Recap – What You Might Have Missed This Week! The Typical Development Query In your development databases typically the amount of data is often very small. Let's say we write something like this: SELECT * FROM Orders WHERE CustomerID = @CustomerID With a few thousand rows this query looks incred...

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