All perguntas(dba)

Esta pergunta é sobre o desempenho do índice do SQL Server com um varchar(2000)índice INCLUDEde cobertura.

Estou tentando melhorar o desempenho em um aplicativo de banco de dados lento e instável. Em alguns casos, os dados são acessados ​​por meio de strings varchar grandes, com as consultas incluindo várias operações de string como SUBSTRING(), SPACE()e DATALENGTH(). Aqui está um exemplo simplificado de acesso;

update fattable set col3 =  
   SUBSTRING(col3,1,10) + '*' + 
   SUBSTRING(col3,12,DATALENGTH(col3)-12)
from fattable where substring(col3,10,1) = 'A' and col2 = 2

O esquema fica assim:

CREATE TABLE [dbo].[FatTable]( 
    [id] [bigint] IDENTITY(1,1) NOT NULL, 
    [col1] [nchar](12) NOT NULL, 
    [col2] [int] NOT NULL, 
    [col3] [varchar](2000) NOT NULL, ... 

O índice a seguir foi definido, com um campo de cobertura na coluna de texto grande.

CREATE NONCLUSTERED INDEX [IndexCol2Col3] ON [dbo].[FatTable]  ( [col2] ASC ) 
    INCLUDE( [col3] )

Pelo que li, é RUIM colocar grandes campos de dados em um índice. Tenho lido vários artigos, incluindo http://msdn.microsoft.com/en-us/library/ms190806.aspx , que discute o impacto da paginação e do tamanho do disco no desempenho do índice. Dito isto, o plano de consulta definitivamente usa o índice de cobertura. Não tenho informações suficientes para determinar quanto isso realmente está me custando em termos de carga do sistema. Eu sei que, no geral, o sistema está funcionando mal e estou preocupado que esse seja um dos problemas. Perguntas:

• Colocar esta varchar(2000)coluna no índice INCLUDEé uma boa ideia?

• Como os INCLUDEcampos são armazenados em nós folha, eles têm muito impacto no desempenho do índice?

Atualização: Obrigado pelas excelentes respostas! Esta é uma pergunta injusta de certa forma - como vocês dizem, não há resposta certa absoluta sem estatísticas e perfis reais. Como tantos problemas de desempenho, acho que a resposta é "depende".

Estou escrevendo um sql dinâmico para descartar e criar uma exibição em um banco de dados diferente.

Então eu escrevi:

set @CreateViewStatement = 
                '
                USE ['+ @DB +'];
                CREATE VIEW [dbo].[MyTable]
                AS

                SELECT ........something
exec (@CreateViewStatement)

Dá-me erro:

'CREATE VIEW' deve ser a primeira instrução em um lote de consulta.

Se eu remover a instrução USE DATABASE, funcionará bem, mas o banco de dados não será mais especificado ....

Como posso resolver este problema?

Pediram-me para criar algo que rastreie o custo diário a ser cobrado nas contas e estou tentando descobrir um esquema de tabela de banco de dados que suporte isso.

Aqui está o que eu sei

• Empresa tem mais de 2,5 milhões de contas
• Destes, trabalham atualmente uma média de 200.000 por mês (isso muda com os níveis de pessoal, que atualmente são baixos)
• Eles têm 13 tipos de custo diferentes que gostariam de rastrear e avisaram que podem adicionar mais no futuro
• Eles querem que os custos sejam rastreados diariamente
• Os custos não são divididos em todo o inventário. Eles são divididos entre o número de contas trabalhadas por mês (200.000) ou os usuários podem inserir identificadores de conta para aplicar um custo a um grupo de contas ou podem simplesmente especificar a quais contas aplicar o custo.

Meu primeiro pensamento foi um banco de dados normalizado:

Id da conta
Encontro
CostTypeId
Quantia

Meu problema com isso é, faça as contas. Esta mesa vai ficar enorme rapidamente. Supondo que todos os 13 tipos de custo sejam aplicados a todas as contas trabalhadas no mês atual, ou seja 200k * 13 * N days in month, cerca de 75 a 80 milhões de registros por mês ou quase um bilhão de registros por ano.

Meu segundo pensamento foi desnormalizar um pouco

Id da conta
Encontro
Custo total
CostType1
CostType2
CostType3
CostType4
CostType5
CostType6
CostType7
CostType8
CostType9
CostType10
CostType11
CostType12
CostType13

Esse método é mais desnormalizado e pode gerar até 6 milhões de registros por mês ( 200k * N days in month), ou cerca de 72 milhões por ano. É muito menor do que o primeiro método, porém se a empresa decidir por um novo tipo de custo no futuro, outra coluna de banco de dados precisará ser adicionada.

Dos dois métodos, qual você prefere? Por quê? Existe outra alternativa que você possa pensar que lidaria melhor com isso?

Estou mais interessado em relatar o desempenho, tanto relatórios resumidos quanto detalhados. O trabalho que distribuirá os custos pelas contas será executado todas as noites, quando não houver ninguém por perto. Uma preocupação secundária é o tamanho do banco de dados. O banco de dados existente já tem quase 300 GB e acredito que o espaço em disco esteja em torno de 500 GB.

O banco de dados é SQL Server 2005

Dado isso:

DECLARE
  TYPE T_ARRAY IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
  MY_ARRAY T_ARRAY;
  V_COUNT INTEGER;

Eu gostaria de fazer:

BEGIN
  -- ... some code filling the MY_ARRAY array

  -- obviously COUNT_ELEMENTS() does not exists, this is what I'm looking for :-)
  V_COUNT := COUNT_ELEMENTS(MY_ARRAY);

  DBMS_OUTPUT.PUT_LINE('My array containts ' || V_COUNT || ' elements.');
END;

Existe algo melhor do que criar um procedimento fazendo um loop básico incrementando um contador? Talvez uma função nativa PL/SQL já faça isso COUNT_ELEMENTS()?

Estou definindo um esquema para um novo conjunto de recursos usando o SQL Server 2008... Nesse caso, cada registro ( por exemplo, linha ) precisará armazenar fragmentos XML. De tempos em tempos; embora não com frequência; Vou precisar consultar o XML para encontrar valores de elementos e atributos. Se deixado por conta própria, eu tenderia a usar o tipo de dados XML , embora tenha sido levado a acreditar que isso é forjado com problemas. Então isso me leva às minhas perguntas.

Diante desse cenário, quais fatores devo considerar ao tentar decidir entre armazenar XML em uma coluna XML ou uma coluna varchar(MAX)

Se ajudar... aqui estão alguns detalhes adicionais:

• Nenhuma decisão foi tomada em relação ao uso de esquemas para esses fragmentos ( por exemplo, XSD's )
• Os tamanhos dos fragmentos variam de pequenos a muito grandes
• Todo o XML será bem formado
• Ao longo de um dia, haverá até ~ 10.000 fragmentos coletados com suporte de consulta on-line necessário por ~ 3 meses
• As consultas ao XML acontecerão ao longo do dia, mas devem permanecer leves com poucas consultas simultâneas desse tipo

Contexto

Esta questão refere-se aos detalhes de implementação de baixo nível de índices em sistemas de banco de dados SQL e NoSQL. A estrutura real do índice (árvore B+, hash, SSTable, etc.) é irrelevante, pois a questão se refere especificamente às chaves armazenadas em um único nó de qualquer uma dessas implementações.

Fundo

Em bancos de dados SQL (por exemplo, MySQL) e NoSQL (CouchDB, MongoDB, etc.), quando você cria um índice em uma coluna ou campo de dados de documento JSON, o que você está realmente fazendo com que o banco de dados faça é criar essencialmente uma lista classificada de todos os esses valores junto com um deslocamento de arquivo no arquivo de dados principal onde reside o registro pertencente a esse valor.

(Para simplificar, posso estar descartando outros detalhes esotéricos de impls específicos)

Exemplo de SQL Clássico Simples

Considere uma tabela SQL padrão que tenha uma chave primária int simples de 32 bits na qual criamos um índice, terminaremos com um índice em disco das chaves inteiras classificadas e associadas a um deslocamento de 64 bits no arquivo de dados onde o registro vive, por exemplo:

id   | offset
--------------
1    | 1375
2    | 1413
3    | 1786

A representação em disco das chaves no índice é mais ou menos assim:

[4-bytes][8-bytes] --> 12 bytes for each indexed value

Seguindo as regras básicas sobre otimização de E/S de disco com sistemas de arquivos e sistemas de banco de dados, digamos que você armazene chaves em blocos de 4 KB no disco, o que significa:

4096 bytes / 12 bytes per key = 341 keys per block

Ignorando a estrutura geral do índice (árvore B+, hash, lista classificada etc.), lemos e gravamos blocos de 341 chaves por vez na memória e retornamos ao disco conforme necessário.

Consulta de exemplo

Usando as informações da seção anterior, digamos que uma consulta chegue para "id=2", a pesquisa de índice de banco de dados clássica é a seguinte:

1. Leia a raiz do índice (neste caso, 1 bloco)
2. Pesquisa binária no bloco classificado para encontrar a chave
3. Obtenha o deslocamento do arquivo de dados do valor
4. Procure o registro no arquivo de dados usando o deslocamento
5. Devolva os dados ao chamador

Configuração da pergunta...

Ok, aqui é onde a pergunta vem junto ...

A etapa 2 é a parte mais importante que permite que essas consultas sejam executadas em tempo O(logn)... as informações devem ser classificadas, MAS você deve ser capaz de percorrer a lista de maneira rápida... mais especificamente, você deve ser capaz de pular para deslocamentos bem definidos à vontade para ler o valor da chave de índice nessa posição.

Depois de ler no bloco, você tem que conseguir pular para a 170ª posição imediatamente, ler o valor da chave e ver se o que você procura é GT ou LT naquela posição (e assim por diante e assim por diante...)

A única maneira de pular os dados no bloco dessa maneira é se os tamanhos dos valores das chaves estiverem todos bem definidos, como nosso exemplo acima (4 bytes e depois 8 bytes por chave).

PERGUNTA

Ok, então é aqui que estou travando com design de índice eficiente... para colunas varchar em bancos de dados SQL ou mais especificamente, campos de forma totalmente livre em bancos de dados de documentos como CouchDB ou NoSQL, onde qualquer campo que você deseja indexar pode ser qualquer comprimento como você implementa os valores-chave que estão dentro dos blocos da estrutura de índice a partir da qual você constrói seus índices?

Por exemplo, digamos que você use um contador sequencial para um ID no CouchDB e esteja indexando tweets... você terá valores que vão de "1" a "100.000.000.000" depois de alguns meses.

Digamos que você crie o índice no banco de dados no dia 1, quando houver apenas 4 tweets no banco de dados, o CouchDB pode ser tentado a usar a seguinte construção para os valores-chave dentro dos blocos de índice:

[1-byte][8-bytes] <-- 9 bytes
4096 / 9 = 455 keys per block

Em algum momento, isso é interrompido e você precisa de um número variável de bytes para armazenar o valor da chave nos índices.

O ponto é ainda mais evidente se você decidir indexar um campo realmente de comprimento variável como um "tweet_message" ou algo assim.

Com as próprias chaves tendo comprimento totalmente variável e o banco de dados não tendo como adivinhar de forma inteligente algum "tamanho máximo de chave" quando o índice é criado e atualizado, como essas chaves são realmente armazenadas dentro dos blocos que representam segmentos dos índices nesses bancos de dados ?

Obviamente, se suas chaves são de tamanho variável e você lê em um bloco de chaves, não apenas não tem ideia de quantas chaves estão realmente no bloco, mas também não tem ideia de como pular para o meio da lista para fazer um binário pesquise sobre eles.

É aqui que estou ficando todo tropeçado.

Com campos de tipo estático em bancos de dados SQL clássicos (como bool, int, char, etc.), entendo que o índice pode apenas predefinir o tamanho da chave e cumpri-lo ... mas neste mundo de armazenamentos de dados de documentos, estou perplexo como eles estão modelando com eficiência esses dados no disco de forma que ainda possam ser verificados em tempo de O(logn) e gostaria de receber qualquer esclarecimento aqui.

Por favor, deixe-me saber se quaisquer esclarecimentos são necessários!

Atualização (Resposta de Greg)

Por favor, veja meus comentários anexados à resposta de Greg. Depois de mais uma semana de pesquisa, acho que ele realmente se deparou com uma sugestão maravilhosamente simples e eficiente de que, na prática, é muito fácil de implementar e usar, ao mesmo tempo em que oferece grandes ganhos de desempenho ao evitar a desserialização de valores-chave com os quais você não se importa.

Eu examinei 3 implementações de DBMS separadas (CouchDB, kivaloo e InnoDB) e todas elas lidam com esse problema desserializando o bloco inteiro na estrutura de dados interna antes de pesquisar os valores dentro de seu ambiente de execução (erlang/C).

É isso que considero tão brilhante na sugestão de Greg; um tamanho de bloco normal de 2048 normalmente teria 50 ou menos deslocamentos, resultando em um bloco muito pequeno de números que precisaria ser lido.

Atualização (possíveis desvantagens da sugestão de Greg)

Para continuar este diálogo comigo mesmo, percebi as seguintes desvantagens...

1. Se cada "bloco" for encabeçado com dados de deslocamento, você não poderá permitir que o tamanho do bloco seja ajustado na configuração posteriormente, pois poderá acabar lendo dados que não começaram com um cabeçalho correto ou um bloco que continha vários cabeçalhos.

2. Se você estiver indexando grandes valores de chave (digamos que alguém esteja tentando indexar uma coluna de char(8192) ou blob(8192)), é possível que as chaves não caibam em um único bloco e precisem ser sobrecarregadas em dois blocos lado a lado . Isso significa que seu primeiro bloco teria um cabeçalho de deslocamento e o segundo bloco começaria imediatamente com os dados da chave.

A solução para tudo isso é ter um tamanho de bloco de banco de dados fixo que não seja ajustável e desenvolver estruturas de dados de bloco de cabeçalho em torno dele ... por exemplo, você fixa todos os tamanhos de bloco em 4 KB (normalmente o mais ideal de qualquer maneira) e escreve cabeçalho do bloco que inclui o "tipo de bloco" no início. Se for um bloco normal, imediatamente após o cabeçalho do bloco deve estar o cabeçalho de deslocamentos. Se for um tipo de "transbordamento", imediatamente após o cabeçalho do bloco estarão os dados de chave brutos.

Atualização (possível up-side incrível)

Depois que o bloco é lido como uma série de bytes e os deslocamentos decodificados; tecnicamente, você poderia simplesmente codificar a chave que está procurando em bytes brutos e, em seguida, fazer comparações diretas no fluxo de bytes.

Uma vez encontrada a chave que você está procurando, o ponteiro pode ser decodificado e seguido.

Outro efeito colateral incrível da ideia de Greg! O potencial para otimização de tempo de CPU aqui é grande o suficiente para que definir um tamanho de bloco fixo possa valer a pena apenas para obter tudo isso.

Tenho dois servidores de banco de dados, conectados via Linked Servers. Ambos são bancos de dados SQL Server 2008R2, e a conexão do servidor vinculado é feita por meio de um link "SQL Server" regular, usando o contexto de segurança do logon atual. Os servidores vinculados estão ambos no mesmo datacenter, portanto, a conexão não deve ser um problema.

Eu uso a seguinte consulta para verificar quais valores da coluna identifierestão disponíveis remotamente, mas não localmente.

SELECT 
    identifier 
FROM LinkedServer.RemoteDb.schema.[TableName]

EXCEPT

SELECT DISTINCT
    identifier 
FROM LocalDb.schema.[TableName] 

Em ambas as tabelas há índices não clusterizados na coluna identifier. Localmente são cerca de 2,6 milhões de linhas, remotamente apenas 54. No entanto, ao analisar o plano de consulta, 70% do tempo de execução é dedicado à "execução de consulta remota". Além disso, ao estudar o plano de consulta completo, o número de linhas locais estimadas é 1em vez de 2695380(que é o número de linhas estimadas ao selecionar apenas a consulta posterior a EXCEPT). Ao executar esta consulta, leva muito tempo.

Isso me faz pensar: Por que isso? A estimativa está "apenas" longe ou as consultas remotas em servidores vinculados são realmente tão caras?

Eu tenho uma tabela InnoDB que quero alterar. A tabela tem cerca de 80 milhões de linhas e encerra alguns índices.

Quero mudar o nome de uma das colunas e adicionar mais alguns índices.

• Qual é a maneira mais rápida de fazer isso (supondo que eu possa sofrer até mesmo tempo de inatividade - o servidor é um escravo não utilizado)?
• É um "simples" alter table, a solução mais rápida?

Neste momento, tudo o que me importa é a velocidade :)

Estou atualizando meu IDENTITYscript de verificação de estouro para contabilizar DECIMALe NUMERIC IDENTITYcolunas .

Como parte da verificação, calculo o tamanho do intervalo do tipo de dados para cada IDENTITYcoluna; Eu uso isso para calcular qual porcentagem desse intervalo foi esgotada. Pois DECIMALe NUMERIC o tamanho desse intervalo é2 * 10^p - 2 onde pestá a precisão.

Criei várias tabelas de teste com colunas DECIMALe NUMERIC IDENTITYe tentei calcular seus intervalos da seguinte maneira:

SELECT POWER(10.0, precision)
FROM sys.columns
WHERE 
       is_identity = 1
   AND type_is_decimal_or_numeric
;

Isso lançou o seguinte erro:

Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting float to data type numeric. 

Eu reduzi para as IDENTITYcolunas do tipo DECIMAL(38, 0)(ou seja, com a precisão máxima), então tentei o POWER()cálculo diretamente naquele valor.

Todas as consultas a seguir

SELECT POWER(10.0, 38.0);
SELECT CONVERT(FLOAT, (POWER(10.0, 38.0)));
SELECT CAST(POWER(10.0, 38.0) AS FLOAT);

também resultou no mesmo erro.

• Por que o SQL Server tenta converter a saída de POWER(), que é do tipo FLOAT, para NUMERIC(especialmente quando FLOATtem uma precedência mais alta )?
• Como posso calcular dinamicamente o intervalo de uma coluna DECIMALou para todas as precisões possíveis (incluindo , é claro)?NUMERICp = 38

Por favor, veja esta pergunta no stackoverflow:

Estou usando um driver EasySoft ODBC para vincular uma instância do SQL Server 2008 R2 Express ao Interbase e estou tendo alguma dificuldade em obter metadados do servidor remoto. Ao procurar na rede, todas as principais sugestões mencionam o uso de OPENQUERY em vez da sintaxe de servidor vinculado de quatro partes.

EG Minha abordagem atual (problemática) é...

CREATE VIEW [LIVE].[vwPRDETS]
AS

SELECT *
FROM [LBLIVE]...[PRDETS] WITH (NOLOCK)

Porém em algumas tabelas recebo o erro ao chamar a view...

Msg 7353, Nível 16, Estado 1, Linha 1 O provedor OLE DB "MSDASQL" para o servidor vinculado "LBLIVE" forneceu metadados inconsistentes. Uma coluna extra foi fornecida durante a execução que não foi encontrada no tempo de compilação.

Além disso, algumas exibições que não consigo criar porque recebo o seguinte ...

Msg 7315, Nível 16, Estado 1, Linha 1 O provedor OLE DB "MSDASQL" para o servidor vinculado "LBLIVE" contém várias tabelas que correspondem ao nome ""SYSDBA"."AUDIT_LBABKP"".

Embora haja apenas uma das tabelas mencionadas.

A abordagem alternativa de pesquisar na net parece ser mais como...

SELECT *
FROM OPENQUERY(<linked sevrer>, 'SELECT <column list> FROM MyTable')

Então, minha pergunta é, se eu usar OPENQUERY na minha definição de exibição, o SQL Server será capaz de otimizar o SQL resultante enviado ao Interbase? Ou realmente não há muita diferença entre as duas abordagens?

É um assunto cruzado e adoraria o ponto de vista de um dba.