{"id":8355,"date":"2026-03-31T16:46:16","date_gmt":"2026-03-31T19:46:16","guid":{"rendered":"https:\/\/powertuning.com.br\/?p=8355"},"modified":"2026-03-31T16:46:16","modified_gmt":"2026-03-31T19:46:16","slug":"pov-o-que-o-especialista-em-tuning-leonardo-albuquerque-descobriu-nos-bastidores-do-full-text-search-durante-testes-que-chegaram-a-microsoft","status":"publish","type":"post","link":"https:\/\/powertuning.com.br\/blog\/pov-o-que-o-especialista-em-tuning-leonardo-albuquerque-descobriu-nos-bastidores-do-full-text-search-durante-testes-que-chegaram-a-microsoft\/","title":{"rendered":"POV: o que o especialista em tuning, Leonardo Albuquerque, descobriu nos bastidores do Full-Text Search durante testes que chegaram \u00e0 Microsoft"},"content":{"rendered":"

Nem todo problema aparece nos dashboards, alguns ficam escondidos nos bastidores do banco de dados. Foi exatamente um desses casos que chamou a aten\u00e7\u00e3o do especialista em tuning Leonardo Albuquerque. A seguir, voc\u00ea confere o relato dele, em primeira pessoa, sobre os testes que revelaram um comportamento ainda n\u00e3o documentado no Full-Text Search.<\/em><\/strong><\/p>\n

…<\/p>\n

Estava eu l\u00e1, estudando e testando algumas funcionalidades da pesquisa de texto completo para uma a\u00e7\u00e3o interna aqui na Power, e me deparei com algumas quest\u00f5es.<\/p>\n

Para contextualizar, no banco Northwind, tenho um cat\u00e1logo de texto completo chamado Catalogoteste, e apenas um \u00edndice de texto completo criado para a tabela dbo.CustomersBig.<\/p>\n

Durante os testes de preenchimento do cat\u00e1logo para simular um ambiente de produ\u00e7\u00e3o onde h\u00e1 concorr\u00eancia com diversas transa\u00e7\u00f5es identifiquei um comportamento n\u00e3o relatado na documenta\u00e7\u00e3o oficial da Microsoft.<\/p>\n

REBUILD:<\/p>\n

Identifiquei que ao executarmos um REBUILD, ou iniciar o preenchimento de um cat\u00e1logo, podemos ver que o comando \u00e9 executado de imediato, por\u00e9m o que voc\u00ea n\u00e3o v\u00ea \u00e9 o que preocupa.<\/p>\n

\"\"<\/p>\n

O REBUILD refaz todo o cat\u00e1logo de texto completo \u201cpor tr\u00e1s dos panos\u201d, preenchendo-o completamente, at\u00e9 ent\u00e3o isso \u00e9 normal, mas o que acontece quando h\u00e1 opera\u00e7\u00f5es DML concorrentes na tabela alvo da reconstru\u00e7\u00e3o do cat\u00e1logo? E o que acontece quando h\u00e1 uma opera\u00e7\u00e3o DML com uma transa\u00e7\u00e3o muito longa aberta durante este preenchimento?<\/p>\n

Ao consultar o preenchimento do meu cat\u00e1logo, vi que estava em andamento com 57 mil linhas, e subindo:<\/p>\n

\"\"<\/p>\n

E, \u00e9 aqui que come\u00e7a a brincadeira, neste exemplo, simulei a execu\u00e7\u00e3o de um update na tabela presente no meu cat\u00e1logo, sess\u00e3o 61, e n\u00e3o confirmei a transa\u00e7\u00e3o, para simular uma transa\u00e7\u00e3o de dura\u00e7\u00e3o longa:<\/p>\n

\"\"<\/p>\n

Assim, que executei o comando, ao acompanhar o preenchimento do cat\u00e1logo novamente, notei que a quantidade de itens n\u00e3o aumentava enquanto a transa\u00e7\u00e3o estava aberta, e ao executar a sp_whoisactive, n\u00e3o tinha nenhum retorno, por\u00e9m ao executar um SELECT utilizando as dmvs internas, vi que havia v\u00e1rios bloqueios de sess\u00f5es em background no objeto (dbo.CustomersBig).<\/p>\n

 <\/p>\n\n\n\n
SELECT<\/strong> r1<\/strong>.<\/strong>session_id<\/strong>
\n,<\/strong>r1<\/strong>.<\/strong>blocking_session_id<\/strong>
\n,<\/strong>r1<\/strong>.<\/strong>wait_type<\/strong>
\n,<\/strong>r1<\/strong>.<\/strong>wait_resource<\/strong>
\n,<\/strong>r1<\/strong>.<\/strong>last_wait_type<\/strong>
\n,<\/strong>r1<\/strong>.<\/strong>command <\/strong>AS<\/strong> BlockedSessionCommand<\/strong>
\n,<\/strong>r2<\/strong>.<\/strong>command <\/strong>AS<\/strong> BlockingSessionCommand<\/strong>
\n,<\/strong>s1<\/strong>.<\/strong>login_name <\/strong>AS<\/strong> BlockedSessionLogin<\/strong>
\n,<\/strong>s2<\/strong>.<\/strong>login_name <\/strong>AS<\/strong> BlockingSessionLogin<\/strong>
\n,<\/strong>s1<\/strong>.<\/strong>host_name<\/strong> AS<\/strong> BlockedSessionHost<\/strong>
\n,<\/strong>s2<\/strong>.<\/strong>host_name<\/strong> AS<\/strong> BlockingSessionHost<\/strong>
\n,<\/strong>r1<\/strong>.<\/strong>STATUS<\/strong> AS<\/strong> BlockedSessionStatus<\/strong>
\n,<\/strong>r2<\/strong>.<\/strong>STATUS<\/strong> AS<\/strong> BlockingSessionStatus<\/strong>
\nFROM<\/strong> sys<\/strong>.<\/strong>dm_exec_requests <\/strong>AS<\/strong> r1<\/strong>
\nINNER JOIN<\/strong> sys<\/strong>.<\/strong>dm_exec_sessions <\/strong>AS<\/strong> s1 <\/strong>ON<\/strong> r1<\/strong>.<\/strong>session_id <\/strong>=<\/strong> s1<\/strong>.<\/strong>session_id<\/strong>
\nINNER JOIN<\/strong> sys<\/strong>.<\/strong>dm_exec_sessions <\/strong>AS<\/strong> s2 <\/strong>ON<\/strong> r1<\/strong>.<\/strong>blocking_session_id <\/strong>=<\/strong> s2<\/strong>.<\/strong>session_id<\/strong>
\nLEFT OUTER JOIN<\/strong> sys<\/strong>.<\/strong>dm_exec_requests <\/strong>AS<\/strong> r2 <\/strong>ON<\/strong> s2<\/strong>.<\/strong>session_id <\/strong>=<\/strong> r2<\/strong>.<\/strong>session_id<\/strong>
\nWHERE<\/strong> r1<\/strong>.<\/strong>blocking_session_id <\/strong><><\/strong> 0<\/strong>
\nAND<\/strong> r1<\/strong>.<\/strong>STATUS<\/strong> =<\/strong> ‘background’<\/strong>
\nORDER<\/strong> BY<\/strong> r1<\/strong>.<\/strong>wait_time <\/strong>DESC<\/strong>;<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Veja que o cat\u00e1logo est\u00e1 parado em 266000 linhas, e na terceira tabela temos a sess\u00e3o 61 bloqueando a 25, ocasionando uma cadeia de bloqueios nas sess\u00f5es em background.<\/p>\n

\"\"<\/p>\n

Ao executar o ROLLBACK na (SPID 61)<\/p>\n

\"\"<\/p>\n

O preenchimento do cat\u00e1logo \u00e9 imediatamente retomado<\/p>\n

\"\"<\/p>\n

Poucos segundos depois o preenchimento continua a todo vapor, at\u00e9 completar a popula\u00e7\u00e3o do cat\u00e1logo.<\/p>\n

\"\"<\/p>\n

REORGANIZE:<\/p>\n

A execu\u00e7\u00e3o deste comando tem um comportamento diferente do REBUILD, ao executar o REORGANIZE, a sess\u00e3o que emite o comando, mant\u00e9m sua execu\u00e7\u00e3o em andamento at\u00e9 a finaliza\u00e7\u00e3o da mesclagem dos fragmentos.<\/p>\n

No exemplo abaixo, executei um reorganize (SPID 63), e em seguida, executei imediatamente um update mantendo a transa\u00e7\u00e3o aberta (SPID 61), e podemos notar na SP_WHOISACTIVE, que o tipo de espera FT_MASTER_MERGE \u00e9 registrado para a sess\u00e3o que executa o REORGANIZE, por\u00e9m n\u00e3o nos mostra nenhum bloqueio entre as sess\u00f5es concorrentes:<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

Mas ao verificar os DMVs, podemos ver que h\u00e1 uma sess\u00e3o em background (SPID 25) sendo bloqueada pela sess\u00e3o 61, a sess\u00e3o 25 est\u00e1 emitindo o comando “FT MASTER MERGE”, internamente este comando realiza a mesclagem mestra dos fragmentos do \u00edndice de texto completo, unindo-os em um \u00fanico fragmento maior, veja que o comando de REORGANIZE foi iniciado na sess\u00e3o 63, mas n\u00e3o \u00e9 ela quem sofre o bloqueio, e sim a sess\u00e3o em BACKGROUND que est\u00e1 executando de fato a mesclagem mestra.<\/p>\n

\"\"<\/p>\n

Enquanto a transa\u00e7\u00e3o n\u00e3o for confirmada ou revertida na sess\u00e3o 61, o comando de mesclagem n\u00e3o ser\u00e1 conclu\u00eddo na sess\u00e3o 63.<\/p>\n

\"\"<\/p>\n

Veja que imediatamente ap\u00f3s eu aplicar o ROLLBACK na sess\u00e3o 61<\/p>\n

\"\"<\/p>\n

O comando na sess\u00e3o 63 finaliza<\/p>\n

\"\"<\/p>\n

Estes testes me levaram a fazer uma contribui\u00e7\u00e3o para a documenta\u00e7\u00e3o oficial da microsoft, visando orientar o administrador que ao executar as manuten\u00e7\u00f5es no \u00edndice de texto completo, se atentar quanto aos bloqueios que podem ocorrer de forma \u201coculta\u201d nas sess\u00f5es em background, o que podem impactar no tempo de execu\u00e7\u00e3o da manuten\u00e7\u00e3o.<\/strong><\/p>\n

PR no Git: https:\/\/github.com\/MicrosoftDocs\/sql-docs\/pull\/10288<\/a><\/p>\n

Trecho adicionado na documenta\u00e7\u00e3o: https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/statements\/alter-fulltext-catalog-transact-sql?view=sql-server-ver17#remarks<\/a><\/p>\n

Caso voc\u00ea queira simular em seu ambiente este cen\u00e1rio, segue abaixo um pequeno tutorial em forma de T-SQL, neste exemplo n\u00f3s utilizamos a base AdventureWorks2025<\/a> disponibilizada pela Microsoft.<\/p>\n\n\n\n
USE<\/strong> AdventureWorks2025<\/strong><\/p>\n

GO<\/strong><\/p>\n

\u00a0<\/strong><\/p>\n

–Cria um novo catalogo na AdventureWorks2025<\/strong><\/p>\n

CREATE<\/strong> FULLTEXT<\/strong> CATALOG<\/strong> FT_TESTE<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>WITH<\/strong> ACCENT_SENSITIVITY <\/strong>=<\/strong> OFF<\/strong><\/p>\n

\u00a0<\/strong><\/p>\n

–Cria um \u00edndice \u00fanico na coluna da tabela EmailAddress<\/strong><\/p>\n

CREATE<\/strong> UNIQUE<\/strong> INDEX<\/strong> CUI01 <\/strong>ON<\/strong> [Person]<\/strong>.<\/strong>EmailAddress<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>WITH<\/strong> (<\/strong>DATA_COMPRESSION <\/strong>=<\/strong> PAGE<\/strong>)<\/strong><\/p>\n

\u00a0<\/strong><\/p>\n

—<\/strong> CRIA UM \u00cdNDICE DE TEXTO COMPLETO PARA A COLUNA EmailAddress<\/strong><\/p>\n

CREATE<\/strong> FULLTEXT<\/strong> INDEX<\/strong> ON<\/strong> [Person]<\/strong>.<\/strong>[EmailAddress] <\/strong>(<\/strong>EmailAddress <\/strong>LANGUAGE<\/strong> 1033<\/strong>)<\/strong> KEY<\/strong> INDEX<\/strong> CUI01 <\/strong>ON<\/strong> FT_TESTE<\/strong>;<\/strong><\/p>\n

GO<\/strong><\/p>\n

\u00a0<\/strong><\/p>\n

—<\/strong> VERIFIQUE O STATUS DA POPULA\u00c7\u00c3O DO CAT\u00c1LOGO, ESPERE A POPULA\u00c7\u00c3O DO \u00cdNDICE TERMINAR, O STATUS DO CAT\u00c1LOGO DEVER\u00c1 ESTAR EM \u201cIDLE\u201d<\/strong><\/p>\n

DECLARE<\/strong> @CatalogName <\/strong>VARCHAR<\/strong>(<\/strong>MAX<\/strong>)<\/strong><\/p>\n

\u00a0<\/strong><\/p>\n

SET<\/strong> @CatalogName <\/strong>=<\/strong> ‘FT_TESTE’<\/strong><\/p>\n

\u00a0<\/strong><\/p>\n

SELECT<\/strong> FULLTEXTCATALOGPROPERTY<\/strong>(<\/strong>cat<\/strong>.<\/strong>name<\/strong>,<\/strong> ‘ItemCount’<\/strong>)<\/strong> AS<\/strong> [ItemCount]<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>,<\/strong>FULLTEXTCATALOGPROPERTY<\/strong>(<\/strong>cat<\/strong>.<\/strong>name<\/strong>,<\/strong> ‘MergeStatus’<\/strong>)<\/strong> AS<\/strong> [MergeStatus]<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>,<\/strong>FULLTEXTCATALOGPROPERTY<\/strong>(<\/strong>cat<\/strong>.<\/strong>name<\/strong>,<\/strong> ‘PopulateCompletionAge’<\/strong>)<\/strong> AS<\/strong> [PopulateCompletionAge]<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>,<\/strong>DATEADD<\/strong>(<\/strong>ss<\/strong>,<\/strong> FULLTEXTCATALOGPROPERTY<\/strong>(<\/strong>cat<\/strong>.<\/strong>name<\/strong>,<\/strong> ‘PopulateCompletionAge’<\/strong>),<\/strong> ‘1\/1\/1990’<\/strong>)<\/strong> AS<\/strong> LastPopulated<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>,(<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>SELECT<\/strong> CASE<\/strong> FULLTEXTCATALOGPROPERTY<\/strong>(<\/strong>cat<\/strong>.<\/strong>name<\/strong>,<\/strong> ‘PopulateStatus’<\/strong>)<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>WHEN<\/strong> 0<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>THEN<\/strong> ‘Idle’<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>WHEN<\/strong> 1<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>THEN<\/strong> ‘Full Population In Progress’<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>WHEN<\/strong> 2<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>THEN<\/strong> ‘Paused’<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>WHEN<\/strong> 3<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>THEN<\/strong> ‘Throttled’<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>WHEN<\/strong> 4<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>THEN<\/strong> ‘Recovering’<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>WHEN<\/strong> 5<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>THEN<\/strong> ‘Shutdown’<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>WHEN<\/strong> 6<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>THEN<\/strong> ‘Incremental Population In Progress’<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>WHEN<\/strong> 7<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>THEN<\/strong> ‘Building Index’<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>WHEN<\/strong> 8<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>THEN<\/strong> ‘Disk Full. Paused’<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>WHEN<\/strong> 9<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>THEN<\/strong> ‘Change Tracking’<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>END<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>)<\/strong> AS<\/strong> PopulateStatus<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>,<\/strong>FULLTEXTCATALOGPROPERTY<\/strong>(<\/strong>cat<\/strong>.<\/strong>name<\/strong>,<\/strong> ‘ImportStatus’<\/strong>)<\/strong> AS<\/strong> [ImportStatus]<\/strong><\/p>\n

FROM<\/strong> sys<\/strong>.<\/strong>fulltext_catalogs <\/strong>AS<\/strong> cat<\/strong><\/p>\n

WHERE<\/strong> cat<\/strong>.<\/strong>name <\/strong>=<\/strong> @CatalogName<\/strong><\/p>\n

GO<\/strong><\/p>\n

\u00a0<\/strong><\/p>\n

–EXECUTE O COMANDO REBUILD\/REORGANIZE NO CAT\u00c1LOGO<\/strong><\/p>\n

ALTER<\/strong> FULLTEXT<\/strong> CATALOG<\/strong> FT_TESTE REORGANIZE<\/strong>;<\/strong><\/p>\n

\u00a0<\/strong><\/p>\n

—<\/strong> EXECUTE ESTA ATUALIZA\u00c7\u00c3O EM OUTRA SESS\u00c3O IMEDIATAMENTE AP\u00d3S EMITIR O REORGANIZE, ANTES DE TERMINAR A EXECU\u00c7\u00c3O DELE, E MANTENHA A TRANSA\u00c7\u00c3O ABERTA<\/strong><\/p>\n

\u00a0<\/strong><\/p>\n

BEGIN TRANSACTION<\/strong><\/p>\n

\u00a0<\/strong><\/p>\n

UPDATE<\/strong> [Person]<\/strong>.<\/strong>[EmailAddress]<\/strong><\/p>\n

SET<\/strong> EmailAddress <\/strong>=<\/strong> ‘ken0@adventure-works.com’<\/strong><\/p>\n

WHERE<\/strong> EmailAddressID <\/strong><<\/strong> 10 <\/strong>\/<\/strong><\/p>\n

\u00a0<\/strong><\/p>\n

—<\/strong> ROLLBACK <\/strong><\/p>\n

—<\/strong> VERIFIQUE AS SESS\u00d5ES EM BACKGROUND PARA DETECTAR OS BLOQUEIOS, LCK_M_*<\/strong><\/p>\n

SELECT<\/strong> r1<\/strong>.<\/strong>session_id<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>,<\/strong>r1<\/strong>.<\/strong>blocking_session_id<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>,<\/strong>r1<\/strong>.<\/strong>wait_type<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>,<\/strong>r1<\/strong>.<\/strong>wait_resource<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>,<\/strong>r1<\/strong>.<\/strong>last_wait_type<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>,<\/strong>r1<\/strong>.<\/strong>command <\/strong>AS<\/strong> BlockedSessionCommand<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>,<\/strong>r2<\/strong>.<\/strong>command <\/strong>AS<\/strong> BlockingSessionCommand<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>,<\/strong>s1<\/strong>.<\/strong>login_name <\/strong>AS<\/strong> BlockedSessionLogin<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>,<\/strong>s2<\/strong>.<\/strong>login_name <\/strong>AS<\/strong> BlockingSessionLogin<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>,<\/strong>s1<\/strong>.<\/strong>host_name<\/strong> AS<\/strong> BlockedSessionHost<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>,<\/strong>s2<\/strong>.<\/strong>host_name<\/strong> AS<\/strong> BlockingSessionHost<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>,<\/strong>r1<\/strong>.<\/strong>STATUS<\/strong> AS<\/strong> BlockedSessionStatus<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>,<\/strong>r2<\/strong>.<\/strong>STATUS<\/strong> AS<\/strong> BlockingSessionStatus<\/strong><\/p>\n

FROM<\/strong> sys<\/strong>.<\/strong>dm_exec_requests r1<\/strong><\/p>\n

INNER JOIN<\/strong> sys<\/strong>.<\/strong>dm_exec_sessions s1 <\/strong>ON<\/strong> r1<\/strong>.<\/strong>session_id <\/strong>=<\/strong> s1<\/strong>.<\/strong>session_id<\/strong><\/p>\n

INNER JOIN<\/strong> sys<\/strong>.<\/strong>dm_exec_sessions s2 <\/strong>ON<\/strong> r1<\/strong>.<\/strong>blocking_session_id <\/strong>=<\/strong> s2<\/strong>.<\/strong>session_id<\/strong><\/p>\n

LEFT JOIN<\/strong> sys<\/strong>.<\/strong>dm_exec_requests r2 <\/strong>ON<\/strong> s2<\/strong>.<\/strong>session_id <\/strong>=<\/strong> r2<\/strong>.<\/strong>session_id<\/strong><\/p>\n

WHERE<\/strong> r1<\/strong>.<\/strong>blocking_session_id <\/strong><><\/strong> 0<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>AND<\/strong> r1<\/strong>.<\/strong>STATUS<\/strong> =<\/strong> ‘background’<\/strong><\/p>\n

ORDER<\/strong> BY<\/strong> r1<\/strong>.<\/strong>wait_time <\/strong>DESC<\/strong>;<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>–EXECUTE O COMANDO ROLLBACK OU COMMIT NA SESS\u00c3O QUE EXECUTA A ATUALIZA\u00c7\u00c3O<\/strong><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong>–E VOC\u00ca VER\u00c1 QUE A SESS\u00c3O EM EXECU\u00c7\u00c3O DA REORGANIZA\u00c7\u00c3O SER\u00c1 LIBERADA IMEDIATAMENTE AP\u00d3S
\n<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\"\" Siga o Leonardo Albuquerque no Linkedin<\/em><\/a>
\n\"\" Siga a Power Tuning no Linkedin<\/em><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Nem todo problema aparece nos dashboards, alguns ficam escondidos nos bastidores do banco de dados. Foi exatamente um desses casos que chamou a aten\u00e7\u00e3o do especialista em tuning Leonardo Albuquerque. A seguir, voc\u00ea confere o relato dele, em primeira pessoa, sobre os testes que revelaram um comportamento ainda n\u00e3o documentado no Full-Text Search. … Estava…
Leia mais<\/a><\/p>\n","protected":false},"author":32,"featured_media":8393,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"content-type":"","_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[176,59,63],"tags":[],"class_list":["post-8355","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-banco-de-dados","category-tuning","category-sql-server"],"jetpack_featured_media_url":"https:\/\/powertuning.com.br\/blog\/wp-content\/uploads\/2026\/03\/Capa_Blog-Leonardo_microsoft.png","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/posts\/8355","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/users\/32"}],"replies":[{"embeddable":true,"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/comments?post=8355"}],"version-history":[{"count":2,"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/posts\/8355\/revisions"}],"predecessor-version":[{"id":8394,"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/posts\/8355\/revisions\/8394"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/media\/8393"}],"wp:attachment":[{"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/media?parent=8355"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/categories?post=8355"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/tags?post=8355"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}