All perguntas(dba)

Eu tenho uma instrução SQL UPDATE com uma cláusula "TOP (X)" e a linha em que estou atualizando valores tem cerca de 4 bilhões de linhas. Quando uso "TOP (10)", obtenho um plano de execução que é executado quase instantaneamente, mas quando uso "TOP (50)" ou maior, a consulta nunca (pelo menos, não enquanto estou esperando) termina e ele usa um plano de execução completamente diferente. A consulta menor usa um plano muito simples com um par de buscas de índice e uma junção de loop aninhada, onde exatamente a mesma consulta (com um número diferente de linhas na cláusula TOP da instrução UPDATE) usa um plano que envolve duas buscas de índice diferentes , um carretel de mesa, paralelismo e várias outras complexidades.

Usei "OPTION (USE PLAN...)" para forçá-lo a usar o plano de execução gerado pela consulta menor - quando faço isso, posso atualizar até 100.000 linhas em alguns segundos. Eu sei que o plano de consulta é bom, mas o SQL Server só escolherá esse plano por conta própria quando apenas um pequeno número de linhas estiver envolvido - qualquer contagem de linha decentemente grande em minha atualização resultará no plano abaixo do ideal.

Achei que o paralelismo poderia ser o culpado, então comecei MAXDOP 1a consulta, mas sem efeito - essa etapa foi concluída, mas a má escolha/desempenho não. Também corri sp_updatestatsesta manhã para garantir que não era essa a causa.

Anexei os dois planos de execução - o mais curto também é o mais rápido. Além disso, aqui está a consulta em questão (vale a pena notar que o SELECT que incluí parece ser rápido nos casos de contagens de linhas pequenas e grandes):

    update top (10000) FactSubscriberUsage3
               set AccountID = sma.CustomerID
    --select top 50 f.AccountID, sma.CustomerID
      from FactSubscriberUsage3 f
      join dimTime t
        on f.TimeID = t.TimeID
      join #mac sma
        on f.macid = sma.macid
       and t.TimeValue between sma.StartDate and sma.enddate 
     where f.AccountID = 0 --There's a filtered index on the table for this

Aqui está o plano rápido :

E aqui está o mais lento :

Existe algo óbvio na maneira como estou configurando minha consulta ou no plano de execução fornecido que se prestaria à má escolha que o mecanismo de consulta está fazendo? Se necessário, também posso incluir as definições das tabelas envolvidas e os índices nelas definidos.

Para aqueles que pediram uma versão apenas de estatísticas dos objetos do banco de dados: eu nem sabia que você poderia fazer isso, mas faz todo o sentido! Tentei gerar os scripts para um banco de dados somente de estatísticas para que outros pudessem testar os planos de execução por si mesmos, mas posso gerar estatísticas/histogramas no meu índice filtrado (erro de sintaxe no script, ao que parece), então estou sem sorte lá. Eu tentei remover o filtro e os planos de consulta estavam próximos, mas não exatamente os mesmos, e não quero enviar ninguém em uma perseguição.

Atualização e alguns planos de execução mais completos: Em primeiro lugar, o Plan Explorer do SQL Sentry é uma ferramenta incrível. Eu nem sabia que existia até ver as outras perguntas do plano de consulta neste site, e tinha muito a dizer sobre como minhas consultas estavam sendo executadas. Embora eu não tenha certeza de como resolver o problema, eles deixaram claro qual é o problema.

Aqui está o resumo para 10, 100 e 1.000 linhas - você pode ver que a consulta de 1.000 linhas está muito, muito fora de linha com as outras:

Você pode ver que a terceira consulta tem um número ridículo de leituras, então obviamente está fazendo algo completamente diferente. Aqui está o plano de execução estimado, com contagens de linhas. Plano de execução estimado de 1.000 linhas:

E aqui estão os resultados reais do plano de execução (aliás, por "nunca termina", eu quis dizer "acaba em uma hora"). Plano de execução real de 1.000 linhas

A primeira coisa que notei foi que, em vez de extrair 60 mil linhas da tabela dimTime como esperado, na verdade está extraindo 1,6 bilhão, com um B . Olhando para minha consulta, não tenho certeza de como ela está retirando tantas linhas da tabela dimTime. O operador BETWEEN que estou usando apenas garante que estou extraindo o registro correto do #mac com base no registro de tempo na tabela Fact. No entanto, quando adiciono uma linha à cláusula WHERE onde filtro t.TimeValue (ou t.TimeID) para um único valor, posso atualizar com êxito 100.000 linhas em questão de segundos. Como resultado disso, e como ficou claro nos planos de execução que incluí, é óbvio que o problema é meu cronograma, mas não tenho certeza de como alteraria os critérios de junção para contornar esse problema e manter a precisão . Alguma ideia?

Para referência, aqui está o plano (com contagens de linhas) para a atualização de 100 linhas. Você pode ver que ele atinge o mesmo índice e ainda com uma tonelada de linhas, mas nem de longe a mesma magnitude de um problema. Execução de 100 linhas com contagens de linha :

Contexto
Estamos desenvolvendo um sistema com um banco de dados grande na parte inferior. É um banco de dados MS SQL executado no SQL Server 2008 R2. O tamanho total do banco de dados é de cerca de 12 GB.

Destes, aproximadamente 8,5 GB estão em uma única tabela BinaryContent. Como o nome sugere, trata-se de uma tabela onde armazenamos arquivos simples, de qualquer tipo, diretamente na tabela como um BLOB. Recentemente, testamos a possibilidade de mover todos esses arquivos do banco de dados para o sistema de arquivos usando o FILESTREAM.

Fizemos as modificações necessárias em nosso banco de dados sem problemas, e nosso sistema continua funcionando bem após a migração. A BinaryContenttabela fica mais ou menos assim:

CREATE TABLE [dbo].[BinaryContent](
    [BinaryContentID] [int] IDENTITY(1,1) NOT NULL,
    [FileName] [varchar](50) NOT NULL,
    [BinaryContentRowGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL
) ON [PRIMARY] FILESTREAM_ON [FileStreamContentFG]
ALTER TABLE [dbo].[BinaryContent] ADD [FileContentBinary] [varbinary](max) FILESTREAM  NULL
ALTER TABLE [dbo].[BinaryContent] ADD  CONSTRAINT [DFBinaryContentRowGUID]  DEFAULT (newsequentialid()) FOR [BinaryContentRowGUID]

Com tudo residindo no PRIMARYgrupo de arquivos, exceto o campo FileBinaryContentque está em um grupo de arquivos separado FileStreamContentFG.

Cenário
Do ponto de vista de um desenvolvedor, geralmente gostaríamos de uma nova cópia do banco de dados de nosso ambiente de produção, para podermos trabalhar com os dados mais recentes. Nesses casos, raramente estamos interessados ​​nos arquivos armazenados em BinaryContent (agora usando FILESTREAM).

Temos isso quase funcionando como gostaríamos. Fazemos backup do banco de dados, sem o fluxo de arquivo assim:

BACKUP DATABASE FileStreamDB
FILEGROUP = 'PRIMARY' 
TO DISK = 'c:\backup\FileStreamDB_WithoutFS.bak' WITH INIT

E restaure-o assim:

RESTORE DATABASE FileStreamDB
FROM DISK = 'c:\backup\FileStreamDB_WithoutFS.bak'

Isso parece estar funcionando bem, e nosso sistema funciona desde que evitemos as partes que usam o FileBinaryContentcampo. Podemos, por exemplo, executar a seguinte consulta sem problemas:

SELECT TOP 10 [BinaryContentID],[FileName],[BinaryContentRowGUID]
--,[FileContentBinary]
FROM [dbo].[BinaryContent]

Naturalmente, se eu descomentar a linha acima, inclusive FileContentBinaryna consulta, recebo um erro:

Dados de objetos grandes (LOB) para a tabela "dbo.BinaryContent" residem em um grupo de arquivos offline ("FileStreamContentFG") que não pode ser acessado.

Nosso sistema lida com arquivos em que o conteúdo é definido como null, então o que eu gostaria de fazer é algo assim:

UPDATE [dbo].[BinaryContent]
SET [FileContentBinary] = null

Mas é claro que isso me dá o mesmo erro acima. Neste ponto estou preso.

Pergunta
Existe alguma maneira de restaurar o banco de dados sem precisar restaurar também tudo do FileStreamContentFGgrupo de arquivos? Atualizando os valores para nulo como estou tentando acima, ou padrão para nulo quando o arquivo está ausente ou algo assim?

Ou talvez eu esteja abordando o problema da maneira errada?

Sou um desenvolvedor por natureza e não tenho muito conhecimento como DBA, então me desculpe se estou deixando passar alguma coisa trivial aqui.

Gostaria de saber se é necessário escrever commit após insert/delete/update em function/procedure?

Exemplo:

create or replace function test_fun
return number is
begin
   delete from a;
   return 0;
end;

ou procedimento

create or replace procedure aud_clear_pro
as
begin
   delete from a;
end;

precisa de commit após a exclusão?

Não consigo entender a seguinte situação:

1. Se eu chamar a função/procedimento da janela SQL, ela requer confirmação

   mas

2. Se eu agendar uma função/procedimento usando dbms_scheduler e executar o trabalho, a instrução delete será confirmada automaticamente.

   PORQUE?

Eu tenho uma tabela filho que é algo assim:

[Tabela de Data de Custo]

| Customer ID | Some Date  | Balance |
+-------------+------------+---------+
|           1 | 2012-04-30 |   20.00 |
|           1 | 2012-03-31 |   50.00 |
|           2 | 2012-04-30 |    0.00 |
|           2 | 2012-03-31 |   10.00 | 
|           3 | 2012-03-31 |   60.00 |
|           3 | 2012-02-29 |   10.00 |

Eu gostaria de obter um conjunto de resultados como este - um registro para cada cliente com a data mais recente:

| Customer ID | Some Date  | Balance |
+-------------+------------+---------+
|           1 | 2012-04-30 |   20.00 | 
|           2 | 2012-04-30 |    0.00 |
|           3 | 2012-03-31 |   60.00 |

Eu sei que posso fazer isso para cada "ID de cliente" individual com o seguinte SQL (sintaxe do SQL Server):

select top 1  [Some Date], [Customer ID], [Balance]
from [Cust Date Table]
where [Customer ID] = 2
order by [Some Date] desc


| Customer ID | Some Date  | Balance |
+-------------+------------+---------+
|           2 | 2012-04-30 |    0.00 |

Mas não tenho certeza de como conseguir os três discos que quero. Não tenho certeza se esta é uma situação que exige uma subconsulta ou outra coisa.

Observe que a data máxima pode ser diferente para qualquer [ID do cliente], (neste exemplo, a data máxima do cliente 3 é 31/03/2012, enquanto os outros registros têm uma data máxima de 30/04/2012). eu tentei

select [Customer ID], MAX([Some Date]) AS [Latest Date], Balance 
from [Cust Date Table] 
group by [Customer ID], Balance; 

O problema é que isso não retorna apenas uma linha para cada cliente - ele retorna várias linhas.

Eu sei que você não pode comparar um valor com NULL e esperar um resultado sem adicionar algo como no código a seguir...

SELECT
    *
FROM 
    A INNER JOIN 
    B ON A.ID = B.ID
WHERE
    A.STRING <> B.STRING OR (A.STRING IS NULL AND B.STRING IS NOT NULL) OR (A.STRING IS NOT NULL AND B.STRING IS NULL) OR 
    A.DT <> B.DT OR (A.DT IS NULL AND B.DT IS NOT NULL) OR (A.DT IS NOT NULL AND B.DT IS NULL) OR 
    A.B <> B.B OR (A.B IS NULL AND B.B IS NOT NULL) OR (A.B IS NOT NULL AND B.B IS NULL) OR 
    A.NUM <> B.NUM OR (A.NUM IS NULL AND B.NUM IS NOT NULL) OR (A.NUM IS NOT NULL AND B.NUM IS NULL) 

Minha pergunta é:

Existe uma maneira mais elegante de testar os valores alterados nas duas tabelas em que qualquer uma delas pode ser nula?

A solução precisa funcionar uniformemente entre os tipos de dados.

Aqui está o código para configurar as tabelas de teste ...

CREATE TABLE A
(
    ID INT IDENTITY(1,1) NOT NULL,
    STRING VARCHAR(20) NULL,
    DT DATETIME NULL,
    B BIT NULL,
    NUM INT NULL
)

CREATE TABLE B
(
    ID INT IDENTITY(1,1) NOT NULL,
    STRING VARCHAR(20) NULL,
    DT DATETIME NULL,
    B BIT NULL,
    NUM INT NULL
)


INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES (NULL, '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', NULL, 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', NULL, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, NULL)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)


INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('STAGE', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2555-11-11 00:00:00.000', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 999)

Eu estava pesquisando outra coisa quando me deparei com essa coisa. Eu estava gerando tabelas de teste com alguns dados e executando diferentes consultas para descobrir como diferentes maneiras de escrever consultas afetam o plano de execução. Aqui está o script que usei para gerar dados de teste aleatórios:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('t') AND type in (N'U'))
DROP TABLE t
GO

CREATE TABLE t 
(
 c1 int IDENTITY(1,1) NOT NULL 
,c2 int NULL
) 
GO

insert into t
select top 1000000 a from
(select t1.number*2048 + t2.number a, newid() b
from [master]..spt_values t1 
cross join  [master]..spt_values t2
where t1.[type] = 'P' and t2.[type] = 'P') a
order by b
GO

update t set c2 = null
where c2 < 2048 * 2048 / 10
GO


CREATE CLUSTERED INDEX pk ON [t] (c1)
GO

CREATE NONCLUSTERED INDEX i ON t (c2)
GO

Agora, com esses dados, chamei a seguinte consulta:

select * 
from t 
where 
      c2 < 1048576 
   or c2 is null
;

Para minha grande surpresa, o plano de execução gerado para esta consulta foi este arquivo . (Desculpe pelo link externo, é muito grande para caber aqui).

Alguém pode me explicar o que há com todas essas " varreduras constantes " e " escalas de computação "? O que está acontecendo?

  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1010], [Expr1011], [Expr1012]))
       |--Merge Interval
       |    |--Sort(TOP 2, ORDER BY:([Expr1013] DESC, [Expr1014] ASC, [Expr1010] ASC, [Expr1015] DESC))
       |         |--Compute Scalar(DEFINE:([Expr1013]=((4)&[Expr1012]) = (4) AND NULL = [Expr1010], [Expr1014]=(4)&[Expr1012], [Expr1015]=(16)&[Expr1012]))
       |              |--Concatenation
       |                   |--Compute Scalar(DEFINE:([Expr1005]=NULL, [Expr1006]=NULL, [Expr1004]=(60)))
       |                   |    |--Constant Scan
       |                   |--Compute Scalar(DEFINE:([Expr1008]=NULL, [Expr1009]=(1048576), [Expr1007]=(10)))
       |                        |--Constant Scan
       |--Index Seek(OBJECT:([t].[i]), SEEK:([t].[c2] > [Expr1010] AND [t].[c2] < [Expr1011]) ORDERED FORWARD)

Eu tenho um conjunto de scripts que precisam ser executados em uma determinada ordem. Gostaria de criar um "arquivo mestre" que listasse todos os outros arquivos e sua ordem correta. Basicamente como um arquivo de inclusão de C++ ou ASP/VBScript.

Eu estava bisbilhotando o SSMS e notei que o "tamanho" das minhas INTcolunas era de 4 bytes (esperado), mas fiquei um pouco chocado ao ver que minhas BITcolunas eram um byte inteiro.

Eu entendi mal o que eu estava olhando?

É o caso que IDENTITY_INSERT só pode ser definido como ON em uma tabela de banco de dados por vez, mas por quê? Como IDENTITYas colunas não são globalmente exclusivas, não consigo pensar em nenhuma situação perigosa que possa ser causada pela inserção de identidades em mais de uma tabela ao mesmo tempo (pelo menos não mais perigoso do que geralmente falsificar IDENTITY INSERT).

IDENTITY INSERT raramente deve ser usado, mas qual é o motivo do limite rígido?

O MS SQL 2008 oferece suporte a TVP: um recurso útil para upload de dados em massa para um procedimento armazenado para processamento.

Em vez de criar um tipo definido pelo usuário, é possível aproveitar uma definição de tabela existente? Por exemplo, é possível criar um procedimento armazenado com a seguinte assinatura?

CREATE PROCEDURE usp_InsertProductionLocation
@TVP **LocationTable** READONLY

A documentação parece sugerir que isso não é possível.

CÓDIGO DE AMOSTRA

/*
Sample code from:
http://msdn.microsoft.com/en-us/library/bb510489.aspx
*/

USE AdventureWorks2008R2;
GO

/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50)
, CostRate INT );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks2008R2].[Production].[Location]
           ([Name]
           ,[CostRate]
           ,[Availability]
           ,[ModifiedDate])
        SELECT *, 0, GETDATE()
        FROM  @TVP;
        GO

/* Declare a variable that references the type. */
DECLARE @LocationTVP 
AS LocationTableType;

/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT [Name], 0.00
    FROM 
    [AdventureWorks2008R2].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

/*
The following is not part of the original source code:
*/

CREATE TABLE LocationTable(
 LocationName VARCHAR(50)
, CostRate INT );
GO