POV: o que o especialista em tuning, Leonardo Albuquerque, descobriu nos bastidores do Full-Text Search durante testes que chegaram à Microsoft
Nem todo problema aparece nos dashboards, alguns ficam escondidos nos bastidores do banco de dados. Foi exatamente um desses casos que chamou a atenção do especialista em tuning Leonardo Albuquerque. A seguir, você confere o relato dele, em primeira pessoa, sobre os testes que revelaram um comportamento ainda não documentado no Full-Text Search.
…
Estava eu lá, estudando e testando algumas funcionalidades da pesquisa de texto completo para uma ação interna aqui na Power, e me deparei com algumas questões.
Para contextualizar, no banco Northwind, tenho um catálogo de texto completo chamado Catalogoteste, e apenas um índice de texto completo criado para a tabela dbo.CustomersBig.
Durante os testes de preenchimento do catálogo para simular um ambiente de produção onde há concorrência com diversas transações identifiquei um comportamento não relatado na documentação oficial da Microsoft.
REBUILD:
Identifiquei que ao executarmos um REBUILD, ou iniciar o preenchimento de um catálogo, podemos ver que o comando é executado de imediato, porém o que você não vê é o que preocupa.
O REBUILD refaz todo o catálogo de texto completo “por trás dos panos”, preenchendo-o completamente, até então isso é normal, mas o que acontece quando há operações DML concorrentes na tabela alvo da reconstrução do catálogo? E o que acontece quando há uma operação DML com uma transação muito longa aberta durante este preenchimento?
Ao consultar o preenchimento do meu catálogo, vi que estava em andamento com 57 mil linhas, e subindo:
E, é aqui que começa a brincadeira, neste exemplo, simulei a execução de um update na tabela presente no meu catálogo, sessão 61, e não confirmei a transação, para simular uma transação de duração longa:
Assim, que executei o comando, ao acompanhar o preenchimento do catálogo novamente, notei que a quantidade de itens não aumentava enquanto a transação estava aberta, e ao executar a sp_whoisactive, não tinha nenhum retorno, porém ao executar um SELECT utilizando as dmvs internas, vi que havia vários bloqueios de sessões em background no objeto (dbo.CustomersBig).
| SELECT r1.session_id ,r1.blocking_session_id ,r1.wait_type ,r1.wait_resource ,r1.last_wait_type ,r1.command AS BlockedSessionCommand ,r2.command AS BlockingSessionCommand ,s1.login_name AS BlockedSessionLogin ,s2.login_name AS BlockingSessionLogin ,s1.host_name AS BlockedSessionHost ,s2.host_name AS BlockingSessionHost ,r1.STATUS AS BlockedSessionStatus ,r2.STATUS AS BlockingSessionStatus FROM sys.dm_exec_requests AS r1 INNER JOIN sys.dm_exec_sessions AS s1 ON r1.session_id = s1.session_id INNER JOIN sys.dm_exec_sessions AS s2 ON r1.blocking_session_id = s2.session_id LEFT OUTER JOIN sys.dm_exec_requests AS r2 ON s2.session_id = r2.session_id WHERE r1.blocking_session_id <> 0 AND r1.STATUS = ‘background’ ORDER BY r1.wait_time DESC; |
Veja que o catálogo está parado em 266000 linhas, e na terceira tabela temos a sessão 61 bloqueando a 25, ocasionando uma cadeia de bloqueios nas sessões em background.
Ao executar o ROLLBACK na (SPID 61)
O preenchimento do catálogo é imediatamente retomado
Poucos segundos depois o preenchimento continua a todo vapor, até completar a população do catálogo.
REORGANIZE:
A execução deste comando tem um comportamento diferente do REBUILD, ao executar o REORGANIZE, a sessão que emite o comando, mantém sua execução em andamento até a finalização da mesclagem dos fragmentos.
No exemplo abaixo, executei um reorganize (SPID 63), e em seguida, executei imediatamente um update mantendo a transação aberta (SPID 61), e podemos notar na SP_WHOISACTIVE, que o tipo de espera FT_MASTER_MERGE é registrado para a sessão que executa o REORGANIZE, porém não nos mostra nenhum bloqueio entre as sessões concorrentes:
Mas ao verificar os DMVs, podemos ver que há uma sessão em background (SPID 25) sendo bloqueada pela sessão 61, a sessão 25 está emitindo o comando “FT MASTER MERGE”, internamente este comando realiza a mesclagem mestra dos fragmentos do índice de texto completo, unindo-os em um único fragmento maior, veja que o comando de REORGANIZE foi iniciado na sessão 63, mas não é ela quem sofre o bloqueio, e sim a sessão em BACKGROUND que está executando de fato a mesclagem mestra.
Enquanto a transação não for confirmada ou revertida na sessão 61, o comando de mesclagem não será concluído na sessão 63.
Veja que imediatamente após eu aplicar o ROLLBACK na sessão 61
O comando na sessão 63 finaliza
Estes testes me levaram a fazer uma contribuição para a documentação oficial da microsoft, visando orientar o administrador que ao executar as manutenções no índice de texto completo, se atentar quanto aos bloqueios que podem ocorrer de forma “oculta” nas sessões em background, o que podem impactar no tempo de execução da manutenção.
PR no Git: https://github.com/MicrosoftDocs/sql-docs/pull/10288
Trecho adicionado na documentação: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-fulltext-catalog-transact-sql?view=sql-server-ver17#remarks
Caso você queira simular em seu ambiente este cenário, segue abaixo um pequeno tutorial em forma de T-SQL, neste exemplo nós utilizamos a base AdventureWorks2025 disponibilizada pela Microsoft.
| USE AdventureWorks2025
GO
–Cria um novo catalogo na AdventureWorks2025 CREATE FULLTEXT CATALOG FT_TESTE WITH ACCENT_SENSITIVITY = OFF
–Cria um índice único na coluna da tabela EmailAddress CREATE UNIQUE INDEX CUI01 ON [Person].EmailAddress WITH (DATA_COMPRESSION = PAGE)
— CRIA UM ÍNDICE DE TEXTO COMPLETO PARA A COLUNA EmailAddress CREATE FULLTEXT INDEX ON [Person].[EmailAddress] (EmailAddress LANGUAGE 1033) KEY INDEX CUI01 ON FT_TESTE; GO
— VERIFIQUE O STATUS DA POPULAÇÃO DO CATÁLOGO, ESPERE A POPULAÇÃO DO ÍNDICE TERMINAR, O STATUS DO CATÁLOGO DEVERÁ ESTAR EM “IDLE” DECLARE @CatalogName VARCHAR(MAX)
SET @CatalogName = ‘FT_TESTE’
SELECT FULLTEXTCATALOGPROPERTY(cat.name, ‘ItemCount’) AS [ItemCount] ,FULLTEXTCATALOGPROPERTY(cat.name, ‘MergeStatus’) AS [MergeStatus] ,FULLTEXTCATALOGPROPERTY(cat.name, ‘PopulateCompletionAge’) AS [PopulateCompletionAge] ,DATEADD(ss, FULLTEXTCATALOGPROPERTY(cat.name, ‘PopulateCompletionAge’), ‘1/1/1990’) AS LastPopulated ,( SELECT CASE FULLTEXTCATALOGPROPERTY(cat.name, ‘PopulateStatus’) WHEN 0 THEN ‘Idle’ WHEN 1 THEN ‘Full Population In Progress’ WHEN 2 THEN ‘Paused’ WHEN 3 THEN ‘Throttled’ WHEN 4 THEN ‘Recovering’ WHEN 5 THEN ‘Shutdown’ WHEN 6 THEN ‘Incremental Population In Progress’ WHEN 7 THEN ‘Building Index’ WHEN 8 THEN ‘Disk Full. Paused’ WHEN 9 THEN ‘Change Tracking’ END ) AS PopulateStatus ,FULLTEXTCATALOGPROPERTY(cat.name, ‘ImportStatus’) AS [ImportStatus] FROM sys.fulltext_catalogs AS cat WHERE cat.name = @CatalogName GO
–EXECUTE O COMANDO REBUILD/REORGANIZE NO CATÁLOGO ALTER FULLTEXT CATALOG FT_TESTE REORGANIZE;
— EXECUTE ESTA ATUALIZAÇÃO EM OUTRA SESSÃO IMEDIATAMENTE APÓS EMITIR O REORGANIZE, ANTES DE TERMINAR A EXECUÇÃO DELE, E MANTENHA A TRANSAÇÃO ABERTA
BEGIN TRANSACTION
UPDATE [Person].[EmailAddress] SET EmailAddress = ‘ken0@adventure-works.com’ WHERE EmailAddressID < 10 /
— ROLLBACK — VERIFIQUE AS SESSÕES EM BACKGROUND PARA DETECTAR OS BLOQUEIOS, LCK_M_* SELECT r1.session_id ,r1.blocking_session_id ,r1.wait_type ,r1.wait_resource ,r1.last_wait_type ,r1.command AS BlockedSessionCommand ,r2.command AS BlockingSessionCommand ,s1.login_name AS BlockedSessionLogin ,s2.login_name AS BlockingSessionLogin ,s1.host_name AS BlockedSessionHost ,s2.host_name AS BlockingSessionHost ,r1.STATUS AS BlockedSessionStatus ,r2.STATUS AS BlockingSessionStatus FROM sys.dm_exec_requests r1 INNER JOIN sys.dm_exec_sessions s1 ON r1.session_id = s1.session_id INNER JOIN sys.dm_exec_sessions s2 ON r1.blocking_session_id = s2.session_id LEFT JOIN sys.dm_exec_requests r2 ON s2.session_id = r2.session_id WHERE r1.blocking_session_id <> 0 AND r1.STATUS = ‘background’ ORDER BY r1.wait_time DESC; –EXECUTE O COMANDO ROLLBACK OU COMMIT NA SESSÃO QUE EXECUTA A ATUALIZAÇÃO –E VOCÊ VERÁ QUE A SESSÃO EM EXECUÇÃO DA REORGANIZAÇÃO SERÁ LIBERADA IMEDIATAMENTE APÓS |
Siga o Leonardo Albuquerque no Linkedin
Siga a Power Tuning no Linkedin
