Skary's questions

No meu cenário, tenho um SQL Server Express que é minha fonte de dados. Periodicamente produz lotes de dados, que devem ser carregados em um repositório central (um SQL Server SE na nuvem). A fonte de dados conhece o repositório central, mas o repositório central não conhece nenhuma fonte de dados possível (pode mudar com o tempo). Não sei se o SQL Server Express suporta envio de logs, nem estou familiarizado com essa tecnologia; de qualquer forma, especificaria que, por outro motivo, não é uma solução viável.

Minha solução atual é fazer upload, da fonte de dados, através do servidor vinculado (apontar para a nuvem SQL Server SE), todos os dados quando necessário. Dá trabalho sem dúvida, mas monitorando o SQL Server SE central (na nuvem) vejo um grande volume de solicitação. A leitura da documentação me parece que o envio de dados é ineficiente e enviará uma linha por vez (isso explica o baixo desempenho que tenho e o volume de solicitações no SQL Server SE central).

Gostaria de saber se existe uma maneira de executar remotamente um pull no SQL Server central, acionado pelo remoto. Dessa forma, posso explorar o fato de que extrair dados deve ser eficiente, empurrá-los e contornar o problema de que o SQL Server central não sabe qual fonte de dados irá contatá-lo e quando.

No meu cenário, preciso otimizar um procedimento armazenado, que usei para importar dados para o banco de dados de um banco de dados Exchange. Estou muito confuso porque a solução com INNER JOIN é inferior à solução LEFT OUTER JOIN, basicamente parece que a maneira como verifico a existência da "relação" causa uma enorme lentidão.

Para ser mais claro, eu tenho um Final_DB com poucas tabelas, uma para os artigos (tbl_ana_Articles), uma para atributos de artigos, também conhecidas como características (tbl_ana_Characteristics), e algumas outras tabelas. Em outro Exchange_DB obtenho relação entre artigos e atributos/características (é usado para atualizar periodicamente relações no Final_DB).

A tabela com relações, fornecida pelo banco de dados Exchange, precisa ser não dinâmica primeiro, antes de ser útil (não é uma tabela de junção cruzada quando a consigo, ela se torna uma junção cruzada após a não dinâmica).

Então basicamente eu escrevo a consulta dessa maneira:

WITH ExchangeArticleCode_CharacteristicCode AS (
  SELECT [ACODAR], SUBSTRING([TNCAR],5,2) AS [TNCAR] , [TVALOR]
  FROM [EXCHANGE_DB].[dbo].[ANART00F]
  UNPIVOT
  (
   [TVALOR]
   FOR [TNCAR] in ([ACAR01], ACAR02, ACAR03, ACAR04 , ACAR05 , ACAR06 , ACAR07 , ACAR08 , ACAR09 , ACAR10 , ACAR11 , ACAR12 , ACAR13 , ACAR14 , ACAR15 , ACAR16 , ACAR17 , ACAR18 , ACAR19 , ACAR20)
  ) AS [UNPIVOT]
  WHERE [TVALOR] IS NOT NULL AND [TVALOR] != ''
)

SELECT Characteristic.[ID]        AS [ID_CHARACTERISTIC]
       ,Article.[ID]               AS [ID_ARTICLE]
       ,Characteristic.[ID_FILTER] AS [ID_FILTER]
FROM ExchangeArticleCode_CharacteristicCode
LEFT OUTER JOIN [dbo].[tbl_ana_Articles] AS Article 
 ON (ExchangeArticleCode_CharacteristicCode.ACODAR collate Latin1_General_CI_AS) = Article.CODE
LEFT OUTER JOIN [dbo].[tbl_ana_Characteristics] AS Characteristic 
 ON (ExchangeArticleCode_CharacteristicCode.TNCAR collate Latin1_General_CI_AS) + '_' + (ExchangeArticleCode_CharacteristicCode.TVALOR collate Latin1_General_CI_AS) = Characteristic.ID_ERP
WHERE Characteristic.[IS_ACTIVE] = 1 

Esta solução é surpreendentemente rápida, mas tem problemas, às vezes há lixo no banco de dados do Exchange, então a junção esquerda não corresponde aos códigos e na tabela de resultados recebo alguns NULL. Se eu tentar evitar o NULL, substituindo LEFT OUTER JOIN por INNER JOIN ou adicionando uma verificação (IS NOT NULL) na condição where, a consulta se tornará muito lenta e pesada para ser executada. Não está claro para mim por que e como evitar isso.

Aqui está o plano de execução da consulta rápida: https://www.brentozar.com/pastetheplan/?id=ryMBHCryp

Aqui está o plano de execução da consulta lenta: https://www.brentozar.com/pastetheplan/?id=SywALRS16

Para ser justo, parece-me que a consulta lenta tem um loop aninhado caro, mas por que o INNER JOIN é traduzido nesse loop aninhado?

Estou usando o SQL Server SE 2019, em um Windows Server 2019 (com unidade SSD) que executa o IIS 10. Sobre o IIS 10, execute um aplicativo ASP.Net, que deve ser considerado COMO ESTÁ (não posso modificá-lo) .

Ultimamente descobri que o aplicativo ASP.Net pode se tornar um pouco tagarela demais e começar a fazer muitas consultas de banco de dados em lotes (poucas rajadas de 20 a 30 consultas/s). Sob carga pesada, isso pode se tornar um problema, então gostaria de melhorar o desempenho do sistema. O SQL Server foi instalado sem nenhuma configuração específica (padrão do assistente de instalação).

Gostaria de saber se existe alguma configuração conhecida que eu possa ajustar (SQL Server/IIS) que possa otimizar o cenário em que há muitas consultas pequenas e rápidas (~5ms por consulta) enviadas em lotes de 20-30 consultas/s . Para que eu possa melhorar a situação em que o aplicativo ASP.Net está sob carga pesada e começar a enviar muitas consultas rápidas ao banco de dados

Eu tenho um banco de dados SQL Server, que é quase um banco de dados somente leitura (o procedimento de gravação acontecerá no horário agendado, normalmente duas vezes por dia). Eu descobri recentemente sobre a tabela na memória (sou um desenvolvedor, não um especialista em DBA) e me pergunto se pode ser uma boa ideia mover o banco de dados para uma versão "na memória"; e quais são os problemas gerais desta configuração.

Tabelas na memória são um tópico novo para mim, estou bastante confuso sobre o fato de se essas tabelas são boas para acelerar apenas a operação de IO ou também consultas simples (em particular consultas que já são discretas). Também estou curioso para entender se esta tecnologia é uma boa escolha também quando o SQL Server pode usar SSD discretamente rápido. Não quero ser muito amplo em minha pergunta, mas também estou curioso sobre possíveis problemas conhecidos que podem ocorrer durante a migração para essa tecnologia.

PS: Para lhe dar um pouco de experiência, a ideia me veio à mente, porque estou enfrentando um pequeno problema de desempenho. As consultas já têm bastante desempenho, mas tenho algumas (já otimizadas o máximo que posso) que levam aproximadamente 0,2-0,3s, muito para minha necessidade. Só por curiosidade, acrescentarei que essa necessidade de tempo se deve ao fato de que essas consultas "lentas" estão vinculadas a uma solicitação da web, que deve ser concluída em menos de um segundo.

Eu preciso escrever um procedimento de armazenamento para atualizar a referência de link em meu banco de dados. O link pode estar contido em alguns campos nvarchar que contêm JSONs (que podem conter alguns URLs).

Para isso atualizo as tabelas em lotes de 8129 itens por iteração, para que a máquina não trave (em teoria).

Mas agora o código parece travar de qualquer maneira, não imprime nenhuma mensagem e o procedimento continua rodando (sem afetar nenhum dado) por muitos minutos, até que eu tenha que matar o procedimento (que por enquanto parece não afetar nenhum dado) .

Se eu tentar usar a mesma lógica em um exemplo de brinquedo, não tenho problemas, então acho que meu problema se deve ao fato de a tabela ser grande (algumas centenas de milhares de linhas).

Aqui o exemplo mínimo que está funcionando, exatamente o mesmo código na tabela maior trava aparentemente sem fazer nada (testado com SQL Server 2019).

Código do procedimento:

ALTER PROCEDURE [dbo].[SiteUrlChangeURL]
    @FullOldUrl nvarchar(500),
    @FullNewUrl nvarchar(500)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SET @FullOldUrl = ISNULL(@FullOldUrl,'');
    SET @FullNewUrl = ISNULL(@FullNewUrl,'');

    IF ( LEN(@FullOldUrl) <= 0 OR LEN(@FullNewUrl) <= 0 )
    BEGIN 
        PRINT('Invalid parameters');
        RETURN 1;
    END

    --ARTICLE
    RAISERROR ('updating articles',0,1) WITH NOWAIT;
    WHILE 1=1
    BEGIN
        UPDATE TOP (8196) [dbo].[tbl_ana_Articles]
           SET [_ATTRIBUTES]    = REPLACE([_ATTRIBUTES]   , @FullOldUrl, @FullNewUrl)
              ,[_DOCUMENTS]     = REPLACE([_DOCUMENTS]    , @FullOldUrl, @FullNewUrl)
              ,[_SEO]           = REPLACE([_SEO]          , @FullOldUrl, @FullNewUrl)
              ,[_TRANSLATIONS]  = REPLACE([_TRANSLATIONS] , @FullOldUrl, @FullNewUrl)
              ,[_TAGS]          = REPLACE([_TAGS]         , @FullOldUrl, @FullNewUrl)
              ,[_NOTES]         = REPLACE([_NOTES]        , @FullOldUrl, @FullNewUrl)
        WHERE 
            [_ATTRIBUTES]   like '%' + @FullOldUrl + '%' OR
            [_DOCUMENTS]    like '%' + @FullOldUrl + '%' OR
            [_SEO]          like '%' + @FullOldUrl + '%' OR
            [_TRANSLATIONS] like '%' + @FullOldUrl + '%' OR
            [_TAGS]         like '%' + @FullOldUrl + '%' OR
            [_NOTES]        like '%' + @FullOldUrl + '%'

        IF (@@ROWCOUNT <= 0)
        BEGIN
            BREAK;
        END
    END

 RETURN 0;

Exemplo :

CREATE TABLE [dbo].[tbl_ana_Articles](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ID_BRAND] [int] NOT NULL,
    [CODE] [nvarchar](40) NOT NULL,
    [CODFOR] [nvarchar](40) NOT NULL,
    [COD_ALT01] [nvarchar](50) NOT NULL,
    [COD_ALT02] [nvarchar](50) NOT NULL,
    [COD_ALT03] [nvarchar](50) NOT NULL,
    [ID_UOM] [int] NOT NULL,
    [IS_ACTIVE] [bit] NOT NULL,
    [_ATTRIBUTES] [nvarchar](max) NOT NULL,
    [_DOCUMENTS] [nvarchar](max) NOT NULL,
    [_SEO] [nvarchar](max) NOT NULL,
    [_TRANSLATIONS] [nvarchar](max) NOT NULL,
    [_TAGS] [nvarchar](max) NOT NULL,
    [_NOTES] [nvarchar](max) NOT NULL,
    [_METADATA] [nvarchar](max) NOT NULL,
    [IS_B2B] [bit] NOT NULL,
    [IS_B2C] [bit] NOT NULL,
    [IS_PROMO] [bit] NOT NULL,
    [IS_NEWS] [bit] NOT NULL,
    [CAN_BE_RETURNED] [bit] NOT NULL,
    [IS_SHIPPABLE] [bit] NOT NULL,
    [HAS_SHIPPING_COSTS] [bit] NOT NULL,
    [IS_PURCHEASABLE] [bit] NOT NULL,
 CONSTRAINT [PK_tbl_ana_articles] PRIMARY KEY CLUSTERED 
(
    [ID] 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



INSERT INTO [dbo].[tbl_ana_Articles]
           ([ID_BRAND]
           ,[CODE]
           ,[CODFOR]
           ,[COD_ALT01]
           ,[COD_ALT02]
           ,[COD_ALT03]
           ,[ID_UOM]
           ,[IS_ACTIVE]
           ,[_ATTRIBUTES]
           ,[_DOCUMENTS]
           ,[_SEO]
           ,[_TRANSLATIONS]
           ,[_TAGS]
           ,[_NOTES]
           ,[_METADATA]
           ,[IS_B2B]
           ,[IS_B2C]
           ,[IS_PROMO]
           ,[IS_NEWS]
           ,[CAN_BE_RETURNED]
           ,[IS_SHIPPABLE]
           ,[HAS_SHIPPING_COSTS]
           ,[IS_PURCHEASABLE])
     VALUES
           (1
           ,'COD1'
           ,'SUPPLIER1'
           ,'CATEGORY1'
           ,'CATEGORY1-BIS'
           ,'CATEGORY2'
           ,1
           ,1
           ,'{ "url" : "https://old.com" }'
           ,''
           ,''
           ,''
           ,''
           ,''
           ,''
           ,1
           ,0
           ,0
           ,0
           ,1
           ,1
           ,0
           ,1);
           
    DECLARE @FullOldUrl AS NVARCHAR(50) = 'https://old.com';
    DECLARE @FullNewUrl AS NVARCHAR(50) = 'https://new.com';

    --ARTICLE
    PRINT('updating articles');
    WHILE 1=1
    BEGIN
        UPDATE TOP (8196) [dbo].[tbl_ana_Articles]
           SET [_ATTRIBUTES]    = REPLACE([_ATTRIBUTES]   , @FullOldUrl, @FullNewUrl)
              ,[_DOCUMENTS]     = REPLACE([_DOCUMENTS]    , @FullOldUrl, @FullNewUrl)
              ,[_SEO]           = REPLACE([_SEO]          , @FullOldUrl, @FullNewUrl)
              ,[_TRANSLATIONS]  = REPLACE([_TRANSLATIONS] , @FullOldUrl, @FullNewUrl)
              ,[_TAGS]          = REPLACE([_TAGS]         , @FullOldUrl, @FullNewUrl)
              ,[_NOTES]         = REPLACE([_NOTES]        , @FullOldUrl, @FullNewUrl)
        WHERE 
            [_ATTRIBUTES]   like '%' + @FullOldUrl + '%' OR
            [_DOCUMENTS]    like '%' + @FullOldUrl + '%' OR
            [_SEO]          like '%' + @FullOldUrl + '%' OR
            [_TRANSLATIONS] like '%' + @FullOldUrl + '%' OR
            [_TAGS]         like '%' + @FullOldUrl + '%' OR
            [_NOTES]        like '%' + @FullOldUrl + '%'

        IF (@@ROWCOUNT <= 0)
        BEGIN
            BREAK;
        END
    END
    
    SELECT * FROM [dbo].[tbl_ana_Articles]
    PRINT('Finished');

Aqui o plano de execução produzido pelo exemplo do brinquedo (não consigo obter o plano de execução do cenário real).

https://www.brentozar.com/pastetheplan/?id=SJhVTcMTo

Estou realmente intrigado sobre o que causou esse problema

--EDITAR:

Executei o procedimento novamente e descobri que, se deixar correr o tempo suficiente (~ 30 min), obtenho o comportamento correto. Então, aparentemente, tenho um problema de desempenho aqui. Não estou realmente interessado em desempenho aqui, porque o procedimento é usado apenas raramente (manualmente) durante a manutenção (somente se o site mudar de domínio).

Mas estou curioso para saber se cometi algum erro aqui, para obter um desempenho tão baixo

Devo admitir que sou um programador e estou tentando aprender um pouco sobre DBA, mesmo que não tenha o mesmo histórico. Estou tentando entender o que fornece segurança e como usar a criptografia de dados em repouso no SQL Server.

Minha pergunta é, como essa criptografia protegerá os dados em caso de acesso não autorizado da máquina que hospeda a instância do SQL Server? Meu palpite é que não.

Quero dizer, suponha que o SQL Server seja executado em uma máquina virtual colocada em um datacenter e "conectada" à Internet. Se um invasor obtiver o controle da máquina virtual, acho que a proteção de dados em repouso pode fazer muito pouco.

Na verdade, o invasor pode obter privilégio de administrador (como usuário do sistema operacional), redefinir a credencial do SQL Server e fazer login como 'sa'; mesmo que o arquivo SQL Servr esteja normalmente criptografado, ele pode exportá-los em texto simples.

Nesse cenário, existe alguma maneira de proteger o SQL Server ou, em caso de violação no nível do sistema operacional, o SQL Server deve ser considerado "perdido"?

Recentemente descobri (tenho que admitir que não sou um DBA) que existe a possibilidade de configurar um tempo limite na propriedade SQL Serve (que se aplica a todos os bancos de dados na instância do SQL Server):

Ignorando o fato de que a imagem exibe um timeout 0 (estou experimentando, não será a configuração final); estou confuso, porque sei que o tempo limite deve ser configurado no cliente que se conecta à instância do SQL Server (tanto a conexão quanto o tempo limite do comando).

Agora eu me pergunto o que acontece se uma configuração do SQL Server diz que o tempo limite é de 10 minutos e um cliente se conecta com uma string de conexão de 20 minutos (comando e conexão). Qual timeout é aplicado?

Eu tenho que presumir que não sou um DBA, mas tenho a necessidade de manter uma instância do SQL Server e tenho poucos problemas com o encadeamento de permissão de procedimento armazenado (espero que os termos estejam corretos).

Para explicar brevemente minhas necessidades, eu tenho um usuário que deve ter concessão apenas em um banco de dados que deve executar um procedimento armazenado (nesse banco de dados "concedido") que contém referência a um banco de dados externo (devo herdar um pouco de concessão)

Em detalhes, meu cenário é que eu tenho três DBs

1. DB de configuração (deve ser lido por todos, mas nunca escrito por ninguém, exceto pelo usuário de configuração)
2. Staging DB (deve ser lido/escrito pelo procedimento de preparação)
3. DB final (deve ser lido/escrito pelo aplicativo)

Então eu tenho três usuários

1. Usuário de configuração: usado para manter dados no banco de dados de configuração
2. Usuário de teste: usado por muitos procedimentos de teste (procedimentos muito longos, podem levar horas) que colocam dados no banco de dados de teste
3. Usuário Final : usado pelo aplicativo que leu os dados quando o procedimento de preparação terminou para implantar os dados no banco de dados de preparação e colocá-los de volta no banco de dados final. O usuário final pode acessar todos os bancos de dados (pelo menos para leitura)

Até aí tudo bem, o procedimento de staging faz seu trabalho e quando completo escreve um semáforo (para notificar que os dados estão prontos). Às vezes, o aplicativo (que usa o usuário final) verifica o semáforo e ingere todos os dados no banco de dados final.

Agora eu tenho que oferecer suporte a um procedimento de teste rápido e sob demanda (ainda preciso executar no Staging DB por outros motivos) que, quando concluído, deve atualizar diretamente os dados no banco de dados final.

Eu escrevi esse procedimento e tudo funciona como pretendido, mas tenho que conceder a operação de leitura/gravação no FinalDB para o usuário de teste e não gosto disso. Existem muitos aplicativos que usam o Staging User e alguns deles não estão "sob meu controle", então não quero que um aplicativo defeituoso que deve gravar dados no Staging DB acabe mexendo com o Final DB. Posso aceitar que meu novo procedimento de teste sob demanda pode ser crítico (e acabar atrapalhando o BD Final).

Então a questão é, Staging User já tem permissão para executar um procedimento armazenado (EG : OnDemandUpdate) porque faz parte do Staging DB, mas que contém insert/update sobre uma tabela externa (do Final DB) e eu quero que a concessão possa ser acorrentado. Como eu posso fazer isso?

conforme indicado no título, inicio um ajuste de desempenho de uma consulta de tabela com paginação gerada por um programa legado que usa Linq To SQL como ORM.

Eu encontrei este recurso no qual é altamente recomendado classificar a tabela antes da paginação: https://rimdev.io/optimizing-linq-sql-skip-take/

Então eu segui a sugestão fornecida e experimentei uma enorme diferença. Está claro para mim que está um pouco relacionado a como row_number é calculado, mas não está claro para mim exatamente o que aconteceu e por que há tanta diferença entre as duas consultas.

Consulta lenta original (conjunto de dados de ~7K elementos, leva ~3s):

SELECT [t7].[ID], [t7].[ID_BRAND], [t7].[CODE], [t7].[CODFOR], [t7].[COD_ALT01], [t7].[COD_ALT02], [t7].[COD_ALT03], [t7].[ID_UOM], [t7].[IS_ACTIVE], [t7].[_ATTRIBUTES] AS [_ATTRIBUTES], [t7].[_DOCUMENTS] AS [_DOCUMENTS], [t7].[_SEO] AS [_SEO], [t7].[_TRANSLATIONS] AS [_TRANSLATIONS], [t7].[_TAGS] AS [_TAGS], [t7].[_NOTES] AS [_NOTES], [t7].[_METADATA] AS [_METADATA], [t7].[IS_B2B], [t7].[IS_B2C], [t7].[IS_PROMO], [t7].[IS_NEWS], [t7].[CAN_BE_RETURNED], [t7].[IS_SHIPPABLE], [t7].[HAS_SHIPPING_COSTS], [t7].[IS_PURCHEASABLE], [t7].[test], [t7].[ID2], [t7].[CODE2], [t7].[BUSINESS_NAME], [t7].[NAME], [t7].[PHONE_01], [t7].[PHONE_02], [t7].[PHONE_03], [t7].[FAX_01], [t7].[FAX_02], [t7].[COUNTRY_01], [t7].[CITY_01], [t7].[ADDRESS_01], [t7].[COUNTRY_02], [t7].[CITY_02], [t7].[ADDRESS_02], [t7].[EMAIL_01], [t7].[EMAIL_02], [t7].[PEC], [t7].[SITE_01], [t7].[SITE_02], [t7].[SITE_03], [t7].[SITE_04], [t7].[VAT_NUMBER], [t7].[SORT], [t7].[GROUPID_01], [t7].[IS_GROUPLEADER_01], [t7].[GROUPID_02], [t7].[IS_GROUPLEADER_02],[t7].[IS_ACTIVE2], [t7].[[_DOCUMENTS]]2] AS [_DOCUMENTS2], [t7].[[_SEO]]2] AS [_SEO2], [t7].[[_METADATA]]2] AS [_METADATA2], [t7].[test2], [t7].[ID3], [t7].[CODE3], [t7].[[_TRANSLATIONS]]2] AS [_TRANSLATIONS2], [t7].[[_METADATA]]3] AS [_METADATA3], [t7].[test3], [t7].[ID4], [t7].[ID_LINE], [t7].[ID_GROUP], [t7].[ID_CLASS], [t7].[ID_FAM], [t7].[ID_ARTICLE]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ID], [t0].[ID_BRAND], [t0].[CODE], [t0].[CODFOR], [t0].[COD_ALT01], [t0].[COD_ALT02], [t0].[COD_ALT03], [t0].[ID_UOM], [t0].[IS_ACTIVE], [t0].[_ATTRIBUTES], [t0].[_DOCUMENTS], [t0].[_SEO], [t0].[_TRANSLATIONS], [t0].[_TAGS], [t0].[_NOTES], [t0].[_METADATA], [t0].[IS_B2B], [t0].[IS_B2C], [t0].[IS_PROMO], [t0].[IS_NEWS], [t0].[CAN_BE_RETURNED], [t0].[IS_SHIPPABLE], [t0].[HAS_SHIPPING_COSTS], [t0].[IS_PURCHEASABLE], [t2].[test], [t2].[ID], [t2].[CODE], [t2].[BUSINESS_NAME], [t2].[NAME], [t2].[PHONE_01], [t2].[PHONE_02], [t2].[PHONE_03], [t2].[FAX_01], [t2].[FAX_02], [t2].[COUNTRY_01], [t2].[CITY_01], [t2].[ADDRESS_01], [t2].[COUNTRY_02], [t2].[CITY_02], [t2].[ADDRESS_02], [t2].[EMAIL_01], [t2].[EMAIL_02], [t2].[PEC], [t2].[SITE_01], [t2].[SITE_02], [t2].[SITE_03], [t2].[SITE_04], [t2].[VAT_NUMBER], [t2].[SORT], [t2].[GROUPID_01], [t2].[IS_GROUPLEADER_01], [t2].[GROUPID_02], [t2].[IS_GROUPLEADER_02], [t2].[IS_ACTIVE], [t2].[_DOCUMENTS], [t2].[_SEO], [t2].[_METADATA], [t4].[test], [t4].[ID], [t4].[CODE], [t4].[_TRANSLATIONS], [t4].[_METADATA], [t6].[test], [t6].[ID], [t6].[ID_LINE], [t6].[ID_GROUP], [t6].[ID_CLASS], [t6].[ID_FAM], [t6].[ID_ARTICLE]) AS [ROW_NUMBER], [t0].[ID], [t0].[ID_BRAND], [t0].[CODE], [t0].[CODFOR], [t0].[COD_ALT01], [t0].[COD_ALT02], [t0].[COD_ALT03], [t0].[ID_UOM], [t0].[IS_ACTIVE], [t0].[_ATTRIBUTES], [t0].[_DOCUMENTS], [t0].[_SEO], [t0].[_TRANSLATIONS], [t0].[_TAGS], [t0].[_NOTES], [t0].[_METADATA], [t0].[IS_B2B], [t0].[IS_B2C], [t0].[IS_PROMO], [t0].[IS_NEWS], [t0].[CAN_BE_RETURNED], [t0].[IS_SHIPPABLE], [t0].[HAS_SHIPPING_COSTS], [t0].[IS_PURCHEASABLE], [t2].[test], [t2].[ID] AS [ID2], [t2].[CODE] AS [CODE2], [t2].[BUSINESS_NAME], [t2].[NAME], [t2].[PHONE_01], [t2].[PHONE_02], [t2].[PHONE_03], [t2].[FAX_01], [t2].[FAX_02], [t2].[COUNTRY_01], [t2].[CITY_01], [t2].[ADDRESS_01], [t2].[COUNTRY_02], [t2].[CITY_02], [t2].[ADDRESS_02], [t2].[EMAIL_01], [t2].[EMAIL_02], [t2].[PEC], [t2].[SITE_01], [t2].[SITE_02], [t2].[SITE_03], [t2].[SITE_04], [t2].[VAT_NUMBER], [t2].[SORT], [t2].[GROUPID_01], [t2].[IS_GROUPLEADER_01], [t2].[GROUPID_02], [t2].[IS_GROUPLEADER_02], [t2].[IS_ACTIVE] AS [IS_ACTIVE2], [t2].[_DOCUMENTS] AS [[_DOCUMENTS]]2], [t2].[_SEO] AS [[_SEO]]2], [t2].[_METADATA] AS [[_METADATA]]2], [t4].[test] AS [test2], [t4].[ID] AS [ID3], [t4].[CODE] AS [CODE3], [t4].[_TRANSLATIONS] AS [[_TRANSLATIONS]]2], [t4].[_METADATA] AS [[_METADATA]]3], [t6].[test] AS [test3], [t6].[ID] AS [ID4], [t6].[ID_LINE], [t6].[ID_GROUP], [t6].[ID_CLASS], [t6].[ID_FAM], [t6].[ID_ARTICLE]
    FROM [dbo].[tbl_ana_Articles] AS [t0]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t1].[ID], [t1].[CODE], [t1].[BUSINESS_NAME], [t1].[NAME], [t1].[PHONE_01], [t1].[PHONE_02], [t1].[PHONE_03], [t1].[FAX_01], [t1].[FAX_02], [t1].[COUNTRY_01], [t1].[CITY_01], [t1].[ADDRESS_01], [t1].[COUNTRY_02], [t1].[CITY_02], [t1].[ADDRESS_02], [t1].[EMAIL_01], [t1].[EMAIL_02], [t1].[PEC], [t1].[SITE_01], [t1].[SITE_02], [t1].[SITE_03], [t1].[SITE_04], [t1].[VAT_NUMBER], [t1].[SORT], [t1].[GROUPID_01], [t1].[IS_GROUPLEADER_01], [t1].[GROUPID_02], [t1].[IS_GROUPLEADER_02], [t1].[IS_ACTIVE], [t1].[_DOCUMENTS], [t1].[_SEO], [t1].[_METADATA]
        FROM [dbo].[tbl_ana_Brands] AS [t1]
        ) AS [t2] ON [t2].[ID] = [t0].[ID_BRAND]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t3].[ID], [t3].[CODE], [t3].[_TRANSLATIONS], [t3].[_METADATA]
        FROM [dbo].[tbl_ana_UoMs] AS [t3]
        ) AS [t4] ON [t4].[ID] = [t0].[ID_UOM]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t5].[ID], [t5].[ID_LINE], [t5].[ID_GROUP], [t5].[ID_CLASS], [t5].[ID_FAM], [t5].[ID_ARTICLE]
        FROM [dbo].[tbl_src_ArticlesCategories] AS [t5]
        ) AS [t6] ON [t6].[ID_ARTICLE] = [t0].[ID]
    WHERE (
        (CASE 
            WHEN 1 = 1 THEN CONVERT(Int,[t0].[IS_ACTIVE])
            ELSE 0
         END)) = 1
    ) AS [t7]
WHERE [t7].[ROW_NUMBER]  BETWEEN 7272 + 1 AND 7284
ORDER BY [t7].[ROW_NUMBER]

Aqui plano de execução de consulta lenta: https://www.brentozar.com/pastetheplan/?id=Sk-rLnY3F

Consulta rápida revisada (conjunto de dados de ~7K elementos, leva ~0s):

SELECT [t7].[ID], [t7].[ID_BRAND], [t7].[CODE], [t7].[CODFOR], [t7].[COD_ALT01], [t7].[COD_ALT02], [t7].[COD_ALT03], [t7].[ID_UOM], [t7].[IS_ACTIVE], [t7].[_ATTRIBUTES] AS [_ATTRIBUTES], [t7].[_DOCUMENTS] AS [_DOCUMENTS], [t7].[_SEO] AS [_SEO], [t7].[_TRANSLATIONS] AS [_TRANSLATIONS], [t7].[_TAGS] AS [_TAGS], [t7].[_NOTES] AS [_NOTES], [t7].[_METADATA] AS [_METADATA], [t7].[IS_B2B], [t7].[IS_B2C], [t7].[IS_PROMO], [t7].[IS_NEWS], [t7].[CAN_BE_RETURNED], [t7].[IS_SHIPPABLE], [t7].[HAS_SHIPPING_COSTS], [t7].[IS_PURCHEASABLE], [t7].[test], [t7].[ID2], [t7].[CODE2], [t7].[BUSINESS_NAME], [t7].[NAME], [t7].[PHONE_01], [t7].[PHONE_02], [t7].[PHONE_03], [t7].[FAX_01], [t7].[FAX_02], [t7].[COUNTRY_01], [t7].[CITY_01], [t7].[ADDRESS_01], [t7].[COUNTRY_02], [t7].[CITY_02], [t7].[ADDRESS_02], [t7].[EMAIL_01], [t7].[EMAIL_02], [t7].[PEC], [t7].[SITE_01], [t7].[SITE_02], [t7].[SITE_03], [t7].[SITE_04], [t7].[VAT_NUMBER], [t7].[SORT], [t7].[GROUPID_01], [t7].[IS_GROUPLEADER_01], [t7].[GROUPID_02], [t7].[IS_GROUPLEADER_02],[t7].[IS_ACTIVE2], [t7].[[_DOCUMENTS]]2] AS [_DOCUMENTS2], [t7].[[_SEO]]2] AS [_SEO2], [t7].[[_METADATA]]2] AS [_METADATA2], [t7].[test2], [t7].[ID3], [t7].[CODE3], [t7].[[_TRANSLATIONS]]2] AS [_TRANSLATIONS2], [t7].[[_METADATA]]3] AS [_METADATA3], [t7].[test3], [t7].[ID4], [t7].[ID_LINE], [t7].[ID_GROUP], [t7].[ID_CLASS], [t7].[ID_FAM], [t7].[ID_ARTICLE]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ID]) AS [ROW_NUMBER], [t0].[ID], [t0].[ID_BRAND], [t0].[CODE], [t0].[CODFOR], [t0].[COD_ALT01], [t0].[COD_ALT02], [t0].[COD_ALT03], [t0].[ID_UOM], [t0].[IS_ACTIVE], [t0].[_ATTRIBUTES], [t0].[_DOCUMENTS], [t0].[_SEO], [t0].[_TRANSLATIONS], [t0].[_TAGS], [t0].[_NOTES], [t0].[_METADATA], [t0].[IS_B2B], [t0].[IS_B2C], [t0].[IS_PROMO], [t0].[IS_NEWS], [t0].[CAN_BE_RETURNED], [t0].[IS_SHIPPABLE], [t0].[HAS_SHIPPING_COSTS], [t0].[IS_PURCHEASABLE], [t2].[test], [t2].[ID] AS [ID2], [t2].[CODE] AS [CODE2], [t2].[BUSINESS_NAME], [t2].[NAME], [t2].[PHONE_01], [t2].[PHONE_02], [t2].[PHONE_03], [t2].[FAX_01], [t2].[FAX_02], [t2].[COUNTRY_01], [t2].[CITY_01], [t2].[ADDRESS_01], [t2].[COUNTRY_02], [t2].[CITY_02], [t2].[ADDRESS_02], [t2].[EMAIL_01], [t2].[EMAIL_02], [t2].[PEC], [t2].[SITE_01], [t2].[SITE_02], [t2].[SITE_03], [t2].[SITE_04], [t2].[VAT_NUMBER], [t2].[SORT], [t2].[GROUPID_01], [t2].[IS_GROUPLEADER_01], [t2].[GROUPID_02], [t2].[IS_GROUPLEADER_02], [t2].[IS_ACTIVE] AS [IS_ACTIVE2], [t2].[_DOCUMENTS] AS [[_DOCUMENTS]]2], [t2].[_SEO] AS [[_SEO]]2], [t2].[_METADATA] AS [[_METADATA]]2], [t4].[test] AS [test2], [t4].[ID] AS [ID3], [t4].[CODE] AS [CODE3], [t4].[_TRANSLATIONS] AS [[_TRANSLATIONS]]2], [t4].[_METADATA] AS [[_METADATA]]3], [t6].[test] AS [test3], [t6].[ID] AS [ID4], [t6].[ID_LINE], [t6].[ID_GROUP], [t6].[ID_CLASS], [t6].[ID_FAM], [t6].[ID_ARTICLE]
    FROM [dbo].[tbl_ana_Articles] AS [t0]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t1].[ID], [t1].[CODE], [t1].[BUSINESS_NAME], [t1].[NAME], [t1].[PHONE_01], [t1].[PHONE_02], [t1].[PHONE_03], [t1].[FAX_01], [t1].[FAX_02], [t1].[COUNTRY_01], [t1].[CITY_01], [t1].[ADDRESS_01], [t1].[COUNTRY_02], [t1].[CITY_02], [t1].[ADDRESS_02], [t1].[EMAIL_01], [t1].[EMAIL_02], [t1].[PEC], [t1].[SITE_01], [t1].[SITE_02], [t1].[SITE_03], [t1].[SITE_04], [t1].[VAT_NUMBER], [t1].[SORT], [t1].[GROUPID_01], [t1].[IS_GROUPLEADER_01], [t1].[GROUPID_02], [t1].[IS_GROUPLEADER_02], [t1].[IS_ACTIVE], [t1].[_DOCUMENTS], [t1].[_SEO], [t1].[_METADATA]
        FROM [dbo].[tbl_ana_Brands] AS [t1]
        ) AS [t2] ON [t2].[ID] = [t0].[ID_BRAND]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t3].[ID], [t3].[CODE], [t3].[_TRANSLATIONS], [t3].[_METADATA]
        FROM [dbo].[tbl_ana_UoMs] AS [t3]
        ) AS [t4] ON [t4].[ID] = [t0].[ID_UOM]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t5].[ID], [t5].[ID_LINE], [t5].[ID_GROUP], [t5].[ID_CLASS], [t5].[ID_FAM], [t5].[ID_ARTICLE]
        FROM [dbo].[tbl_src_ArticlesCategories] AS [t5]
        ) AS [t6] ON [t6].[ID_ARTICLE] = [t0].[ID]
    WHERE (
        (CASE 
            WHEN 1 = 1 THEN CONVERT(Int,[t0].[IS_ACTIVE])
            ELSE 0
         END)) = 1
    ) AS [t7]
WHERE [t7].[ROW_NUMBER] BETWEEN 7272 + 1 AND 7284
ORDER BY [t7].[ROW_NUMBER]

Aqui o plano de execução de consulta rápida: https://www.brentozar.com/pastetheplan/?id=B10l82K2Y

Nota: todo o código de consulta é gerado automaticamente pelo ORM

As duas consultas parecem muito semelhantes e não está claro para mim o que melhora tão drasticamente o desempenho. Eu realmente aprecio uma dica sobre o que ajuda tanto o SQL Server, para que eu possa entender melhor como ajustar o ORM no futuro.

Estou tendo um desempenho ruim em uma instrução de inserção/atualização de linha única em uma tabela quando uma coluna nvarchar(max) tem poucos MB de dados.

Aqui minha estrutura de tabela:

CREATE TABLE [dbo].[tbl_set_Cart](
    [ID] [int] NOT NULL,
    [AS400_CUSTOMER_COD] [nvarchar](50) NOT NULL,
    [AS400_LISTIN] [int] NOT NULL,
    [VALUE] [nvarchar](max) NOT NULL,
    [DELIVERY_COSTS] [nvarchar](max) NOT NULL,
    [ITEMS_COUNT] [int] NOT NULL,
    [ITEMS] [nvarchar](max) NOT NULL,
    [KIND] [int] NOT NULL,
    [CHECKOUT_INFO] [nvarchar](max) NOT NULL,
    [ISSUES] [nvarchar](max) NOT NULL,
    [LAST_CHECK] [datetime] NOT NULL,
    [USER_ID] [int] NOT NULL,
    [IMPERSONATED_USER_ID] [int] NOT NULL,
    [OVERRIDE_PRICES] [bit] NOT NULL,
    [HAS_ISSUE] [bit] NOT NULL,
    [IS_CONFIRMED] [bit] NOT NULL,
    [IS_COLLECTED] [bit] NOT NULL,
    [_METADATA] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_tbl_set_Cart] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Aqui um exemplo de uma declaração de atualização

DECLARE @p0 AS INT = [cart_id];
DECLARE @p1 AS INT = [entry_count];
DECLARE @p2 AS NVARCHAR(MAX) = '..document..' --~8MB;

UPDATE [dbo].[tbl_set_Cart]
SET [ITEMS_COUNT] = @p1, [ITEMS] = @p2
WHERE [ID] = @p0

Executando este comando na mesma máquina do banco de dados (um ambiente de desenvolvimento para que não haja outra carga de trabalho) obtive este desempenho:

Realmente me surpreende que seja tão lento carregar um texto no SQL Server, então o que estou perguntando é se há uma maneira melhor de conseguir isso.

O documento é um grande JSON que não excederá os 10 MB de dados (eu fiz o benchmark relatado acima no pior cenário) e não tenho problemas em convertê-lo em um BLOB ou outras estruturas de dados se o desempenho melhorar. Se for uma ideia melhor, também posso usar um procedimento armazenado para carregar o texto grande.

Meio Ambiente:

Um processador lógico Windows Server 2019 16 2,1 GHz e Microsoft SQL Server Standard (64 bits) versão 15.0.2070.41 com 4 GB de RAM reservada, 16 núcleos licenciados.

(é um ambiente apenas de desenvolvimento e, neste caso, sou o único trabalhando sem nenhum outro programa ou atividade agendada em execução.)

Testei novamente um carrinho diferente (um pouco menor) aqui vezes (estatísticas do cliente) e plano de execução.

link aqui: https://www.brentozar.com/pastetheplan/?id=SJcPhDUFK

Se eu inserir uma nova linha é mais rápido:

link aqui: https://www.brentozar.com/pastetheplan/?id=S1FDjD8KF

Para a instrução insert, usei os dados gerados pelo SQL Server (tarefa -> tabela de scripts -> somente dados)

O que espero alcançar é um tempo de atualização <0,5s

No meu cenário o json geralmente nasce pequeno e depois aumenta dia a dia até o tamanho que estou testando. Posso começar a pensar em armazená-los como arquivo?

Eu pré-aloquei espaço no arquivo de log. E verifique novamente se o modelo de recuperação está definido como simples. O que posso fazer para melhorar ainda mais o desempenho?