SQL Server Performance Blog where milliseconds matter

SQL Server 2025 Vector Data Type – Why It Matters for AI (and Known Bugs ⚠️) Hi SQL SERVER Guys, If you missed my previous deep dive on REGEX performance, you can read it here: 👉  SQL Server 2025, REGEXP_LIKE Can Trigger Batch Mode Today we move into something even more interesting: the new Vector Data Type in SQL Server 2025 that introduces native support for AI workloads. This is not just a new data type… it’s a major shift toward AI-native databases . What Is the Vector Data Type? The VECTOR data type is designed to store embeddings . Embeddings are numerical representations of: text images documents code Example: DECLARE @v VECTOR(3) = [0.1, 0.5, 0.9]; Each value represents a dimension in a vector space. When Was It Introduced? The VECTOR data type was introduced in SQL Server 2025 as part of Microsoft's push into: AI integration semantic search RAG (Retrieval-Augmented Generation) This aligns SQL Server with modern ...

SQL Server 2025 – REGEXP_LIKE Can Trigger Batch Mode Hi SQL SERVER Guys, If you missed my previous post about REGEX performance in SQL Server 2025, you can check it here: 👉  SQL Server 2025! This is gold 💰 ... When REGEXP is FASTER than LIKE! try it! Today I want to show you something even more surprising. A query where REGEXP_LIKE triggers Batch Mode on Rowstore … without any columnstore index. This is a detail of the engine that almost nobody has noticed yet . Why This Is Interesting This example connects three concepts every DBA should know: SQL Server engine internals Batch Mode on Rowstore Regular Expressions And it shows how SQL Server 2025 can completely change the execution strategy . Step 1 – Create a Large Dataset DROP TABLE IF EXISTS Logs; CREATE TABLE Logs ( id INT IDENTITY, message NVARCHAR(200) ); INSERT INTO Logs(message) SELECT 'error number ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NVARCHAR) ...

SQL Server 2025 Surprise! When REGEXP_LIKE is faster Than LIKE Hi SQL SERVER Guys, If you missed my previous post about the new REGEX functions, you can find it here: 👉 Why REGEX Functions in SQL Server 2025 Matter While if you missed my previous post, you can find it here: 👉  Why REGEX functions in SQL SERVER 2025 can melt you CPU? Try yourself! Today we go one step further… and this is something that might sound crazy at first . In some scenarios, REGEXP_LIKE can be up to 10x faster than LIKE . Yes… faster than LIKE! The Test: REGEX vs LIKE Let’s start with a simple dataset: CREATE TABLE Logs ( id INT IDENTITY, message NVARCHAR(400) ); INSERT INTO Logs(message) SELECT REPLICATE('error ',5) + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NVARCHAR) FROM sys.objects a CROSS JOIN sys.objects b; This creates hundreds of thousands of rows . Query 1 – LIKE SET STATISTICS TIME ON; SELECT COUNT(*) FROM Logs WHERE m...

SQL Server 2025 REGEXP_LIKE Can Melt Your CPU 😱 Hi SQL SERVER Guys, With the introduction of REGEX functions in SQL Server 2025, we finally have powerful pattern matching inside the engine. But… there is a very interesting (and dangerous) behavior that many developers don’t expect. It is called catastrophic backtracking . PS. if you missed my previous post:  SQL Server 2025 LocalDB Crash – REGEXP Functions Break Due to Missing RegExpr.dll What is Catastrophic Backtracking? In the world of Regular Expressions, catastrophic backtracking happens when: the pattern contains nested repetitions the regex engine tries millions of combinations The result? CPU spikes to 100% queries look stuck execution time grows exponentially A Simple Test Query This is a safe but very effective demo: DECLARE @s NVARCHAR(MAX) = REPLICATE('aaaaaaaaaaaaaaaaaaaaaaaaaaaa', 10) + 'b'; SELECT REGEXP_LIKE(@s,'(a+)+b'); At first glance, t...

SQL Server 2025 LocalDB Crash – REGEXP Functions Break Due to Missing RegExpr.dll ⚠️ SQL Server 2025 bug – LocalDB crash caused by missing RegExpr.dll when using REGEXP functions Hi SQL Server Guys, In my previous article we talked about SQL Server LocalDB and how it works internally. If you missed it, you can read it here: Good to Know: What Is SQL Server LocalDB? LocalDB is a lightweight version of SQL Server designed mainly for developers. It runs in user-mode , starts automatically when needed and does not require a Windows service. We also recently talked about the very powerful REGEX expressions introduced in SQL Server 2025 here:  SQL Server 2025 Regex Support These functions make text processing inside SQL Server much easier. But here comes an interesting problem. A bug in SQL Server 2025 LocalDB can cause the database engine to crash when using the new REGEXP functions. New REGEX Functions in SQL Server 2025 SQL Server 2025 intro...

Good to Know: What Is SQL Server LocalDB? 🧠 SQL Server Internals Series – LocalDB Explained Hi SQL Server Guys, Here is something that many developers use every day without really thinking about it: SQL Server LocalDB. LocalDB is an ultra-lightweight version of Microsoft SQL Server designed specifically for developers. It is perfect for scenarios like: local development environments testing and experimentation desktop applications quick prototypes The interesting part is that LocalDB is not a different database engine. It actually uses the same SQL Server engine used by full SQL Server installations. Key Characteristics of LocalDB SQL Server LocalDB was designed to be extremely simple to use. Unlike a traditional SQL Server installation, LocalDB: runs in user-mode does not require a Windows service starts automatically on the first connection uses the same SQL Server engine This makes it extremely convenient for developers who just ...

SQL Server Performance: JSON vs XML – Which One Is Faster? SQL Server Performance Series – JSON vs XML Hi SQL Server Guys, In performance discussions, usually a very important question came up: Should we use JSON or XML in SQL Server? At first glance this may look like a simple design decision. But in real production systems the choice between JSON and XML can have a significant impact on performance, scalability, and maintainability . In many projects, choosing the wrong format can introduce unnecessary overhead and slow down critical queries . In other words, the decision between JSON and XML can directly influence the success of the system we are building. That is exactly why this comparison is so important. Let’s look at what happens when we compare JSON and XML in SQL Server. JSON vs XML Performance Test By doing internal tests comparing JSON and XML processing, the results i got were quite interesting. When parsing and retrieving values f...

SQL Server Performance Weekly Recap – What You Might Have Missed This Week SQL Server Performance Series – Weekly Roundup Hi SQL SERVER Guys, This week on the blog we explored several important SQL Server performance topics. Some of them are fundamental concepts every SQL Server developer should understand. Others show real-world tuning scenarios that happen very often in production systems. If you missed some of the articles, here is a quick recap of the most recent posts published on the blog. Avoid That Damn Table Spool – A SQL Server Tuning Story In this article we analyzed a real-world query optimization scenario where a single execution plan operator was responsible for major performance problems. The culprit? A Table Spool . We explored why SQL Server sometimes introduces this operator and how a small query rewrite can completely change the execution plan and dramatically improve performance. 👉 Read the full article: Avoid That Damn Table...

Why SELECT * Is Still Killing SQL Server Performance in 2026? SQL Server Performance Series – Query Best Practices Hi SQL Server Guys, Every SQL Server developer has heard this advice at least once: Never use SELECT * in production queries. And yet, many applications still use it everywhere. At first glance, SELECT * seems convenient. It saves time when writing queries and automatically returns all columns from a table. But in real production systems, this small shortcut can create serious performance problems. Even in modern SQL Server versions, SELECT * can still negatively affect performance in several ways. 1. Unnecessary Network Cost When you use SELECT *, SQL Server returns every column in the table. But most applications do not actually need all that data. For example: SELECT * FROM Orders WHERE CustomerID = 10 If the Orders table contains 30 columns but the application only needs 3 of them, SQL Server still sends all 30 columns o...

The Most Common SQL Server Indexing Mistake (And How to Fix It) SQL Server Performance Series – Indexing Pitfalls Hi SQL Server Guys, Indexes are one of the most powerful performance features in SQL Server. But they are also one of the most misunderstood. Many developers believe that adding more indexes automatically improves performance. Unfortunately the opposite is often true. One of the most common SQL Server performance problems I see in real systems is a database full of unnecessary or poorly designed indexes. Today we look at the most common SQL Server indexing mistake and how to fix it. The Real Problem: Too Many Indexes In many databases you will find tables with a surprising number of indexes. Sometimes ten. Sometimes twenty. Sometimes even more. This usually happens because new indexes are added over time whenever a query becomes slow. But very rarely are old or redundant indexes removed. Over time this creates what we could...

The SQL Server Index Strategy That Works 90% of the Time 👌👍 SQL Server Performance Series – Indexing Best Practices Hi SQL SERVER Guys,  Indexing is one of the most important aspects of SQL Server performance tuning. A well-designed index can make a query run in milliseconds. A poorly designed index can make the same query scan millions of rows. The problem is that indexing strategies are often overcomplicated. In reality, a relatively simple approach works in most real-world situations. Today we look at an indexing strategy that works surprisingly well in about 90% of cases. Clustered vs Nonclustered Indexes Every SQL Server table should have a good clustered index . The clustered index defines the physical order of the table data. Without it, SQL Server creates a heap structure, which can lead to inefficient scans and fragmentation. Typical choices for clustered indexes include: primary keys identity columns monotonically increasing...

Why Your SQL Server Query Is Fast in SSMS but Slow in Production? SQL Server Performance Series – Understanding Real World Query Behavior Hi SQL Server Guys, This is one of the most frustrating situations every SQL Server developer eventually faces. A query that runs perfectly fast in SSMS suddenly becomes painfully slow in production. You test it locally. You execute it in SQL Server Management Studio. And the result appears instantly. But when the application executes exactly the same query, performance drops dramatically. What is happening? The answer usually lies in how SQL Server builds and reuses execution plans . 1. Parameter Sniffing One of the most common causes of this problem is something called parameter sniffing . When SQL Server executes a parameterized query for the first time, it creates an execution plan based on the parameter values used during that first execution. SQL Server then caches that execution plan and reuses it ...