Lesson Learned #351: Troubleshooting FullText issues

[Guest Jose_Manuel_Jurado]

Today, we got a new service request where our customer asks about the time spent populating the full-text. Following, I would like to share with you some lessons learned during this process, specifically working with Azure SQL Database.

 

 

 

For this example, we are going to use a General Purpose database with 8 vCores. Let's get started with the creation of the table and fulltext.

 

 

 

---------------

-- Create the FullText Catalog

---------------

CREATE FULLTEXT CATALOG Ft WITH ACCENT_SENSITIVITY = OFF AS DEFAULT

 

---------------------------------------

-- Create the table documents with a PK

----------------------------------------

DROP TABLE IF EXISTS dbo.Documents

CREATE TABLE dbo.Documents ( IdDoc BIGINT IDENTITY(1,1), Doc NVARCHAR(MAX))

CREATE UNIQUE CLUSTERED INDEX PK_Documents_ID ON dbo.Documents (IdDoc)

 

 

 

 

 

 

 

Next, we are going to create the FullText Index.

 

 

 

 

 

---------------------------------------------------------------------------------------

-- Create Index, we are going to use the language id 3082 that corresponds with Spanish

---------------------------------------------------------------------------------------

CREATE FULLTEXT INDEX ON dbo.Documents

(Doc Language 3082)

KEY INDEX PK_Documents_ID ON Ft;

 

 

 

 

 

Finally, we are going to add several rows in this table:

 

 

 

 

 

INSERT INTO dbo.Documents (Doc) values('La cromatófora se mimetizaba con el entorno, cambiando de color según su estado de ánimo.')

INSERT INTO dbo.Documents (Doc) values('Durante la tormenta, los relámpagos iluminaban el cielo de manera fugaz y efímera.')

INSERT INTO dbo.Documents (Doc) values('El ornitóptero surcaba los aires con elegancia, imitando el vuelo de las aves.')

INSERT INTO dbo.Documents (Doc) values('La hipopotomonstrosesquipedaliofobia es el miedo irracional a las palabras largas.')

INSERT INTO dbo.Documents (Doc) values('El caleidoscopio mostraba un espectro de colores brillantes y fascinantes.')

INSERT INTO dbo.Documents (Doc) values('El astrónomo observaba las estrellas con su telescopio refractor.')

INSERT INTO dbo.Documents (Doc) values('La escultura abstracta despertaba sentimientos de perplejidad y desconcierto.')

INSERT INTO dbo.Documents (Doc) values('En el museo, había una exhibición de insectos exóticos y arácnidos venenosos.')

INSERT INTO dbo.Documents (Doc) values('El piano de cola resonaba en la sala de conciertos, llenando el espacio con su sonido melodioso.')

INSERT INTO dbo.Documents (Doc) values('El científico estudiaba los fósiles en busca de restos de un arqueoptérix.')

INSERT INTO dbo.Documents (Doc) values('El arcoíris se desplegó en el horizonte, formando un semicírculo multicolor.')

INSERT INTO dbo.Documents (Doc) values('El bibliotecario recomendó una obra literaria de estilo barroco y alta complejidad.')

INSERT INTO dbo.Documents (Doc) values('La equinoccio se produce dos veces al año, cuando el día y la noche tienen la misma duración.')

INSERT INTO dbo.Documents (Doc) values('El perro callejero tenía una peculiaridad: su heterocromía en ambos ojos.')

INSERT INTO dbo.Documents (Doc) values('El surrealismo se caracteriza por la representación de imágenes oníricas y subconscientes.')

INSERT INTO dbo.Documents (Doc) values('El farmacéutico preparó una decocción con hierbas medicinales para aliviar el malestar.')

INSERT INTO dbo.Documents (Doc) values('La aeronave sobrevolaba majestuosamente las montañas nevadas.')

INSERT INTO dbo.Documents (Doc) values('La arquitectura gótica se distingue por sus imponentes contrafuertes y rosetones.')

INSERT INTO dbo.Documents (Doc) values('El químico realizó una destilación para obtener un compuesto puro.')

INSERT INTO dbo.Documents (Doc) values('El petricor impregnaba el aire después de la lluvia, evocando recuerdos de infancia.')

INSERT INTO dbo.Documents (Doc) values('La coreografía contemporánea desafió los límites de la danza tradicional.')

INSERT INTO dbo.Documents (Doc) values('El entomólogo estudiaba minuciosamente los insectos, clasificándolos por especies.')

INSERT INTO dbo.Documents (Doc) values('El mariposeo de las luciérnagas iluminaba la noche con destellos intermitentes.')

INSERT INTO dbo.Documents (Doc) values('El médico recetó un analgésico para aliviar el dolor crónico.')

INSERT INTO dbo.Documents (Doc) values('La gastronomía molecular combina la ciencia y la cocina en creaciones innovadoras.')

INSERT INTO dbo.Documents (Doc) values('El enigmático cuadro del pintor abstracto generaba diversas interpretaciones.')

INSERT INTO dbo.Documents (Doc) values('El camarero recomendó probar el exquisito plato de pulpo a la gallega.')

INSERT INTO dbo.Documents (Doc) values('El etnógrafo estudia y documenta las costumbres y tradiciones de diferentes culturas.')

INSERT INTO dbo.Documents (Doc) values('La bioluminiscencia de las medusas creaba un espectáculo fascinante en el océano.')

INSERT INTO dbo.Documents (Doc) values('El lingüista analizaba la estructura sintáctica de las oraciones en diferentes idiomas.')

INSERT INTO dbo.Documents (Doc) values('El astrólogo predijo el futuro a través de la interpretación de las posiciones planetarias.')

INSERT INTO dbo.Documents (Doc) values('El arquitecto diseñó un rascacielos vanguardista con formas futuristas.')

INSERT INTO dbo.Documents (Doc) values('La clorofila es el pigmento responsable de la fotosíntesis en las plantas.')

INSERT INTO dbo.Documents (Doc) values('El escultor esculpía con maestría en el mármol, creando figuras realistas.')

INSERT INTO dbo.Documents (Doc) values('La filosofía existencialista reflexiona sobre el sentido y la libertad del ser humano.')

INSERT INTO dbo.Documents (Doc) values('La melodía del violín resonaba en el teatro, envolviendo al público en su sonido.')

INSERT INTO dbo.Documents (Doc) values('El herpetólogo estudiaba los reptiles y anfibios en su hábitat natural.')

INSERT INTO dbo.Documents (Doc) values('El alquimista buscaba la piedra filosofal, una sustancia capaz de transmutar metales.')

INSERT INTO dbo.Documents (Doc) values('La efervescencia del refresco le dio un toque burbujeante y refrescante.')

INSERT INTO dbo.Documents (Doc) values('El paleontólogo desenterró un fósil de dinosaurio en el yacimiento arqueológico.')

INSERT INTO dbo.Documents (Doc) values('La edafología estudia los suelos y su relación con las plantas y el medio ambiente.')

INSERT INTO dbo.Documents (Doc) values('El ventrílocuo hacía que su muñeco cobrara vida, asombrando al público.')

INSERT INTO dbo.Documents (Doc) values('La gastritis es una inflamación del revestimiento del estómago que causa malestar.')

INSERT INTO dbo.Documents (Doc) values('El botánico identificó una nueva especie de orquídea en el bosque tropical.')

INSERT INTO dbo.Documents (Doc) values('El renacimiento fue un periodo de gran desarrollo artístico y cultural en Europa.')

INSERT INTO dbo.Documents (Doc) values('La tectónica de placas explica el movimiento de las grandes masas continentales.')

INSERT INTO dbo.Documents (Doc) values('El espeleólogo exploraba las profundidades de las cuevas en busca de nuevos descubrimientos.')

INSERT INTO dbo.Documents (Doc) values('El quokka es un marsupial australiano conocido por su expresión facial amigable.')

INSERT INTO dbo.Documents (Doc) values('El antropólogo estudia la evolución y las características de las diferentes culturas.')

INSERT INTO dbo.Documents (Doc) values('La fenomenología se centra en el estudio de la conciencia y la experiencia humana.')

INSERT INTO dbo.Documents (Doc) values('El acuarelista pintó un paisaje bucólico utilizando colores translúcidos.')

INSERT INTO dbo.Documents (Doc) values('El estegosaurio era un dinosaurio herbívoro que se caracterizaba por sus placas óseas.')

INSERT INTO dbo.Documents (Doc) values('El silabario es un sistema de escritura que utiliza sílabas en lugar de letras individuales.')

INSERT INTO dbo.Documents (Doc) values('La selva amazónica alberga una gran diversidad de flora y fauna.')

INSERT INTO dbo.Documents (Doc) values('El sismólogo estudia los terremotos y los fenómenos relacionados con la corteza terrestre.')

INSERT INTO dbo.Documents (Doc) values('El canóforo sostenía un jarrón ceremonial en sus manos, lleno de flores frescas.')

INSERT INTO dbo.Documents (Doc) values('La fonética se ocupa del estudio de los sonidos del habla y su producción.')

INSERT INTO dbo.Documents (Doc) values('El zooplancton es un conjunto de organismos microscópicos que habitan en el agua.')

INSERT INTO dbo.Documents (Doc) values('El arco de medio punto es una característica distintiva de la arquitectura románica.')

INSERT INTO dbo.Documents (Doc) values('El meteorólogo pronosticó lluvias intensas y vientos fuertes para los próximos días.')

INSERT INTO dbo.Documents (Doc) values('El boticario preparó una poción con ingredientes mágicos para curar al enfermo.')

INSERT INTO dbo.Documents (Doc) values('El claroscuro es una técnica artística que juega con los contrastes de luz y sombra.')

INSERT INTO dbo.Documents (Doc) values('El geólogo analizó la composición de las rocas y los minerales en el laboratorio.')

INSERT INTO dbo.Documents (Doc) values('La marimba es un instrumento musical de percusión que se originó en África.')

INSERT INTO dbo.Documents (Doc) values('El literato escribía poesía llena de metáforas y figuras retóricas.')

INSERT INTO dbo.Documents (Doc) values('El quirófano es la sala de operaciones donde se realizan intervenciones quirúrgicas.')

INSERT INTO dbo.Documents (Doc) values('La migración de las aves es un fenómeno fascinante que ocurre cada año.')

INSERT INTO dbo.Documents (Doc) values('El hexámetro es un tipo de verso compuesto por seis pies poéticos.')

INSERT INTO dbo.Documents (Doc) values('El astrágalo es un hueso del pie que forma parte del sistema de articulación.')

INSERT INTO dbo.Documents (Doc) values('La epistemología estudia la naturaleza del conocimiento y los métodos de adquisición.')

INSERT INTO dbo.Documents (Doc) values('El psicólogo utilizó la técnica del psicoanálisis para explorar el inconsciente del paciente.')

INSERT INTO dbo.Documents (Doc) values('La crisálida es la etapa de transformación de la mariposa antes de su eclosión.')

INSERT INTO dbo.Documents (Doc) values('El viaducto se elevaba sobre el valle, permitiendo el paso del tren por encima.')

INSERT INTO dbo.Documents (Doc) values('El telurismo es una corriente artística que representa paisajes naturales con gran detalle.')

INSERT INTO dbo.Documents (Doc) values('El dodecaedro es un poliedro regular que tiene doce caras pentagonales.')

INSERT INTO dbo.Documents (Doc) values('La palinología estudia los granos de polen y su relación con las plantas y el medio ambiente.')

INSERT INTO dbo.Documents (Doc) values('El pentagrama es el sistema de escritura musical que se utiliza para notar las notas y los ritmos.')

INSERT INTO dbo.Documents (Doc) values('La pituitaria es una glándula endocrina que regula la secreción de hormonas en el cuerpo.')

INSERT INTO dbo.Documents (Doc) values('El astrágalo es un hueso del pie que forma parte del sistema de articulación.')

INSERT INTO dbo.Documents (Doc) values('La entomofobia es el miedo irracional a los insectos.')

INSERT INTO dbo.Documents (Doc) values('El fonema es la unidad mínima de sonido en un idioma.')

INSERT INTO dbo.Documents (Doc) values('La fotólisis es un proceso químico que se produce mediante la acción de la luz.')

INSERT INTO dbo.Documents (Doc) values('El equilibrista caminaba sobre la cuerda floja con gran destreza y equilibrio.')

INSERT INTO dbo.Documents (Doc) values('El astrónomo observaba los astros a través de un telescopio de alta potencia.')

INSERT INTO dbo.Documents (Doc) values('La serendipia es el descubrimiento afortunado de algo valioso mientras se busca otra cosa.')

INSERT INTO dbo.Documents (Doc) values('El renacuajo se transformó en una hermosa rana tras su metamorfosis.')

INSERT INTO dbo.Documents (Doc) values('El filólogo estudia la historia y la evolución de las lenguas y las obras literarias.')

INSERT INTO dbo.Documents (Doc) values('La pirotecnia es el arte de fabricar fuegos artificiales y espectáculos pirotécnicos.')

INSERT INTO dbo.Documents (Doc) values('El enólogo experto cató el vino, apreciando sus matices y aromas.')

INSERT INTO dbo.Documents (Doc) values('La bailarina ejecutó un arabesco perfecto durante su actuación en el escenario.')

INSERT INTO dbo.Documents (Doc) values('El ornitólogo identificó el canto de las aves y sus diferentes especies.')

INSERT INTO dbo.Documents (Doc) values('El arqueólogo desenterró una antigua vasija de cerámica en el yacimiento arqueológico.')

INSERT INTO dbo.Documents (Doc) values('La endoscopia es un procedimiento médico que permite la visualización del interior del cuerpo')

INSERT INTO dbo.Documents (Doc) values('El equinoccio de primavera marca el inicio de la estación y el día y la noche tienen la misma duración.')

INSERT INTO dbo.Documents (Doc) values('La ictericia es una enfermedad que se caracteriza por la coloración amarillenta de la piel y los ojos.')

INSERT INTO dbo.Documents (Doc) values('El fenómeno de la fluorescencia se produce cuando un material emite luz bajo la excitación de la radiación.')

INSERT INTO dbo.Documents (Doc) values('El exoesqueleto es una estructura externa dura que protege el cuerpo de algunos animales.')

INSERT INTO dbo.Documents (Doc) values('La iridiscencia es el fenómeno óptico en el que se producen colores brillantes y cambiantes.')

INSERT INTO dbo.Documents (Doc) values('El eufemismo es una figura retórica que se utiliza para suavizar o disfrazar algo desagradable o inapropiado.')

INSERT INTO dbo.Documents (Doc) values('El mecanismo de relojería funcionaba con precisión, moviendo las agujas sin problemas.')

INSERT INTO dbo.Documents (Doc) values('La sístole y la diástole son los dos movimientos del corazón en cada ciclo de bombeo de la sangre.')

INSERT INTO dbo.Documents (Doc) values('El fractal es una estructura geométrica que se repite a diferentes escalas y se caracteriza por su complejidad.')

INSERT INTO dbo.Documents (Doc) values('El cianotipo es una técnica fotográfica antigua que utiliza sales de hierro para obtener imágenes en tonos azules.')

INSERT INTO dbo.Documents (Doc) values('El xilófono es un instrumento musical de percusión que consta de láminas de madera de diferentes tamaños y alturas.')

INSERT INTO dbo.Documents (Doc) values('La magnanimidad es una cualidad que se atribuye a las personas generosas y nobles de corazón.')

INSERT INTO dbo.Documents (Doc) values('La lisergia es un estado alterado de la conciencia que se produce por la ingestión de sustancias alucinógenas.')

INSERT INTO dbo.Documents (Doc) values('La laringoscopia es un procedimiento médico que se utiliza para examinar la laringe y las cuerdas vocales.')

INSERT INTO dbo.Documents (Doc) values('El neonato es un recién nacido en sus primeros días de vida.')

INSERT INTO dbo.Documents (Doc) values('El triciclo es un vehículo de tres ruedas que se utiliza principalmente para el transporte de niños.')

INSERT INTO dbo.Documents (Doc) values('El antropocentrismo es una visión del mundo que coloca al ser humano como el centro y la medida de todas las cosas.')

INSERT INTO dbo.Documents (Doc) values('La escafandra es un traje especial que permite la respiración y la movilidad en ambientes submarinos.')

INSERT INTO dbo.Documents (Doc) values('La grafología es el estudio de la escritura a mano con el fin de revelar rasgos de personalidad y características psicológicas.')

INSERT INTO dbo.Documents (Doc) values('El camaleón es un reptil capaz de cambiar de color para adaptarse a su entorno y comunicarse con otros individuos.')

INSERT INTO dbo.Documents (Doc) values('La elocuencia es la capacidad de hablar o escribir de manera persuasiva y efectiva.')

INSERT INTO dbo.Documents (Doc) values('La briología es el estudio de los musgos y su ecología.')

INSERT INTO dbo.Documents (Doc) values('El pterodáctilo era un reptil volador que vivió en la era de los dinosaurios.')

INSERT INTO dbo.Documents (Doc) values('La química orgánica se ocupa del estudio de los compuestos químicos que contienen carbono.')

INSERT INTO dbo.Documents (Doc) values('El alquimista buscaba la piedra filosofal, una sustancia legendaria capaz de transmutar los metales en oro.')

INSERT INTO dbo.Documents (Doc) values('La equinodermos es una clase de animales marinos que incluye a los erizos de mar y las estrellas de mar.')

INSERT INTO dbo.Documents (Doc) values('La fosforescencia es la propiedad que tienen algunos materiales de emitir luz después de haber sido expuestos a la luz.')

 

 

 

 

 

Let's add more rows here, running multiple times the following query.

 

 

 

 

 

insert into dbo.Documents (Doc) select Doc from dbo.Documents

 

 

 

 

 

Based on the language that we are using (in this case is Spanish), the first query that I would like to run will be exec sys.sp_help_fulltext_system_components 'all' when could see the details about wordbreaker and filter component type to see the versions that Azure SQL Database is using.

 

 

 

 

 

 

Additionally we could run other DMV like select * from sys.fulltext_system_stopwords where language_id=3082 to know the the stop words for Spanish language.

 

 

 

We know the stopwords and the version, but, let's execute the following query select * from sys.dm_fts_active_catalogs sys.dm_fts_active_catalogs (Transact-SQL) - SQL Server | Microsoft Learn to obtain an important information about the current status of FullText Catalog. The most important thing is that the catalog is ready status. This DMV allows you to list all of the active full-text catalogs that are currently undergoing some population activity on the Azure SQL Database.

 

 

 

Once we have this information, we are going to run the following TSQL to determine the FullText Index information in combination with FullText Catalog. In this query we could if the index is enabled, if the population is Auto or Manual, etc..

 

 

 

 

 

SELECT idx.is_enabled,

idx.change_tracking_state,

idx.change_tracking_state_desc,

idx.crawl_start_date,

idx.crawl_end_date,

cat.name,

idx.crawl_type,

idx.crawl_type_desc,

idx.has_crawl_completed

from sys.fulltext_indexes idx

INNER JOIN sys.fulltext_catalogs cat on idx.fulltext_catalog_id = cat.fulltext_catalog_id

 

 

 

 

 

It is very interesting for example, the column has_crawl_completed that report us if the update process has been finished and also when using the column crawl_end_date. So, right now, there is not activity.

 

 

 

 

 

 

I know how important is the number of Threads, vCores that our database needs to maintain the fulltext for this reason, running the query select * from sys.dm_fts_fdhosts - sys.dm_fts_fdhosts (Transact-SQL) - SQL Server | Microsoft Learn in my case will be 16 threads for 8 vCores. Also, as you know, FullText Engine is going to executed as a separate process of your server.

 

 

 

 

 

 

Finally, to know the amount of memory and different pools using for FullText Engine I executed the queries to see the memory buffer and memory pools of fulltext process. sys.dm_fts_memory_buffers (Transact-SQL) - SQL Server | Microsoft Learn and sys.dm_fts_memory_pools (Transact-SQL) - SQL Server | Microsoft Learn

 

 

 

 

 

select * from sys.dm_fts_memory_buffers

select * from sys.dm_fts_memory_pools

 

 

 

 

 

So, everything is ready, we know the details of Threads, memory and pools, no activity ongoing, let's get started adding data, for example, 983,040 rows and we review the current status of the process:

 

 

 

Running select * from sys.dm_fts_index_population sys.dm_fts_index_population (Transact-SQL) - SQL Server | Microsoft Learn we could see a very useful information, about the status of the population process that in my case, is running, in the background

 

 

 

 

 

 

We have two main batch process that is running the data population as you could see running select * from sys.dm_fts_outstanding_batches - sys.dm_fts_outstanding_batches (Transact-SQL) - SQL Server | Microsoft Learn a lot of details about the process, errors, etc.. This is useful for troubleshooting population issues and determining resource usage. Note that no currently outstanding batches, the DMV returns 0 rows.

 

 

 

Other topic that I saw after a huge amount of updates (insert, deletes and updates) is the number of fragments select * from sys.fulltext_index_fragments that I have for this FullText Index, you could see more information here. sys.fulltext_index_fragments (Transact-SQL) - SQL Server | Microsoft Learn If you have many of these fragments consider to reoganize the catalog to improve the reading data process, for example, ALTER FULLTEXT CATALOG ft REORGANIZE

 

 

 

Enjoy!

 

Continue reading...