Shnugo's questions

Estou bastante familiarizado com o SQL-Server como desenvolvedor, mas não tanto na função de DBA.

Atualmente estou trabalhando em um banco de dados bastante grande (~ 7 TB, vivendo em 7 arquivos, todos iguais em tamanho e todos dentro de um único grupo de arquivos). O banco de dados está ficando sem espaço em disco.

O cliente vai aumentar o espaço disponível em 2 TB.

Para declarar algumas coisas primeiro:

• O sistema é um cluster de failover
• É - por que nunca - nenhuma opção para adicionar um segundo grupo de arquivos.
• Eu sei que usar um segundo grupo de arquivos pode oferecer grandes benefícios... (não desejado pelo cliente. É um sistema antigo e ninguém se atreve a tocá-lo mais do que o necessário...)

As opções que estou pensando:

• Eu poderia usar ALTER DATABASEcom MODIFY FILEpara aumentar o tamanho dos arquivos existentes (~ 1 TB cada no momento).

• Eu poderia usar ADD FILEpara adicionar um ou dois arquivos adicionais semelhantes aos existentes.

As questões

• Existe algo especial para ter em mente com failover de clusters '?

• Existe alguma razão para preferir uma das opções?

observação: O objetivo é ter o menor impacto possível .

Estou no SQL-Server 12.0.5203.0

Há uma consulta cobrindo vários bancos de dados. Eu acho que não é útil postar a consulta real (como é bastante complexa e confidencial, basta pensar em uma consulta como

SELECT t1.Column1
      ,t2.Column2
      ,t3.Column3
FROM db1.schema.TableName1 t1 with (nolock)
JOIN db1.schema.TableName2 t2 with (nolock) ON SomeCriteria
JOIN db2.schema.TableName3 t3 with (nolock) ON SomeCriteria  
JOIN db3.schema.TableName4 t4 with (nolock) ON SomeCriteria
WHERE SomeCriteria  
  AND t4.ColumnA = (SELECT DISTINCT ColumnX
                      FROM db2.schema.TableName5 with (nolock) 
                      WHERE ColumnY = 2902)

Por favor: Esta não é minha pergunta... Não discuta o uso de WITH(NOLOCK)ou GROUP BYcontra DISTINCT, thx :-D

Os usuários relataram tempos limite devido a durações de execução de mais de um minuto. Não consegui reproduzir isso, pois exatamente a mesma consulta no mesmo ambiente trouxe exatamente o mesmo resultado em menos de um segundo.

Então - definindo todas as configurações possíveis para os mesmos valores - encontrei algo estranho: a diferença depende do banco de dados em uso.

Isso é reproduzível: Se houver USE master;, é 1 segundo, com USE db1;(ou qualquer outro) é terrivelmente lento.

Algumas observações gerais

• Todas as tabelas usadas são totalmente qualificadas com database.schema.tablealias
• Todas as tabelas são chamadas comWITH(NOLOCK)
• O criador de perfil para a execução rápida mostra CPU(30), Reads(20000), Duration(30)
• lento (mesmo SSMS, apenas duas janelas): CPU(11000), Reads(13M!!!), Duration(12000)
• Os planos de execução são extremamente diferentes
  • slow: começa com uma busca de índice retornando 2,6 milhões de linhas
    Combina isso com outra busca de índice empurrando para 27 milhões de linhas (estimada em 45!)
    Filtra até 1626 linhas, que é a contagem do resultado final
  • rápido: começando com pequenos conjuntos de cerca de 100 linhas, nunca mais de 8000 linhas

Minhas perguntas

• O que está acontecendo aqui? Por que o banco de dados, de onde venho , é tão importante?

Lições aprendidas e uma solução

O que eu não sabia: Os planos de consulta são armazenados com cada banco de dados separadamente, portanto, o banco de dados de contexto pode ser muito importante. Eu realmente não consigo entender a vantagem desse conceito... Por que não armazenar melhor os planos em um local central, um por ação? Mas essa é uma pergunta diferente...

Usando uma consulta contra sys.dm_exec_cached_planse dm_exec-query_plan()com query_plan.exist('//*:StmtSimple[contains(@*:StatementText[1],"Some specific part of the query")]')=1, encontrei alguns planos armazenados. Após a remoção tudo foi rápido e bem.

Mas no dia seguinte o mau comportamento estava de volta.

A solução: depois de uma análise detalhada do melhor plano, reorganizei tudo JOINspara essa ordem de execução e uso OPTION(FORCE ORDER). Isso parece resolver o problema.

Minha pergunta é baseada nisso: https://stackoverflow.com/q/35575990/5089204

Para dar uma resposta, fiz o seguinte cenário de teste.

cenário de teste

Primeiro eu crio uma tabela de teste e a preencho com 100.000 linhas. Um número aleatório (0 a 1000) deve levar a aproximadamente 100 linhas para cada número aleatório. Esse número é colocado em uma coluna varchar e como um valor em seu XML.

Aí eu faço uma chamada como o OP aí precisa com .exist() e com .nodes() com uma pequena vantagem para o segundo, mas ambos demoram de 5 a 6 segundos. Na verdade, faço as chamadas duas vezes: uma segunda vez na ordem trocada e com parâmetros de pesquisa ligeiramente alterados e com "//item" em vez do caminho completo para evitar falsos positivos por meio de resultados ou planos em cache.

Então eu crio um índice XML e faço as mesmas chamadas

Agora - o que realmente me surpreendeu! - o .nodescaminho completo é muito mais lento do que antes (9 segundos), mas .exist()é reduzido para meio segundo, com caminho completo até cerca de 0,10 segundos. (enquanto .nodes()com caminho curto é melhor, mas ainda muito atrás .exist())

Perguntas:

Meus próprios testes resumem: os índices XML podem explodir extremamente um banco de dados. Eles podem acelerar as coisas extremamente (s. edit 2), mas também podem retardar suas consultas. Gostaria de entender como funcionam... Quando se deve criar um índice XML? Por que .nodes()com um índice pode ser pior do que sem? Como alguém poderia evitar o impacto negativo?

CREATE TABLE #testTbl(ID INT IDENTITY PRIMARY KEY, SomeData VARCHAR(100),XmlColumn XML);
GO

DECLARE @RndNumber VARCHAR(100)=(SELECT CAST(CAST(RAND()*1000 AS INT) AS VARCHAR(100)));

INSERT INTO #testTbl VALUES('Data_' + @RndNumber,
'<error application="application" host="host" type="exception" message="message" >
  <serverVariables>
    <item name="name1">
      <value string="text" />
    </item>
    <item name="name2">
      <value string="text2" />
    </item>
    <item name="name3">
      <value string="text3" />
    </item>
    <item name="name4">
      <value string="text4" />
    </item>
    <item name="name5">
      <value string="My test ' +  @RndNumber + '" />
    </item>
    <item name="name6">
      <value string="text6" />
    </item>
    <item name="name7">
      <value string="text7" />
    </item>
  </serverVariables>
</error>');

GO 100000

DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesFullPath_no_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
WHERE XmlColumn.exist('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistFullPath_no_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
WHERE XmlColumn.exist('//item[@name="name5" and value/@string="My test 500"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistShortPath_no_index;
GO

DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('//item[@name="name5" and value/@string="My test 500"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesShortPath_no_index;
GO

CREATE PRIMARY XML INDEX PXML_test_XmlColum1 ON #testTbl(XmlColumn);
CREATE XML INDEX IXML_test_XmlColumn2 ON #testTbl(XmlColumn) USING XML INDEX PXML_test_XmlColum1 FOR PATH;
GO

DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesFullPath_with_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
WHERE XmlColumn.exist('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistFullPath_with_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
WHERE XmlColumn.exist('//item[@name="name5" and value/@string="My test 500"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistShortPath_with_index;
GO

DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('//item[@name="name5" and value/@string="My test 500"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesShortPath_with_index;
GO

DROP TABLE #testTbl;

EDIÇÃO 1 - Resultados

Este é um resultado com o SQL Server 2012 instalado localmente em um laptop médio Neste teste não consegui reproduzir o impacto extremamente negativo no NodesFullPath_with_index, embora seja mais lento do que sem o índice ...

NodesFullPath_no_index    6.067
ExistFullPath_no_index    6.223
ExistShortPath_no_index   8.373
NodesShortPath_no_index   6.733

NodesFullPath_with_index  7.247
ExistFullPath_with_index  0.217
ExistShortPath_with_index 0.500
NodesShortPath_with_index 2.410

EDIT 2 Teste com XML maior

De acordo com a sugestão de TT, usei o XML acima, mas copiei os item-nodes para chegar a cerca de 450 itens. Deixei o hit-node bem alto no XML (porque acho que .exist()isso pararia no primeiro hit, enquanto .nodes()continuaria)

A criação do índice XML aumentou o arquivo mdf para ~ 21 GB, ~ 18 GB parecem pertencer ao índice (!!!)

NodesFullPath_no_index    3min44
ExistFullPath_no_index    3min39
ExistShortPath_no_index   3min49
NodesShortPath_no_index   4min00

NodesFullPath_with_index  8min20
ExistFullPath_with_index  8,5 seconds !!!
ExistShortPath_with_index 1min21
NodesShortPath_with_index 13min41 !!!