Perguntas[sql-server-2017](dba)

Depois de mover um banco de dados de um servidor para outro, começamos a ter um problema:

> Msg 15581, Level 16, State 7, Procedure xxxxx, Line 24 [Batch Start Line 2]
Please create a master key in the database or open the master key in the session before performing this operation.

Msg 15315, Level 16, State 1, Procedure xxxxx, Line 62 [Batch Start Line 2]
The key '<CustomKeyName>' is not open. Please open the key before using it.

O procedimento em questão tem:

 OPEN SYMMETRIC KEY MySymKey
 DECRYPTION BY CERTIFICATE MySymCert; 

Posso verificar que depois de mover o banco de dados, posso ver a symmetric keye a certificateconsultando sys.symmetric_keyse sys.certificates.

Nós, sim, não temos a chave mestra. Estou curioso para saber se esse processo funcionaria:

1. No servidor antigo (no escopo do BD):

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'a_cool_pass';

2. backup do BD novamente

3. Restaurar o BD no novo servidor novamente (com substituição)

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'the_cool_Pass'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

essa seria uma solução adequada para essa situação?

OU posso simplesmente remover a symmetric_key e o cert e recriá-los?

edição: REGENERATINGa chave mestra passou no servidor antigo, backup/restauração no novo, OPEN MASTER KEY DECRYPTION BY PASSWORD =tive o problema The key is not encrypted using the specified decryptor.(o que eu achei estranho. Tentarei novamente amanhã.

(precisaria fazer uma restauração de backup do certificado?

Eu tenho um procedimento armazenado do SQL Server que essencialmente 'nivela' um banco de dados complexo em uma tabela única e simplificada em um banco de dados separado para permitir que os usuários consultem os dados sem precisar entender a estrutura do banco de dados. O SP trunca a tabela de destino, insere a chave primária e outros campos e, em seguida, executa uma série de atualizações para preencher todos os outros campos da tabela usando funções de usuário e diversas consultas para formatar os dados. Existem aprox. 9 milhões de linhas na tabela e o SP levam cerca de 2,5 horas para ser executado e durante esse tempo o arquivo de log cresce para mais de 30 GB. O banco de dados está em um servidor Azure com espaço em disco limitado e recentemente o SP travou porque o arquivo de log ficou sem espaço.

O banco de dados de destino está no modo de recuperação simples e o SP é executado durante um fim de semana, portanto, não há outros processos ou usuários utilizando-o. O SP não contém nenhuma transação ou commit e não fragmenta o trabalho, apenas executa do início ao fim, inserindo, atualizando (e ocasionalmente excluindo) as linhas. Como posso torná-lo mais eficiente e usar menos espaço no arquivo de log? Deveria usar transações, confirmar ou reduzir o arquivo de log à medida que ele é executado? Ou alguma outra coisa?

Tenho a infraestrutura mostrada no diagrama abaixo.

O Servidor Web 1 está fora do domínio do Windows. O Web Server 2 está dentro do domínio do Windows.

SQL Server 1 e 2 estão dentro do domínio.

Há uma conexão de servidor vinculada do SQL Server 1 ao SQL Server 2.

Ambos os servidores SQL aceitam apenas a autenticação do Windows, não a autenticação SQL.

Ambos os SQL Servers possuem uma configuração Kerberos SPN com delegação irrestrita.

Um usuário de domínio no Web Server 2 tem um login SQL configurado no SQL Server 1 e no SQL Server 2 e pode consultar dados no SQL Server 1 e no SQL Server 2 por meio do servidor vinculado.

Um usuário local no Servidor Web 1 tem um logon local com o mesmo nome e senha configurado no SQL Server 1 e no SQL Server 2 e pode consultar dados no SQL Server 1, mas não no SQL Server 2 por meio do servidor vinculado. O seguinte erro é fornecido no log do SQL Server:

Falha no login do usuário 'NT AUTHORITY\ANONYMOUS LOGON'. Motivo: Não foi possível encontrar um login correspondente ao nome fornecido.

A questão: o Web Server 1, fora do domínio do Windows, pode consultar dados com êxito do SQL Server 2 por meio da conexão do Linked Server no SQL Server 1? Se sim, como?

A consulta abaixo parece simples e direta, mas produz resultados inesperados.

CREATE TABLE #NUMBERS
(
    N BIGINT
);

INSERT INTO #NUMBERS VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9)
;



WITH
A AS
(   
    -- CHOOSE A ROW AT RANDOM
    SELECT   TOP 1 *
    FROM     #NUMBERS            
    ORDER BY NewID()           
),
B AS
(
    SELECT A.N AS QUANTITY, 'METERS' AS UNIT FROM A
    
    UNION ALL

    SELECT A.N*100 AS QUANTITY, 'CENTIMETERS' AS UNIT FROM A
    
    UNION ALL

    SELECT A.N*1000 AS QUANTITY, 'MILLIMETERS' AS UNIT FROM A
    
    UNION ALL

    SELECT A.N*1000000 AS QUANTITY, 'MICRONS' AS UNIT FROM A

    UNION ALL

    SELECT A.N*1000000000 AS QUANTITY, 'NANOMETERS' AS UNIT FROM A
)
SELECT   *
FROM     B
ORDER BY B.QUANTITY
;

Eu esperaria que ele executasse o CTE A uma vez e, em seguida, carregasse esses resultados para o CTE B para produzir resultados mais ou menos assim:

No entanto, produz resultados como este:

Isso significa que ele está voltando e executando CTE A cinco vezes, uma vez para cada menção de A em CTE B. Isso não é apenas indesejado e não intuitivo, mas também parece desnecessariamente ineficiente.

O que está acontecendo e como um gênio CTE o reescreveria para produzir os resultados desejados?

BTW, as páginas de documentação da Microsoft sobre CTEs contêm esta declaração enigmática que pode ou não estar relacionada:

Se mais de um CTE_query_definition for definido, as definições de consulta deverão ser unidas por um destes operadores de conjunto: UNION ALL, UNION, EXCEPT ou INTERSECT.

Por fim, reescrever a consulta para eliminar CTE B não ajudou:

WITH
A AS
(   
    -- CHOOSE A ROW AT RANDOM
    SELECT   TOP 1 *
    FROM     #NUMBERS            
    ORDER BY NewID()           
)
SELECT   *
FROM     (
          SELECT A.N AS QUANTITY, 'METERS' AS UNIT FROM A
    
          UNION ALL

          SELECT A.N*100 AS QUANTITY, 'CENTIMETERS' AS UNIT FROM A
    
          UNION ALL

          SELECT A.N*1000 AS QUANTITY, 'MILLIMETERS' AS UNIT FROM A
    
          UNION ALL

          SELECT A.N*1000000 AS QUANTITY, 'MICRONS' AS UNIT FROM A

          UNION ALL

          SELECT A.N*1000000000 AS QUANTITY, 'NANOMETERS' AS UNIT FROM A

         ) AS B
ORDER BY B.QUANTITY
;

Eu tenho o assembly CLR no SSDT e para implantar isso ele tem que ser confiável. Pelo que entendi, existem 4 opções de como fazer isso

Primeira opção, use TRUSTWORTHY

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
  
ALTER DATABASE SourceDatabase SET TRUSTWORTHY ON;

Segunda opção, desative a segurança estrita

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
  
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;

Terceira opção, assine a montagem com chave ou certificado

Seems complicated and I was not able to manage that yet. I will appreciate the instructions, because the workflow is not clear here.

Quarta opção, usesp_add_trusted_assembly

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;

declare @assembly varbinary(max) = 0x4D5A90000300000004000000FFFF0000... -- I have to manually copy that from the failed SQL publish file.

declare @hash varbinary(64) = HASHBYTES('SHA2_512', @assembly);

EXEC sys.sp_add_trusted_assembly @hash, N'Foo Assembly';

Na 4ª opção tenho que cadastrar manualmente o assembly como confiável e só depois disso posso publicar o assembly. É possível automatizar de alguma forma esse processo?

Estou pensando em criar pre-deployment scriptque possa rodar o código da 4ª opção mas não sei como preencher a variável @assembly do arquivo do assembly .dll.

Como alternativa, se for possível implantar o assembly como não confiável, posso torná-lo confiável no servidor com o seguinte código ( post-deployment script)

-- Register all database assemblies as trusted
declare @name nvarchar(4000),
@content varbinary(max);

DECLARE appCursor CURSOR FAST_FORWARD FOR
    SELECT [name], content
    FROM   SourceDatabase.sys.assembly_files
 
OPEN appCursor
FETCH NEXT FROM appCursor INTO @name, @content
 
WHILE @@FETCH_STATUS = 0
BEGIN
   
   DECLARE @hash varbinary(64) = HASHBYTES('SHA2_512', @content);

    EXEC sys.sp_add_trusted_assembly @hash, @name;

   FETCH NEXT FROM appCursor INTO @name, @content
END
CLOSE appCursor
DEALLOCATE appCursor
GO

Alguma ideia ou qual é a sua abordagem?

Tenho recebido este erro ao tentar instalar o SQL Server 2017 padrão. Estou instalando isso em um sistema recém-formatado também. A rede está definida como privada, não tenho certeza se isso importa.

Também iniciei a instalação como administrador, adicionei o usuário atual à lista de administradores do sistema SQL e continuo recebendo esse erro. Eu até reformatei este computador e continuo recebendo o mesmo erro.

Alguém tem uma pista de como consertar isso? O sistema também não está sob nenhuma política de domínio.

Apesar de muitas perguntas sobre o mesmo tópico, não consegui encontrar a solução certa. Estou usando um software que agrega IDs a outra tabela como um valor separado por vírgula em vez de uma tabela de junção.

O software expõe três visualizações Department, Teame Task.

PorDepartment

| ID     |  NAME |
|--------|-------|
|  12345 |    IT |
|  25695 |    HR |
|  89685 |   PAY |
|  47896 |   OPS |

Team

| ID     |  NAME |    DEPARTMENT |
|--------|-------|---------------|
|  34252 | TEAM1 |         12345 |
|  54234 | TEAM2 |         12345 |
|  52385 | TEAM1 |          NULL |
|  91231 | TEAM1 |         89685 |

Task

| ID     |  NAME |             Team |
|--------|-------|------------------|
|      1 |    Ta |34252,54234,52385 |
|      2 |    Tb |      52385,91231 |
|      3 |    Tc |             NULL |
|      4 |    Td |            34252 |

Eu tento alcançar a seguinte estrutura:

| DepID  |   Dep | TeamID |   Team | TaskID |  Task |
|--------|-------|--------|--------|--------|-------|

Com uma linha para cada tarefa, potencialmente, aparecendo mais de uma vez, se atribuída a várias equipes, e não se atribuída a nenhuma equipe ou se a equipe ainda não estiver atribuída a um departamento. Mais uma vez, só tenho essas três visões. Qual seria a maneira mais eficiente de criar essa estrutura? Estou no SQL Server 2017.

Eu tentei começar a partir da tabela de tarefas CROSS APPLY STRING_SPLITe, em seguida, RIGHT JOINpara equipes, bem como a partir de LEFT JOINequipes de departamento.

Estamos enfrentando um erro aparentemente aleatório 824 em nossos servidores DEV/QA/PROD SQL 2017 Enterprise. Os servidores executam códigos quase idênticos ingerindo arquivos diários idênticos por meio de um processo ETL em nosso data warehouse. Os erros foram notados pela primeira vez por volta de maio de 2022, mas devido a limpezas de log, não podemos ter certeza, pois o processo ETL (fornecido pelo fornecedor) está capturando esses erros, registrando um aviso e continuando o processamento em vez de falhar!

DEV/QA foram corrigidos para CU30 (última CU) -- a condição ainda existe. A produção está alguns patches atrasada na CU22 e está programada para ser corrigida nas próximas semanas.

Exemplo:

O SQL Server detectou um erro de E/S baseado em inconsistência lógica: soma de verificação incorreta (esperado 0xc30164e7; real 0x9f2bc675c). Ocorreu durante uma leitura de página (7:1306400) no banco de dados ID 2 no deslocamento 0x0000027de40000 no arquivo 'H:\tempdb_mssql_6.ndf'.

Como afirmado, isso está acontecendo aleatoriamente em todos os nossos ambientes. Todos os servidores são virtualizados. DEV/QA estão ambos utilizando a mesma SAN. A produção está em uma SAN separada em um data center diferente. Não tenho detalhes sobre a marca/modelo dos dispositivos SAN.

Na maioria das vezes, quando isso acontece, parece estar principalmente no tempdb (mas nem sempre). Além disso, as páginas suspeitas são, na maioria das vezes, vazias. Também parece acontecer com mais frequência aos sábados, pois aconteceu 3-4 seguidos.

Também é observado que os valores esperados/reais listados no erro geralmente são os mesmos - mas nem sempre.

Também foi observado que um procedimento armazenado específico parece ser mais suscetível a esse erro, mas isso aconteceu em vários outros locais no trabalho de ETL, novamente impactando bancos de dados diferentes. O procedimento armazenado que parece acionar esse erro geralmente adiciona uma coluna computada PERSISTED e, em seguida, um ROW_NUMBER() com base nessa coluna computada -- para 5 tabelas, variando de 200K a 7,5M linhas de tamanho. Modificamos esse procedimento ontem (no controle de qualidade) para limitar o número de linhas atualizadas com o valor ROW_NUMBER() (somente onde rownum=1) e alteramos essa atualização de uma abordagem de uma só vez para uma abordagem em lote de 25K. O erro aconteceu novamente hoje no controle de qualidade. Por isso, removemos a opção PERSISTED na coluna computada. Estamos literalmente tentando de tudo para impedir isso no controle de qualidade, pois parece mais impactado.

DBCC CHECKDB é executado em todos os bancos de dados de produção, exceto um, diariamente. O único banco de dados ignorado é bastante grande, com pouco menos de 4 TB e o DBCC CHECKDB leva cerca de 12 horas para ser concluído. DBCC CHECKDB foi executado em um backup de produção recente no controle de qualidade neste grande banco de dados e estava limpo.

Até o momento, apenas o tempdb foi afetado na produção. O DEV/QA viu alguns outros bancos de dados afetados, mas o DBCC CHECKDB não produz nada, mesmo quando as páginas suspeitas contêm uma entrada. Restauramos o banco de dados de produção para DEV/QA quando não é tempdb, apenas para garantir.

Quando o tempdb é afetado, interrompemos o SQL, excluindo o arquivo tempdb incorreto e reiniciando o serviço.

Vale a pena notar que H: está em 100% de uso na produção, pois os arquivos mdf/ndf consomem toda a unidade de 150 GB, mas há amplo espaço livre dentro dos arquivos. O DEV/QA não está em 100% e tem cerca de 30-40 GB de espaço livre, e ambos mostram o tempdb sendo impactado. Então eu não acho que seja um problema de espaço em disco.

Entrei em contato com nosso DBA, que acredita que o problema está sendo causado pelos próprios dados. Embora eu não concorde com a falha dos dados, atualizamos o sistema que gera os arquivos CSV que estão sendo lidos pelo trabalho ETL em abril de 2022. O sistema de origem agora é baseado em linux em vez de em Windows. O processo ETL não exigiu alterações para ler os novos arquivos. Os dados dentro dos arquivos mudaram um pouco na estrutura, mas eles eram praticamente idênticos, e os procs CLR podem se adaptar às mudanças estruturais com base em um arquivo de layout de esquema que é enviado diariamente. Os arquivos são suficientemente grandes (55+GB por dia) para preencher mais de 300 tabelas, cada uma composta por uma grande quantidade de campos nvarchar(max).

Entrei em contato com nossa equipe de infraestrutura para verificar a integridade da VM/SAN - e embora a VM da produção tenha relatórios de alta E/S durante a fase de ingestão de arquivos (os carimbos de data/hora se alinham perfeitamente) - não há erros relatados. Nossa equipe de infraestrutura durante suas investigações migrou a VM prod para um host mais rápido e os arquivos foram movidos para uma nova SAN relativamente não utilizada.

Em 26 de julho, o DEV/QA SAN sofreu uma falha de unidade que resultou na substituição e reconstrução de duas unidades. Desde a falha da unidade, não tivemos nenhum incidente enquanto trabalhávamos com a Microsoft, no entanto, no sábado passado e na segunda-feira passada - o controle de qualidade teve mais erros relacionados ao tempdb.

Abrimos um tíquete com a Microsoft e, após várias semanas/chamadas, fomos informados de que eles eram a equipe errada (eles apenas ajudam a corrigir a corrupção) e precisávamos de um tíquete premier. Portanto, estamos solicitando uma cotação da Microsoft para um ticket premier para ajudar na análise da causa raiz.

Enquanto isso, espero que a produção não tenha mais problemas e espero que alguém possa oferecer mais informações ou orientações sobre o que podemos fazer para analisar esse problema ou limitar sua ocorrência até que possamos obter Microsoft devidamente engajada.

Editar 1a - Saída completa @@VERSION

DEV: Microsoft SQL Server 2017 (RTM-CU30) (KB5013756) - 14.0.3451.2 (X64)   Jun 22 2022 18:20:15   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor) 
QA: Microsoft SQL Server 2017 (RTM-CU30) (KB5013756) - 14.0.3451.2 (X64)   Jun 22 2022 18:20:15   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor) 
PROD: Microsoft SQL Server 2017 (RTM-CU22-GDR) (KB4583457) - 14.0.3370.1 (X64)   Nov  6 2020 18:19:52   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor) 

Editar 1b - Suspeita de falha no procedimento ocorre no código após o comentário: BATCH UPDATE RN - CURRENT/PAST ROWS

SET NOCOUNT ON;
DECLARE @schema SYSNAME, @table SYSNAME, @RNCurrDepth SMALLINT, @RNFutureDepth SMALLINT, @SQL NVARCHAR(MAX);

BEGIN TRY
    DECLARE IDComp3 CURSOR LOCAL
    FOR
    SELECT DISTINCT S.name, T.name, P.RNCurrentDescDepth, P.RNFutureAscDepth
    FROM sys.columns C
    JOIN sys.tables T ON C.object_id=T.object_id
    JOIN sys.schemas S ON T.schema_id=S.schema_id
    JOIN dbo.ParseIDComp3Control P ON S.name=P.TableSchema AND T.name=TableName /* Control Table to limit the tables parsed, and how many ROW_NUMBERS to update */
    WHERE C.name='ID_COMP_3'
    AND P.Active=1
    ORDER BY T.name;
    OPEN IDComp3;
    FETCH NEXT FROM IDComp3 INTO @schema, @table, @RNCurrDepth, @RNFutureDepth;
    WHILE @@FETCH_STATUS=0
    BEGIN
        --RAISERROR(@TABLE,0,1) WITH NOWAIT;

        DROP TABLE IF EXISTS #TMP;
        CREATE TABLE #TMP ([@ID] NVARCHAR(150)
                          ,LEAD_CO_MNE NVARCHAR(50)
                          ,RNCURRENTDESC INT
                          ,RNFUTUREASC INT);

        DROP TABLE IF EXISTS #TMP_BATCH;
        CREATE TABLE #TMP_BATCH ([@ID] NVARCHAR(150)
                                ,LEAD_CO_MNE NVARCHAR(50)
                                ,RNCURRENTDESC INT
                                ,RNFUTUREASC INT);

        /* DROP ID_COMP_3 COLUMNS IF EXISTS, TO ENSURE WE HAVE NO VALUES */
        SET @SQL='';
        SELECT @SQL+='ALTER TABLE '+QUOTENAME(S.name)+'.'+QUOTENAME(T.name)+' DROP COLUMN '+QUOTENAME(C.name)+';'+CHAR(10)
            FROM sys.columns C
            JOIN sys.tables T ON C.object_id=T.object_id
            JOIN sys.schemas S ON T.schema_id=S.schema_id
            WHERE C.name IN ('ID_COMP_3_DATE','ID_COMP_3_ID')
            AND S.name=@schema
            AND T.name=@table
        EXEC SP_EXECUTESQL @SQL;

        /* ADD COMPUTED COLUMNS IF NOT EXISTS */
        IF NOT EXISTS (SELECT 1
                        FROM sys.columns C
                        JOIN sys.tables T ON C.object_id=T.object_id
                        JOIN sys.schemas S ON T.schema_id=S.schema_id
                        WHERE C.name='ID_COMP_3_DATE'
                        AND S.name=@schema
                        AND T.name=@table)
        BEGIN   
            SET @SQL = 'ALTER TABLE '+QUOTENAME(@schema)+'.'+QUOTENAME(@table)+'ADD ID_COMP_3_DATE AS TRY_CONVERT(DATE,LEFT(ID_COMP_3,8),112),
                                                                                    ID_COMP_3_ID AS TRY_CONVERT(INT,RIGHT(ID_COMP_3,LEN(ID_COMP_3)-CHARINDEX(''.'',ID_COMP_3)));'
            EXEC SP_EXECUTESQL @SQL;
        END

        /* DROP INDEXES IF EXISTS (SO WE CAN DROP COLUMNS) */
        SET @SQL='';
        SELECT @SQL+='DROP INDEX ' + QUOTENAME(I.name) + ' ON ' + QUOTENAME(S.name)+'.'+QUOTENAME(T.name)+CHAR(10)
            FROM sys.indexes I
            JOIN sys.tables T ON I.object_id=T.object_id
            JOIN sys.schemas S ON T.schema_id=S.schema_id
            WHERE T.name=@table
            AND S.name=@schema
            AND I.name IN ('IX_'+t.name+'_RN_Current_Desc', 'IX_'+t.name+'_RN_Future_Asc')
        EXEC SP_EXECUTESQL @SQL;

        /* DROP RN COLUMNS IF EXISTS, TO ENSURE WE HAVE NO VALUES */
        SET @SQL='';
        SELECT @SQL+='ALTER TABLE '+QUOTENAME(S.name)+'.'+QUOTENAME(T.name)+' DROP COLUMN '+QUOTENAME(C.name)+';'+CHAR(10)
            FROM sys.columns C
            JOIN sys.tables T ON C.object_id=T.object_id
            JOIN sys.schemas S ON T.schema_id=S.schema_id
            WHERE C.name IN ('RN_Current_Desc','RN_Future_Asc')
            AND S.name=@schema
            AND T.name=@table
        EXEC SP_EXECUTESQL @SQL;

        /* ADD RN COLUMNS */
        IF NOT EXISTS (SELECT 1
                        FROM sys.columns C
                        JOIN sys.tables T ON C.object_id=T.object_id
                        JOIN sys.schemas S ON T.schema_id=S.schema_id
                        WHERE C.name='RN_Current_Desc'
                        AND S.name=@schema
                        AND T.name=@table)
        BEGIN   
            SET @SQL = 'ALTER TABLE '+QUOTENAME(@schema)+'.'+QUOTENAME(@table)+'ADD RN_Current_Desc SMALLINT,
                                                                                    RN_Future_Asc SMALLINT;'
            EXEC SP_EXECUTESQL @SQL;
        END

        /* ADD INDEX TO @ID/RNS */
        SET @SQL = 'CREATE NONCLUSTERED INDEX [IX_'+@table+'_RN_Current_Desc] ON '+QUOTENAME(@schema)+'.'+QUOTENAME(@table)+' ([@ID], LEAD_CO_MNE, RN_Current_Desc ASC) WHERE (RN_Current_Desc <= '+CONVERT(VARCHAR(6),@RNCurrDepth)+');
                    CREATE NONCLUSTERED INDEX [IX_'+@table+'_RN_Future_Asc] ON '+QUOTENAME(@schema)+'.'+QUOTENAME(@table)+' ([@ID], LEAD_CO_MNE, RN_Future_Asc DESC)  WHERE (RN_Current_Desc <= '+CONVERT(VARCHAR(6),@RNFutureDepth)+');
                    CREATE NONCLUSTERED INDEX [IX_TMP_Current_Desc] ON #TMP ([@ID], LEAD_CO_MNE) INCLUDE(RNCURRENTDESC, RNFUTUREASC);
                    CREATE NONCLUSTERED INDEX [IX_TMPBATCH_Current_Desc] ON #TMP_BATCH ([@ID], LEAD_CO_MNE) INCLUDE(RNCURRENTDESC, RNFUTUREASC);';
        EXEC SP_EXECUTESQL @SQL;

        /* BATCH UPDATE RN - CURRENT/PAST ROWS */
        SET @SQL = ';WITH X AS
                    (
                    SELECT [@ID]
                          ,LEAD_CO_MNE
                          ,ROW_NUMBER() OVER(PARTITION BY ID_COMP_1, LEAD_CO_MNE ORDER BY ID_COMP_3_DATE DESC, ID_COMP_3_ID DESC) AS RNCURRENTDESC
                    FROM '+QUOTENAME(@schema)+'.'+QUOTENAME(@table)+'
                    WHERE ID_COMP_3_DATE <= MIS_DATE
                    )
                    INSERT INTO #TMP ([@ID],LEAD_CO_MNE,RNCURRENTDESC)
                    SELECT [@ID], LEAD_CO_MNE, RNCURRENTDESC
                    FROM X
                    WHERE RNCURRENTDESC <= @RNCurrDepth;
                    
                    DECLARE @BATCH INT = 25000;
                    WHILE @BATCH > 0
                    BEGIN
                        UPDATE TOP(@BATCH) X
                        SET RN_Current_Desc = T.RNCURRENTDESC
                        FROM '+QUOTENAME(@schema)+'.'+QUOTENAME(@table)+' X
                        JOIN #TMP T ON X.[@ID]=T.[@ID] AND X.LEAD_CO_MNE=T.LEAD_CO_MNE
                        WHERE X.RN_Current_Desc IS NULL;

                        SET @BATCH=@@ROWCOUNT;
                    END'
        EXEC SP_EXECUTESQL @SQL, N'@RNCurrDepth SMALLINT', @RNCurrDepth=@RNCurrDepth;

        TRUNCATE TABLE #TMP;

        /* RN - FUTURE ROWS */
        SET @SQL = ';WITH X AS
                    (
                    SELECT [@ID]
                          ,LEAD_CO_MNE
                          ,ROW_NUMBER() OVER(PARTITION BY ID_COMP_1, LEAD_CO_MNE ORDER BY ID_COMP_3_DATE ASC, ID_COMP_3_ID DESC) AS RNFUTUREASC
                    FROM '+QUOTENAME(@schema)+'.'+QUOTENAME(@table)+'
                    WHERE ID_COMP_3_DATE > MIS_DATE
                    )
                    INSERT INTO #TMP ([@ID],LEAD_CO_MNE,RNFUTUREASC)
                    SELECT [@ID],LEAD_CO_MNE,RNFUTUREASC
                    FROM X
                    WHERE RNFUTUREASC <= @RNFutureDepth

                    DECLARE @BATCH INT = 25000;
                    WHILE @BATCH > 0
                    BEGIN
                        UPDATE TOP(@BATCH) X
                        SET RN_Future_Asc = T.RNFUTUREASC
                        FROM '+QUOTENAME(@schema)+'.'+QUOTENAME(@table)+' X
                        JOIN #TMP T ON X.[@ID]=T.[@ID] AND X.LEAD_CO_MNE=T.LEAD_CO_MNE
                        WHERE X.RN_Future_Asc IS NULL;

                        SET @BATCH=@@ROWCOUNT;
                    END'
        EXEC SP_EXECUTESQL @SQL, N'@RNFutureDepth SMALLINT', @RNFutureDepth=@RNFutureDepth;

        FETCH NEXT FROM IDComp3 INTO @schema, @table, @RNCurrDepth, @RNFutureDepth;
    END
    CLOSE IDComp3
    DEALLOCATE IDComp3
END TRY
BEGIN CATCH

    DECLARE @Message NVARCHAR(MAX) = ERROR_MESSAGE(), 
            @Severity INT = ERROR_SEVERITY(), 
            @State SMALLINT =ERROR_STATE()
    RAISERROR(@Message, @Severity, @State);
    RETURN(1);
END CATCH
    
RETURN(0)

Editar 1c - Esquema de Tabela de Amostra

CREATE TABLE dbo.ABC123
(
    [LEAD_CO_MNE] [nvarchar](50) NOT NULL,
    [BRANCH_CO_MNE] [nvarchar](50) NULL,
    [MIS_DATE] [date] NOT NULL,
    [@ID] [nvarchar](150) NOT NULL,
    [ACTIVITY] [nvarchar](150) NULL,
    [ACTION] [nvarchar](max) NULL,
    [CHANGE_DATE_TYPE] [nvarchar](50) NULL,
    [CHANGE_PERIOD] [nvarchar](150) NULL,
    [CHANGE_DATE] [datetime2](7) NULL,
    [CHANGE_ACTIVITY] [nvarchar](150) NULL,
    [PRIOR_DAYS] [int] NULL,
    [CHG_TO_PRODUCT] [nvarchar](150) NULL,
    [ALLOWED_PRODUCT] [nvarchar](max) NULL,
    [RESERVED_6] [nvarchar](150) NULL,
    [RESERVED_5] [nvarchar](150) NULL,
    [INITIATION_TYPE] [nvarchar](50) NULL,
    [DEFAULT_ACTIVITY] [nvarchar](150) NULL,
    [RESERVED_4] [nvarchar](150) NULL,
    [RESERVED_3] [nvarchar](150) NULL,
    [RESERVED_2] [nvarchar](150) NULL,
    [RESERVED_1] [nvarchar](150) NULL,
    [LOCAL_REF] [nvarchar](max) NULL,
    [PR_ATTRIBUTE] [nvarchar](max) NULL,
    [PR_VALUE] [nvarchar](max) NULL,
    [PR_BRK_RES] [nvarchar](max) NULL,
    [PR_BRK_MSG] [nvarchar](max) NULL,
    [PR_BRK_CHARGE] [nvarchar](max) NULL,
    [PR_RESERVED_3] [nvarchar](max) NULL,
    [PR_RESERVED_2] [nvarchar](max) NULL,
    [PR_RESERVED_1] [nvarchar](max) NULL,
    [PR_APP_METHOD] [nvarchar](max) NULL,
    [PR_APP_PERIOD] [nvarchar](max) NULL,
    [SYS_RESERVE7] [nvarchar](150) NULL,
    [SYS_RESERVE6] [nvarchar](150) NULL,
    [SYS_RESERVE5] [nvarchar](150) NULL,
    [SYS_RESERVE4] [nvarchar](150) NULL,
    [SYS_RESERVE3] [nvarchar](150) NULL,
    [SYS_RESERVE2] [nvarchar](150) NULL,
    [SYS_RESERVE1] [nvarchar](150) NULL,
    [DEFAULT_ATTR_OPTION] [nvarchar](150) NULL,
    [DEFAULT_NEGOTIABLE] [nvarchar](50) NULL,
    [NR_ATTRIBUTE] [nvarchar](max) NULL,
    [NR_OPTIONS] [nvarchar](max) NULL,
    [NR_RESERVED2] [nvarchar](max) NULL,
    [NR_RESERVED1] [nvarchar](max) NULL,
    [NR_STD_COMP] [nvarchar](max) NULL,
    [NR_TYPE] [nvarchar](max) NULL,
    [NR_VALUE] [nvarchar](max) NULL,
    [NR_MESSAGE] [nvarchar](max) NULL,
    [CHANGED_FIELDS] [nvarchar](max) NULL,
    [NEGOTIATED_FLDS] [nvarchar](max) NULL,
    [ID_COMP_1] [nvarchar](150) NULL,
    [ID_COMP_2] [nvarchar](150) NULL,
    [ID_COMP_3] [nvarchar](150) NULL,
    [ID_COMP_4] [nvarchar](150) NULL,
    [ID_COMP_5] [nvarchar](150) NULL,
    [ID_COMP_6] [nvarchar](150) NULL,
    [RESERVED2_ID] [nvarchar](150) NULL,
    [TARGET_PRODUCT] [nvarchar](50) NULL,
    [STMT_NOS] [nvarchar](max) NULL,
    [OVERRIDE] [nvarchar](max) NULL,
    [RECORD_STATUS] [nvarchar](50) NULL,
    [CURR_NO] [int] NULL,
    [INPUTTER] [nvarchar](max) NULL,
    [DATE_TIME] [nvarchar](max) NULL,
    [AUTHORISER] [nvarchar](150) NULL,
    [CO_CODE] [nvarchar](50) NULL,
    [DEPT_CODE] [nvarchar](50) NULL,
    [AUDITOR_CODE] [nvarchar](50) NULL,
    [AUDIT_DATE_TIME] [int] NULL,
    [ARRANGEMENT_KEY] [nvarchar](150) NULL,
    [ETL_DQ_RevisionCount] [int] NULL,
    [ETL_DQ_ColumnsRevised] [nvarchar](4000) NULL,
    [ETL_DQ_ErrorMessage] [nvarchar](4000) NULL,
    [ETL_CHANGE_PERIOD] [nvarchar](100) NULL,
    [API_ATTRIBUTE] [nvarchar](max) NULL,
    [NR_ATTRIBUTE_RULE] [nvarchar](max) NULL,
    [NR_VALUE_SOURCE] [nvarchar](max) NULL,
    [OWNING_COMPANY] [nvarchar](max) NULL,
    [ID_COMP_3_DATE]  AS (TRY_CONVERT([date],left([ID_COMP_3],(8)),(112))),
    [ID_COMP_3_ID]  AS (TRY_CAST(right([ID_COMP_3],len([ID_COMP_3])-charindex('.',[ID_COMP_3])) AS [int])),
    [RN_Current_Desc] [smallint] NULL,
    [RN_Future_Asc] [smallint] NULL,
 CONSTRAINT [PK_ABC123] PRIMARY KEY NONCLUSTERED 
(
    [@ID] ASC,
    [LEAD_CO_MNE] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Eu tenho um aplicativo de consulta digitalizando uma tabela inteira com um campo de texto.

A consulta está fazendo isso muitas leituras:

Contagem de varredura 1, leituras lógicas 170586 , leituras físicas 3, leituras antecipadas 174716, leituras lógicas lob 7902578 , leituras físicas lob 8743, leituras antecipadas lob 0.

Plano de consulta com leituras lógicas de lob

Se eu remover o campo de texto da seleção, as leituras se tornarão o seguinte:

Contagem de varredura 1, leituras lógicas 170588 , leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0 , leituras físicas lob 0, leituras antecipadas lob 0.

Plano de consulta sem leituras lógicas de lob

A única coisa que eu não entendo é como o lob reads funciona:

se eu somar as leituras lógicas com leituras lógicas lob recebo um total de 8.073.164 leituras lógicas , que, se estiver correto, é cerca de 64 GB .

Mas todo o banco de dados tem apenas 7 GB!

Provavelmente estou perdendo algo sobre a adição de leituras lógicas e leituras lógicas de lob.

O que o número de leituras lógicas lob realmente representa?

Estou tentando configurar um servidor vinculado no SQL Server 2017 usando o arquivo de hosts local do Windows no servidor em que o servidor vinculado está sendo configurado. A razão para isso é permitir que eu codifique o nome do servidor vinculado, mas configure o servidor para o qual ele aponta alterando o arquivo de hosts local.

Estou usando a autenticação do Windows em ambos os servidores de banco de dados. Eu consigo acessar os dois.

Quando crio um servidor vinculado referenciando o endereço IP, assim, funciona. Eu posso conectar e consultar uma tabela de amostra.

EXEC sp_addlinkedserver @server = '192.168.1.109'
, @srvproduct = 'SQL Server'

Quando adiciono a seguinte linha ao arquivo host, o servidor vinculado criado acima, que se conecta via endereço IP, não funciona mais.

192.168.1.109 hostsfiletest

Estou tendo o erro a seguir:

Msg 18456, Level 14, State 1, Line 18
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Normalmente, eu pensaria que isso é algo a ver com a falta de SPNs, mas ambos os servidores nessa situação têm SPNs configurados, posso me conectar se remover a linha acima do arquivo hosts mais uma vez.

Além disso, se eu tentar configurar outro servidor vinculado usando o nome no arquivo hosts, assim, ele também falha, com o mesmo erro.

EXEC sp_addlinkedserver @server = 'hostsfiletest'
, @srvproduct = 'SQL Server'

Não tenho certeza onde estou errando.

Posso fazer ping no servidor de destino do servidor em que estou configurando o Linked Server e posso me conectar à instância SQL de destino desse mesmo servidor usando hostsfiletest como o nome da instância , para que a linha no arquivo hosts esteja claramente fazendo algo. Quando removo a linha do arquivo hosts, não consigo conectar usando hostsfiletest como o nome da instância.

Como outro teste, quando coloco a mesma linha acima no arquivo de hosts local do meu laptop, consigo me conectar ao servidor de destino no SSMS usando hostsfiletest como o nome do servidor.

Eu ficaria grato por qualquer ajuda. Também estou aberto a outras maneiras de alcançar a mesma coisa, se houver maneiras mais fáceis de configurar um servidor vinculado que aponte para um local configurável.