8kb's questions

Estou tentando entender por que usar uma variável de tabela está impedindo o otimizador de usar uma busca de índice e, em seguida, marcar uma pesquisa em vez de uma varredura de índice.

Preenchendo a tabela:

CREATE TABLE dbo.Test 
(
    RowKey INT NOT NULL PRIMARY KEY, 
    SecondColumn CHAR(1) NOT NULL DEFAULT 'x',
    ForeignKey INT NOT NULL 
) 

INSERT dbo.Test 
(
    RowKey, 
    ForeignKey
) 
SELECT TOP 1000000 
    ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
    ABS(CHECKSUM(NEWID()) % 10)     
FROM sys.all_objects s1
CROSS JOIN sys.all_objects s2 

CREATE INDEX ix_Test_1 ON dbo.Test (ForeignKey) 

Preencha uma variável de tabela com um único registro e tente pesquisar a chave primária e a segunda coluna pesquisando na coluna da chave estrangeira:

DECLARE @Keys TABLE (RowKey INT NOT NULL) 

INSERT @Keys (RowKey) VALUES (10)

SELECT 
    t.RowKey,
    t.SecondColumn
FROM
    dbo.Test t 
INNER JOIN 
    @Keys k
ON
    t.ForeignKey = k.RowKey

Segue abaixo o plano de execução:

Agora, a mesma consulta usando uma tabela temporária:

CREATE TABLE #Keys (RowKey INT NOT NULL) 

INSERT #Keys (RowKey) VALUES (10) 

SELECT 
    t.RowKey,
    t.SecondColumn
FROM
    dbo.Test t 
INNER JOIN 
    #Keys k
ON
    t.ForeignKey = k.RowKey

Este plano de consulta usa uma pesquisa de busca e marcador:

Por que o otimizador deseja fazer a pesquisa de favoritos com a tabela temporária, mas não com a variável da tabela?

A variável de tabela é usada neste exemplo para representar dados provenientes de um tipo de tabela definido pelo usuário em um procedimento armazenado.

Percebo que a busca de índice pode não ser apropriada se o valor da chave estrangeira ocorrer centenas de milhares de vezes. Nesse caso, uma varredura provavelmente seria uma escolha melhor. Para o cenário que criei, não havia nenhuma linha com valor 10. Ainda acho o comportamento interessante e gostaria de saber se há uma razão para isso.

SQL Fiddle

Adicionar OPTION (RECOMPILE)não alterou o comportamento. O UDDT tem uma chave primária.

@@VERSIONé SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) (Build 7601: Service Pack 1) (Hipervisor)

Estou executando esta consulta no banco de dados AdventureWorks2012 :

SELECT 
    s.SalesOrderID,
    d.CarrierTrackingNumber,
    d.ProductID,
    d.OrderQty
FROM Sales.SalesOrderHeader s 
JOIN Sales.SalesOrderDetail d 
    ON s.SalesOrderID = d.SalesOrderID
WHERE s.CustomerID = 11077

Se eu olhar para o plano de execução estimado, vejo o seguinte:

A busca de índice inicial (canto superior direito) está usando o índice IX_SalesOrderHeader_CustomerID e pesquisando no literal 11077. Tem uma estimativa de 2,6192 linhas.

Se eu usar DBCC SHOW_STATISTICS ('Sales.SalesOrderHeader', 'IX_SalesOrderHeader_CustomerID') WITH HISTOGRAM, isso mostra que o valor 11077 está entre as duas chaves amostradas 11019 e 11091.

O número médio de linhas distintas entre 11019 e 11091 é 2,619718, ou arredondado para 2,61972, que é o valor das linhas estimadas mostradas para a busca do índice.

A parte que não entendo é o número estimado de linhas para a busca de índice clusterizado na tabela SalesOrderDetail.

Se eu correr DBCC SHOW_STATISTICS ('Sales.SalesOrderDetail', 'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID'):

Portanto, a densidade do SalesOrderID (no qual estou entrando) é 3,178134E-05. Isso significa que 1/3.178134E-05 (31465) é igual ao número de valores exclusivos de SalesOrderID na tabela SalesOrderDetail.

Se houver 31465 SalesOrderID exclusivos em SalesOrderDetail, com uma distribuição uniforme, o número médio de linhas por SalesOrderID é 121317 (número total de linhas) dividido por 31465. A média é 3,85561

Portanto, se o número estimado de linhas a serem percorridas for 2,61972 e a média a ser retornada em 3,85561, acho que o número estimado de linhas seria 2,61972 * 3,85561 = 10,10062.

Mas o número estimado de linhas é 11,4867.

Acho que meu entendimento da segunda estimativa está incorreto e os números diferentes parecem indicar isso. o que estou perdendo?

Eu encontrei isso em algum código-fonte hoje:

        SELECT
            @Error = ERROR_NUMBER (),
            @ErrorMsg = N'An Error occured while populating the TABLE ' + @DestinationTableName +
                        N' with data. Error Code: ' + CAST ( @Error AS nvarchar(20) ) + 
                        N', Error Description: "' + ERROR_MESSAGE () + N'".' 
                        + NCHAR(0X0D) + NCHAR(0X0A) + NCHAR(0X0D) + NCHAR(0X0A),
            @ErrorSeverity = ERROR_SEVERITY (),
            @ErrorState = ERROR_STATE () ;

Parece que a string da mensagem de erro está adicionando dois feeds de linha após a descrição. 0X0D é 13 e 0X0A é 10.

Existe uma razão para usar hexadecimal em vez de apenas números inteiros?

Normalmente o que eu fiz é NCHAR(13) + NCHAR(10)...

Muitos de nossos bancos de dados armazenam códigos de rastreamento numéricos (como números de série) como números inteiros. Há pouca chance de esses códigos obterem caracteres alfabéticos, mas ainda parece um tipo de dados incorreto, pois você nunca executaria operações matemáticas neles.

Qual é o tipo de dados apropriado para esses tipos de identificadores?

Antigamente, minha equipe tinha uma política de que qualquer modificação de procedimento armazenado exigia comentários em dois lugares:

1. Parte superior do procedimento armazenado com um resumo geral das alterações feitas
2. Comentários em cada linha onde a alteração foi feita

Normalmente era assim:

CREATE PROCEDURE usp_Test
/*****************************************************************
The purpose of this stored procedure is to get data.
Created by 8kb 2001-01-01
Modified: removed OR clauses from JOIN statement..8kb 2001-06-01
******************************************************************/
AS
BEGIN

SELECT * 
FROM t1
JOIN t2
ON t1.colA = t2.colA
-- Removed OR clause..8kb 2001-06-01
-- OR t1.ColB = t2.ColB

END

Mas agora, com o controle de código-fonte integrado, posso resumir as alterações no controle de código-fonte e usar a funcionalidade comparar/diferenciar para ver as diferenças entre as versões antiga e nova.

Ainda há valor em escrever comentários de alteração linha por linha em procedimentos armazenados que fazem parte do controle de origem integrado?

Com base em minha análise, um modelo dimensional completo para nosso data warehouse exigirá a extração de mais de 200 tabelas de origem. Algumas dessas tabelas serão extraídas como parte de um carregamento incremental e outras serão um carregamento completo.

Para observar, temos cerca de 225 bancos de dados de origem, todos com o mesmo esquema.

Pelo que vi, criar um fluxo de dados simples no SSIS com uma origem OLE DB e um destino OLE DB requer que as colunas e os tipos de dados sejam determinados em tempo de design. Isso significa que acabarei com mais de 200 fluxos de dados apenas para a extração.

De uma perspectiva de manutenção, isso me parece um grande problema. Se eu precisasse fazer algum tipo de alteração abrangente no código de extração, teria que modificar 200 fluxos de dados diferentes.

Como opção alternativa, escrevi um pequeno script que lê os bancos de dados de origem, nomes de tabelas e colunas que desejo extrair de um conjunto de tabelas de metadados. O código é executado em vários loops e usa SQL dinâmico para extrair das tabelas de origem por meio de um servidor vinculado e OPENQUERY.

Com base em meus testes, isso ainda não é tão rápido quanto usar um fluxo de dados SSIS com origem e destino OLEDB. Então, estou me perguntando que tipo de alternativas eu tenho. Os pensamentos até agora incluem:

1. Usando EZAPI para gerar pacotes SSIS programaticamente com fluxo de dados simples. As tabelas e colunas a serem extraídas viriam das mesmas tabelas de metadados mencionadas anteriormente.
2. Adquira software de terceiros (componente de fluxo de dados dinâmico)

Qual é a melhor maneira de abordar isso? Quando se trata de programação .NET, sou iniciante, portanto, o tempo necessário para aprimorar apenas o básico também é uma preocupação.

Estou trabalhando com um aplicativo herdado que possui cerca de dez anos de dados de clientes. A maioria desses dados não é usada nas operações do dia-a-dia, mas há um requisito de negócios para ter os dados disponíveis para o cliente até sua aposentadoria do sistema.

Estamos explorando o arquivamento dos dados em uma cópia do banco de dados existente e, em seguida, a eliminação dos registros da produção após um determinado ponto no tempo.

Minha preocupação é que o banco de dados passa por uma mudança substancial de esquema a cada trimestre devido aos esforços de desenvolvimento.

Se eu fosse arquivar uma cópia espelhada dos dados, também precisaria aplicar todos os scripts de alteração que vão contra a produção?

Existem estratégias alternativas? Parece que não importa a forma de armazenamento que você escolher (ou seja, banco de dados, arquivos simples, xml), você sempre precisará de alguma forma de mapear os esquemas mais antigos para os mais novos.

Digamos que eu tenha um banco de dados OLTP simples com pedidos, produtos e clientes:

E a partir dele, estou construindo um data mart com uma tabela de fatos de pedidos, dimensão de produto, dimensão de cliente e dimensão de data:

Ao carregar a tabela de pedidos em fact_orders (digamos que eu estivesse usando uma transformação de pesquisa SSIS para atribuir as chaves substitutas), isso significa que a fonte de dados para os pedidos também precisaria ter os valores naturais de "chave estrangeira" que foram associados com o pedido no sistema OLTP?

Em outras palavras, os dados que estão sendo carregados viriam de uma consulta como essa?

SELECT 
  order_date,                   -- needed to get date surrogate key
  customer_name,                -- needed to get customer surrogate key
  product_name,                 -- needed to get product surrogate key
  order_number,                 -- denegenerate dimension,
  qty_ordered AS order_qty,     -- measure
  total_amount AS order_amount  -- measure
FROM orders o 
  INNER JOIN customers c 
    ON o.customer_id = c.customer_id
  INNER JOIN products p 
    ON o.product_id = p.product_id 

Eu estava executando alguns testes simples em um editor e assinante na mesma instância:

1. Eu inseri 50.000 linhas em um artigo publicado e os dados foram enviados corretamente para o assinante.
2. Excluí inadvertidamente o 49.985º registro do assinante (mas não percebi na época)
3. Excluí 50.000 linhas do artigo publicado

Enquanto observava o tamanho da tabela de assinantes, notei que a contagem de linhas caía quase para zero e depois voltava para 50.000. As exclusões começariam a ser executadas novamente, a contagem de linhas cairia e, em seguida, voltaria para 50.000. Isso continuou acontecendo repetidamente.

Executei um rastreamento padrão e vi todas as exclusões funcionando normalmente. Depois que estava quase completo, esta declaração apareceu:

IF @@TRANCOUNT > 0 ROLLBACK 

Então eu modifiquei o rastreamento para incluir mensagens de erro e vi isso:

The row was not found at the Subscriber when applying the replicated command.

Portanto, parece que quando o agente de distribuição está executando todas as exclusões uma a uma, o processo ainda está incluído em uma transação (o DELETE original era uma linha). Se houver um erro, ele executa um ROLLBACK e começa novamente.

Minhas perguntas:

1. Existe uma maneira padrão de sair do loop?
2. Seria aceitável recriar a linha ausente para que o processo pudesse ser concluído?
3. O que você faz se isso acontecer na produção? Acho que não é bem uma pergunta. Mas isso parece que um erro simples pode ter algumas complicações bastante sérias, a menos que a replicação seja monitorada com muito cuidado.

Estou trabalhando com um banco de dados herdado que foi importado do MS Access. Existem cerca de vinte tabelas com chaves primárias exclusivas não agrupadas que foram criadas durante a atualização do MS Access > SQL Server.

Muitas dessas tabelas também têm índices exclusivos e não agrupados que são duplicatas da chave primária.

Estou tentando limpar isso.

Mas o que descobri é que depois de recriar as chaves primárias como índices clusterizados e, em seguida, tentar reconstruir a chave estrangeira, a chave estrangeira está referenciando o antigo índice duplicado (que era exclusivo).

Eu sei disso porque não vai me deixar descartar os índices duplicados.

Eu acho que o SQL Server sempre escolheria uma chave primária, se existisse. O SQL Server tem um método de escolha entre um índice exclusivo e uma chave primária?

Para duplicar o problema (no SQL Server 2008 R2):

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Child') DROP TABLE Child
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Parent') DROP TABLE Parent
GO

-- Create the parent table
CREATE TABLE Parent (ParentID INT NOT NULL IDENTITY(1,1)) 

-- Make the parent table a heap
ALTER TABLE Parent ADD CONSTRAINT PK_Parent PRIMARY KEY NONCLUSTERED (ParentID) 

-- Create the duplicate index on the parent table
CREATE UNIQUE NONCLUSTERED INDEX IX_Parent ON Parent (ParentID) 

-- Create the child table
CREATE TABLE Child  (ChildID  INT NOT NULL IDENTITY(1,1), ParentID INT NOT NULL ) 

-- Give the child table a normal PKey
ALTER TABLE Child ADD CONSTRAINT PK_Child PRIMARY KEY CLUSTERED (ChildID) 

-- Create a foreign key relationship with the Parent table on ParentID
ALTER TABLE Child ADD CONSTRAINT FK_Child FOREIGN KEY (ParentID) 
REFERENCES Parent (ParentID) ON DELETE CASCADE NOT FOR REPLICATION

-- Try to clean this up
-- Drop the foreign key constraint on the Child table
ALTER TABLE Child DROP CONSTRAINT FK_Child

-- Drop the primary key constraint on the Parent table
ALTER TABLE Parent DROP CONSTRAINT PK_Parent

-- Recreate the primary key on Parent as a clustered index
ALTER TABLE Parent ADD CONSTRAINT PK_Parent PRIMARY KEY CLUSTERED (ParentID) 

-- Recreate the foreign key in Child pointing to parent ID
ALTER TABLE Child ADD CONSTRAINT FK_Child FOREIGN KEY (ParentID) 
REFERENCES Parent (ParentID) ON DELETE CASCADE NOT FOR REPLICATION

-- Try to drop the duplicate index on Parent 
DROP INDEX IX_Parent ON Parent 

Mensagem de erro:

Msg 3723, Nível 16, Estado 6, Linha 36 Um DROP INDEX explícito não é permitido no índice 'Parent.IX_Parent'. Ele está sendo usado para imposição de restrição FOREIGN KEY.

Hoje, enquanto solucionava um problema do agente de serviços, descobri que o proprietário do banco de dados era o login do Windows de um funcionário que havia deixado a empresa. Seu login foi removido e, portanto, as notificações de consulta estavam falhando.

Supostamente, a melhor prática para lidar com isso é tornar 'sa' o proprietário do banco de dados. Nós mudamos e isso limpou a fila.

Minha pergunta (muito elementar): qual é o proprietário do banco de dados e qual é o seu propósito?

Qual é a melhor estratégia de extração para aproximadamente 200 bancos de dados de origem do SQL Server 2005 (mesmo esquema) para uma carga diária em uma área de preparação em preparação para limpeza, eliminação de duplicação e transformações do data warehouse?

Até agora, imaginei as seguintes possibilidades:

1. Replicação transacional: crie 200 assinantes do SQL Server 2008 R2 extraindo dados de seus respectivos editores em 2005. Habilite o Change Data Capture nas tabelas necessárias no assinante e nas tabelas de sombra para realizar carregamentos incrementais em nosso banco de dados temporário.
2. Rowversion: adicione uma coluna rowversion em cada tabela de origem necessária e use-a em conjunto com um processo SSIS para extrair os dados alterados diretamente para o banco de dados de preparação.
3. Arquivos BCP: crie uma tarefa automatizada para fazer um despejo noturno de arquivos BCP de todas as tabelas de origem. Use o SSIS para carregar essas tabelas no banco de dados de preparo como parte de um carregamento completo (em vez de incremental).

Pensamentos adicionais:

1. Estou nervoso com a sobrecarga administrativa e de hardware necessária para oferecer suporte a uma topologia de replicação transacional totalmente nova em 200 bancos de dados.
2. O tamanho total combinado dos bancos de dados é de cerca de 100 GB. Mas a maior parte disso faz parte dos logs de transações e outras tabelas que não serão usadas em nenhum fato ou dimensão. Em outras palavras, os arquivos BCP não serão enormes, e é por isso que estou considerando uma estratégia de extração completa, embora tudo o que li recomende contra ela.
3. Estou aberto a sugestões, documentos, etc.

Há muitas postagens de blog e artigos de práticas recomendadas exaltando as virtudes de colocar o arquivo de dados do SQL Server em um disco rígido e o log de transações em outro. A razão fornecida é que o arquivo de banco de dados terá leituras e gravações aleatórias, enquanto o log de transações terá apenas gravações sequenciais.

Mas e se você tiver centenas de bancos de dados? Existe um verdadeiro benefício de desempenho em colocar centenas de arquivos de log de transações em um disco separado? Se vários logs de transações estiverem sendo gravados, acho que as gravações do log de transações seriam tão aleatórias quanto as gravações do banco de dados.

Estou trabalhando com um banco de dados herdado que implementou notificações de consulta em uma tabela chamada ColorList .

Ao executar um rastreamento do lado do servidor para um problema não relacionado, observei a consulta

SELECT color FROM ColorList

estava sendo executado a cada 10 milissegundos .

É assim que a notificação de consulta funciona? O SQL Server armazena os resultados da consulta original e, em seguida, executa a consulta indefinidamente até detectar uma alteração?

No Capítulo 2 Vendas no varejo de The Data Warehouse Toolkit de Ralph Kimball , o autor descreve uma dimensão de data com três campos que parecem totalmente estranhos para mim:

Date Key (PK)
Date
Full Date Description
Day of Week
**Day Number in Epoch**
**Week Number in Epoch**
**Month Number in Epoch**
Day Number in Calendar Month
Day Number in Calendar Year
...

Entendo que uma época é a "instância no tempo escolhida como uma era específica" e que a época padrão atual para J2000.0 começa em 1º de janeiro de 2000. Mas não vejo como esses números seriam úteis em uma dimensão de data ou um cubo usado para obter informações sobre vendas no varejo.

1. Os números na época são normalmente usados ​​em dimensões de data?
2. Que época seria usada para um negócio moderno?
3. Como um usuário fatiaria os dados com base no Epoch e por quê?

Quando escrevo testes de unidade para meus procedimentos armazenados, eles geralmente se parecem com isto:

/* Unit test for stored procedure which runs a query */

/* Define #temp table with structure of query results */

/* Define and set variables for stored procedure parameters */ 

/* If stored procedure queries from a particular table...*/ 

/* Truncate table and populate with sample data */ 

/* Call stored procedure using variables but insert results into table */ 

/* INSERT #temp EXEC usp_TestProc @P1=1, @P2 = 'X' */

/* Query #temp table to confirm that all fields have certain values */

/* Query #temp table to confirm that certain number of rows exist */ 

/* If results are incorrect, display error message */ 

Embora isso funcione, parece desajeitado de alguma forma e é muito difícil de manter ao longo de um projeto de longo prazo em que os requisitos mudam constantemente.

1. Existe uma maneira melhor (ou padrão) de testar um procedimento armazenado como este?
2. A dificuldade de manutenção é simplesmente parte do trabalho e vale o esforço para fazer os testes de unidade? Devo dizer que, na maioria dos meus trabalhos, os DBAs raramente fazem testes de unidade em primeiro lugar.

Recentemente, vi esta resposta no SO, que usa o valor "local-name" ao destruir xml usando .nodes(). Nunca vi isso antes:

select T.N.value('local-name(.)', 'varchar(128)') as Name,
       T.N.value('.', 'varchar(max)') as Value
from @xml.nodes('/data/*') as T(N);

Onde posso encontrar o documento definitivo sobre comandos como .nodes()e .value()?

A sugestão no BOL é bastante vaga:

Faça backup do master quantas vezes for necessário para proteger os dados de forma suficiente para suas necessidades de negócios. Recomendamos um agendamento regular de backup, que você pode complementar com um backup adicional após uma atualização substancial.

Se você se aventurar mais , encontrará estes detalhes:

Os tipos de operações que fazem com que o mestre seja atualizado e que requerem um backup, incluem o seguinte:

• Criando ou excluindo um banco de dados do usuário.
• Se um banco de dados do usuário crescer automaticamente para acomodar novos dados, o
  mestre não será afetado.
• Adicionar ou remover arquivos e grupos de arquivos.
• Adicionar logins ou outras operações relacionadas à segurança de login.
• As operações de segurança do banco de dados, como adicionar um usuário a um banco de dados, não afetam o mestre.
• Alteração das opções de configuração do banco de dados ou de todo o servidor.
• Criando ou removendo dispositivos lógicos de backup.
• Configurar o servidor para consultas distribuídas e chamadas de procedimento remoto (RPCs), como adicionar servidores vinculados ou logins remotos.

Portanto, se todos os nossos logins forem adicionados por meio de grupos do Windows e não fizermos nenhuma outra alteração no banco de dados, isso significa que um backup único do mestre é suficiente?

Se não, qual é o intervalo de backup padrão para o banco de dados mestre?

Eu tenho dois trabalhos do SQL Agent que estão programados para serem executados em intervalos diferentes. A primeira tarefa executa um backup completo uma vez por dia. A segunda tarefa executa backups de log de transações a cada quinze minutos.

Como o banco de dados cresceu, o backup completo está demorando mais do que o planejado originalmente (mesmo com compactação). E notei em meus logs que os backups do log de transações agora estão sendo executados ao mesmo tempo.

Devo alterar o agendamento do backup do log de transações para que não seja executado quando o backup completo estiver em execução? Isso importa?

Suponha que eu tenha duas consultas que serão executadas com frequência em meu banco de dados:

SELECT 
  UserID,
  UserName,
  UserGender
FROM Users
WHERE UserID = @User

SELECT 
  UserID,
  UserName,
  UserGender
FROM Users
WHERE UserName LIKE @Name + '%

Eles devem estar em dois procedimentos armazenados separados ou apenas um único que cria as instruções dinamicamente usando sp_executesql?

Se eles estiverem em dois procedimentos armazenados, precisarei modificar ambos os procedimentos se quiser adicionar ou remover uma coluna na instrução SELECT. E se eu usar SQL dinâmico, presumivelmente estou sacrificando uma pequena quantidade de desempenho.

Este é um caso em que a manutenção e a adesão ao princípio DRY (não se repita) usando SQL dinâmico teriam precedência sobre o ganho de desempenho de um procedimento armazenado?