Something you didn't know about validating full-text indexing

My favourite way to compare technical experience with others is to ask them about the ways in which something can fail. Anyone can recite BOL but it's edge-case knowledge that shows a DBA has been hardened through trench warfare. After all if you've seen my talk about operational validation, or better yet implemented it, you'll know that at scale, rare events aren't rare.

So do you know how full-text indexing can fail? If you don't, then read on.

First I used setup to add the full-text feature to an existing database instance. Note that this doesn't restart or otherwise affect the existing instance (it sucks that features aren't documented from this perspective… it's extremely common to want to add something to an instance and need to know whether it will cause an outage or not!)

It does however write this to the ERRORLOG:

A new instance of the full-text filter daemon host process has been successfully started.

So let's start using it, create a demo database!

Create Database FT;
Go
Use FT;
Create Table dbo.FT (
	[Id] Int Identity(1, 1) Constraint PK_FT Primary Key,
	[Something] Nvarchar(Max)
);
Insert dbo.FT Values ('Kitty wants to give you a throat hug');
Create Fulltext Catalog FT;
Create Fulltext Index On dbo.FT (Something) Key Index PK_FT On FT;
Waitfor Delay '00:00:10' -- Time to populate
Select * From FT.dbo.FT Where Contains(Something, 'hug');
Go

Okay everything is working. Now let's break it. I've already done Install-Module DbData to install my DbData module from the PowerShell Gallery to make things a bit easier. My test server name is C1N1.

$wmi = Get-DbWmi C1N1
$wmi.Services["MSSQLFDLauncher"].Stop()
$wmi.Services["MSSQLFDLauncher"].StartMode = "Disabled"
$wmi.Services["MSSQLFDLauncher"].Alter()

And test it again…

Select * From FT.dbo.FT Where Contains(Something, 'hug');

How about that, it's still working! Now let's restart the SQL Server instance.

$wmi.Services["MSSQLSERVER"].Stop()
Start-Sleep -Seconds 2
$wmi.Services["MSSQLSERVER"].Start()

This error will be logged in the ERRORLOG now:

Error: 9954, Severity: 16, State: 1.
SQL Server failed to communicate with filter daemon launch service  (Windows error: Windows Error: hr = 0x80070422(failed to retrieve text for this error)). full-text filter daemon process failed to start. full-text search functionality will not be available.

Pretty cool. And if we try to query again…

Select * From dbo.FT Where Contains(Something, 'hug');

We'll get this error:

Msg 30046, Level 16, State 1, Line 16
SQL Server encountered error 0x80070422 while communicating with full-text filter daemon host (FDHost) process. Make sure that the FDHost process is running. To re-start the FDHost process, run the sp_fulltext_service 'restart_all_fdhosts' command or restart the SQL Server instance.

Awesome. Also note that the error message gives you syntax which doesn't exist on the new Microsoft Docs documentation for this function.

Now what happens when we fix the service and start it again?

$wmi.Services["MSSQLFDLauncher"].StartMode = "Manual"
$wmi.Services["MSSQLFDLauncher"].Alter()
$wmi.Services["MSSQLFDLauncher"].Start()

Select * From FT.dbo.FT Where Contains(Something, 'hug');

We'll get a new error this time:

Msg 30053, Level 16, State 102, Line 16
An error has occurred during the full-text query. Common causes include: word-breaking errors or timeout, FDHOST permissions/ACL issues, service account missing privileges, malfunctioning IFilters, communication channel issues with FDHost and sqlservr.exe, etc.

Beautiful. We've fully broken it in every which way. How do we fix it?

Exec sp_fulltext_service 'restart_all_fdhosts';

Ta-da!

The first error message holds the key. It's not enough to just enable the service, you need to run that command also (which will also start the service for you as well as doing something internally) or restart SQL Server after the service is enabled.

Here's where we go to the next level. You now know that full-text indexing can have a problem on startup that isn't visible externally but surely this could never happen to you, right? But I guarantee you that if you have 500 servers many of them currently have this issue. When a developer or application support person encounters it that's going to take half an hour or an hour of investigation, then logging incidents, then passing it on to you, for another half hour or hour of investigation, and then more time, and more time, and then doing a root cause analysis, and then sharing it with your team, and then…

Wouldn't it be so much easier to just write a validation test for this now so that if this ever occurs you can just proactively go fix it? Well you can!

Select * From sys.dm_fts_fdhosts;

If you're on SQL 2008 and above, if you have full-text indexing installed, if this query doesn't return rows, you have a broken instance. Let's put that into a Jojoba test (which you can run in PowerShell, or run through Jenkins with full reporting output, and is automatically highly parallelised in the background to run across 500 servers in about 30 seconds). I've done this after installing my Jojoba module from the PowerShell Gallery with Install-Module Jojoba.

function Test-FullTextIndexing {
        [CmdletBinding()]
        param (
                [Parameter(Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)]
                [Alias("ComputerName")]
                [string] $InputObject,

                [string] $JojobaBatch = [System.Guid]::NewGuid().ToString(),
                [int]    $JojobaThrottle = $env:NUMBER_OF_PROCESSORS
        )

        begin {
        }

        process {
                Start-Jojoba {
                        $smo = Get-DbSmo $InputObject
                        if ($smo.Version -lt [version] "10.0") {
                                Write-JojobaSkip "Unable to test on < SQL 2008"
                                return
                        }
                        if ($smo.IsFullTextInstalled) {
                                $dbData = New-DbConnection $InputObject master | New-DbCommand "Select * From sys.dm_fts_fdhosts;" | Get-DbData -OutputAs DataRows
                                if (!$dbData) {
                                        Write-JojobaFail "Full-text indexing is broken"
                                        Write-Output "Try enabling the full-text filter daemon service and running Exec sp_fulltext_service 'restart_all_fdhosts';"
                                }
                        } else {
                                Write-JojobaSkip "Full-text indexing is not installed"
                        }
                }
        }

        end {
                Publish-Jojoba
        }
}

When it's broken…

When it's fixed…

If you're interested in this kind of stuff then you probably want to start with watching my webinar on operational validation of SQL Server at scale with Jenkins and PowerShell.

Share on: