Skip to main content

SQL Server Full-Text Search: when a stop word broke our search

One of our users complained that the search functionality was broken. Searches that should have returned results came back empty. The cause turned out to be a single word: 'both'.

If you are new to Full-Text Search, start with my previous post. It explains the building blocks I use below.

The problem

The search in our application is built on SQL Server Full-Text Search. The terms a user types are combined into a CONTAINS query. A simplified version looks like this:

SELECT ProductId, Name
FROM dbo.Products
WHERE CONTAINS(Description, '"bike" AND "both"');

This returned no rows, even though we knew there were products matching both terms. Searching for "bike" alone worked fine.

Looking for the cause

The cause is simple: 

Stop words

Stop words are words that are not indexed because they are considered too common to be useful. SQL Server ships with a system stoplist per language, and 'both' is on the English one:

SELECT stopword
FROM sys.fulltext_system_stopwords
WHERE language_id = 1033
  AND stopword = 'both';

With sys.dm_fts_parser you can see how SQL Server treats the query string:

SELECT display_term, special_term
FROM sys.dm_fts_parser('"bike" AND "both"', 1033, 0, 0);

In the output the special_term column marks 'both' as a Noise Word. Now run the same query with NULL as the stoplist:

SELECT display_term, special_term
FROM sys.dm_fts_parser('"bike" AND "both"', 1033, NULL, 0);

This time both terms are an Exact Match.

Remark: the third parameter is the stoplist id. 0 means the system stoplist, NULL means no stoplist. Not what you would expect, so double check it.

The explanation: the stop word is not stored in the index. When a boolean query contains a stop word, SQL Server returns zero rows and raises the warning. By default it doesn't ignore the word and evaluate the rest of the query, it gives up on the whole condition.

What are the options?

There are a few ways to deal with this:

  • Enable transform noise words: a server configuration option. With the value set to 1, stop words are ignored and the rest of the query is evaluated. It is a server-wide setting, so it affects every database on the instance. And the stop word is still not searchable, it is silently dropped.
  • Use a custom stoplist: create a stoplist from the system one and remove the words that matter. This works, but you need to know up front which words that are.
  • Remove the stop words in your application: fragile, and you have to maintain a list per language.
  • Turn the stoplist off: no stop words, every word is indexed.

We went for the last one. A user who types a word expects the search to take that word into account.

The fix

One statement:

ALTER FULLTEXT INDEX ON dbo.Products SET STOPLIST = OFF;

Remark: by default SQL Server repopulates the full-text index after you change the stoplist. On a large table this takes a while, so plan it. You can add WITH NO POPULATION to postpone it, but until you start a population the existing rows don't contain the previously ignored words.

You can follow the progress through the catalog. A value of 0 means the population is finished:

SELECT FULLTEXTCATALOGPROPERTY('ProductCatalog', 'PopulateStatus') AS PopulateStatus;

After the population, check that the word is now in the index:

SELECT display_term, document_count
FROM sys.dm_fts_index_keywords(DB_ID(), OBJECT_ID('dbo.Products'))
WHERE display_term = 'both';

Run the original query again and the results are back.

What does it cost?

Turning off the stoplist is not free:

  • The index gets bigger. Every common word is now indexed.
  • Population takes longer.
  • Very common words match almost every row. A search for 'the' is now valid, but it won't help the user much.

For our search this is a good trade. If you only have a few words that cause trouble, a custom stoplist without those words is the more precise option:

CREATE FULLTEXT STOPLIST ProductStoplist FROM SYSTEM STOPLIST;

ALTER FULLTEXT STOPLIST ProductStoplist DROP 'both' LANGUAGE 1033;

ALTER FULLTEXT INDEX ON dbo.Products SET STOPLIST ProductStoplist;

Wrapping up

That's it! Next time a full-text search returns nothing, look at how the query is parsed before you blame the data. Debugging through sys.dm_fts_parser would have saved us some time.

More information

Labels:

Popular posts from this blog

Podman– Command execution failed with exit code 125

After updating WSL on one of the developer machines, Podman failed to work. When we took a look through Podman Desktop, we noticed that Podman had stopped running and returned the following error message: Error: Command execution failed with exit code 125 Here are the steps we tried to fix the issue: We started by running podman info to get some extra details on what could be wrong: >podman info OS: windows/amd64 provider: wsl version: 5.3.1 Cannot connect to Podman. Please verify your connection to the Linux system using `podman system connection list`, or try `podman machine init` and `podman machine start` to manage a new Linux VM Error: unable to connect to Podman socket: failed to connect: dial tcp 127.0.0.1:2655: connectex: No connection could be made because the target machine actively refused it. That makes sense as the podman VM was not running. Let’s check the VM: >podman machine list NAME         ...
Read more

Cache stampede: when our cache turned against us

While investigating some performance issues, we ran into an ASP.NET Core API that cached a fairly expensive aggregation query for 60 seconds. Under normal load, that was fine: one request rebuilds the cache, everyone else reads from it. Under peak load, dozens of requests would arrive in that same expiry window, all see a cache miss, and all fire the same expensive query in parallel. The database didn't like that. That was the moment when our caching layer stopped helping and started hurting. A burst of requests comes in at the same time, all miss the cache, and all go hammer the database or the downstream API at once. That's a cache stampede . The cache was supposed to protect our backend, and for a few hundred milliseconds it did the opposite. Why this happens IMemoryCache.GetOrCreate (and its async sibling) looks like it protects you, but it doesn't add any locking on its own. Look at the naive version: public async Task<Report> GetReportAsync(string key) ...
Read more

VS Code Planning mode

After the introduction of Plan mode in Visual Studio , it now also found its way into VS Code. Planning mode, or as I like to call it 'Hannibal mode', extends GitHub Copilot's Agent Mode capabilities to handle larger, multi-step coding tasks with a structured approach. Instead of jumping straight into code generation, Planning mode creates a detailed execution plan. If you want more details, have a look at my previous post . Putting plan mode into action VS Code takes a different approach compared to Visual Studio when using plan mode. Instead of a configuration setting that you can activate but have limited control over, planning is available as a separate chat mode/agent: I like this approach better than how Visual Studio does it as you have explicit control when plan mode is activated. Instead of immediately diving into execution, the plan agent creates a plan and asks some follow up questions: You can further edit the plan by clicking on ‘Open in Editor’: ...
Read more