Ross Bush's questions

A variável @SQL na declaração abaixo é sempre truncada quando eu "inline" uma variável. Se eu enviar a variável e imprimir, ela não trunca para 4000, no entanto, não consigo vincular a variável enviada em uma declaração USE [@DatabaseName]. Como contornar.

Isso funciona, no entanto, o nome do banco de dados é codificado na instrução USE

DECLARE @DatabaseName NVARCHAR(MAX) = 'MyDatabase'
DECLARE @Sql NVARCHAR(MAX)=
'
USE [MyDatabase]
PRINT @DatabaseName
SELECT
...<20,000 more chars
'

DECLARE @params NVARCHAR(MAX) ='@DatabaseName NVARCHAR(MAX)'
EXEC sp_executesql @SQL, @params,@DatabaseName

Isso gera uma exceção com a mensagem ' O banco de dados '@DatabaseName' não existe. Certifique-se de que o nome foi inserido corretamente .'

DECLARE @DatabaseName NVARCHAR(MAX) = 'MyDatabase'
DECLARE @Sql NVARCHAR(MAX)=
'
USE [@DatabaseName]
PRINT @DatabaseName
SELECT
...<20,000 more chars
'

DECLARE @params NVARCHAR(MAX) ='@DatabaseName NVARCHAR(MAX)'
EXEC sp_executesql @SQL, @params,@DatabaseName

Isso trunca a consulta para NVARCHAR(4000)

DECLARE @DatabaseName NVARCHAR(MAX) = 'MyDatabase'
DECLARE @Sql NVARCHAR(MAX)=
'
USE ['+@DatabaseName+']
PRINT @DatabaseName
SELECT
...<20,000 more chars
'

DECLARE @params NVARCHAR(MAX) ='@DatabaseName NVARCHAR(MAX)'
EXEC sp_executesql @SQL, @params,@DatabaseName

Também tentei usar N'string' sem sucesso

DECLARE @DatabaseName NVARCHAR(MAX) = N'MyDatabase'
DECLARE @Sql NVARCHAR(MAX)=
N'
USE ['+@DatabaseName+']
PRINT @DatabaseName
SELECT
...<20,000 more chars
'

DECLARE @params NVARCHAR(MAX) =N'@DatabaseName NVARCHAR(MAX)'
EXEC sp_executesql @SQL, @params,@DatabaseName

Acho que entendo por que isso USE [@Variable]não funciona, enquanto não sei como explicar por que USE ['+@Variable+']está sendo truncado para 4000 quando ambas as variáveis ​​estão no máximo, a menos que o SQL Server esteja fazendo a conversão para baixo sp_executesql.

De qualquer forma, alguém sabe de uma solução alternativa para fazer isso funcionar? Eu basicamente quero direcionar dinamicamente qualquer banco de dados com uma instrução sql maior que 4000 nchars.

Estou tentando resolver um problema usando o ms-replication e não tenho certeza se é a melhor opção para o problema em questão.

Problema: É preciso replicar todas as inserções iniciais e todas as atualizações, ignorando exclusões para uma assinatura. Essencialmente, um banco de dados de destino que continua a crescer e os dados nunca são removidos, mesmo quando os registros são excluídos na origem. Ser capaz de restaurar na origem sem destruir o destino seria um grande bônus.

Posso fazer a maior parte disso funcionar via ms-replication configurando ignore deletes em todos os artigos de tabela e configurando o modo de preparação de tabela para do-nothing. Isso lidaria com a manutenção de dados durante restaurações e manteria registros excluídos no destino, no entanto, como lidar com inserções que resultariam em violações de PK após uma restauração na origem. Não consigo encontrar nenhuma maneira documentada de "Ignorar registros existentes durante a inserção" e, mesmo que haja um hack, isso reduziria muito o desempenho da replicação?

NOTA: Devo mencionar que dar suporte a um backup não significa voltar a um ponto no tempo há muito tempo, significa apenas que se ocorrer uma falha grave, o banco de dados de origem precisa ser restaurado usando a cadeia de log mais recente. Haveria no máximo duas horas de inserções para lidar. Isso provavelmente poderia ser resolvido excluindo manualmente os registros no destino que não estão na origem, no entanto, eu estava tentando evitar isso completamente.

Não estou muito familiarizado com a base do processo de snapshot, além de tentar carregar dados via BCP. Se o modo de preparação da tabela for do nothing, há uma opção para pular opcionalmente a fase de "carregamento em massa" do snapshot e então continuar com o leitor de log normalmente após o snapshot ter sido aplicado?

Usando replicação transacional, um banco de dados criptografado (criptografia TDE) pode ser replicado para um destino não criptografado sem problemas?

há uma configuração de publicador --> distribuidor --> assinante e tudo foi bem configurado neste sistema e alguns testes simples de adição de algumas tabelas e reposicionamento de tabelas parecem funcionar bem.

O problema é que temos mais de 6 mil artigos por banco de dados e muitos bancos de dados em um sistema multilocatário. O sistema está em operação há mais de 20 anos e quase todos os bancos de dados pegaram pilhas e visualizações ou procedimentos armazenados não compiláveis. Não podemos apontar e clicar cada vez que um banco de dados é restaurado e/ou precisa ser republicado/publicado; portanto, o seguinte está sendo testado.

Entendo que isso pode ser programado, mas estamos seguindo este conselho para reposicionar tabelas .

O teste inicial

• Crie a assinatura com 1 artigo tblAccountingBatch.
• Execute o script abaixo para adicionar todas as tabelas com uma PK e todos os artigos compiláveis.
• Execute novamente o agente de snapshot e pegue as novas tabelas.

Os resultados

• A interface do usuário mostra que todos os ~5 mil artigos estão ativos e selecionados na sub.
• O snapshot é executado e gera 1 artigo para a tabela inicial, tblAccountingBatch.
• EXEC sp_helpsubscription mostra apenas uma tabela - tblAccountingBatch.
• Tento criar um script para adicionar outro que esteja marcado na interface do usuário usando sp_addarticle e recebo um erro informando que o artigo já foi adicionado.

Alguém sabe onde a desconexão pode estar. O snapshot não parece ver os artigos adicionados após a criação do sub.

Me deparei com este KB que descreve algo muito semelhante; no entanto, o DBA diz que esses patches foram aplicados nos ambientes de teste.

Script para adicionar todos os artigos válidos após a criação da assinatura

SET NOCOUNT ON

USE VP_UPS_V05
GO

DECLARE @PublicationName NVARCHAR(200) = 'UPS_PUB'

EXEC sp_changepublication @publication = @PublicationName, @property = 'allow_anonymous' , @value = 'false' 
EXEC sp_changepublication @publication = @PublicationName, @property = 'immediate_sync' , @value = 'false' 


IF(CHARINDEX('VP_',DB_NAME()) = 0 OR CHARINDEX('_V05',DB_NAME()) =0 ) BEGIN
    RAISERROR('This script can only be applied against a Client database', 16, 1)
    RETURN
END


DECLARE @Debug BIT = 0

DECLARE @FullName nvarchar(1000);
DECLARE @ShortName nvarchar(300)
DECLARE @ObjectType nvarchar(300)
DECLARE @HasPrimaryKey BIT
DECLARE @Sql nvarchar(1000);
DECLARE @Result int;


DECLARE @Exceptions TABLE(ShortName NVARCHAR(300), FullName NVARCHAR(300),ObjectType NVARCHAR(300), Result INT, ErrorMessage NVARCHAR(MAX))


DECLARE @IgnoredTables TABLE (TableName NVARCHAR(100))
INSERT INTO @IgnoredTables VALUES
    ('tblAccountingBatch')
    

DECLARE ObjectCursor CURSOR FOR
SELECT 
    ShortName,LongName,ObjectType,HasPrimaryKey
FROM
(
    SELECT  
        OBJECT_NAME(o.object_id) ShortName , 
        QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(o.object_id)) LongName, 
        CASE WHEN type_desc = 'USER_TABLE' THEN CASE WHEN  OBJECTPROPERTY(o.object_id,'tablehasprimaryKey') = 1 THEN 1 ELSE 0 END ELSE 0 END AS HasPrimaryKey,
        type_desc AS ObjectType,
        CASE 
            WHEN type_desc='USER_TABLE' THEN 0
            WHEN type_desc='VIEW' THEN 1 
            WHEN type_desc='SQL_SCALAR_FUNCTION' THEN 2
            WHEN type_desc='SQL_TABLE_VALUED_FUNCTION' THEN 3
            WHEN type_desc='SQL_INLINE_TABLE_VALUED_FUNCTION' THEN 4
            WHEN type_desc='SQL_TRIGGER' THEN 5
            WHEN type_desc='SQL_STORED_PROCEDURE' THEN 6
            ELSE
                7
        END AS ProcessOrder
    FROM 
        sys.objects o
        LEFT OUTER JOIN @IgnoredTables IgnoredTable ON type_desc = 'USER_TABLE' AND IgnoredTable.TableName = OBJECT_NAME(o.object_id)
    WHERE 
        type_desc IN ('USER_TABLE','SQL_STORED_PROCEDURE','SQL_TRIGGER','SQL_SCALAR_FUNCTION','SQL_TABLE_VALUED_FUNCTION','SQL_INLINE_TABLE_VALUED_FUNCTION','VIEW')    
        AND 
        ISNULL(OBJECTPROPERTY(o.object_id, 'IsSchemaBound'), 0) = 0
        AND
        SCHEMA_NAME(o.schema_id)='dbo'
        AND
        IgnoredTable.TableName IS NULL
        AND
        ((type_desc = 'USER_TABLE' AND OBJECT_NAME(o.object_id) LIKE 'tbl%')OR(type_desc<>'USER_TABLE'))
)AS X 
Order BY ProcessOrder, ShortName;

--RETURN

OPEN ObjectCursor;

DECLARE @TotalObjects INT = @@CURSOR_ROWS
DECLARE @CurrentObject INT = 0

FETCH NEXT FROM ObjectCursor INTO @ShortName, @FullName, @ObjectType, @HasPrimaryKey

DECLARE @IsError BIT = 0
DECLARE @CompileResult BIT = 0
DECLARE @ErrorMessage NVARCHAR(MAX)

DECLARE @InsertCommand NVARCHAR(300)
DECLARE @DeleteCommand NVARCHAR(300)
DECLARE @UpdateCommand NVARCHAR(300)
DECLARE @ArticleType NVARCHAR(300)

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CurrentObject = @CurrentObject + 1
    PRINT 'Processing Object '+CAST(@CurrentObject AS VARCHAR(10))+'/'+CAST(@TotalObjects AS VARCHAR(10))+' - '+@ObjectType+' '+@ShortName
     
    SET @IsError = 0
    SET @ErrorMessage = ''

    IF(@ObjectType = 'USER_TABLE') BEGIN 
        IF(@HasPrimaryKey = 0 ) BEGIN
            SET @IsError = 1
            SET @ErrorMessage = 'Missing Primary Key'   
        END     
    END ELSE BEGIN      
        SET @Sql = N'EXEC sp_refreshsqlmodule ''' + @FullName + '''';       
        BEGIN TRY
            EXEC @CompileResult = sp_executesql @Sql;
            IF @CompileResult <> 0 RAISERROR('Failed', 16, 1);
        END TRY
        BEGIN CATCH
            SET @IsError = 1
            SET @ErrorMessage = ERROR_MESSAGE()             
        END CATCH
    END

    IF(@IsError = 0 AND @Debug=0) BEGIN
        BEGIN TRY           
            IF(@ObjectType = 'USER_TABLE') BEGIN

                SET @InsertCommand = 'CALL sp_MSins_dbo_'+@ShortName
                SET @DeleteCommand = 'CALL sp_MSdel_dbo_'+@ShortName
                SET @UpdateCommand = 'CALL sp_MSupd_dbo_'+@ShortName

                exec sp_addarticle 
                    @publication = @PublicationName, 
                    @article = @ShortName, 
                    @source_owner = 'dbo', 
                    @source_object = @ShortName, 
                    @type = 'logbased', 
                    @description = null, 
                    @creation_script = null, 
                    @pre_creation_cmd = 'drop', 
                    @schema_option = 0x000000000803509F, 
                    @identityrangemanagementoption = 'manual', 
                    @destination_table = @ShortName, 
                    @destination_owner = 'dbo', 
                    @vertical_partition = 'false', 
                    @ins_cmd = @InsertCommand, 
                    @del_cmd = @DeleteCommand, 
                    @upd_cmd = @UpdateCommand
            END ELSE IF (@ObjectType IN ('SQL_INLINE_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION','SQL_STORED_PROCEDURE','SQL_TABLE_VALUED_FUNCTION','VIEW')) BEGIN
                IF(@ObjectType='SQL_STORED_PROCEDURE') 
                    SET @ArticleType = 'proc schema only'
                ELSE IF(@ObjectType = 'VIEW')   
                    SET @ArticleType = 'view schema only'
                ELSE
                    SET @ArticleType ='func schema only'
                    
                exec sp_addarticle 
                    @publication =@PublicationName, 
                    @article = @ShortName, 
                    @source_owner = 'dbo', 
                    @source_object = @ShortName, 
                    @type = @ArticleType, 
                    @description = null, 
                    @creation_script = null, 
                    @pre_creation_cmd = N'drop', 
                    @schema_option = 0x0000000008000001, 
                    @destination_table = @ShortName, 
                    @destination_owner = 'dbo'

            END
        END TRY
        BEGIN CATCH
            SET @IsError = 1
            SET @ErrorMessage = 'Could Not Add Article to Replication - '+ERROR_MESSAGE()  
            IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        END CATCH
    END 
    
    IF(@IsError = 1)  
        INSERT INTO @Exceptions SELECT  @ShortName, @FullName, @ObjectType, 0, @ErrorMessage
        
    FETCH NEXT FROM ObjectCursor INTO @ShortName, @FullName, @ObjectType, @HasPrimaryKey
END

CLOSE ObjectCursor;
DEALLOCATE ObjectCursor; 

SELECT * FROM @Exceptions

Preciso ser capaz de iterar todas as tabelas e emitir um TRUNCATEcomando, porém, não quero que o loop seja interrompido por bloqueio. Essencialmente, estou procurando a melhor maneira de determinar se um SCH-Mbloqueio pode ser obtido antes de emitir um comando truncado.

Microsoft SQL Server 2019 (RTM-CU26) - 15.0.4365.2 (X64) Standard Edition (64 bits) no Windows Server 2016 Datacenter 10.0

Existe uma tabela que parece ter atualizações constantes. Há também um procedimento armazenado que é executado periodicamente e itera por tabelas e tenta truncar uma partição de dados usando o comando abaixo:

TRUNCATE TABLE [dbo].[Table] WITH (PARTITIONS (10))

O procedimento de loop sempre fica travado na mesma tabela, a tabela com atualizações constantes falha mesmo quando os dados não estão sendo atualizados na partição destinada à exclusão.

O processo de chamada não pode ser atualizado. Existe um truque para vincular as atualizações a uma partição fora da modificação da consulta ou alguma outra maneira de forçar as partições que não mudam a não serem bloqueadas/bloqueadas?

Um bloqueio de página causará falha no truncamento da partição? Por exemplo, duas partições poderiam compartilhar uma única página e tentar excluir com a partição (2) falha porque algum processo está atualizando dados na partição 1 com a qual 2 está compartilhando uma página?

CREATE TABLE [dbo].[Table]
(
      [TableID] INT NOT NULL
      ...
    , [SourceID] NVARCHAR(4000) NOT NULL
    , CONSTRAINT [PK_Table_TableID] PRIMARY KEY NONCLUSTERED([TableTableID] ASC , [SourceID] ASC) ON psSourceID(SourceID)
    , CONSTRAINT [CIX_Table_TableID] UNIQUE CLUSTERED ([SourceID] ASC, [TableID] ASC) ON psSourceID(SourceID)
)
ON psSourceID(SourceID)
GO

ALTER TABLE [dbo].[Table] SET (LOCK_ESCALATION = AUTO)
GO

O TRUNCATEcomando está sendo bloqueado por uma sessão que executa atualizações na tabela. Em ambientes inferiores, um processo de bloqueio nunca foi atingido após muitas tentativas, portanto, presumia-se que WITH PARTITIONsó colocaria bloqueios de metadados por partição, não por toda a tabela.

Não sou fã de gatilhos e SQL dinâmico, porém, o que estou trabalhando requer ambos.

CREATE TRIGGER [dbo].[GenerateDynamicFormItemViews] ON [dbo].[tblFormItems] AFTER INSERT, UPDATE
AS
BEGIN   
    SET NOCOUNT ON;
    DECLARE @DatabaseName NVARCHAR(100)
    DECLARE @FormItemID INT

    DECLARE db_cursor CURSOR FOR
    SELECT SourceID,FormItemID from Inserted

    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @DatabaseName, @FormItemID

    WHILE @@FETCH_STATUS = 0  
    BEGIN  

        BEGIN TRY    
            EXEC spAddEditFormItemView @FormItemID, @DatabaseName WITH RESULT SETS NONE
        END TRY    
        BEGIN CATCH 
            INSERT INTO AdminErrorLog(SourceID, ErrorNumber, ErrorState, ErrorSeverity, ErrorProcedure, ErrorLine, ErrorMessage, ErrorDateTime) 
            SELECT @DatabaseName, ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE(), GETDATE()
        END CATCH     

        FETCH NEXT FROM db_cursor INTO @DatabaseName, @FormItemID
    END

    CLOSE db_cursor  
    DEALLOCATE db_cursor
END 

Eis aqui o que está acontecendo.

1. Uma tarefa de replicação do AWS DMS está replicando dados em lotes de 10.000. A implementação da replicação é uma caixa preta, mas parece haver transações aninhadas nas conexões.
2. EXEC spAddEditFormItemView chama um procedimento armazenado que executa o SQL dinâmico e gera erros em um registro.
3. O bloco CATCH nunca é executado
4. O erro nunca chega ao AdminErrorLog.
5. O AWS Batch apresenta erros e nunca é confirmado e toda a tarefa falha.
6. Em eventos estendidos, o seguinte erro sql é detectado. Este é o erro que falhou na tarefa. Eu li sobre condições em que os comandos não podem estar em estado de confirmação ou reversão, mas realmente não entendi totalmente esse conceito.

mensagem: A transação atual não pode ser confirmada e não pode suportar operações gravadas no arquivo de log. Reverta a transação.

gravidade: 16

Alguém pode explicar por que o bloco catch não está sendo capturado e por que o aplicativo cliente está recebendo, o que eu acho, uma exceção sql e revertendo tudo. Meu palpite é que as exceções dinâmicas de SQL estão sendo tratadas de maneira diferente e a transação de gatilho implícita está sendo revertida em vez de capturar o erro. Além disso, alguém sabe uma maneira de evitar que a exceção seja propagada?

Aposto que a solução mais segura seria modificar o gatilho para amontoar os comandos sql em uma tabela e, em seguida, fazer com que um trabalho do agente sql procure comandos para serem executados a cada minuto ou mais.

EDIT - Adicionando procedimento para recriar: No SSSM:

EXEC [spAddEditFormItemView] 30032,'VP_BENCHMARKING_V05'

Comandos concluídos com sucesso.

Prazo de conclusão: 2023-11-27T16:11:18.1762097-05:00

No gatilho ele força um rollback com uma mensagem de erro horrível.

ALTER PROCEDURE [dbo].[spAddEditFormItemView] ( 
    @FormItemID int,
    @Schema nvarchar(100)
)
AS
    DECLARE @SQL NVARCHAR(MAX) = 'e2e2e2e2e'
    BEGIN TRY   
        EXEC (@SQLCommand)      
    END TRY
    BEGIN CATCH
        DECLARE @X INT
    END CATCH

EDIT: agora posso duplicar no SSMS.

Há um cenário em que muitos, mais de 175 bancos de dados devem ser combinados em um grande banco de dados em nuvem e isso deve ser suportado em vários ambientes, desenvolvimento, teste, produção de estágio, etc. Atualmente, a chave primária é baseada no identificador do cliente, que é o banco de dados de onde vieram esses dados. A segurança baseada em linha será usada neste cenário com base na mesma coluna. Algumas das tabelas maiores chegarão a quase 1 bilhão de linhas.

O problema é que, principalmente nos ambientes inferiores, as restaurações são comuns. Para acomodar o truncamento de grandes faixas de dados em tempo hábil, tenho pesquisado o particionamento com base no identificador do cliente (ou seja, "COKE"|"PEPSI" etc.), que também é o PK de todas as tabelas. Isso permitiria a movimentação de dados para dentro e para fora e, possivelmente, uma indexação mais eficiente. A chave do cliente pode ser usada na função de particionamento? Tenho experiência com partição baseada em data à direita, no entanto, não tenho certeza de como é gerenciável ou possível alterar uma função de partição adicionando uma nova partição entre duas existentes?

Talvez isso possa ser resolvido usando um identificador sequencial para clientes e, à medida que novos clientes entram, eles obtêm o próximo id sequencialmente e uma nova partição é adicionada à direita? O particionamento por nome de cliente é prático no Sql Server.

Existem N bancos de dados com estruturas de tabelas idênticas em um sistema multilocatário e multibanco de dados. O desejo é replicar uma tabela (s) desses bancos de dados em uma única tabela maior em um banco de dados OLAP, presumo que possa funcionar.

-Usando replicação transacional-

1. Recrie o PK para todos os artigos de tabela no editor, incluindo um campo recém-adicionado que identifica o banco de dados.

2. Defina a opção "Quando o artigo existir" para Não excluir e usar o filtro de linha (que inclui o identificador do banco de dados).

3. Adicione um filtro de linha para cada tabela usando o identificador exclusivo do banco de dados.

Minha pergunta é, dado o cenário acima, se um novo instantâneo for criado para um editor, os dados obsoletos do assinante seriam removidos e apenas para esse editor? Receio que seja isso que a tabela suspensa e a recriação pretendem resolver :/

Em termos simples, se eu tiver

Se um novo instantâneo para uma publicação chamada Database007 foi reinicializado. Todos os dados da assinatura do Database007 seriam removidos e reidratados para o Database007 ou eu atingiria uma violação de PK.

Também tenho pesquisado o CDC, no entanto, isso não parece suportar um cenário de replicação N-1. Além disso, sinta-se à vontade para descartar quaisquer outras ideias.

Estou pesquisando uma tabela de relatórios DW que crescerá muito. Para simplificar, vou mostrar a tabela da seguinte forma:

BigTable
--------
TableID INT IDENTITY NOT NULL,
CompanyName NVARCHAR(100) NOT NULL

Cada consulta usará o nome da empresa para consultar em uma partição de dados (não em uma partição física).

Como essa tabela pode conter mais de um bilhão de linhas e cada empresa terá uma distribuição de dados bastante uniforme, as consultas por empresa devem ser o mais rápidas possível. Estou na fase de configurar alguns testes, mas antes de fazê-lo pensei em perguntar e ver se seria uma perda de tempo.

Minha ideia era determinar que, se a partição de dados de cada empresa fosse colocada lado a lado no disco por meio de um índice clusterizado, a recuperação de dados seria mais rápida do que apenas usar um índice não clusterizado para cobrir CompanyName.

Exemplo 1: Aqui está a variação em que a coluna IDENTITY é o PK, mas não CLUSTERED. O CompanayName e TableID se combinam para formar o Índice Clusterizado para que os dados sejam ordenados por empresa no disco.

CREATE TABLE [dbo].[BigTable](
    [TableID] [int] IDENTITY(1,1) NOT NULL,
    [CompanyName] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_BigTable] PRIMARY KEY NONCLUSTERED 
(
    [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE UNIQUE CLUSTERED INDEX [CLUSTERED_ByCompanyName_TableID] ON [dbo].[BigTable]
(
    [CompanyName] ASC,
    [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

E aqui está a maneira tradicional de criar tabelas com índices de cobertura.

CREATE TABLE [dbo].[BigTable](
    [TableID] [int] IDENTITY(1,1) NOT NULL,
    [CompanyName] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_BigTable] PRIMARY KEY CLUSTERED 
(
    [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_ByCompanyName] ON [dbo].[BigTable]
(
    [CompanyName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

Alguém sabe imediatamente se haveria alguma melhoria de desempenho a ser obtida usando o primeiro exemplo sobre o segundo exemplo?

EDIT: Estou inclinado a usar um índice clusterizado com a empresa. O TableID é apenas um campo de autoincremento para usar como PK se uma linha precisar de uma referência exclusiva. Eu sinto que as buscas/varreduras de índice agrupadas são mais rápidas do que as buscas/busca(s) de índice.

Eu gostaria que você pudesse particionar facilmente ou fragmentar com base em algo como companyid.

Uma consulta básica seria na forma de

SELECT
    SUM(FieldA) OVER (PARTITION BY ...) a,
    COUNT(1) OVER (PARTITION BY...) b
    ...       
FROM
    BigTable 
WHERE
    CompanyName = 'NABISCO'
GROUP BY
   ....
ORDER BY
   ....

Faça as seguintes consultas:

DECLARE @X VARCHAR(200) = '1,2,3,4'

SELECT
    *,
    dbo.aUserDefinedScalarFunction(4) AS ScalarValue
FROM
   MyTable T
   INNER JOIN dbo.aUserDefineTableFunction(@X) A ON T.SomeID=A.SomeID
WHERE 
  (T.ID1 IS NULL OR T.ID1 IN (SELECT [value] FROM STRING_SPLIT(@X,',')))
  AND
  (T.ID2 IS NULL OR T.ID2 IN (SELECT Value FROM dbo.MySplitterFunction(@X))

Normalmente crio #tempTables indexadas para as WHEREcondições acima, descobri que para ter um desempenho melhor em grandes conjuntos de dados. No entanto, ainda não consigo encontrar respostas definitivas para as seguintes perguntas:

1. O analisador de consultas otimizará aUserDefinedScalarFunction(4) como ScalarValue ou será avaliado para cada registro?

2. O INNER JOIN dbo.aUserDefineTableFunction(@X) será materializado em uma tabela temporária uma vez ou será executado para cada registro? A função retorna table (não uma variável de tabela).

3. O resultado de SELECT [value] FROM STRING_SPLIT(@X,',') é otimizado ou é avaliado para cada comparação?

4. O resultado de SELECT Value FROM dbo.MySplitterFunction(@X) é otimizado ou é avaliado durante cada comparação?

Estou tentando executar um script do PowerShell que usa Microsoft.SqlServer.Management.IntegrationServices em um pipeline do Azure. O pacote SSIS é invocado com êxito, no entanto, o pacote falha quando uma conexão remota é tentada. Eu sinto que isso está relacionado ao salto duplo, mas não consigo descobrir como resolver o problema.

Build Server(Domain\ServiceUser) --> Integrations Server(Domain\ServiceUser) --> Database Server(Domain\ServiceUser)

NOTA: Domain\ServiceUser tem direitos ao SSISDB nessa instância e ao banco de dados remoto.

My Project Import:Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.".

Para ajudar a diagnosticar se o problema está relacionado à segurança. Estou usando o procedimento abaixo para tentar invocar o pacote. Quando faço isso, recebo o seguinte erro:

--EXECUTE AS LOGIN  = 'Domain\ServiceUser'  
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx',
    @execution_id=@execution_id OUTPUT,
    @folder_name='ProjectFolder',
    @project_name='ProjectFolderImport',
    @use32bitruntime=False,
    @reference_id=Null
EXEC [SSISDB].[catalog].[start_execution] @execution_id

The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.
Msg 6522, Level 16, State 1, Procedure start_execution_internal, Line 0 [Batch Start Line 0]
A .NET Framework error occurred during execution of user-defined routine or aggregate "start_execution_internal": 
System.Data.SqlClient.SqlException: The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.
System.Data.SqlClient.SqlException: 

E quando eu descomento o EXECUTE AS, recebo o seguinte:

Cannot execute as the server principal because the principal "Domain\ServiceUser" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Eu sei que o pacote pode fazer uma conexão remota com o servidor db porque quando eu crio uma tarefa para executar o pacote e seleciono "Executar como" SSIS Package Execution Proxy User "SSIS_Exec" ele funciona bem. Eu preciso descobrir como fazer com que meu código ou procedimento armazenado faça algo semelhante para ser executado como SSIS_Exec.

Normalmente evito operações de cursor como a placa, no entanto, encontrei meu primeiro problema em que o uso de um cursor supera uma consulta. Então sou forçado a usá-lo.

Eu criei um procedimento armazenado de relatório elaborado que é usado por muitos usuários. Eu uso um cursor para iterar uma lista e inserir dados em um @TEMP_TABLE, finalmente selecionando a tabela temporária como conjunto de resultados.

O cursor é usado da seguinte maneira:

DECLARE HIGHLIGHTS_REPORT_CURSOR CURSOR LOCAL FOR
SELECT RowID, UserID,GradeID,ClassID,MenuSetID,CurrentSequence FROM @DATA   
OPEN HIGHLIGHTS_REPORT_CURSOR
FETCH NEXT FROM HIGHLIGHTS_REPORT_CURSOR INTO @RowID,@UserID,@GradeID,@ClassID,@MenuSetID,@CurrentSequence
WHILE(@@FETCH_STATUS=0)BEGIN
    ...
    FETCH NEXT FROM HIGHLIGHTS_REPORT_CURSOR INTO @RowID, @UserID,@GradeID,@ClassID,@MenuSetID,@CurrentSequence
END
CLOSE HIGHLIGHTS_REPORT_CURSOR
DEALLOCATE HIGHLIGHTS_REPORT_CURSOR

Então, está tudo bem, o relatório passou pelo controle de qualidade e sem problemas até que o usuário baseou-se agora, estou recebendo logs de exceções enviados para mim esporadicamente com o seguinte erro:

Server Log Reference ID : b91a8f4a-b944-4355-bba3-2855fd126c2b
Message : A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
Source : HighlightsReport

Stack Trace :    at System.Data.SqlClient.SqlConnection.OnError(SqlExceptionexception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

Posso afirmar com 100% de confiança que não há outro cursor com o mesmo nome. Não há CLOSE\DEALLOCATE CURSORchamadas erradas entre BEGINe END.

Aprendi que o SQL Server padroniza a propriedade de banco de dados "cursor padrão" GLOBALdurante a instalação. Como os procedimentos de relatório são os únicos SPs em que os cursores são usados, alterei o "cursor padrão" para LOCAL.

Isso não ajudou:(Talvez "cursor padrão" seja uma configuração que exija uma reinicialização. Em vez de reiniciar, usei o LOCALatributo da CURSORdeclaração.

Isso garante que meu cursor tenha como escopo o SP, mas ainda receba o erro acima???

Estou começando a pensar que este é um problema de simultaneidade com várias conexões atingindo o SP ao mesmo tempo. Isso explicaria o comportamento esporádico e por que isso não foi detectado durante o controle de qualidade.

Seria possível que duas conexões chamassem o procedimento aproximadamente ao mesmo tempo em que uma conexão atinge o DEALLOCATE HIGHLIGHTS_REPORT_CURSORlogo antes da segunda conexão atingir o FETCH NEXT FROM HIGHLIGHTS_REPORT_CURSOR INTObloco?

Presumi que usar LOCAL garantisse que cada conexão teria sua cópia, mas parece que não é o caso? Alguma ideia?

Atualizada. Isso é bizarro, recebi três mensagens de exceção empilhadas em uma. Gostaria de saber se uma conexão de cliente chamando o mesmo sp em rápida sucessão causa algo semelhante.

Server Log Reference ID : b91a8f4a-b944-4355-bba3-2855fd126c2b
Message : A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
          A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
          A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
Source : HighlightsReport

Atualização 2 A parte que me faz pensar que este é um problema de simultaneidade do usuário é o fato de que esses erros sempre vêm em grupos de dois. Recebo um relatório de exceção com o erro mencionado acima de dois usuários diferentes ao mesmo tempo.

No SQL Server 2008, criei um Sql Server Agent Alert na semana passada para monitorar eventos de impasse. O Alert chama um job sem agendamento enviando o token gráfico de impasse xml obtido por WMI SELECT * FROM DEADLOCK_GRAPH. O trabalho chama um SP que armazena o gráfico e um e-mail é enviado.

Tenho monitorado as esperas do Sql Server e descobri que depois de implementar o alerta descrito acima, as categorias de espera para BROKER_TASK_STOP e SQLTRACE_WAIT_ENTRIES dispararam. Esses valores passaram de não estar no radar para contribuir com 42,59% e 5,11% do total de esperas, respectivamente.

Devo me preocupar? Eu li em algum lugar que BROKER_TASK_STOP altos podem não ser uma preocupação real.

Recentemente, atualizamos um servidor para SQL Server 2008 R2. O servidor estava executando o SQL Server 2005. Estamos usando

• Replicação Transacional
• Data Warehouse de gerenciamento e coletor de dados no servidor de produção

Estamos começando a ver erros de bloqueio no log do aplicativo. Isso não acontecia no SQL 2005.

No banco de dados de relatórios, posso replicar um impasse usando uma consulta "ad-hoc" com uma junção interna e varredura de tabela em uma tabela ativa. No entanto, posso executar procedimentos de relatório que levam minutos para serem concluídos sem problemas.

O sql 2008 é mais sensível a impasses no sql 2005? Vou começar a desfazer algumas das mudanças que implementamos e testamos. Antes de seguir esse caminho, gostaria de ver se mais alguém notou o aumento de impasses ao atualizar para o 2008 R2.

Tenho monitorado o crescimento de arquivos por meio do coletor de dados no sql server 2008 r2 por duas semanas. O banco de dados está crescendo consistentemente em torno de 35(MB)/dia. O banco de dados ainda não atingiu o tamanho inicial de 2 GB.

O crescimento automático dos arquivos de banco de dados está definido para 5 MB e eu gostaria de tentar uma abordagem diferente, por isso estou procurando sugestões e/ou comentários.

Há uma tarefa de ajuste que é executada toda semana na noite de domingo à 1h30. A tarefa irá:

• Verifique a integridade do banco de dados
• Encolher o arquivo de log - (Isso é bom porque o modo de log é simples)
• Encolher banco de dados
• Reorganizar Índice
• Índice de reconstrução
• Atualizar estatísticas
• Limpar histórico

Gostaria de adicionar mais duas etapas ao plano de ajuste semanal:

1. Aumente o arquivo de banco de dados em 500 MB se o espaço usado atingir um determinado limite ou tamanho total.
2. Aumente o arquivo de log em 250 MB (após a redução) se o espaço usado atingir um determinado limite de tamanho total.

Ao colocar a carga de crescimento em horas off-line, espero ganhar desempenho reduzindo o número de eventos de crescimento automático durante cargas pesadas.

Eu tenho duas perguntas relacionadas a arquivos de crescimento automático.

• O melhor lugar para colocar as etapas de crescimento do arquivo seria antes das etapas atuais ou depois?
• Se eu usar o ALTER DATABASE|MODIFY FILEpara aumentar o arquivo, como posso determinar se SpaceUsedInFile >= (TotalFileSpace-@AllowanceThreshold)?