Kin Shah's questions

Tentando entender o deadlock que está ocorrendo com o nível de isolamento de leitura repetível quando o proc armazenado é invocado.

Este é o xml de deadlock:

    <TextData>
  <deadlock-list>
 <deadlock victim="process6fac029848">
  <process-list>
   <process id="process6fac029848" taskpriority="0" logused="0" waitresource="KEY: 8:72078600230076416 (d3c590b351cd)" waittime="1171" 
            ownerId="26116633497" transactionname="delete" lasttranstarted="2021-01-07T10:35:38.317" XDES="0x12553cbe3b0" 
            lockMode="U" schedulerid="3" kpid="19776" status="suspended" spid="92" sbid="0" ecid="0" priority="0" trancount="2" 
            lastbatchstarted="2021-01-07T10:35:38.310" lastbatchcompleted="2021-01-07T10:35:38.310" lastattention="1900-01-01T00:00:00.310" 
            clientapp=".Net SqlClient Data Provider" hostname="web01" hostpid="34852" loginname="someuser" 
            isolationlevel="repeatable read (3)" xactid="26116633497" currentdb="8" currentdbname="db1" lockTimeout="4294967295" clientoption1="673316896" 
            clientoption2="128056">
    <executionStack>
     <frame procname="db1.schema.deleteSP" line="124" stmtstart="8308" stmtend="8870" 
            sqlhandle="somehandle">
UPDATE [schema].[TableName] WITH(UPDLOCK)
        SET
            Status = 1 /* STATUS_DELETED */,
            userid = @id,
            UpdateTime = GETUTCDATE()
        FROM @oldatt oa
        WHERE [TableName].[Id] = oa.Id     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 8 Object Id = 1688915952]    </inputbuf>
   </process>
   <process id="process6fab017088" taskpriority="0" logused="284" waitresource="KEY: 8:72064801531101184 (4b819525e255)" waittime="1283" 
   ownerId="26116632474" transactionname="delete" lasttranstarted="2021-01-07T10:35:38.290" XDES="0x44037ec3b0" 
   lockMode="X" schedulerid="25" kpid="25152" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" 
   lastbatchstarted="2021-01-07T10:35:38.287" lastbatchcompleted="2021-01-07T10:35:38.283" lastattention="1900-01-01T00:00:00.283" 
   clientapp=".Net SqlClient Data Provider" hostname="web02" hostpid="57552" loginname="someuser" 
   isolationlevel="repeatable read (3)" xactid="26116632474" currentdb="8" currentdbname="db1" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
    <executionStack>
     <frame procname="db1.schema.deleteSP" line="124" stmtstart="8308" stmtend="8870" sqlhandle="somehandle">
UPDATE [schema].[TableName] WITH(UPDLOCK)
        SET
            Status = 1 /* STATUS_DELETED */,
            userid = @id,
            UpdateTime = GETUTCDATE()
        FROM @oldatt oa
        WHERE [TableName].[Id] = oa.Id     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 8 Object Id = 1688915952]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72078600230076416" dbid="8" objectname="db1.schema.TableName" indexname="IX_TableName_Id_Status_userid_UpdateTime" id="lock287c93a100" mode="U" associatedObjectId="72078600230076416">
    <owner-list>
     <owner id="process6fab017088" mode="U" />
    </owner-list>
    <waiter-list>
     <waiter id="process6fac029848" mode="U" requestType="wait" />
    </waiter-list>
   </keylock>
   <keylock hobtid="72064801531101184" dbid="8" objectname="db1.schema.TableName" indexname="IX_TableName_SomeId_Id" id="lock55fc471880" mode="S" associatedObjectId="72064801531101184">
    <owner-list>
     <owner id="process6fac029848" mode="S" />
    </owner-list>
    <waiter-list>
     <waiter id="process6fab017088" mode="X" requestType="convert" />
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>
</TextData>

a tabela de criação com índices relevantes:

CREATE TABLE [schema].[TableName](
    [Id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [SomeId] [int] NOT NULL,
    [Userid] [int] NOT NULL,
    [UpdateTime] [datetime] NOT NULL,
    [RecordStatus] [tinyint] NOT NULL,
 CONSTRAINT [PK_Id] 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]
GO
--- 2 indexes that are part of deadlock 
IX_TableName_SomeId_Id = filtered Index - SomeId, Id (include status) where status = 0
IX_TableName_Id_Status_userid_UpdateTime - non clustered index - Id include(status, userid, updatetime)

A chamada SP é:

• leitura repetível de nível de isolamento
• inicia a transação
• faz uma seleção da tabela base e insere as linhas que serão afetadas em uma variável temporária.
• executa outro SP
• atualiza a tabela base. <<--- é aqui que acontece o impasse
• mescla os dados com alguma lógica de negócios
• se não houver erro, confirma a transação

A tabela base possui um gatilho que insere e atualiza a tabela de auditoria para rastrear alterações.

Estou apenas tentando entender por que estou recebendo muitos impasses.

Atualizar :

declare @oldtable(
        Id bigint,
        someId int
        PRIMARY key clustered (id)
    )
    insert into @oldtable (
        id,
        someid
    )
    select id, someid
    from [schema].[TableName]
    where id = @id and status = 0 and someid = @someid 

Controle de fluxo de procedimento armazenado:

variables 
    @id 
    @someid 
    
begin 
    set nocount on
    set transaction isolation level repeatable read
    set xact_abort on

    begin tran delete
    begin try

    declare @oldtable(
        Id bigint,
        someId int
        PRIMARY key clustered (id)
    )
    insert into @oldtable (
        id,
        someid
    )
    select id, someid
    from [schema].[TableName]
    where id = @id and status = 0 and someid = @someid 

    some SP runs

    -- update statememt that causes deadlock 
    -- it basically sets status = 1 -- deleted (soft delete)

    -- merge data into tables using CTE and MERGE 
    END TRY

    begin catch 
        -- error handling 
        -- roll back tran 
    end catch

    if @@trancount > 0
    begin
        commit tran delete
    END

END

Eu estava lendo as ALTER PARTITION FUNCTION melhores práticas que dizem -

Sempre mantenha partições vazias em ambas as extremidades do intervalo de partição para garantir que a divisão de partição (antes de carregar novos dados) e a mesclagem de partição (após descarregar dados antigos) não incorram em nenhuma movimentação de dados. Evite dividir ou mesclar partições preenchidas. Isso pode ser extremamente ineficiente, pois pode causar até quatro vezes mais geração de log e também pode causar bloqueios graves.

Perguntas :

• É suficiente garantir que não haverá movimentação de dados garantindo que o novo limite corresponda a uma data futura ou eu realmente preciso da partição vazia?
• Acho que certas otimizações são feitas se a partição estiver vazia, mas é correto dizer que o maior custo está na própria movimentação de dados?
• Existe algo novo no SQL 2014 que possa ajudar (estamos passando de 2008 para 2014 .. sim, estou ciente de que o SQL Server 2019 está fora .. mas eventualmente chegaremos lá) ?

Eu vi muito em Managed Lock Priority (por exemplo, https://blogs.technet.microsoft.com/dataplatforminsider/2013/08/16/improved-application-availability-during-online-operations-in-sql-server-2014/ ); no entanto, isso parece se aplicar apenas a Table Partition Switch e Online Index Rebuild, mas não a um arquivo ALTER PARTITITON FUNCTION.

Atualização : @AmitBanerjee - Gerente de Programa Sênior do Grupo de Produtos do Microsoft SQL Server confirmou que a MS examinará o problema, pois é um defeito.

Alguém encontrou problemas ao restaurar backups feitos no SQL Server 2016 com TDE habilitado e usando MAXTRANSFERSIZE> 65536 (no meu caso, escolhi 65537 para poder compactar o banco de dados TDE ) e CHECKSUM?

Abaixo segue uma reprodução:

--- create database 
create database test_restore
go
-- create table
create table test_kin (fname char(10))
go
-- Enable TDE 

use master
GO
CREATE CERTIFICATE test_restore WITH SUBJECT = 'test_restore_cert'
GO
SELECT name, pvt_key_encryption_type_desc, * FROM sys.certificates WHERE name = 'test_restore'
GO
use test_restore
go
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE test_restore
GO 
alter database test_restore set encryption ON

Faça backup completo apenas da cópia .. faça duas vezes ..

backup database test_restore 
to disk = 'D:\temporary-short-term\test_restore_KIN_test_restore_1.bak' -- change as per your location !!
with init, stats =10  -- overwrite ..using INIT !!
, maxtransfersize = 65537
, compression
,CHECKSUM

Agora faça um verifyonly...

restore verifyonly from disk = 'D:\temporary-short-term\test_restore_KIN_test_restore_1.bak'

Mensagem de erro :

Msg 3241, nível 16, estado 40, linha 11 A família de mídia no dispositivo 'D:\temporary-short-term\test_restore_KIN_test_restore_1.bak' está formada incorretamente. O SQL Server não pode processar esta família de mídia. Msg 3013, Nível 16, Estado 1, Linha 11 VERIFY DATABASE está terminando de forma anormal.

Resultados (1 = ON, 0 = OFF) com diferentes combinações:

+-------------------------+-------------+----------+--------+
| MAXTRANSFERSIZE (65537) | COMPRESSION | CHECKSUM | RESULT |
+-------------------------+-------------+----------+--------+
|                       1 |           1 |        1 | FAIL   |
|                       1 |           1 |        0 | PASS   |
|                       1 |           0 |        1 | FAIL   |
|                       0 |           0 |        0 | PASS   |
|                       0 |           1 |        1 | PASS   |
|                       0 |           1 |        0 | PASS   |
+-------------------------+-------------+----------+--------+

O problema acontece em:

Microsoft SQL Server 2016 (RTM-CU1) (KB3164674) - 13.0.2149.0 (X64) Jul 11 ​​2016 22:05:22 Copyright (c) Microsoft Corporation Enterprise Edition (64 bits) no Windows Server 2012 R2 Standard 6.3 (Build 9600 :)

Estou executando abaixo do tsql para encontrar os servidores AlwaysON secundários da réplica primária:

set transaction isolation level read uncommitted 
set statistics time, io on
SELECT  
ISNULL(agstates.primary_replica, '') AS [Primary_AG_Server],
dbcs.database_name AS [DatabaseName]
 ,ar.replica_server_name as [Secondary_AG_Servers]

   FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local <> 1 -- is_local is not 1 .. so we get secondary server names
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
    group by agstates.primary_replica, dbcs.database_name, ar.replica_server_name
    set statistics time, io off

A consulta acima leva 12 minutos e 38 segundos para ser executada e retorna 34 linhas . Abaixo estão as estatísticas IO e saída de tempo:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 10 ms, elapsed time = 10 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(34 row(s) affected)
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysguidrefs'. Scan count 0, logical reads 212, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysclsobjs'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 1266 ms,  elapsed time = 747852 ms.

Aqui está o link para o plano de execução real .

Qualquer coisa que pode ser feito para torná-lo mais rápido?

Abaixo é do Plan Explorer

Clique para ampliar

Clique para ampliar

Obrigado !

Tenho uma tabela com a estrutura abaixo:

CREATE TABLE [dbo].[AUDIT_SCHEMA_VERSION](
    [SCHEMA_VER_MAJOR] [int] NOT NULL,
    [SCHEMA_VER_MINOR] [int] NOT NULL,
    [SCHEMA_VER_SUB] [int] NOT NULL,
    [SCHEMA_VER_DATE] [datetime] NOT NULL,
    [SCHEMA_VER_REMARK] [varchar](250) NULL
);

alguns dados de amostra (parece problema com o sqlfiddle .. colocando alguns dados de amostra):

INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,6,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,6,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,7,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,10,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,12,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,12,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,13,CAST('20140417 18:10:44.100' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,5,0,CAST('20140417 18:14:14.157' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,6,0,CAST('20140417 18:14:23.327' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,7,0,CAST('20140417 18:14:32.270' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,8,0,CAST('20141209 09:38:40.700' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,9,0,CAST('20141209 09:43:04.237' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,10,0,CAST('20141209 09:45:19.893' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,13,0,CAST('20150323 14:54:30.847' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,10,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,14,CAST('20140417 18:11:07.977' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,15,CAST('20140417 18:11:13.130' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,2,0,CAST('20140417 18:12:11.200' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,3,0,CAST('20140417 18:12:33.330' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,4,0,CAST('20140417 18:12:48.803' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,13,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,11,0,CAST('20141209 09:45:58.993' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,12,0,CAST('20141209 09:46:50.070' as DATETIME),'Stored procedure build');

Aqui está o SQLFiddlecom alguns dados de amostra.

Alguém com experiência em T-sql pode me orientar sobre como alcançar o resultado final? Eu sei que PIVOT(com colunas dinâmicas) será a abordagem certa, mas não consigo descobrir.

Resultados esperados :

Até agora, tenho abaixo:

select row_number() over (
        partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_DATE 
        ) as rownum
    ,CONVERT(varchar(10), SCHEMA_VER_DATE, 110) as UPG_DATE
    ,CONVERT(varchar(1), SCHEMA_VER_MAJOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_MINOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_SUB) as SCHEMA_VER
from audit_schema_version
where SCHEMA_VER_REMARK like 'Stored procedure build'
order by UPGRADE_DATE 

Pergunta

1. Por que, apesar do log_send_queue_size aumentar para esses dois bancos de dados, o log_send_rate continuou diminuindo?
2. Não houve problemas de largura de banda na rede neste momento e nenhum outro banco de dados apresentou esse problema. Se isso acontecer novamente , existe uma correção recomendada, além de ter que restaurar manualmente o banco de dados primário no secundário para ressincronizar o par?

Meio Ambiente :

SQL 2012, SP1 CU7 (Compilação 3393)

Windows Server 2012 Standard (Compilação 9200)

Grupo de Disponibilidade de 10 bancos de dados (PRDDB1-AG1)

2 réplicas AG, uma em Londres e outra em Nova York (LDSERVER1 & NYSERVER1), primária em NY, secundária em Londres.

2 bancos de dados no AG1, E-DB1(arquivo de log de 50 GB) e T-DB2(arquivo de log de 250 GB)

O T-DB2banco de dados importa arquivos de clientes, os processa (muita atividade de log) e, em seguida, gera/atualiza os dados no banco de E-DB1dados.

Esse processo gera muita rotatividade de dados e atividade de log em ambos os bancos de dados. Temos picos ocasionais de latência entre as réplicas de banco de dados de Londres e Nova York, talvez uma ou duas vezes por semana, no máximo, mas sempre desaparecem em algumas horas.

Questão :

Na semana passada, vimos um log_send_queue_size crescente e um log_send_rate decrescente. Isso começou na segunda-feira e continuou até a noite de sexta-feira, quando foi resolvido manualmente (consulte a seção Corrigir abaixo) . Em seu nível mais baixo, o log_send_rate do banco de dados E-DB1 era de pouco mais de 100 KB/s com um log_send_queue de mais de 40 GB. O banco de dados T-DB2 tinha um log_send_rate de 2.000 KB/s diminuindo para 300 KB/s, com um log_send_queue de mais de 300 GB.

Isso levou a uma quantidade crescente de latência entre as réplicas primária e secundária desses dois bancos de dados no grupo de disponibilidade. Isso foi caracterizado por um acúmulo de atividade de log dentro do log de transações para cada banco de dados afetado, o que é esperado. Devido a essa latência, os logs de cada banco de dados afetado se expandiam a ponto de a unidade de log correr o risco de ficar sem espaço.

Essa latência ocorreu apenas nesses dois bancos de dados, apesar de alguns picos bastante grandes na atividade transacional em todos os bancos de dados do grupo de disponibilidade, como é normal.

Ao longo desse problema, não houve acúmulo na fila de redo no secundário e o redo_rate permaneceu alto. Isso implicaria que o problema era devido à baixa taxa de envio para ambos os bancos de dados afetados.

Etapas tentadas

1. Suspenda a movimentação de dados do banco de dados T-DB2. Eu esperava que isso liberasse largura de banda de rede para o banco de dados prioritário, E-DB1. Sem efeito.

2. Reinicializou o nó secundário (LDPRDENTDB1). Sem efeito.

Fixar

3. As etapas a seguir resolveram o problema. Como os arquivos de log cresceram para mais de 300 GB, precisei limpá-los e reduzi-los antes que ficássemos sem espaço em disco.

   uma. Bancos de dados removidos do grupo de disponibilidade.

   b. Bancos de dados descartados no secundário.

   c. Bancos de dados adicionados novamente ao grupo de disponibilidade no primário (NYSERVER1, opção de sincronização manual).

   d. Backup dos bancos de dados no primário e restaurado no secundário (70 GB copiados de NY para LD, em pouco menos de 24 horas)

   e. Bancos de dados adicionados novamente ao grupo de disponibilidade no secundário.

Estou tentando descobrir quem alterou a senha de um login no SQL Server 2008 R2.

Já verifiquei o rastreamento padrão - e ele não registra esse evento. O rastreamento padrão incluirá estes eventos relacionados à segurança:

/*
    Audit Add DB user event
    Audit Add login to server role event
    Audit Add Member to DB role event
    Audit Add Role event
    Audit Add login event
    Audit Backup/Restore event
    Audit Change Database owner
    Audit DBCC event
    Audit Database Scope GDR event (Grant, Deny, Revoke)
    Audit Login Change Property event
    Audit Login Failed
    Audit Login GDR event
    Audit Schema Object GDR event
    Audit Schema Object Take Ownership
    Audit Server Starts and Stops 
*/

Além disso, examinei o backup do log de transações para descobrir isso, mas sem sorte.

Existe alguma outra maneira de descobrir?

Além disso, estou ciente de que um rastreamento do lado do servidor ajudará, mas, infelizmente, em nosso rastreamento do lado do servidor, não incluímos o arquivo Audit Login Change Password Event.

O melhor artigo que encontrei é de Aaron Bertrand: Rastreando alterações de senha de login no SQL Server

Quero entender por que haveria uma diferença tão grande na execução da mesma consulta no UAT (executado em 3 segundos) versus PROD (executado em 23 segundos).

Ambos UAT e PROD estão tendo exatamente dados e índices.

CONSULTA:

set statistics io on;
set statistics time on;

SELECT CONF_NO,
       'DE',
       'Duplicate Email Address ''' + RTRIM(EMAIL_ADDRESS) + ''' in Maintenance',
       CONF_TARGET_NO
FROM   CONF_TARGET ct
WHERE  CONF_NO = 161
       AND LEFT(INTERNET_USER_ID, 6) != 'ICONF-'
       AND ( ( REGISTRATION_TYPE = 'I'
               AND (SELECT COUNT(1)
                    FROM   PORTFOLIO
                    WHERE  EMAIL_ADDRESS = ct.EMAIL_ADDRESS
                           AND DEACTIVATED_YN = 'N') > 1 )
              OR ( REGISTRATION_TYPE = 'K'
                   AND (SELECT COUNT(1)
                        FROM   CAPITAL_MARKET
                        WHERE  EMAIL_ADDRESS = ct.EMAIL_ADDRESS
                               AND DEACTIVATED_YN = 'N') > 1 ) ) 

NO UAT:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 11 ms, elapsed time = 11 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(3 row(s) affected)
Table 'Worktable'. Scan count 256, logical reads 1304616, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PORTFOLIO'. Scan count 1, logical reads 84761, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CAPITAL_MARKET'. Scan count 256, logical reads 9472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CONF_TARGET'. Scan count 1, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 2418 ms,  elapsed time = 2442 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

No PRO:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(3 row(s) affected)
Table 'PORTFOLIO'. Scan count 256, logical reads 21698816, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CAPITAL_MARKET'. Scan count 256, logical reads 9472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CONF_TARGET'. Scan count 1, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 23937 ms,  elapsed time = 23935 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Observe que no PROD a consulta sugere um índice ausente e isso é benéfico como testei, mas esse não é o ponto de discussão.

Eu só quero entender que: ON UAT - por que o sql server cria uma tabela de trabalho e no PROD não? Ele cria um spool de tabela no UAT e não no PROD. Além disso, por que os tempos de execução são tão diferentes no UAT vs PROD?

Observação :

Estou executando o sql server 2008 R2 RTM em ambos os servidores (em breve irei corrigir com o SP mais recente).

UAT: Memória máxima de 8 GB. MaxDop, afinidade do processador e threads de trabalho máximo é 0.

Logical to Physical Processor Map:
*-------  Physical Processor 0
-*------  Physical Processor 1
--*-----  Physical Processor 2
---*----  Physical Processor 3
----*---  Physical Processor 4
-----*--  Physical Processor 5
------*-  Physical Processor 6
-------*  Physical Processor 7

Logical Processor to Socket Map:
****----  Socket 0
----****  Socket 1

Logical Processor to NUMA Node Map:
********  NUMA Node 0

PROD: memória máxima de 60 GB. MaxDop, afinidade do processador e threads de trabalho máximo é 0.

Logical to Physical Processor Map:
**--------------  Physical Processor 0 (Hyperthreaded)
--**------------  Physical Processor 1 (Hyperthreaded)
----**----------  Physical Processor 2 (Hyperthreaded)
------**--------  Physical Processor 3 (Hyperthreaded)
--------**------  Physical Processor 4 (Hyperthreaded)
----------**----  Physical Processor 5 (Hyperthreaded)
------------**--  Physical Processor 6 (Hyperthreaded)
--------------**  Physical Processor 7 (Hyperthreaded)

Logical Processor to Socket Map:
********--------  Socket 0
--------********  Socket 1

Logical Processor to NUMA Node Map:
********--------  NUMA Node 0
--------********  NUMA Node 1

ATUALIZAR :

XML do Plano de Execução UAT:

http://pastebin.com/z0PWvw8m

XML do Plano de Execução do PROD:

http://pastebin.com/GWTY16YY

UAT Execution Plan XML - com plano gerado para PROD:

http://pastebin.com/74u3Ntr0

Configuração do servidor:

PROD: PowerEdge R720xd - CPU Intel(R) Xeon(R) E5-2637 v2 @ 3,50 GHz.

UAT: PowerEdge 2950 - CPU Intel(R) Xeon(R) X5460 @ 3,16 GHz

Eu postei em answers.sqlperformance.com

ATUALIZAR :

Obrigado a @swasheck pela sugestão

Alterando a memória máxima no PROD de 60GB para 7680 MB, consigo gerar o mesmo plano no PROD. A consulta é concluída ao mesmo tempo que o UAT.

Agora eu preciso entender - POR QUÊ? Além disso, com isso, não poderei justificar este servidor monstro para substituir o servidor antigo!

Estou tentando alocar um conjunto de CPUs em um servidor NUMA de 2 nós conforme abaixo:

Abaixo está o coreinfo que mostra processadores físicos, processadores lógicos e nós NUMA no servidor.

Logical to Physical Processor Map:
**--------------  Physical Processor 0 (Hyperthreaded)
--**------------  Physical Processor 1 (Hyperthreaded)
----**----------  Physical Processor 2 (Hyperthreaded)
------**--------  Physical Processor 3 (Hyperthreaded)
--------**------  Physical Processor 4 (Hyperthreaded)
----------**----  Physical Processor 5 (Hyperthreaded)
------------**--  Physical Processor 6 (Hyperthreaded)
--------------**  Physical Processor 7 (Hyperthreaded)

Logical Processor to Socket Map:
********--------  Socket 0
--------********  Socket 1

Logical Processor to NUMA Node Map:
********--------  NUMA Node 0
--------********  NUMA Node 1

Estou executando 2 instâncias do SQL Server 2008R2 Standard Edition.

O que tenho feito é:

Vincule o NÓ 0 à Instância 1. Assim , a instância 1 terá 8 processadores lógicos.

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE = 0
GO

2013-11-07 13:01:20.79 Server      Processor affinity turned on: node 0, processor mask 0x00000000000000ff. Threads will execute on CPUs per affinity settings. This is an informational message; no user action is required.
2013-11-07 13:01:20.79 Server      Processor affinity turned on: node 1, processor mask 0x0000000000000000. Threads will execute on CPUs per affinity settings. This is an informational message; no user action is required.
2013-11-07 13:01:20.82 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2013-11-07 13:01:20.82 Server      Lock partitioning is enabled.  This is an informational message only. No user action is required.
2013-11-07 13:01:20.83 Server      Node configuration: node 0: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x00000000000000ff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2013-11-07 13:01:20.83 Server      Node configuration: node 1: CPU mask: 0x000000000000ff00:0 Active CPU mask: 0x0000000000000000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

Eu posso ver a instância 1 capaz de ouvir no NODE 0 conforme abaixo:

2013-11-07 13:01:21.05 Interfaces de rede do servidor SQL Server inicializaram ouvintes no nó 0 de uma configuração de servidor de vários nós (NUMA) com máscara de afinidade de nó 0x0000000000000001 . Esta é apenas uma mensagem informativa. Não é necessária nenhuma ação do usuário.

AGORA NA INSTANCE2:

Vincule o NÓ 1 à instância 2. Assim , a instância 2 terá 8 processadores lógicos.

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE = 1
GO

O log de erros também confirma isso:

2013-11-07 13:03:51.16 Server      Detected 16 CPUs. This is an informational message; no user action is required.
2013-11-07 13:03:51.49 Server      Processor affinity turned on: node 0, processor mask 0x000000000000ff00. Threads will execute on CPUs per affinity settings. This is an informational message; no user action is required.
2013-11-07 13:03:51.49 Server      Processor affinity turned on: node 1, processor mask 0x0000000000000000. Threads will execute on CPUs per affinity settings. This is an informational message; no user action is required.
2013-11-07 13:03:51.52 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2013-11-07 13:03:51.52 Server      Lock partitioning is enabled.  This is an informational message only. No user action is required.
2013-11-07 13:03:51.53 Server      Node configuration: node 0: CPU mask: 0x000000000000ff00:0 Active CPU mask: 0x000000000000ff00:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2013-11-07 13:03:51.53 Server      Node configuration: node 1: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x0000000000000000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

Mas por que INSTANCE2 está ouvindo em NUMA 0 e NÃO NUMA 1?

Ambas as instâncias terão MAXDOP definido como 4.

2013-11-07 13:03:51.78 Interfaces de rede do servidor SQL Server inicializaram ouvintes no nó 0 de uma configuração de servidor de vários nós (NUMA) com máscara de afinidade de nó 0x0000000000000001. Esta é apenas uma mensagem informativa. Não é necessária nenhuma ação do usuário.

Além disso, abaixo estão minhas CONFIGURAÇÕES TCP/IP:

Observação:

Eu tentei as coisas abaixo também:

1. desligue primeiro a afinidade da CPU e depois vincule as portas para ver se funciona. Isso funciona bem e consigo ver NUMA 0 ouvindo em uma porta e NUMA 1 ouvindo na segunda porta. Isso é o que eu quero, mas não quero desligar o CPU Affinity. Estou na direção certa?

2. Afinidade da CPU LIGADA. Tente atribuir NUMA às portas. Isso não funciona no sentido de que Instance1 está vinculado a NUMA 0 e Instance 2 não mostra no log de erros que está escutando em NUMA 1.

Obrigado !

Precisa de ajuda com o código abaixo, pois falha com erro de truncamento

Ocorreu um erro de truncamento. O comando foi abortado.

create table monCacheQuality (
        ServerName sysname
        ,CollectionDateTime smalldatetime not null
        ,PhysicalWrites decimal(15, 0) not null
        ,PhysicalReads decimal(15, 0) not null
        ,LogicalReads decimal(15, 0) not null
        ,CacheQuality decimal(15, 0) not null
        ,CacheHitPct decimal(15,4) not null
        )

-- Main code starts here 
declare @physical_read1 decimal(15, 0)
    ,@logical_read1 decimal(15, 0)
    ,@physical_write1 decimal(15, 0)
    ,@cache_search1 decimal (15,4)

declare @physical_read2 decimal(15, 0)
    ,@logical_read2 decimal(15, 0)
    ,@physical_write2 decimal(15, 0)
    ,@cache_search2 decimal (15,4)

while (1=1)
begin
    select @physical_write1 = PhysicalWrites
        ,@physical_read1 = PhysicalReads
        ,@logical_read1 = LogicalReads
        ,@cache_search1 = CacheSearches
    from master..monDataCache

    waitfor delay '00:00:20' -- Log every 20 sec

    select @physical_write2 = PhysicalWrites
        ,@physical_read2 = PhysicalReads
        ,@logical_read2 = LogicalReads
        ,@cache_search2 = CacheSearches
    from master..monDataCache

    insert monCacheQuality
    select @@servername as ServerName
        ,getUTCdate()
        ,@physical_write2 - @physical_write1
        ,@physical_read2 - @physical_read1
        ,@logical_read2 - @logical_read1
        ,case 
            when @physical_read2 - @physical_read1 = 0
                then - 1
            else (@logical_read2 - @logical_read1) / (@physical_read2 - @physical_read1)
            end as CacheQuality
        ,100-(((@physical_read2-@physical_read1)/(@cache_search2-@cache_search1))*100) as CacheHitPct
end