Geoff Patterson's questions

A consulta a seguir executa uma janela SUMem uma tabela columnstore com 1500 total rows, cada uma com o valor 0 ou 1, e transborda o INTtipo de dados. Por que isso está acontecendo?

SELECT a, p, s, v, m, n,
    SUM(CASE WHEN n IS NULL THEN 0 ELSE 1 END)
        OVER (PARTITION BY s, v, a ORDER BY p) AS lastNonNullPartition
FROM (
    SELECT a, p, s, v, m, n,
        RANK() OVER (PARTITION BY v, s, a, p ORDER BY m) AS rank
    FROM #t /* A columnstore table with 1,500 rows */
)  x
WHERE x.rank = 1
--Msg 8115, Level 16, State 2, Line 1521
--Arithmetic overflow error converting expression to data type int.

Roteiro completo

Veja este arquivo para um script de reprodução totalmente contido.

Plano de consulta

Aqui está um plano de consulta estimado anotado ( XML completo em Colar o Plano ).

Consultas semelhantes executadas com sucesso

Se alguma das seguintes modificações for feita, o erro não ocorrerá:

• Use o sinalizador de rastreamento 8649para preferir um plano paralelo, independentemente do limite de custo para paralelismo
• Use o sinalizador de rastreamento 9453para desativar o modo em lote
• Use a COUNTfunção de agregação em vez da SUMfunção
• Remova o WHERE x.rank = 1predicado

Por exemplo, esta consulta é executada com sucesso:

SELECT a, p, s, v, m, n,
    SUM(CASE WHEN n IS NULL THEN 0 ELSE 1 END)
        OVER (PARTITION BY s, v, a ORDER BY p) AS lastNonNullPartition
FROM (
    SELECT a, p, s, v, m, n,
        RANK() OVER (PARTITION BY v, s, a, p ORDER BY m) AS rank
    FROM #t /* A columnstore table with 1,500 rows */
)  x
WHERE x.rank = 1
OPTION (QUERYTRACEON 9453/* Disable batch mode */) 

Considere o seguinte plano de consulta no SQL Server 2014:

No plano de consulta, uma junção automática ar.fId = ar.fIdproduz uma estimativa de 1 linha. No entanto, esta é uma estimativa logicamente inconsistente: artem 20,608linhas e apenas um valor distinto de fId(refletido com precisão nas estatísticas). Portanto, essa junção produz o produto cruzado completo de linhas ( ~424MMlinhas), fazendo com que a consulta seja executada por várias horas.

Estou tendo dificuldade em entender por que o SQL Server apresentaria uma estimativa que pode ser facilmente comprovada como inconsistente com as estatísticas. Alguma ideia?

Investigação inicial e detalhes adicionais

Com base na resposta de Paul aqui , parece que as heurísticas SQL 2012 e SQL 2014 para estimar a cardinalidade da junção devem lidar facilmente com uma situação em que dois histogramas idênticos precisam ser comparados.

Comecei com a saída do sinalizador de rastreamento 2363, mas não consegui entender isso facilmente. O trecho a seguir significa que o SQL Server está comparando histogramas para fIde bIdpara estimar a seletividade de uma junção que usa apenas fId? Se assim for, isso obviamente não seria correto. Ou estou interpretando mal a saída do sinalizador de rastreamento?

Plan for computation:
  CSelCalcExpressionComparedToExpression( QCOL: [ar].fId x_cmpEq QCOL: [ar].fId )
Loaded histogram for column QCOL: [ar].bId from stats with id 3
Loaded histogram for column QCOL: [ar].fId from stats with id 1
Selectivity: 0

Observe que criei várias soluções alternativas, que são incluídas no script de reprodução completo e reduzem essa consulta para milissegundos. Esta pergunta é focada em entender o comportamento, como evitá-lo em consultas futuras e determinar se é um bug que deve ser registrado na Microsoft.

Aqui está um script de reprodução completo , aqui está a saída completa do sinalizador de rastreamento 2363 e aqui estão as definições de consulta e tabela, caso você queira examiná-las rapidamente sem abrir o script completo:

WITH cte AS (
    SELECT ar.fId, 
        ar.bId,
        MIN(CONVERT(INT, ar.isT)) AS isT,
        MAX(CONVERT(INT, tcr.isS)) AS isS
    FROM  #SQL2014MinMaxAggregateCardinalityBug_ar ar 
    LEFT OUTER JOIN #SQL2014MinMaxAggregateCardinalityBug_tcr tcr
        ON tcr.rId = 508
        AND tcr.fId = ar.fId
        AND tcr.bId = ar.bId
    GROUP BY ar.fId, ar.bId
)
SELECT s.fId, s.bId, s.isS, t.isS
FROM cte s 
JOIN cte t 
    ON t.fId = s.fId 
    AND t.isT = 1

CREATE TABLE #SQL2014MinMaxAggregateCardinalityBug_ar (
    fId INT NOT NULL,
    bId INT NOT NULL,
    isT BIT NOT NULL
    PRIMARY KEY (fId, bId)
)

CREATE TABLE #SQL2014MinMaxAggregateCardinalityBug_tcr (
    rId INT NOT NULL,
    fId INT NOT NULL,
    bId INT NOT NULL,
    isS BIT NOT NULL
    PRIMARY KEY (rId, fId, bId, isS)
)

No trecho de plano de consulta a seguir, parece óbvio que a estimativa de linha para o Concatenationoperador deve ser ~4.3 billion rows, ou a soma das estimativas de linha para suas duas entradas.

No entanto, uma estimativa de ~238 million rowsé produzida, levando a uma estratégia Sort/ abaixo do ideal Stream Aggregateque derrama centenas de GB de dados em tempdb. Uma estimativa logicamente consistente neste caso teria produzido um Hash Aggregate, removido o derramamento e melhorado drasticamente o desempenho da consulta.

Isso é um bug no SQL Server 2014? Existem circunstâncias válidas nas quais uma estimativa menor do que as entradas poderia ser razoável? Que soluções alternativas podem estar disponíveis?

Aqui está o plano de consulta completo (anônimo). Não tenho acesso de administrador de sistema a este servidor para fornecer saídas QUERYTRACEON 2363ou sinalizadores de rastreamento semelhantes, mas posso obter essas saídas de um administrador se forem úteis.

O banco de dados está no nível de compatibilidade 120 e, portanto, está usando o novo Estimador de cardinalidade do SQL Server 2014.

As estatísticas são atualizadas manualmente toda vez que os dados são carregados. Dado o volume de dados, estamos usando a taxa de amostragem padrão. É possível que uma taxa de amostragem mais alta (ou FULLSCAN) possa ter um impacto.

Temos vários bancos de dados nos quais um grande número de tabelas é criado e descartado. Pelo que podemos dizer, o SQL Server não realiza nenhuma manutenção interna nas tabelas base do sistema , o que significa que elas podem se tornar muito fragmentadas com o tempo e aumentar de tamanho. Isso coloca pressão desnecessária no pool de buffers e também afeta negativamente o desempenho de operações, como calcular o tamanho de todas as tabelas em um banco de dados.

Alguém tem sugestões para minimizar a fragmentação nessas tabelas internas principais? Uma solução óbvia seria evitar a criação de tantas tabelas (ou criar todas as tabelas transitórias no tempdb), mas para o propósito desta questão, digamos que o aplicativo não tenha essa flexibilidade.

Editar: pesquisas adicionais mostram esta pergunta sem resposta , que parece intimamente relacionada e indica que alguma forma de manutenção manual ALTER INDEX...REORGANIZEpode ser uma opção.

Pesquisa inicial

Os metadados sobre essas tabelas podem ser visualizados em sys.dm_db_partition_stats:

-- The system base table that contains one row for every column in the system
SELECT row_count,
    (reserved_page_count * 8 * 1024.0) / row_count AS bytes_per_row, 
    reserved_page_count/128. AS space_mb
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('sys.syscolpars')
    AND index_id = 1
-- row_count:       15,600,859
-- bytes_per_row:   278.08
-- space_mb:        4,136

No entanto, sys.dm_db_index_physical_statsnão parece oferecer suporte à visualização da fragmentação dessas tabelas:

-- No fragmentation data is returned by sys.dm_db_index_physical_stats
SELECT *
FROM sys.dm_db_index_physical_stats(
    DB_ID(),
    OBJECT_ID('sys.syscolpars'),
    NULL,
    NULL,
    'DETAILED'
)

Os scripts de Ola Hallengren também contêm um parâmetro para considerar a desfragmentação de is_ms_shipped = 1objetos, mas o procedimento ignora silenciosamente as tabelas básicas do sistema mesmo com esse parâmetro ativado. Ola esclareceu que esse é o comportamento esperado; apenas tabelas de usuário (não tabelas de sistema) que são ms_shipped (por exemplo msdb.dbo.backupset, ) são consideradas.

-- Returns code 0 (successful), but does not do any work for system base tables.
-- Instead of the expected commands to update statistics and reorganize indexes,
-- no commands are generated. The script seems to assume the target tables will
-- appear in sys.tables, but this does not appear to be a valid assumption for
-- system tables like sys.sysrowsets or sys.syscolpars.
DECLARE @result int;
EXEC @result = IndexOptimize @Databases = 'Test',
        @FragmentationLow = 'INDEX_REORGANIZE',
        @FragmentationMedium = 'INDEX_REORGANIZE',
        @FragmentationHigh = 'INDEX_REORGANIZE',
        @PageCountLevel = 0,
        @UpdateStatistics = 'ALL',
        @Indexes = '%Test.sys.sysrowsets.%',
        -- Proc works properly if targeting a non-system table instead
        --@Indexes = '%Test.dbo.Numbers.%',
        @MSShippedObjects = 'Y',
        @Execute = 'N';
PRINT(@result);

Informações adicionais solicitadas

Eu usei uma adaptação da consulta de Aaron abaixo do uso do buffer pool da tabela de sistema de inspeção, e descobri que existem dezenas de GB de tabelas do sistema no buffer pool para apenas um banco de dados, com ~ 80% desse espaço sendo espaço livre em alguns casos .

-- Compute buffer pool usage by system table
SELECT OBJECT_NAME(p.object_id),
    COUNT(b.page_id) pages,
    SUM(b.free_space_in_bytes/8192.0) free_pages
FROM sys.dm_os_buffer_descriptors b
JOIN sys.allocation_units a
    ON a.allocation_unit_id = b.allocation_unit_id
JOIN sys.partitions p
    ON p.partition_id = a.container_id
    AND p.object_id < 1000 -- A loose proxy for system tables
WHERE b.database_id = DB_ID()
GROUP BY p.object_id
ORDER BY pages DESC

Para um COUNT(DISTINCT)que tem aproximadamente 1 bilhão de valores distintos, estou obtendo um plano de consulta com uma agregação de hash estimada em apenas aproximadamente 3 milhões de linhas.

Por que isso está acontecendo? O SQL Server 2012 produz uma boa estimativa, então isso é um bug no SQL Server 2014 que devo relatar no Connect?

A consulta e estimativa ruim

-- Actual rows: 1,011,719,166
-- SQL 2012 estimated rows: 1,079,130,000 (106% of actual)
-- SQL 2014 estimated rows: 2,980,240 (0.29% of actual)
SELECT COUNT(DISTINCT factCol5)
FROM BigFactTable
OPTION (RECOMPILE, QUERYTRACEON 9481) -- Include this line to use SQL 2012 CE

-- Stats for the factCol5 column show that there are ~1 billion distinct values
-- This is a good estimate, and it appears to be what the SQL 2012 CE uses
DBCC SHOW_STATISTICS (BigFactTable, _WA_Sys_00000005_24927208)
--All density   Average Length  Columns
--9.266754E-10  8               factCol5
SELECT 1 / 9.266754E-10
-- 1079126520.46229

O plano de consulta

roteiro completo

Aqui está uma reprodução completa da situação usando apenas um banco de dados de estatísticas .

O que eu tentei até agora

Pesquisei nas estatísticas da coluna relevante e descobri que o vetor de densidade mostra aproximadamente 1,1 bilhão de valores distintos. O SQL Server 2012 usa essa estimativa e produz um bom plano. O SQL Server 2014, surpreendentemente, parece ignorar a estimativa muito precisa fornecida pelas estatísticas e, em vez disso, usa uma estimativa muito menor. Isso produz um plano muito mais lento que não reserva memória suficiente e derrama para tempdb.

Eu tentei trace flag 4199, mas isso não corrigiu a situação. Por fim, tentei me aprofundar nas informações do otimizador por meio de uma combinação de sinalizadores de rastreamento (3604, 8606, 8607, 8608, 8612), conforme demonstrado na segunda metade deste artigo . No entanto, não consegui ver nenhuma informação explicando a estimativa incorreta até que ela aparecesse na árvore de saída final.

Problema de conexão

Com base nas respostas a esta pergunta, também registrei isso como um problema no Connect

Eu tenho uma tabela com uma coluna de string e um predicado que verifica se há linhas com um determinado comprimento. No SQL Server 2014, vejo uma estimativa de 1 linha, independentemente do comprimento que estou verificando. Isso está gerando planos muito ruins porque, na verdade, existem milhares ou até milhões de linhas e o SQL Server está optando por colocar essa tabela no lado externo de um loop aninhado.

Existe uma explicação para a estimativa de cardinalidade de 1,0003 para o SQL Server 2014 enquanto o SQL Server 2012 estima 31.622 linhas? Existe uma boa solução alternativa?

Aqui está uma pequena reprodução do problema:

-- Create a table with 1MM rows of dummy data
CREATE TABLE #customers (cust_nbr VARCHAR(10) NOT NULL)
GO

INSERT INTO #customers WITH (TABLOCK) (cust_nbr)
    SELECT TOP 1000000 
        CONVERT(VARCHAR(10),
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS cust_nbr
    FROM master..spt_values v1
    CROSS JOIN master..spt_values v2
GO

-- Looking for string of a certain length.
-- While both CEs yield fairly poor estimates, the 2012 CE is much
-- more conservative (higher estimate) and therefore much more likely
-- to yield an okay plan rather than a drastically understimated loop join.
-- 2012: 31,622 rows estimated, 900K rows actual
-- 2014: 1 row estimated, 900K rows actual
SELECT COUNT(*)
FROM #customers
WHERE LEN(cust_nbr) = 6
OPTION (QUERYTRACEON 9481) -- Optionally, use 2012 CE
GO

Aqui está um script mais completo mostrando testes adicionais

Também li o whitepaper sobre o Estimador de cardinalidade do SQL Server 2014 , mas não encontrei nada que esclarecesse a situação.

Tenho uma query que roda em 800 milissegundos no SQL Server 2012 e leva cerca de 170 segundos no SQL Server 2014 . Acho que reduzi isso a uma estimativa de cardinalidade ruim para o Row Count Spooloperador. Eu li um pouco sobre operadores de spool (por exemplo, here e here ), mas ainda estou tendo problemas para entender algumas coisas:

• Por que essa consulta precisa de um Row Count Spooloperador? Não acho que seja necessário para correção, então qual otimização específica ele está tentando fornecer?
• Por que o SQL Server estima que a junção ao Row Count Spooloperador remove todas as linhas?
• Isso é um bug no SQL Server 2014? Se sim, vou arquivar no Connect. Mas eu gostaria de uma compreensão mais profunda primeiro.

Observação: posso reescrever a consulta como a LEFT JOINou adicionar índices às tabelas para obter um desempenho aceitável no SQL Server 2012 e no SQL Server 2014. Portanto, esta questão é mais sobre entender esta consulta específica e planejar em profundidade e menos sobre como formular a consulta de maneira diferente.

A consulta lenta

Veja este Pastebin para um script de teste completo. Aqui está a consulta de teste específica que estou analisando:

-- Prune any existing customers from the set of potential new customers
-- This query is much slower than expected in SQL Server 2014 
SELECT *
FROM #potentialNewCustomers -- 10K rows
WHERE cust_nbr NOT IN (
    SELECT cust_nbr
    FROM #existingCustomers -- 1MM rows
)

SQL Server 2014: o plano de consulta estimado

O SQL Server acredita que o Left Anti Semi Joinpara o Row Count Spoolfiltrará as 10.000 linhas para 1 linha. Por esse motivo, ele seleciona um LOOP JOINpara a junção subsequente a #existingCustomers.

SQL Server 2014: o plano de consulta real

Como esperado (por todos menos o SQL Server!), o Row Count Spoolnão removeu nenhuma linha. Portanto, estamos fazendo um loop de 10.000 vezes quando o SQL Server esperava fazer um loop apenas uma vez.

SQL Server 2012: o plano de consulta estimado

Ao usar o SQL Server 2012 (ou OPTION (QUERYTRACEON 9481)no SQL Server 2014), o Row Count Spoolnão reduz o número estimado de linhas e uma junção de hash é escolhida, resultando em um plano muito melhor.

A reescrita LEFT JOIN

Para referência, aqui está uma maneira de reescrever a consulta para obter um bom desempenho em todos os SQL Server 2012, 2014 e 2016. No entanto, ainda estou interessado no comportamento específico da consulta acima e se ela é um bug no novo Estimador de cardinalidade do SQL Server 2014.

-- Re-writing with LEFT JOIN yields much better performance in 2012/2014/2016
SELECT n.*
FROM #potentialNewCustomers n
LEFT JOIN (SELECT 1 AS test, cust_nbr FROM #existingCustomers) c
    ON c.cust_nbr = n.cust_nbr
WHERE c.test IS NULL

Por que a segunda INSERTinstrução é aproximadamente 5x mais lenta que a primeira?

Pela quantidade de dados de log gerados, acho que o segundo não se qualifica para o registro mínimo. No entanto, a documentação no Guia de desempenho de carregamento de dados indica que ambas as inserções devem poder ser minimamente registradas. Portanto, se o log mínimo é a principal diferença de desempenho, por que a segunda consulta não se qualifica para o log mínimo? O que pode ser feito para melhorar a situação?

Consulta nº 1: Inserindo linhas de 5MM usando INSERT...WITH (TABLOCK)

Considere a consulta a seguir, que insere 5MM de linhas em um heap. Esta consulta é executada 1 seconde gerada 64MBpor dados de log de transações, conforme relatado por sys.dm_tran_database_transactions.

CREATE TABLE dbo.minimalLoggingTest (n INT NOT NULL)
GO
INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
SELECT n
-- Any table/view/sub-query that correctly estimates that it will generate 5MM rows
FROM dbo.fiveMillionNumbers
-- Provides greater consistency on my laptop, where other processes are running
OPTION (MAXDOP 1)
GO

Consulta nº 2: Inserindo os mesmos dados, mas o SQL subestima o número de linhas

Agora, considere esta consulta muito semelhante, que opera exatamente com os mesmos dados, mas extrai de uma tabela (ou SELECTinstrução complexa com muitas junções em meu caso de produção real) onde a estimativa de cardinalidade é muito baixa. Esta consulta executa 5.5 secondse gera 461MBdados de log de transações.

CREATE TABLE dbo.minimalLoggingTest (n INT NOT NULL)
GO
INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
SELECT n
-- Any table/view/sub-query that produces 5MM rows but SQL estimates just 1000 rows
FROM dbo.fiveMillionNumbersBadEstimate
-- Provides greater consistency on my laptop, where other processes are running
OPTION (MAXDOP 1)
GO

roteiro completo

Consulte este Pastebin para obter um conjunto completo de scripts para gerar os dados de teste e executar qualquer um desses cenários. Observe que você deve usar um banco de dados que esteja no SIMPLE modelo de recuperação .

Contexto empresarial

Estamos movendo com frequência milhões de linhas de dados e é importante que essas operações sejam o mais eficientes possível, tanto em termos de tempo de execução quanto de carga de E/S do disco. Inicialmente, tínhamos a impressão de que criar uma tabela heap e usar INSERT...WITH (TABLOCK)era uma boa maneira de fazer isso, mas agora ficamos menos confiantes, pois observamos a situação demonstrada acima em um cenário de produção real (embora com consultas mais complexas, não o versão simplificada aqui).

Ao encontrar linhas distintas em duas tabelas onde não podemos necessariamente garantir que sejam pré-classificadas, é uma boa ideia usar um FULL OUTER JOINem vez de um UNION? Há alguma desvantagem nessa abordagem? Se for consistentemente mais rápido, por que o otimizador de consulta não escolhe o mesmo plano para UNION que FULL OUTER JOINusaria?

Conseguimos trazer uma consulta de produção específica de ~ 10 minutos para ~ 3 minutos reescrevendo a UNIONcomo um arquivo FULL OUTER JOIN. A UNIONparece ser a maneira mais intuitiva de escrever a lógica, mas ao explorar as duas opções, observei que FULL OUTER JOINé mais eficiente em termos de uso de memória e CPU.

Consulte os scripts a seguir se desejar executar uma versão simplificada e anônima de nossa consulta de produção:

Script de configuração

-- Create a 500K row table
SELECT TOP 500000 ROW_NUMBER() OVER (ORDER BY NEWID()) AS id, v1.number % 5 AS val
INTO #t1
FROM master..spt_values v1
CROSS JOIN master..spt_values v2

-- Create a 5MM row table that will match some, but not all, of the 500K row table
SELECT TOP 5000000 ROW_NUMBER() OVER (ORDER BY NEWID()) AS id, v1.number % 5 AS val
INTO #t2
FROM master..spt_values v1
CROSS JOIN master..spt_values v2

-- Optionally, key both tables to see the impact it has on query plans and performance
-- Both queries end up with essentially the same plan and performance in this case
-- So that means that at least there is not a downside to using the FULL OUTER JOIN when the data is sorted
--ALTER TABLE #t1
--ADD UNIQUE CLUSTERED (id)
--ALTER TABLE #t2
--ADD UNIQUE CLUSTERED (id)

JUNÇÃO EXTERNA COMPLETA

O FULL OUTER JOINescolhe a menor das duas tabelas como o lado da construção de uma junção de hash, o que significa que o uso de memória é proporcional ao tamanho da tabela menor (500 mil linhas).

-- CPU time = 3058 ms,  elapsed time = 783 ms.
-- MaxUsedMemory: 29016 KB
-- Table '#t1'. Scan count 5, logical reads 1301, physical reads 0
-- Table '#t2'. Scan count 5, logical reads 12989, physical reads 0
SELECT COUNT(*), AVG(id), AVG(val)
FROM (
    SELECT COALESCE(t1.id, t2.id) AS id, COALESCE(t1.val, t2.val) AS val
    FROM #t1 t1
    FULL OUTER JOIN #t2 t2
        ON t2.id = t1.id
        AND t2.val = t1.val
) x
GO

UNIÃO

O UNIONcria uma tabela de hash para um agregado de hash no conjunto de dados geral, o que significa que o uso de memória é proporcional ao número total de linhas distintas (linhas de 5,4 MM neste caso; geralmente, pelo menos o número de linhas no maior dos duas mesas). O uso de memória é 10 vezes maior que o FULL OUTER JOIN, e tanto o tempo de CPU quanto o tempo decorrido também são mais lentos. Se eu escalasse isso até o ponto em que a agregação de hash não pudesse caber na concessão de memória de uma única consulta, a diferença de desempenho se tornaria enorme (como foi em nossa grande consulta de produção).

-- CPU time = 4651 ms,  elapsed time = 1188 ms.
-- MaxUsedMemory: 301600 KB
-- Table '#t1'. Scan count 5, logical reads 1301, physical reads 0
-- Table '#t2'. Scan count 5, logical reads 12989, physical reads 0
SELECT COUNT(*), AVG(id), AVG(val)
FROM (
    SELECT t1.id, t1.val
    FROM #t1 t1
    UNION 
    SELECT t2.id, t2.val
    FROM #t2 t2
) x

Quais diretrizes devem ser consideradas para manter índices de texto completo?

Devo RECONSTRUIR ou REORGANIZAR o catálogo de texto completo (ver BOL )? O que é uma cadência de manutenção razoável? Que heurística (semelhante aos limites de fragmentação de 10% e 30%) poderia ser usada para determinar quando a manutenção é necessária?

(Tudo abaixo é simplesmente informação extra elaborando a questão e mostrando o que eu pensei até agora.)

Informações extras: minha pesquisa inicial

Existem muitos recursos sobre a manutenção do índice b-tree (por exemplo, esta questão , os scripts de Ola Hallengren e várias postagens de blog sobre o assunto em outros sites). No entanto, descobri que nenhum desses recursos fornece recomendações ou scripts para manter índices de texto completo.

Há documentação da Microsoft que menciona que desfragmentar o índice b-tree da tabela base e, em seguida, executar REORGANIZE no catálogo de texto completo pode melhorar o desempenho, mas não aborda nenhuma recomendação mais específica.

Também encontrei esta pergunta , mas ela é focada principalmente no rastreamento de alterações (como as atualizações de dados na tabela subjacente são propagadas no índice de texto completo) e não no tipo de manutenção agendada regularmente que pode maximizar a eficiência do índice.

Informações extras: testes básicos de desempenho

Este SQL Fiddle contém código que pode ser usado para criar um índice de texto completo com controle de AUTOalterações e examinar o tamanho e o desempenho da consulta do índice à medida que os dados na tabela são modificados. Quando executo a lógica do script em uma cópia dos meus dados de produção (em oposição aos dados fabricados artificialmente no violino), aqui está um resumo dos resultados que estou vendo após cada etapa de modificação de dados:

Mesmo que as declarações de atualização neste script tenham sido bastante planejadas, esses dados parecem mostrar que há muito a ganhar com a manutenção regular.

Informações extras: Ideias iniciais

Estou pensando em criar uma tarefa noturna ou semanal. Parece que esta tarefa pode executar uma RECONSTRUÇÃO ou REORGANIZAÇÃO.

Como os índices de texto completo podem ser muito grandes (dezenas ou centenas de milhões de linhas), gostaria de poder detectar quando os índices no catálogo estão fragmentados o suficiente para justificar uma RECONSTRUÇÃO/REORGANIZAÇÃO. Estou um pouco incerto sobre o que a heurística pode fazer sentido para isso.

Estou vendo o seguinte comportamento de E/S do tempdb durante um período de uma hora:

Há uma boa quantidade de E/S de disco gerada por várias cargas de trabalho de DW em execução na máquina, algumas das quais não cabem nos aproximadamente 280 GB de memória alocada para SQL. Um aspecto interessante é que grande parte da E/S está concentrada nas unidades de disco giratórias (E) em vez das unidades de estado sólido (F e G) que lidam com a E/S com muito mais eficiência.

Pré-alocamos 300 GB completos cada (600 GB no total) nas unidades F e G para tempdb (usando 12 arquivos) e pré-alocamos 1,3 TB para tempdb na unidade E (atualmente 1 arquivo). Os dados de E/S acima sugerem que o uso de tempdb é distribuído entre arquivos com base no tamanho atual do arquivo. Não consegui encontrar documentação sobre isso, mas também executei uma consulta como a seguinte para investigar mais a fundo:

-- While running this query, writes to tempdb are distributed to E/F/G drives
-- in proportion to their current size.  This was shown by both
-- sys.dm_io_virtual_file_stats and the space used on the tempdb files before and after
SELECT TOP 100000000 *
INTO #temp
FROM [A_Really_Big_Table]

O comportamento ideal seria que F e G fossem usados ​​exclusivamente, a menos que ambos estivessem cheios, caso em que a unidade de disco giratória deve fornecer espaço tempdb adicional para que cargas de trabalho ocasionais muito grandes possam ser atendidas sem esgotar o espaço tempdb.

Estamos no caminho certo ao observar que o uso do tempdb é distribuído entre os arquivos com base no tamanho atual dos arquivos? Foi um pouco surpreendente ver esse tipo de distribuição em vez de ver um uso igual de cada arquivo (o que provavelmente foi a suposição de quem configurou esse hardware e decidiu alocar apenas um para tempdb no disco giratório).

Com base no comentário de Paul White em resposta a esta pergunta , estamos pensando na seguinte abordagem:

• Reduza os arquivos tempdb no disco giratório. Com base em nosso teste inicial, isso deve mudar a distribuição atual do trabalho mais para as unidades de estado sólido
• Configure os arquivos tempdb de estado sólido para pré-alocar seu espaço (como já fazemos agora)
• Configure os arquivos tempdb do disco giratório para iniciar sem alocação. Certifique-se de que a inicialização instantânea do arquivo esteja ativada. O Tempdb aumentará no disco giratório apenas conforme necessário (o que provavelmente ocorrerá no máximo uma vez por semana).
• Crie um plano de manutenção que reduza os arquivos tempdb no disco giratório após períodos de pico de carga, colocando a distribuição de volta em favor dos arquivos tempdb de estado sólido.

Isso parece razoável? Existem abordagens alternativas ou problemas potenciais a serem considerados? Obviamente, testaremos a abordagem na medida do possível, mas não poderemos fazê-lo em hardware de teste completamente equivalente.

Desculpas antecipadamente pela pergunta muito detalhada. Incluí consultas para gerar um conjunto de dados completo para reproduzir o problema e estou executando o SQL Server 2012 em uma máquina de 32 núcleos. No entanto, não acho que isso seja específico do SQL Server 2012 e forcei um MAXDOP de 10 para este exemplo específico.

Eu tenho duas tabelas que são particionadas usando o mesmo esquema de partição. Ao juntá-los na coluna usada para particionamento, notei que o SQL Server não é capaz de otimizar uma junção de mesclagem paralela tanto quanto se poderia esperar e, portanto, optou por usar um HASH JOIN. Nesse caso específico, posso simular manualmente um MERGE JOIN paralelo muito mais ideal, dividindo a consulta em 10 intervalos separados com base na função de partição e executando cada uma dessas consultas simultaneamente no SSMS. Usando WAITFOR para executá-los precisamente ao mesmo tempo, o resultado é que todas as consultas são concluídas em aproximadamente 40% do tempo total usado pelo HASH JOIN paralelo original.

Existe alguma maneira de fazer com que o SQL Server faça essa otimização por conta própria no caso de tabelas particionadas de forma equivalente? Eu entendo que o SQL Server geralmente pode incorrer em muita sobrecarga para fazer um MERGE JOIN paralelo, mas parece que há um método de fragmentação muito natural com sobrecarga mínima neste caso. Talvez seja apenas um caso especializado que o otimizador ainda não é inteligente o suficiente para reconhecer?

Aqui está o SQL para configurar um conjunto de dados simplificado para reproduzir este problema:

/* Create the first test data table */
CREATE TABLE test_transaction_properties 
    ( transactionID INT NOT NULL IDENTITY(1,1)
    , prop1 INT NULL
    , prop2 FLOAT NULL
    )

/* Populate table with pseudo-random data (the specific data doesn't matter too much for this example) */
;WITH E1(N) AS (
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
    UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
, E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b)
, E4(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b)
, E8(N) AS (SELECT 1 FROM E4 a CROSS JOIN E4 b)
INSERT INTO test_transaction_properties WITH (TABLOCK) (prop1, prop2)
SELECT TOP 10000000 (ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 5) + 1 AS prop1
                , ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) * rand() AS prop2
FROM E8

/* Create the second test data table */
CREATE TABLE test_transaction_item_detail
    ( transactionID INT NOT NULL
    , productID INT NOT NULL
    , sales FLOAT NULL
    , units INT NULL
    )

 /* Populate the second table such that each transaction has one or more items
     (again, the specific data doesn't matter too much for this example) */
INSERT INTO test_transaction_item_detail WITH (TABLOCK) (transactionID, productID, sales, units)
SELECT t.transactionID, p.productID, 100 AS sales, 1 AS units
FROM test_transaction_properties t
JOIN (
    SELECT 1 as productRank, 1 as productId
    UNION ALL SELECT 2 as productRank, 12 as productId
    UNION ALL SELECT 3 as productRank, 123 as productId
    UNION ALL SELECT 4 as productRank, 1234 as productId
    UNION ALL SELECT 5 as productRank, 12345 as productId
) p
    ON p.productRank <= t.prop1

/* Divides the transactions evenly into 10 partitions */
CREATE PARTITION FUNCTION [pf_test_transactionId] (INT)
AS RANGE RIGHT
FOR VALUES
(1,1000001,2000001,3000001,4000001,5000001,6000001,7000001,8000001,9000001)

CREATE PARTITION SCHEME [ps_test_transactionId]
AS PARTITION [pf_test_transactionId]
ALL TO ( [PRIMARY] )

/* Apply the same partition scheme to both test data tables */
ALTER TABLE test_transaction_properties
ADD CONSTRAINT PK_test_transaction_properties
PRIMARY KEY (transactionID)
ON ps_test_transactionId (transactionID)

ALTER TABLE test_transaction_item_detail
ADD CONSTRAINT PK_test_transaction_item_detail
PRIMARY KEY (transactionID, productID)
ON ps_test_transactionId (transactionID)

Agora estamos finalmente prontos para reproduzir a consulta abaixo do ideal!

/* This query produces a HASH JOIN using 20 threads without the MAXDOP hint,
    and the same behavior holds in that case.
    For simplicity here, I have limited it to 10 threads. */
SELECT COUNT(*)
FROM test_transaction_item_detail i
JOIN test_transaction_properties t
    ON t.transactionID = i.transactionID
OPTION (MAXDOP 10)

No entanto, usar um único thread para processar cada partição (exemplo para a primeira partição abaixo) levaria a um plano muito mais eficiente. Eu testei isso executando uma consulta como a abaixo para cada uma das 10 partições precisamente no mesmo momento, e todas as 10 terminaram em pouco mais de 1 segundo:

SELECT COUNT(*)
FROM test_transaction_item_detail i
INNER MERGE JOIN test_transaction_properties t
    ON t.transactionID = i.transactionID
WHERE t.transactionID BETWEEN 1 AND 1000000
OPTION (MAXDOP 1)