Vaccano's questions

Fundo

Aqui está um exemplo próximo do que estou trabalhando:

CREATE TABLE sandboxTesting.TemporalTest (    
    GroupNumber VARCHAR(25) NOT NULL,
    StartEffectiveWhen DATE NOT NULL,
    EndEffectiveWhen DATE NULL,
    ModifiedWhen DATETIME NULL,
    IsReady BIT NOT NULL DEFAULT 0,
    RowValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    RowValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (RowValidFrom, RowValidTo),
    CONSTRAINT PK_TemporalTest PRIMARY KEY CLUSTERED  
    (
        GroupNumber, StartEffectiveWhen
    )
) WITH (SYSTEM_VERSIONING=ON (HISTORY_TABLE=sandboxTesting.TemporalTestHistory))
GO 

CREATE TRIGGER sandboxTesting.OnModify ON sandboxTesting.TemporalTest AFTER UPDATE AS
BEGIN
    
    UPDATE  temporalTst
    SET     temporalTst.IsReady = 0,
            temporalTst.ModifiedWhen = GETDATE()
    FROM    sandboxTesting.TemporalTest temporalTst
            JOIN deleted del
                ON del.GroupNumber = temporalTst.GroupNumber
                AND del.StartEffectiveWhen = temporalTst.StartEffectiveWhen
    WHERE   -- All business columns go here with OR statements in between them.
            -- The idea is that if anything changes except the IsReady flag, then we 
            -- set the IsReady back to false.  (IsReady has to be set by itself)
            del.EndEffectiveWhen <> temporalTst.EndEffectiveWhen 
            OR (del.EndEffectiveWhen IS NULL AND temporalTst.EndEffectiveWhen IS NOT NULL)
            OR (del.EndEffectiveWhen IS NOT NULL AND temporalTst.EndEffectiveWhen IS NULL)
END
GO

-- Insert new test
INSERT INTO [sandboxTesting].[TemporalTest] ([GroupNumber], [StartEffectiveWhen], [EndEffectiveWhen],  [ModifiedWhen])
VALUES ('12345', '2024-01-1', NULL, NULL)
GO

-- Set is as ready 
UPDATE  sandboxTesting.TemporalTest
SET     IsReady = 1
WHERE   GroupNumber = '12345' AND StartEffectiveWhen = '2024-01-1'
GO 

-- Change the End date
UPDATE  sandboxTesting.TemporalTest
SET     EndEffectiveWhen = '2024-09-02'
WHERE   GroupNumber = '12345' AND StartEffectiveWhen = '2024-01-1'
        
-- Set the new end date as ready for billing.
UPDATE  sandboxTesting.TemporalTest
SET     IsReady = 1
WHERE   GroupNumber = '12345' AND StartEffectiveWhen = '2024-01-1'
GO 


-- Select the Data
SELECT * FROM sandboxTesting.TemporalTest for SYSTEM_TIME ALL 
ORDER BY GroupNumber, StartEffectiveWhen desc, RowValidFrom DESC, RowValidTo DESC, ModifiedWhen desc

-- Select the Raw Data (for comparison)
SELECT * FROM sandboxTesting.TemporalTest
UNION ALL 
SELECT * FROM sandboxTesting.TemporalTestHistory
ORDER BY GroupNumber, StartEffectiveWhen desc, RowValidFrom DESC, RowValidTo DESC, ModifiedWhen desc

Quando executo isso, este é o primeiro resultado:

E o segundo conjunto de saída se parece com isto:

Isso é diferente porque o primeiro resultado da consulta usa a for SYSTEM_TIME ALLcláusula, enquanto o segundo apenas consulta os dados brutos.

A diferença é que, no primeiro conjunto de dados, a segunda e a terceira linhas do segundo conjunto de dados foram filtradas. Elas foram removidas porque a segunda e a terceira linhas têm uma data de início e uma data de término que são as mesmas. (Basicamente dizendo que essas linhas nunca estiveram realmente em vigor.)

Pergunta

O que preciso saber é se posso confiar que essa "diferença de tempo zero" sempre estará presente para dados que foram atualizados por meio de um AFTERgatilho? (Preciso escrever algumas consultas que falharão se esse não for o caso.)

O que quero dizer com isso é o seguinte: se meu servidor estivesse sendo bombardeado por milhares de consultas, todas realizando quantidades absurdas de E/S e cálculos, os valores RowValidFrome RowValidTopara a segunda e terceira linhas do segundo conjunto de dados ainda teriam uma diferença de 0?

Em outras palavras, esses valores são os mesmos por causa da lógica da transação? Ou são os mesmos porque meu servidor é rápido e não está realmente sob nenhuma pressão agora?

Aqui está o SQL Fiddle para minha pergunta: https://sqlfiddle.com/sql-server/online-compiler?id=ab1634d7-fec7-4918-ac1c-3f4fcac8dc92

Eu tenho os seguintes dados de amostra:

DROP TABLE IF EXISTS #Price
CREATE TABLE #Price (DataId INT IDENTITY(1,1), NameOfWidget VARCHAR(50), Price MONEY, PriceScheduleId INT, 
                     StartEffectiveWhen DATE, EndEffectiveWhen DATE)

INSERT INTO #Price (NameOfWidget, Price, PriceScheduleId, StartEffectiveWhen, EndEffectiveWhen)
VALUES
    ('CoolWidget', 3.51, 1, '2015-1-1', '2021-12-31'),
    ('CoolWidget', 2.00, 2, '2017-1-1', '2022-12-31'),
    ('CoolWidget', 4.23, 1, '2021-1-1', '2100-12-31'),
    ('CoolWidget', 2.00, 2, '2021-1-1', '2100-12-31'),
    ('OtherWidget', 13.24, 1, '2014-1-1', '2100-12-31')

Agora preciso colocar esses dados no seguinte formato:

NameOfWidget    StartEffectiveWhen  EndEffectiveWhen
CoolWidget      2015-01-01          2016-12-31
CoolWidget      2017-01-01          2021-12-31
CoolWidget      2021-01-01          2022-12-31
CoolWidget      2023-01-01          2100-12-31
OtherWidget     2015-01-01          2100-12-31

Isso segue a seguinte lógica, agrupado por NameOfWidget:

1. Encontra o mais baixo StartEffectiveWhen.
2. Encontra o próximo valor mais baixo StartEffectiveWhenou EndEffectiveWhen. Essa data se torna a próxima EndEffectiveWhen. Mas se fosse um EndEffectiveWhen, então subtraímos um dia dele.
3. Em seguida, repete as etapas acima, exceto que exclui os dados já utilizados.

O objetivo é ter uma linha para cada "janela" do período de tempo.

O código abaixo faz o que preciso, mas usa um loop para fazer isso.

Como sempre, meus dados reais são muito mais complexos. Ele também possui 56 milhões de linhas. (O código abaixo leva 3 horas para ser executado em meus dados reais)

Estou esperando uma maneira de fazer o que tenho abaixo sem precisar fazer um loop.

Meu código (lento, baseado em loop)

DROP TABLE IF EXISTS #EffectiveRange
CREATE TABLE #EffectiveRange (EffectiveDateId INT IDENTITY(1,1), StartEffectiveWhen DATE, EndEffectiveWhen DATE, EndWhenOfRowsThatMatchStartDate DATE, SecondStartWhen DATE, NameOfWidget VARCHAR(50), CalculationRound INT)

DECLARE @CalculationRound INT = 1

-- This is < 15 in my real code
WHILE (@CalculationRound < 5)
BEGIN

    -- Find the first/next range in source price table.
    INSERT INTO  #EffectiveRange(StartEffectiveWhen, EndWhenOfRowsThatMatchStartDate, SecondStartWhen, NameOfWidget, CalculationRound)
    SELECT  MIN(price.StartEffectiveWhen) StartWhen, NULL, NULL, price.NameOfWidget, @CalculationRound
    FROM    #Price price            
    WHERE   price.StartEffectiveWhen > 
            (SELECT MAX(maxValue.StartWhen) 
             FROM 
                (SELECT MAX(rangesSub.StartEffectiveWhen) AS StartWhen
                 FROM   #EffectiveRange AS rangesSub
                 WHERE  rangesSub.NameOfWidget = price.NameOfWidget
                 UNION ALL
                 SELECT CAST('1/1/1900' AS DATE) AS StartWhen) AS maxValue)
    GROUP BY price.NameOfWidget 

    -- Find the end date for the rows that match the start date we just found.
    UPDATE  #EffectiveRange SET
       EndWhenOfRowsThatMatchStartDate = calc.EndWhenOfRowsThatMatchStartDateCalc
    FROM 
        (
            SELECT  MIN(price.EndEffectiveWhen) AS EndWhenOfRowsThatMatchStartDateCalc, price.NameOfWidget
            FROM    #Price price
                    JOIN #EffectiveRange ranges
                        ON ranges.NameOfWidget = price.NameOfWidget
                        AND ranges.CalculationRound = @CalculationRound
            WHERE   price.StartEffectiveWhen = ranges.StartEffectiveWhen
            GROUP BY price.NameOfWidget
        ) AS calc
        JOIN #EffectiveRange ranges
            ON ranges.NameOfWidget = calc.NameOfWidget
            AND ranges.CalculationRound = @CalculationRound

    -- Find the next largest start date for the calculation round.        
    UPDATE  #EffectiveRange SET
        SecondStartWhen = calc.SecondStartWhen
    FROM 
        (
            SELECT  MIN(price.StartEffectiveWhen) SecondStartWhen, price.NameOfWidget
            FROM    #Price price
                    JOIN #EffectiveRange ranges
                        ON ranges.NameOfWidget = price.NameOfWidget
                        AND ranges.CalculationRound = @CalculationRound
            WHERE   price.StartEffectiveWhen > ranges.StartEffectiveWhen
            GROUP BY price.NameOfWidget
        ) AS calc
        JOIN #EffectiveRange ranges
            ON ranges.NameOfWidget = calc.NameOfWidget
            AND ranges.CalculationRound = @CalculationRound

    -- Send the EndWhen to be the lesser of EndWhenOfRowsThatMatchStartDate and secondStartDate.  
    -- This will define our window of effectiveness for this round of the test. (once we have all of the windows (aka each time a change was made),
    -- we will caclulate the price for each window.
    UPDATE #EffectiveRange SET
        EndEffectiveWhen = IIF((EndWhenOfRowsThatMatchStartDate < SecondStartWhen) OR SecondStartWhen IS NULL, EndWhenOfRowsThatMatchStartDate, DATEADD(DAY, -1, SecondStartWhen))
    WHERE   CalculationRound = @CalculationRound

    SET @CalculationRound = @CalculationRound + 1
END

-- Show the final result
SELECT  ranges.NameOfWidget, ranges.StartEffectiveWhen, ranges.EndEffectiveWhen
FROM    #EffectiveRange ranges
ORDER BY ranges.NameOfWidget, ranges.StartEffectiveWhen


DROP TABLE IF EXISTS #EffectiveRange
DROP TABLE IF EXISTS #Price

Atualizar

Este SQL Fiddle mostra o que acabei fazendo:

https://sqlfiddle.com/sql-server/online-compiler?id=b0d81632-b14d-4374-a80e-0835750f48bc

@Akina me fez pensar sobre meu problema na direção certa. (Obrigado @Akina!)

Por precaução, aqui está a consulta que acabei usando:

DROP TABLE IF EXISTS #Price
CREATE TABLE #Price (DataId INT IDENTITY(1,1), NameOfWidget VARCHAR(50), Price MONEY, PriceScheduleId INT, StartEffectiveWhen DATE, EndEffectiveWhen DATE)

INSERT INTO #Price (NameOfWidget, Price, PriceScheduleId, StartEffectiveWhen, EndEffectiveWhen)
VALUES
    ('CoolWidget', 3.51, 1, '2015-1-1', '2021-12-31'),
    ('CoolWidget', 2.00, 2, '2017-1-1', '2022-12-31'),
    ('CoolWidget', 4.23, 1, '2021-1-1', '2100-12-31'),
    ('CoolWidget', 2.00, 2, '2021-1-1', '2100-12-31'),
    ('OtherWidget', 13.24, 1, '2014-1-1', '2018-5-4'),
    ('OtherWidget', 13.24, 1, '2018-5-6', '2019-12-31'),
    ('OtherWidget', 13.24, 1, '2020-1-1', '2100-12-31')

;WITH OrderedDates AS 
(
    SELECT  priceStart.NameOfWidget, priceStart.StartEffectiveWhen AS DateWhen, 1 AS IsStartDate, 0 AS IsEndDate
    FROM    #Price priceStart

    UNION 

    SELECT  priceStart.NameOfWidget, priceStart.EndEffectiveWhen AS DateWhen, 0 AS IsStartDate, 1 AS IsEndDate
    FROM    #Price priceStart
    
)
SELECT  OrderedDates.NameOfWidget,
        CASE 
            WHEN LAG(OrderedDates.DateWhen) OVER (PARTITION BY OrderedDates.NameOfWidget ORDER BY OrderedDates.DateWhen) IS NULL THEN '1900-1-1'
            WHEN LAG(OrderedDates.IsStartDate ) OVER (PARTITION BY OrderedDates.NameOfWidget ORDER BY OrderedDates.DateWhen) = 1 
                THEN  LAG(OrderedDates.DateWhen) OVER (PARTITION BY OrderedDates.NameOfWidget ORDER BY OrderedDates.DateWhen)
            ELSE DATEADD(DAY, 1, LAG(OrderedDates.DateWhen) OVER (PARTITION BY OrderedDates.NameOfWidget ORDER BY OrderedDates.DateWhen))
        END AS StartEffectiveWhen, 
        
        CASE
            WHEN OrderedDates.IsEndDate = 1 THEN OrderedDates.DateWhen
            ELSE DATEADD(DAY, -1, OrderedDates.DateWhen)
        END AS EndEffectiveWhen
FROM    OrderedDates
ORDER BY OrderedDates.NameOfWidget

Pelo que entendi, quando você define uma coluna em uma tabela, você define sua precisão. Essa precisão leva 1 byte e é armazenada no nível da coluna. Se você usar uma precisão de 5 ou mais, uma coluna DateTime2 terá 8 bytes por linha. (A precisão não é armazenada no nível da linha.)

Mas quando você converte esse mesmo DateTime2 como um VarBinary, levará 9 bytes. Isso ocorre porque ele precisa do byte de precisão armazenado no nível da coluna.

Estou curioso para saber como isso se relaciona quando um DateTime2 é armazenado na memória. Digamos que eu tenha 1.000.000 DateTime2s na memória (cada um com precisão de 5 ou mais). Isso vai ocupar 8.000.000 bytes de memória ou 9.000.000 bytes de memória?

Basicamente, eu gostaria de saber se um DateTime2 de precisão padrão causará mais pressão na expectativa de vida da página do que um DateTime normal?

Fundo

Eu tenho um usuário SQL que tem a seguinte permissão:

GRANT CREATE SEQUENCE ON SCHEMA::seq TO SequenceCreator  
GO

Eu gostaria de poder fazer com que esse usuário criasse uma sequência que pertence a ele. Mas quando eu executo o seguinte:

EXECUTE AS USER = 'SequenceCreator'
CREATE SEQUENCE seq.Testing START WITH 1 INCREMENT BY 1 NO CACHE 
REVERT

O proprietário é dbo, herdado do esquema. Como mostra a seguinte consulta:

;with objects_cte as
(    select o.name, o.type_desc,
            case
                when o.principal_id is null then s.principal_id
                else o.principal_id
            end as principal_id
    from    sys.objects o
            INNER join sys.schemas s
                ON o.schema_id = s.schema_id )
select cte.name, dp.name AS owner
from    objects_cte cte
        JOIN sys.database_principals dp
            ON cte.principal_id = dp.principal_id
WHERE  cte.name = 'Testing'

Resultados:

name            name
Testing         dbo

Mas quando eu (as dbo) executo isso:

ALTER AUTHORIZATION ON seq.[Test--Things] TO SequenceCreator

Em seguida, os resultados dessa consulta mudam para:

name      owner
Testing   SequenceCreator

Mas se eu largar a sequência e executar isso:

EXECUTE AS USER = 'SequenceCreator'
CREATE SEQUENCE seq.Testing START WITH 1 INCREMENT BY 1 NO CACHE 
ALTER AUTHORIZATION ON seq.[Testing] TO SequenceCreator
REVERT

Estou tendo o erro a seguir:

Cannot find the object 'Testing', because it does not exist or you do not have permission.

Isso é esperado porque o usuário SequenceCreatornão tem permissão para ver muito menos altera sequência.

Mas parece que, já que o usuário está criando, deve ser capaz de definir quem é o proprietário também. (Semelhante a uma CREATE SCHEMA seq AUTHORIZATION dbochamada)

Pergunta

Como posso fazer com que o usuário criador ( SequenceCreator) possua a sequência que ele cria sem precisar de um usuário de nível superior para transferir a propriedade?

Temos um gatilho Após Inserir e Atualizar em uma de nossas tabelas. O gatilho basicamente cria uma carga útil json e a enfileira em um sistema RabbitMQ.

Hoje um grande script de inserção foi executado na mesa (mais de 50.000 inserções). Este cenário não foi testado ou contabilizado e agora estamos tendo problemas de desempenho nesse banco de dados.

Notamos que o RabbitMQ está tendo os registros chegando lentamente por um longo período de tempo. Mesmo que os dados estejam no lugar por um tempo (porque é um After Trigger).

Parece que os gatilhos After foram enfileirados de alguma forma e estão trabalhando muito lentamente no sistema.

Como os eventos After Trigger são monitorados para execução? Eles estão na fila em algum lugar? Existe uma maneira que eu possa limpá-los?

Eu tenho uma visão parecida com esta:

CREATE VIEW Shipment.Shipment AS

    SELECT  CASE 
                WHEN shipmentOld.SHIPMENT_ID = 0 OR shipmentOld.SHIPMENT_ID = -1 THEN NULL
                ELSE shipmentOld.SHIPMENT_ID
            END AS ShipmentId, 

            OtherValue, SomeOtherValue, OtherStuff
    FROM    dbo.tblShipment_Old shipmentOld
GO

Então, se eu fizer uma consulta assim:

SELECT * FROM Shipment.Shipment WHERE ShipmentId = 18140

Eu recebo uma varredura completa do índice. O motivo é que ele está executando a expressão case para ShipmentId em todas as linhas da tabela e comparando o resultado com meu ShipmentId (18140). A própria definição de não-sargável.

Estou usando essa visão para normalizar dados legados para que eu possa escrever um novo aplicativo sobre eles. Eu só quero que a expressão case seja executada na saída.

Acho que isso não é possível, mas pensei em perguntar antes de buscar opções mais extremas. Então aqui está a minha pergunta:

É possível ainda fazer com que minha saída ShipmentId passe pela expressão case, mas também tenha uma consulta sargável quando ShipmentId for usado na cláusula where ?

Estamos recebendo a seguinte exceção em um aplicativo:

Tempo limite de execução expirado. O período de tempo limite decorrido antes da conclusão da operação ou o servidor não está respondendo.

Então, eu olhei para a consulta e com certeza estava demorando muito. Então eu ajustei para levar menos de um segundo.

Mas ainda estou recebendo timeouts.

Então eu configurei uma sessão Extended Events para o evento Attention para pegar os tempos limite e ver o que está acontecendo (usando esta página como um guia).

Os resultados foram confusos. Vejo minhas consultas na lista, mas a duração registrada no rastreamento é inferior a um segundo!

Aqui estão alguns valores de exemplo que estou obtendo na coluna de duração para o tempo limite:

Estou confuso. Por que estou recebendo um erro de tempo limite do SQL Server, quando a duração mostra uma média de meio segundo?

NOTA: A saída do evento estendido mostra algumas linhas com mais de 30 segundos. Estes são timeouts legítimos para minha visão e estou trabalhando neles. (Apenas indico isso para mostrar que o evento estendido Attention parece estar funcionando para essas consultas.)

Achei que poderia estar bloqueando, então configurei um evento estendido do Relatório de Processos Bloqueados para ver (seguindo este guia ). Embora haja bloqueios acontecendo no servidor, eles não estão nos bancos de dados em que minha consulta está sendo executada.

O aplicativo está usando uma API chamada Dapper (um Micro ORM bastante popular que ouvi dizer que é usado para executar o Stack Exchange). Imagino que tenha um gerenciamento de conexão decente. Mas mesmo que eles tenham usado mal suas conexões, pelo que entendi, isso leva a vazamentos de recursos, não a tempos limite do SQL.

O tempo limite da consulta no aplicativo é definido como 120 segundos. Isso deve ser bastante tempo para obter a maioria das consultas que estou vendo relatadas como tempos limite.

Eu tenho uma consulta assim:

SELECT  ordIdent.Identifier, ord.OrderId
FROM    OrderIdentifier ordIdent
        JOIN [order] ord
            ON ordIdent.OrderId = ord.OrderId
WHERE   ordIdent.Identifier = '29584'
        OR ord.ClientOrderId = '29584'

A ideia é que estamos pesquisando usando o valor '29584' e não sabemos realmente se é a ClientOrderIdou an Identifier, então queremos pesquisar ambos.

Isso dá um desempenho realmente muito ruim. Ele puxa milhões de linhas (125 mil de uma e 20 milhões da outra)

Mas esta consulta:

SELECT  ordIdent.Identifier, ord.OrderId
FROM    OrderIdentifier ordIdent
        JOIN [order] ord
            ON ordIdent.OrderId = ord.OrderId
WHERE   ord.ClientOrderId = '01193504' 

UNION

SELECT  ordIdent.Identifier, ord.OrderId
FROM    OrderIdentifier ordIdent
        JOIN [order] ord
            ON ordIdent.OrderId = ord.OrderId
WHERE   ordIdent.Identifier = '01193504'

tem bom desempenho. Normalmente eu apenas faria isso e encerraria o dia.

Mas minha consulta real é muito grande e eu prefiro não chamá-la duas vezes (embora ainda seja melhor do que usar o "ou".) se eu tiver outras opções.

Parece que eu deveria fazer algum tipo de junção à esquerda para fazer isso funcionar. Mas eu não consigo envolver minha cabeça em torno disso.

Existe uma maneira de fazer isso em uma consulta que tenha uma junção à esquerda em vez de um "OR"? (Ou algum outro tipo de opção?)

ATUALIZAÇÃO:
OK, eu comecei a pensar sobre isso e percebi que poderia apenas fatorar o OR em um CTE que usa o unione, em seguida, usar o CTE na consulta real.

Vou deixar isso em aberto caso haja uma maneira melhor de lidar com isso, mas um CTE corrige meu problema.

Esta é uma consulta estática que mostra onde estou tentando obter

declare @Airbill VARCHAR(50) = '12345678912'
select stuff(stuff(@Airbill, 4, 0, '-'), 9, 0, '-')

Mas eu preciso torná-lo mais variável. Tenho a seguinte variável:

DECLARE @AirbillMask VARCHAR(50) = '999-9999-9999'

O que pode (e muda) mudar. É sempre '9's e '-'s. Mas a colocação deles muda.

• Se @AirbillMaské 999-99-99-9999meu resultado desejado seria 123-45-67-8912.
• Se @AirbillMaské 999-9999-9999meu resultado desejado seria 123-4567-8912.

Estou executando dbo.FindPatternLocation(@AirbillMask, '-') que retorna uma tabela de locais de traço.

Eu estava planejando encontrar um caminho para STUFFas partes dos @Airbilllocais correspondentes para produzir um campo calculado de FormattedAirbill, mas fiquei preso tentando iterar nas STUFFchamadas.

Existe uma maneira de ligar STUFFdentro de outra STUFFchamada, repetidamente, até que eu esteja sem filas?

Eu sinto que um CTE recursivo ou uma aplicação cruzada poderia fazer isso, mas não consigo entender isso.

NOTA: Eu preferiria fazer isso sem loop, se possível.

Eu estava escrevendo um INSTEAD OF INSERTgatilho e bati um erro neste código:

CASE 
   WHEN Inserted.KeyId IS NOT NULL THEN Inserted.KeyId
   ELSE NEXT VALUE FOR SomeSchema.NextKeyId
END

O erro foi:

A função NEXT VALUE FOR não pode ser usada dentro de CASE, CHOOSE, COALESCE, IIF, ISNULL e NULLIF.

A leitura mostra que essa é uma limitação bastante difícil, que acho que posso aceitar, mas gostaria de saber por que essa limitação existe.

Parece estranho para mim que ele não possa simplesmente chamar esse método para a parte do conjunto que corresponde a essa ramificação da instrução case.

Alguém pode explicar por que uma CASEdeclaração não permite NEXT VALUE FOR?

Acabei de obter uma configuração do Sql Server 2016 Availability Group (um primário, um secundário síncrono e um secundário assíncrono). Eu tenho cinco bancos de dados nos servidores.

Eu configurei um aplicativo para executar consultas no servidor (usando o Entity Framework com uma cadeia de conexão de "parceiro de failover").

Quando tentei testar o failover reiniciando o servidor primário, recebi o seguinte erro:

Servidor MySecondaryServer, banco de dados mestre não está configurado para espelhamento de banco de dados.

Parece estranho espelhar um banco de dados do sistema como este, então não o configuramos como um dos "Bancos de Dados de Disponibilidade".

Então, aqui está minha pergunta: Devo adicionar o banco de dados mestre à lista de "Bancos de Dados de Disponibilidade" e isso causará problemas se eu fizer isso?

Eu sou um desenvolvedor tentando trabalhar com um novo cluster Sql Server 2016 Always On.

Ao ler a documentação, estou obtendo informações conflitantes.

1. Alguma documentação me diz que eu só preciso adicionar "parceiro de failover = MySecondaryServer" à minha string de conexão.
2. Outros sites falam sobre o uso de um "Listener". E na minha seção Always On no SSMS 2016 há uma seção de Ouvintes do Grupo de Disponibilidade.

Qual é a diferença entre essas opções?

Essas opções são concorrentes (ou seja, você escolhe apenas uma) ou elas funcionam juntas?

Observações: Eu tentei o "parceiro de failover" e obtive sucesso limitado. Quando o primário foi reinicializado, tive que reiniciar meu serviço do Entity Framework antes que ele "falhasse" no servidor secundário. Estou pensando em configurar um Listener para ver se funcionará de forma mais dinâmica.

tenho a seguinte consulta

SELECT Ice.IceId, Ice.Code, Box.Ice, LEN(Box.Ice) AS IceCharLength,
       DATALENGTH(Box.Ice) AS DataLength, Box.Ice, ASCII(Box.Ice) AS ASCII, 
       CAST(Box.ice AS VARBINARY) AS VarBinary
FROM dbo.tblESPShipBox Box
LEFT JOIN ReferenceManual.IceType Ice
       ON Ice.Code = CASE
                        WHEN Box.ICE IS NULL THEN 'N'
                        WHEN RTRIM(Box.ICE) = '' THEN 'N'
                        WHEN Box.Ice IN ('', ' ', '/') THEN 'N'
                        ELSE Box.ICE
                      END
WHERE BOX_SEQ_NUM = '000023' AND BOX_TYPE_ID = 0

Ele retorna os seguintes resultados:

IceId       Code Ice  IceCharLength DataLength  Ice  ASCII       VarBinary
----------- ---- ---- ------------- ----------- ---- ----------- -------------
2           N         0             1                32          0x20
NULL        NULL      1             1                0           0x00

O estranho é que Box.ICEpara a primeira linha fica '' (Single Space String). A segunda linha parece ser '' (sem string de espaço). (A maneira como vejo isso é copiando a célula e colando em outro texto para ver se ela cria um espaço ou não.)

A Box.ICEcoluna é um char(1)então eu realmente não vejo como um '' (sem string de espaço) entrou lá.

Mas a parte estranha é que minha instrução de caso ainda deve corresponder a um caractere '' (sem espaço). Mas claramente não.

Como posso descobrir o que realmente está neste campo e fazer com que corresponda à minha declaração de caso?

Coisas que tentei:

• SSMSBoost usado para fazer uma cópia especial com caracteres incluídos da célula no NotePad++ (com mostrar todos os caracteres ativados). Fiz isso para garantir que não haja caracteres unicode não jogáveis.

Digamos que eu tenha uma exibição indexada semelhante a esta:

ALTER VIEW dbo.MyIndexedView WITH SCHEMABINDING
AS 
    SELECT ord.SomeColumn, COUNT_BIG(*) AS Count
    FROM dbo.Ordered ord
    WHERE ord.CreatedWhen >  CONVERT(DATETIME, '2014-11-01', 121)
    GROUP BY ord.SomeColumn
GO

CREATE UNIQUE CLUSTERED INDEX [CIX_MyIndexedView] ON dbo.MyIndexedView (SomeColumn)
GO

Se eu alterar o valor de data e hora nesta exibição (digamos para '2014-11-01'), o índice clusterizado precisará ser regenerado.

Existe uma maneira de fazer com que a exibição e o índice existentes permaneçam em vigor até que o novo seja totalmente criado e, em seguida, troque-os? Igual aOnline=On

Eu tenho um serviço da Web que pode disparar uma cadeia de eventos para um pedido. Ele faz isso quando recebe uma mensagem informando (máximo de cerca de 2 por segundo).

O problema que tenho é que tenho mais de uma instância do Web Service e, às vezes, diferentes partes do pedido podem atingir cada instância exatamente ao mesmo tempo. Isso faz com que a cadeia de eventos seja disparada mais de uma vez para a ordem (ruim).

Então, eu estava pensando, eu poderia fazer uma tabela que é apenas o ID do pedido e um sinalizador dizendo se a "cadeia de eventos" já foi disparada.

Eu poderia então iniciar uma transação no meu código. Algo assim:

mySqlConnection.BeginTransaction(); // C# Code

Eu poderia selecionar a linha para o pedido em questão assim:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT  OrderId, IsChainFired
FROM    OrderedChainFlag WITH ( ROWLOCK, XLOCK, HOLDLOCK )
WHERE   OrderId = 123456

Então, se IsChainFired for falso, dispara a "Cadeia de Eventos". Eu então executaria:

UPDATE  OrderedChainFlag 
SET     IsChainFired = 1
WHERE   OrderId = 123456

e execute (mesmo que IsChainFired seja verdadeiro):

myConnection.CommitTransaction(); // C# Code

Isso impediria que mais de uma instância do serviço lesse que a cadeia está livre para ser executada ao mesmo tempo.

Minha preocupação (e minha pergunta) é escalonamento de bloqueio . Se isso permanecer em bloqueios de linha, será uma solução fantástica para o meu problema. Mas se os bloqueios aumentarem para página ou tabela, terei acabado de criar um gargalo em meu sistema.

Então, há algo que eu possa fazer para garantir que isso permaneça no nível de bloqueio de linha? (ou é mesmo uma boa ideia usar o sistema de bloqueio de banco de dados como um semáforo como este?)

NOTA: OrderId seria a chave primária, o índice clusterizado e o índice de particionamento da tabela.

Eu tenho uma produção "Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)" que está mostrando sintomas estranhos de buffer e expectativa de vida da página (PLE).

Estou executando isso a cada minuto no meu servidor (para rastrear esse problema):

SELECT @ple = CAST([cntr_value] AS VARCHAR(20))
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

SELECT @usedBufferPages = CAST(COUNT(*) /128 AS VARCHAR(20)) 
FROM sys.dm_os_buffer_descriptors

DECLARE @StartDate VARCHAR(8) = Convert(VARCHAR(8), GETDATE(), 14)
RAISERROR ('%s. PLE at %s and Used Buffers at %s at %s ', 0, 
            1,@runCountString ,@ple, @usedBufferPages, @StartDate) WITH NOWAIT  

Este é um exemplo de saída:

16. PLE em 858 e buffers usados ​​em 7290 em 09:51:42
17. PLE em 918 e Buffers Usados ​​em 7342 em 09:52:42
18. PLE em 978 e Buffers Usados ​​em 7408 em 09:53:43
19. PLE em 1039 e Buffers Usados ​​em 7547 em 09:54:43
20. PLE em 1100 e buffers usados ​​em 7697 em 09:55:44
21. PLE em 1160 e buffers usados ​​em 7901 em 09:56:45
22. PLE em 1221 e buffers usados ​​em 7961 em 09:57:46
23. PLE em 1282 e buffers usados ​​em 8012 em 09:58:46
24. PLE em 11 e Buffers Usados ​​em 313 em 09:59:46
25. PLE em 31 e buffers usados ​​em 966 em 10:00:46
26. PLE em 90 e buffers usados ​​em 1580 em 10:01:47
27. PLE em 151 e buffers usados ​​em 3072 em 10:02:47
28. PLE em 211 e buffers usados ​​em 3152 em 10:03:47
29. PLE em 271 e buffers usados ​​em 3729 em 10:04:47  

No item #24 o SQL Server informa o PLE passando de 1.282 para 11 . O SQL Server também relata que os buffers usados ​​vão de 8.012 para 313 .

Primeiro, procurei por consultas de execução ruim e encontrei algumas corrigidas (não tiveram efeito sobre o problema). Porém, não estou encontrando nenhuma consulta de problema relacionada às vezes em que tenho problemas de PLE/Buffer. Além disso, se fosse uma consulta em execução ruim, eu pensaria que os Buffers estariam cheios dos dados dessa consulta, não vazios/ausentes/com erros.

Em seguida, pensei que a máquina virtual estava restringindo sua memória quando isso aconteceu. Mas perguntei ao meu System Admin e ele me garantiu que a memória não é dinâmica ou compartilhada de forma alguma. (O que é atribuído, ele recebe, o tempo todo.) Além disso, executo esse script a cada 10 minutos e quando o PLE informa menos de 50:

  SELECT * FROM sys.dm_os_sys_memory

E relata os mesmos/semelhantes valores quando os PLE/Buffers estão altos e quando estão baixos. Para completar, aqui está um exemplo dos valores antes e depois do nº 24 acima:

total_physical_memory_kb available_physical_memory_kb total_page_file_kb available_page_file_kb system_cache_kb kernel_paged_pool_kb kernel_nonpaged_pool_kb system_high_memory_signal_state system_low_memory_signal_state system_memory_state_desc
20970996 4758672 24378868 7929404 4844160 686076 182752 1 0 A memória física disponível é alta
20970996 4743468 24378868 7892632 4845000 686580 182688 1 0 A memória física disponível é alta

Eu verifiquei a sessão de integridade do sistema e não mostra nada relacionado. (Tudo o que tem são falhas de representação, e seus tempos não se correlacionam com os tempos que os PLE/Buffers mostram problemas.

Acompanhei a frequência com que isso ocorre, não consigo ver um padrão ou conectá-lo a nenhum trabalho ou atividade agendada.

Aqui está um gráfico que mostra PLE e Buffers ao longo de 21 horas:

Então estou perplexo. Acho que o cerne da questão são os buffers e não o PLE. (Acho que o PLE está recebendo um relatório falso de baixo porque todos os buffers desapareceram de alguma forma.)

Mas não consigo pensar em nenhuma maneira de isso acontecer. Ou o que fazer a seguir.

Eu adoraria conselhos sobre coisas adicionais para verificar ou sugestões sobre o que esse problema pode ser.

Atualizações de perguntas nos comentários:

Então, quanta memória é fornecida ao servidor? A VM tem 20 GB de memória.
O que é memória máxima do servidor?

nome valor value_in_use descrição
memória máxima do servidor (MB) 13000 13000 Tamanho máximo da memória do servidor (MB)
memória mínima do servidor (MB) 0 16 Tamanho mínimo da memória do servidor (MB)

NOTA: Eu li um pouco sobre isso agora e parece que essas configurações estão erradas para o meu servidor.

Qual é o tamanho do banco de dados? Existem dois bancos de dados transacionais em execução neste servidor (estou no processo de obter servidores para isolá-los). Seus tamanhos são 383 GB e 378 GB.

Quais outros aplicativos e serviços estão sendo executados nesse servidor? Este servidor hospeda os dados do meu aplicativo. Não há outras coisas atingindo-o. (Eu tenho um armazenamento de dados operacionais replicado para relatórios e tal.

Qual é a tecnologia VM VM Ware.
Esta VM está sendo executada em um host que hospeda apenas VMs com alocação de recursos semelhante? Temos muitas VMs em nossa empresa. Todos de tamanhos variados. Este é um dos maiores embora.

Você pode confirmar o que o administrador do sistema está lhe dizendo sobre a alocação de memória sem apenas ter que acreditar nele? Não posso. Não tenho acesso a essas ferramentas.

(Na minha experiência, os administradores do sistema dirão muitas coisas para passar a responsabilidade e culpar o aplicativo ou qualquer outra pessoa se isso significar que eles não precisam fazer nada.) Entendo perfeitamente esse sentimento.

Esse padrão certamente parece uma forte pressão de memória, concordo. Eu esperava encontrar algo para provar que o SQL está sentindo a pressão da memória. Assim, posso enviá-lo de volta aos administradores do sistema para mais pesquisas.

Estatísticas de tempo de espera

WaitType Wait_S Resource_S Signal_S WaitCount Porcentagem AvgWait_S AvgRes_S AvgSig_S
---------------------- ----------- ----------- ------ --- ---------- ------------ ---------- --------- ------ ---
PAGEIOLATCH_SH 16250,10 16219,14 30,96 2171649 29,59 0,0075 0,0075 0,0000   
CXPACKET 14214,03 13238,56 975,47 1187935 25,88 0,0120 0,0111 0,0008   
PAGEIOLATCH_EX 6814,59 6806,21 8,38 638725 12,41 0,0107 0,0107 0,0000   
WRITELOG 5157,42 4873,44 283,98 3588476 9,39 0,0014 0,0014 0,0001   
BACKUPIO 2569,51 2538,12 31,39 1704119 4,68 0,0015 0,0015 0,0000   
LCK_M_IX 2477,15 2477,10 0,05 113 4,51 21,9217 21,9213 0,0004   
ASYNC_IO_COMPLETION 2079,99 2079,66 0,33 836 3,79 2,4880 2,4876 0,0004   
BACKUPBUFFER 1807,75 1759,11 48,64 380189 3,29 0,0048 0,0046 0,0001   
IO_COMPLETION 986,23 985,84 0,39 116112 1,80 0,0085 0,0085 0,0000   

Eu tenho um banco de dados de produção que está enfrentando problemas de expectativa de vida da página (PLE) extremamente flutuantes. (Ele cai para zero em momentos aleatórios.)

Eu tenho pesquisado o problema do PLE e encontrei algo que parece apontar para um problema do VMWare, mas não tenho certeza se estou usando os dados corretamente. Parece que estou perdendo páginas de buffer/cache.

Estou usando esta consulta:

SELECT  COUNT(*) AS cached_pages_count, 
        CASE database_id
            WHEN 32767 THEN 'ResourceDb'
            ELSE DB_NAME(database_id)
        END AS database_name
FROM    sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id), database_id
ORDER BY cached_pages_count DESC;

(Encontrado aqui )

Estou totalizando os resultados (a contagem) antes e depois do travamento do meu PLE. Um exemplo é 1.097.820 antes e 131.394 depois. Portanto, pareço "perder" 966.426 páginas.

Meu palpite é que o hardware de todas as máquinas virtuais está sob estresse, então ele irá trocar aleatoriamente alguma memória do servidor por um tempo. (Isso é apenas um palpite.) Quando isso acontece, todas as páginas são perdidas, então o PLE despenca.

Então, estou usando a sys.dm_os_buffer_descriptorsvisão corretamente? Pelo que li, sempre mostra páginas de buffer/cache usadas. Portanto, se estiver vazio (ou significativamente reduzido), não tenho mais memória ou está vazio. (Eu adoraria uma maneira de confirmar esta conclusão.)

Ou há outra explicação de por que a contagem cai tanto?

As informações abaixo da linha foram adicionadas a partir dos comentários do OP

Nossos administradores de sistema gerenciam as VMs. Espero entender minha consulta antes de ir até eles com esses dados. O tempo das falhas do PLE parece aleatório do ponto de vista do banco de dados. (Sem reindexação ou outras coisas de alto desempenho acontecendo durante as falhas do PLE)

Eu fiz uma tonelada de trabalho para ver se estava relacionado à carga de trabalho. E embora haja uma consulta com baixo desempenho, não é suficiente para usar todo o cache. [Não há] nenhuma reconstrução ou outra atividade de usuário não rotineira no servidor quando as contagens de buffer diminuem. E mesmo que fosse, eu não veria isso sendo usado na minha consulta acima? (Ou seja, se fosse uma ação do SQL Server, as contagens não permaneceriam as mesmas, apenas com coisas diferentes?)

Não tenho acesso às configurações do VMWare. Eu esperava entender melhor minhas descobertas antes de envolver aqueles que entendem. O objetivo dessa pergunta era garantir que eu estava usando a exibição corretamente primeiro.

No final da cadeia de comentários:

Eu estava tentando dizer que o problema do PLE me levou à perda do problema das Buffer Pages. A consulta que eu estava usando para obter o PLE mostrava um PLE baixo porque as páginas estavam sendo perdidas. Então o que havia neles se foi. Foi uma leitura falsa porque a quantidade de memória foi reduzida.

Aqui está a minha @@Versão:

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
    Dec 28 2012 20:23:12 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Eu li que depois do SQL Server 2000, a capacidade de "desidentificar" uma coluna de identidade foi removida. E que isso era "By Design" (não apenas um recurso ausente).

Aqui está um exemplo que encontrei em um blog . Envolve a atualização das tabelas do sistema. (E essa capacidade foi removida após o SQL Server 2000.) Entendo que fazer isso por meio de tabelas do sistema não é uma boa ideia. Só estou me perguntando por que um recurso para fazer isso de outra maneira não existe.

Trabalhar em torno disso vai me causar uma quantidade considerável de trabalho. (Copiando muitas centenas de milhões de linhas para novas tabelas em um ambiente intolerante ao tempo de inatividade.)

Então pensei em perguntar "Por quê".

O que mudou no Sql Server 2005 e versões posteriores que tornaram isso uma coisa ruim? Ou sempre foi ruim e simplesmente não foi bloqueado?

Qual "prática recomendada" (ou princípio semelhante) seria violado ao tornar uma coluna de identidade uma coluna normal novamente?

--

Atualização para responder à solicitação de "por que estou fazendo isso":
Este é um resumo de alto nível: vou começar a adicionar partições às minhas tabelas. (Para que eu possa arquivar/eliminar dados antigos.) Tudo isso é fácil. Mas ocasionalmente preciso mover um registro para uma partição diferente para que ele não seja removido (quando uma partição surge para arquivamento/exclusão). (Estou aumentando minha coluna de particionamento em 2 para que sempre haja espaço para mover a linha para uma partição diferente.)

Mas se a coluna de particionamento for uma coluna de identidade, devo excluir e reinserir o valor (não há como atualizar o valor de uma coluna de identidade). O que causa problemas com a replicação.

Portanto, estou querendo usar uma sequência em vez de uma coluna de identidade. Mas essa opção é muito difícil em grandes bancos de dados.

Tenho várias tabelas grandes que preciso copiar em cópias particionadas em um banco de dados transacional.

Estou preocupado com o impacto que isso causará no desempenho dos meus aplicativos de produção.

Em uma tentativa de mitigar isso, estou analisando o Resource Governor. Vou definir meu script para usar um pool de recursos limitado a um máximo de 5% de CPU e memória.

A única parte disso que não tenho certeza é o efeito no PLE.

Pelo que entendi, o select da tabela lerá o valor no cache. Isso forçará a saída de outra coisa que estava no cache. (O conceito básico do PLE.) Mas os itens "forçados" serão limitados aos 5% que especifiquei?

Ou seja, depois de usar os 5% da memória, isso forçará apenas os itens em cache nos mesmos 5%? (Deixando os outros 95% inalterados.)

Outra maneira de perguntar isso é: o limite de memória de 5% é o mesmo que dizer um limite de cache de 5%?

Estou comparando duas tabelas. Eles são:

CREATE TABLE Table1(
    Column1 [bigint] NOT NULL,
    Column2 [VARBINARY](max) NULL,
    Column3 [int] NULL,
    Column4 [DATETIME] NULL,
    Column5 [INT] NULL,
    Column6 [DATETIME] NULL,
    [RowVersionId] [timestamp] NOT NULL,
 CONSTRAINT [PK_BagImage] PRIMARY KEY CLUSTERED (Column1 ASC)
)

Esta é a segunda tabela:

CREATE TABLE Table2(
    Column1 [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    Column2 [BIGINT] NULL,
    Column3 [VARCHAR](1) NOT NULL,
    Column4 [int] NOT NULL,
    Column5 [varchar](11) NOT NULL,
    Column6 [varchar](13) NOT NULL,
    Column7 [datetime] NOT NULL,
    Column8 [bigint] NULL,
    Column9 [bit] NOT NULL,
    Column10 [uniqueidentifier] NOT NULL,
    [RowVersionId] [timestamp] NOT NULL,
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED ( Column1 ASC)
) 

Nenhum deles tem índices não agrupados. Cada um deles tem uma chave estrangeira, mas os planos de consulta para procurá-los estão ficando mais rápidos! (Busca de índice agrupado que leva 00:00:00).

Quando copio 55K de linhas na primeira leva 45 minutos. Quando copio 7,7 milhões de linhas no segundo, leva 7 minutos.

Isso tudo parece fazer sentido para mim. (copiar imagens deve demorar mais, certo?)

Mas eu queria calcular um IO aproximado para esta operação. Então eu corri dbcc showcontignessas mesas.

Tabela MinimumRecordSize MaximumRecordSize AverageRecordSize  
Tabela1 75 123 81.733
Tabela 2 101 101 101

Então é aqui que estou ficando confuso. Se o tamanho médio do registro for 20 a menos para a tabela com as imagens, por que demora muito mais para inserir os dados? Especialmente quando estou inserindo mais 7 milhões de linhas na segunda tabela.