All perguntas(dba)

Tenho um cenário em que o postgres está fazendo uma quantidade enorme de trabalho extra.

Estou tentando consultar uma view que é computacionalmente muito pesada. No entanto, nunca pretendi retornar grandes conjuntos de dados dela, então imaginei que provavelmente seria ok...

No entanto, quando tento filtrá-lo, parece que o PG Planner está fazendo uma materialização completa da visualização e só então aplicando a filtragem.

SELECT * FROM record_view ORDER BY last_updated desc LIMIT 1

Então pensei que talvez pudesse forçá-lo a ter um desempenho melhor.

SELECT * FROM record_view WHERE id IN (
  SELECT record_id FROM record_field GROUP BY record_id ORDER BY MAX(updated_at) desc LIMIT 1
)

Demora cerca de 6x mais do que se eu executasse as duas consultas sequencialmente.

Eu até tentei adicionar índices bem específicos para tentar priorizar a primeira consulta:

CREATE INDEX idx_record_field_recordid_updatedat_desc
    ON record_field (record_id, updated_at DESC);

Detalhes

-- Object Definitions
create table "public"."object" (
    "id" text not null,
    "created_at" timestamp with time zone not null default now(),
    "label" text
);

-- Field Definitions

create table "public"."field" (
    "id" text not null,
    "object_id" text not null,
    "label" text not null,
    "created_at" timestamp with time zone not null default now(),
    "type" text not null default 'string'::text,
    "is_unique" boolean not null default false
);

alter table "public"."object" enable row level security;
alter table "public"."field" enable row level security;

CREATE UNIQUE INDEX "object_pkey" ON public.object USING btree (id);
CREATE UNIQUE INDEX field_pkey ON public.field USING btree (id, object_id);


alter table "public"."object" add constraint "object_pkey" PRIMARY KEY using index "object_pkey";
alter table "public"."field" add constraint "field_pkey" PRIMARY KEY using index "field_pkey";

alter table "public"."field" add constraint "field_object_id_fkey" FOREIGN KEY (object_id) REFERENCES object(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
alter table "public"."field" validate constraint "field_object_id_fkey";

create table "public"."record" (
    "id" uuid not null default gen_random_uuid(),
    "created_at" timestamp with time zone not null default now(),
    "object_id" text not null
);


alter table "public"."record" enable row level security;

CREATE UNIQUE INDEX record_pkey ON public.record USING btree (id, object_id);

create table "public"."record_field" (
    "field_id" text not null,
    "object_id" text not null,
    "record_id" uuid not null,
    "updated_at" timestamp with time zone not null default now(),
    "data" jsonb
);

alter table "public"."record_field" enable row level security;

CREATE UNIQUE INDEX record_field_pkey ON public.record_field USING btree (field_id, object_id, record_id);

alter table "public"."record" add constraint "record_pkey" PRIMARY KEY using index "record_pkey";

alter table "public"."record_field" add constraint "record_field_pkey" PRIMARY KEY using index "record_field_pkey";

alter table "public"."record" add constraint "record_object_id_fkey" FOREIGN KEY (object_id) REFERENCES object(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;

alter table "public"."record" validate constraint "record_object_id_fkey";

alter table "public"."record_field" add constraint "record_field_field_id_object_id_fkey" FOREIGN KEY (field_id, object_id) REFERENCES field(id, object_id) ON UPDATE CASCADE ON DELETE CASCADE not valid;

alter table "public"."record_field" validate constraint "record_field_field_id_object_id_fkey";

alter table "public"."record_field" add constraint "record_field_object_id_record_id_fkey" FOREIGN KEY (object_id, record_id) REFERENCES record(object_id, id) not valid;

alter table "public"."record_field" validate constraint "record_field_object_id_record_id_fkey";

CREATE OR REPLACE VIEW "public"."record_view" 
WITH (security_invoker = true) AS
SELECT 
    rf.object_id,
    rf.record_id AS id,
    json_object_agg(f.id, rf.data) AS data,
    MAX(rf.updated_at) AS last_updated
FROM 
    record_field rf
JOIN 
    field f 
    ON rf.field_id = f.id 
    AND rf.object_id = f.object_id
GROUP BY 
    rf.object_id, 
    rf.record_id;

Explique os resultados

explain analyse SELECT * FROM record_view WHERE id IN (
  SELECT record_id FROM record_field GROUP BY record_id ORDER BY max(updated_at) desc LIMIT 1
)

| QUERY PLAN                                                                                                                                                                           |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Hash Join  (cost=430728.66..482205.64 rows=562 width=63) (actual time=2267.232..2879.726 rows=1 loops=1)                                                                             |
|   Hash Cond: (rf.record_id = "ANY_subquery".record_id)                                                                                                                               |
|   ->  HashAggregate  (cost=296770.54..346821.66 rows=112444 width=63) (actual time=1468.243..2332.244 rows=112011 loops=1)                                                           |
|         Group Key: rf.object_id, rf.record_id                                                                                                                                        |
|         Planned Partitions: 256  Batches: 257  Memory Usage: 3113kB  Disk Usage: 245328kB                                                                                            |
|         ->  Hash Join  (cost=20.25..48817.12 rows=1779038 width=86) (actual time=0.041..598.819 rows=1779038 loops=1)                                                                |
|               Hash Cond: ((rf.field_id = f.id) AND (rf.object_id = f.object_id))                                                                                                     |
|               ->  Seq Scan on record_field rf  (cost=0.00..39456.38 rows=1779038 width=63) (actual time=0.012..254.655 rows=1779038 loops=1)                                         |
|               ->  Hash  (cost=14.10..14.10 rows=410 width=64) (actual time=0.018..0.021 rows=25 loops=1)                                                                             |
|                     Buckets: 1024  Batches: 1  Memory Usage: 10kB                                                                                                                    |
|                     ->  Seq Scan on field f  (cost=0.00..14.10 rows=410 width=64) (actual time=0.008..0.010 rows=25 loops=1)                                                         |
|   ->  Hash  (cost=133958.11..133958.11 rows=1 width=16) (actual time=538.110..539.750 rows=1 loops=1)                                                                                |
|         Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                                                                 |
|         ->  Subquery Scan on "ANY_subquery"  (cost=133958.10..133958.11 rows=1 width=16) (actual time=538.105..539.745 rows=1 loops=1)                                               |
|               ->  Limit  (cost=133958.10..133958.10 rows=1 width=24) (actual time=538.103..539.577 rows=1 loops=1)                                                                   |
|                     ->  Sort  (cost=133958.10..134239.21 rows=112444 width=24) (actual time=538.094..539.562 rows=1 loops=1)                                                         |
|                           Sort Key: (max(record_field.updated_at)) DESC                                                                                                              |
|                           Sort Method: top-N heapsort  Memory: 25kB                                                                                                                  |
|                           ->  Finalize GroupAggregate  (cost=104908.23..133395.88 rows=112444 width=24) (actual time=497.679..531.461 rows=112011 loops=1)                           |
|                                 Group Key: record_field.record_id                                                                                                                    |
|                                 ->  Gather Merge  (cost=104908.23..131147.00 rows=224888 width=24) (actual time=497.673..515.269 rows=115025 loops=1)                                |
|                                       Workers Planned: 2                                                                                                                             |
|                                       Workers Launched: 2                                                                                                                            |
|                                       ->  Sort  (cost=103908.21..104189.32 rows=112444 width=24) (actual time=481.532..484.165 rows=38342 loops=3)                                   |
|                                             Sort Key: record_field.record_id                                                                                                         |
|                                             Sort Method: quicksort  Memory: 3654kB                                                                                                   |
|                                             Worker 0:  Sort Method: quicksort  Memory: 3332kB                                                                                        |
|                                             Worker 1:  Sort Method: quicksort  Memory: 3556kB                                                                                        |
|                                             ->  Partial HashAggregate  (cost=82357.15..92168.30 rows=112444 width=24) (actual time=452.259..458.555 rows=38342 loops=3)              |
|                                                   Group Key: record_field.record_id                                                                                                  |
|                                                   Planned Partitions: 4  Batches: 1  Memory Usage: 4881kB                                                                            |
|                                                   Worker 0:  Batches: 1  Memory Usage: 4369kB                                                                                        |
|                                                   Worker 1:  Batches: 1  Memory Usage: 4625kB                                                                                        |
|                                                   ->  Parallel Seq Scan on record_field  (cost=0.00..29078.66 rows=741266 width=24) (actual time=0.296..355.722 rows=593013 loops=3) |
| Planning Time: 11.046 ms                                                                                                                                                             |
| Execution Time: 2919.026 ms                                                                                                                                                          |

Consultas por conta própria

explain analyse SELECT record_id FROM record_field GROUP BY record_id ORDER BY MAX(updated_at) desc LIMIT 1

| QUERY PLAN                                                                                                                                                         |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Limit  (cost=133958.10..133958.10 rows=1 width=24) (actual time=547.848..550.734 rows=1 loops=1)                                                                   |
|   ->  Sort  (cost=133958.10..134239.21 rows=112444 width=24) (actual time=547.846..550.732 rows=1 loops=1)                                                         |
|         Sort Key: (max(updated_at)) DESC                                                                                                                           |
|         Sort Method: top-N heapsort  Memory: 25kB                                                                                                                  |
|         ->  Finalize GroupAggregate  (cost=104908.23..133395.88 rows=112444 width=24) (actual time=499.786..542.183 rows=112011 loops=1)                           |
|               Group Key: record_id                                                                                                                                 |
|               ->  Gather Merge  (cost=104908.23..131147.00 rows=224888 width=24) (actual time=499.776..525.336 rows=115000 loops=1)                                |
|                     Workers Planned: 2                                                                                                                             |
|                     Workers Launched: 2                                                                                                                            |
|                     ->  Sort  (cost=103908.21..104189.32 rows=112444 width=24) (actual time=485.506..489.133 rows=38333 loops=3)                                   |
|                           Sort Key: record_id                                                                                                                      |
|                           Sort Method: quicksort  Memory: 3579kB                                                                                                   |
|                           Worker 0:  Sort Method: quicksort  Memory: 3486kB                                                                                        |
|                           Worker 1:  Sort Method: quicksort  Memory: 3476kB                                                                                        |
|                           ->  Partial HashAggregate  (cost=82357.15..92168.30 rows=112444 width=24) (actual time=467.843..473.231 rows=38333 loops=3)              |
|                                 Group Key: record_id                                                                                                               |
|                                 Planned Partitions: 4  Batches: 1  Memory Usage: 4881kB                                                                            |
|                                 Worker 0:  Batches: 1  Memory Usage: 4625kB                                                                                        |
|                                 Worker 1:  Batches: 1  Memory Usage: 4625kB                                                                                        |
|                                 ->  Parallel Seq Scan on record_field  (cost=0.00..29078.66 rows=741266 width=24) (actual time=0.114..380.557 rows=593013 loops=3) |
| Planning Time: 2.946 ms                                                                                                                                            |
| Execution Time: 554.166 ms                                                                                                                                         |

explain analyse SELECT * FROM record_view WHERE id IN ('91381946-82fd-480b-a910-16e5e8ae0461')

| QUERY PLAN                                                                                                                                                  |
| ----------------------------------------------------------------------------------------------------------------------------------------------------------- |
| GroupAggregate  (cost=84.26..84.66 rows=16 width=63) (actual time=8.467..8.515 rows=1 loops=1)                                                              |
|   Group Key: rf.object_id, rf.record_id                                                                                                                     |
|   ->  Sort  (cost=84.26..84.30 rows=16 width=86) (actual time=8.174..8.224 rows=16 loops=1)                                                                 |
|         Sort Key: rf.object_id                                                                                                                              |
|         Sort Method: quicksort  Memory: 26kB                                                                                                                |
|         ->  Hash Join  (cost=67.69..83.94 rows=16 width=86) (actual time=8.064..8.127 rows=16 loops=1)                                                      |
|               Hash Cond: ((f.id = rf.field_id) AND (f.object_id = rf.object_id))                                                                            |
|               ->  Seq Scan on field f  (cost=0.00..14.10 rows=410 width=64) (actual time=3.815..3.820 rows=25 loops=1)                                      |
|               ->  Hash  (cost=67.45..67.45 rows=16 width=63) (actual time=1.477..1.486 rows=16 loops=1)                                                     |
|                     Buckets: 1024  Batches: 1  Memory Usage: 10kB                                                                                           |
|                     ->  Bitmap Heap Scan on record_field rf  (cost=4.55..67.45 rows=16 width=63) (actual time=0.449..0.476 rows=16 loops=1)                 |
|                           Recheck Cond: (record_id = '91381946-82fd-480b-a910-16e5e8ae0461'::uuid)                                                          |
|                           Heap Blocks: exact=1                                                                                                              |
|                           ->  Bitmap Index Scan on record_field_record_id_idx  (cost=0.00..4.55 rows=16 width=0) (actual time=0.436..0.443 rows=16 loops=1) |
|                                 Index Cond: (record_id = '91381946-82fd-480b-a910-16e5e8ae0461'::uuid)                                                      |
| Planning Time: 4.332 ms                                                                                                                                     |
| Execution Time: 8.961 ms                                                                                                                                    |

Dado o seguinte trabalho do SQL Server Agent

USE [msdb]
GO


BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Do Nothing', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'Me', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Wait', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'WAITFOR DELAY ''00:00:10''', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

E o seguinte código de chamada

EXEC msdb.dbo.sp_start_job @job_name = 'Do nothing' -- waits for 10 seconds
WAITFOR DELAY '00:00:05'
EXEC msdb.dbo.sp_start_job @job_name = 'Do nothing' -- fails, job still running
WAITFOR DELAY '00:00:10'
EXEC msdb.dbo.sp_start_job @job_name = 'Do nothing' -- should succeed

Recebo o seguinte erro:

Job 'Do nothing' started successfully. Msg 22022, Level 16, State 1, Line 25 SQLServerAgent Error: Request to run job Do Nothing (from User Me) refused because the job is already running from a request by User Me. Job 'Do nothing' started successfully.

Então podemos ver que a segunda sp_start_jobchamada falhou porque o trabalho ainda estava em execução, então o SQL Server continuou a execução

a falha é nível 16 e quando eu faço o seguinte

PRINT 'hello'
    RAISERROR ('error',16,1)
PRINT 'hello'

Recebo o mesmo comportamento de "falhar e continuar"

hello
Msg 50000, Level 16, State 1, Line 50
error
hello

no entanto, se eu fizer o seguinte, o que também gera um erro de nível 16, a segunda impressão não será executada

PRINT 'hello'
    ;THROW 51000, 'error', 1;
PRINT 'hello'

hello
Msg 51000, Level 16, State 1, Line 50
error

Os documentos para o estado THROW

Qualquer erro que ocorra em uma instrução THROW faz com que o lote de instruções seja encerrado.

Os documentos para o estado RAISERROR

O erro é retornado ao chamador se RAISERROR for executado: Fora do escopo de qualquer bloco TRY. Com uma gravidade de 10 ou menor em um bloco TRY. Com uma gravidade de 20 ou maior que encerra a conexão com o banco de dados.

Minha pergunta é: sp_start_jobusar RAISERROR em vez de throw - Eu olhei a definição e não consigo ver se em algum lugar

Meu cluster riak tem 10 nós executando a versão 2.9.8. Todos os nós têm a mesma versão. O nó que nomeou [email protected] usou cerca de 95% do espaço do disco. E outros nós usaram menos de 50% do espaço do disco.

Tentei descobrir o erro de compactação de dados como diz esta postagem :

find . -name "LOG" -exec grep -l 'Compaction error' {} \;

./308285501624487334308589769401090949458673270784/LOG
./336830455478606531929755488790080852186328203264/LOG
./365375409332725729550921208179070754913983135744/LOG
./793549717144513693868406999013919295828807122944/LOG

As mensagens de erro nos logs de partição são como abaixo:

2024/05/25-16:30:51.332435 7f04c47f8700 Finalize level: 5, grooming 1
2024/05/25-16:30:51.332506 7f04c47f8700 Finalize level: 6, grooming 0
2024/05/25-16:30:51.332570 7f04c3ff7700 Compacting 1@6 + 0@7 files
2024/05/25-16:30:51.333295 7f04c3ff7700 compacted to: files[ 3 0 3 765 482 109 126 ]
2024/05/25-16:30:51.333312 7f04c3ff7700 Compaction error: IO error: /data/riak/leveldb/308285501624487334308589769401090949458673270784/sst_7/307388.sst: No such file or directory
2024/05/25-16:30:51.333319 7f04c3ff7700 Waiting after background compaction error: IO error: /data/riak/leveldb/308285501624487334308589769401090949458673270784/sst_7/307388.sst: No such file or directory
2024/05/25-16:30:52.334919 7f04c3ff7700 Finalize level: 5, grooming 1
2024/05/25-16:30:52.335003 7f04c3ff7700 Finalize level: 6, grooming 0
2024/05/25-16:30:52.335061 7f04c37f6700 Compacting 1@6 + 0@7 files
2024/05/25-16:30:52.335507 7f04c37f6700 compacted to: files[ 3 0 3 765 482 109 126 ]
2024/05/25-16:30:52.335522 7f04c37f6700 Compaction error: IO error: /data/riak/leveldb/308285501624487334308589769401090949458673270784/sst_7/307389.sst: No such file or directory
2024/05/25-16:30:52.335528 7f04c37f6700 Waiting after background compaction error: IO error: /data/riak/leveldb/308285501624487334308589769401090949458673270784/sst_7/307389.sst: No such file or directory
2024/05/25-16:30:53.337142 7f04c37f6700 Finalize level: 5, grooming 1

Todas as partições usaram cerca de 30 GB cada uma, exceto os nós que têm erros de compactação. Abaixo o tamanho dessas partições:

1.3T  ../308285501624487334308589769401090949458673270784
67G  ../336830455478606531929755488790080852186328203264
159G  ../365375409332725729550921208179070754913983135744
577G  ../793549717144513693868406999013919295828807122944

O disco continuou crescendo por causa desses erros de compactação? Após reparar essas partições/vnodes, o espaço será liberado? Se não, o que posso fazer?

Meu banco de dados usa InnoDB e, ao usá-lo, mysqldumptenho a opção de configurar --single-transactionpara evitar o bloqueio das tabelas e, ao mesmo tempo, manter uma visualização consistente delas.

Estou tentando mudar para o MySQL Shellutil.dumpSchema recomendado , e só vejo duas opções: consistent: truee consistent: false. A documentação para consistent: trueespecifica que as tabelas serão bloqueadas para backup.

É possível usar util.dumpSchemapara despejar em uma única transação via InnoDB da mesma forma? Ou isso é feito automaticamente se as tabelas usarem InnoDB?

Criei com sucesso um login para um usuário em nosso Azure SQL DB usando o seguinte código:

CRIAR LOGIN [xxxxxxxx] COM SENHA=N'4QfaE+AV0TdFBpiP/ZnqCg/clPPwEeT2ZfZzDEdM2k8='

Porém quando tento acessar o banco de dados com o login recebo o erro

The server principal "xxxxxxxxxxxxxx" is not able to access the database "xxxxxxx" under the current security context.
Cannot open database "axxxxxxx" requested by the login. The login failed.
Login failed for user 'xxxxxxxxxxx'.

Acho que o problema é que preciso dar permissões ao usuário para acessar o banco de dados. Alguém pode me dizer como dar acesso total ao usuário para o banco de dados, por favor.

Estou usando o SSMS 20.2

Atualmente, temos um banco de dados SQL 2016 replicando para um banco de dados SQL 2012 e estamos atualizando nossa instância SQL 2016 para SQL 2019. De acordo com a documentação de compatibilidade, a replicação entre as versões não seria mais possível: https://learn.microsoft.com/en-us/sql/relational-databases/replication/replication-backward-compatibility

Não conseguimos atualizar a instância do SQL 2012, então fiquei pensando se uma abordagem de "vários saltos" funcionaria, ou seja, replicação do SQL 2012 para um novo SQL 2016 e depois replicação da nova instância do SQL 2016 para a instância do SQL 2019.

Tenho uma tabela com a seguinte estrutura:

create table test_table
(queuename    number,
 duration_sum number,
 rating_sum   number,
 rating_avg   number,
 rating_cnt   number
 )

Aqui estão os dados de exemplo:

insert into test_table (queuename, duration_sum, rating_sum, rating_avg,rating_cnt)
values (1000,50,40,60,70);
insert into test_table (queuename, duration_sum, rating_sum, rating_avg,rating_cnt)
values (1010,12,40,25,34);
insert into test_table (queuename, duration_sum, rating_sum, rating_avg,rating_cnt)
values (2000,50,34,60,23);
insert into test_table (queuename, duration_sum, rating_sum, rating_avg,rating_cnt)
values (3000,90,40,60,67);
commit;

O que preciso é o resultado abaixo:

 queuename         1000     1010     2000     3000     
 duration_sum       50       12       50       90 
 rating_sum         40       40       34       40 
 rating_avg         60       25       60       60  
 rating_cnt         70       34       23       67 

Estou tentando usar pivotisso e isso é o que escrevi até agora:

select *
from (
    select 
        queuename,
        'duration_sum' as measure, duration_sum as value from test_table
    union all
    select 
        queuename,
        'rating_sum' as measure, rating_sum as value from test_table
    union all
    select 
        queuename,
        'rating_avg' as measure, rating_avg as value from test_table
    union all
    select 
        queuename,
        'rating_cnt' as measure, rating_cnt as value from test_table
         )
pivot (
    max(value)
    for queuename in (1000 as "1000", 1010 as "1010", 2000 as "2000", 3000 as "3000")
       )
order by measure;

Essa é uma abordagem melhor para pivotingo resultado? Acho que pode haver uma abordagem melhor para usar pivota instrução em vez de usar union. Eu deveria dizer que 'queuename' é único.

Desde já, obrigado.

Resumo/Resumo;

Ao analisar a primeira página do DCM em um banco de dados (que documenta quais extensões foram modificadas, para que o processo de backup DIFF não precise verificar todo o banco de dados em busca de alterações, mas possa pular para as extensões para verificar as páginas que foram alteradas), onde está o local da próxima página do DCM documentada?

Eu teria pensado que isso seria documentado no m_nextPageatributo do cabeçalho da página. Mas ao verificar, isso não parece ser o caso.

Encontrando as páginas do DCM

Eu estava instruindo nosso aprendiz sobre o que os backups diferenciais contêm e como o DBMS documenta as alterações nas páginas individuais e depois na página consolidada do DCM.

Referência: Guia de arquitetura de páginas e extensões (Microsoft Learn | SQL)

Comecei então a brincar com um banco de dados muito grande e executei os seguintes comandos:

DBCC TRACEON (3604);
DBCC PAGE ( 6, 1, 0, 3 );       -- File Header 

Isso produziu a seguinte saída:

PAGE: (1:0)


BUFFER:


BUF @0x000002CC7AC80780

bpage = 0x000002CC2D11E000          bhash = 0x0000000000000000          bpageno = (1:0)
bdbid = 6                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 48700                       bstat = 0x9
blog = 0x15a                        bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000002CC2D11E000

m_pageId = (1:0)                    m_headerVersion = 1                 m_type = 15
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x208
m_objId (AllocUnitId.idObj) = 99    m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0           Metadata: IndexId = 0               Metadata: ObjectId = 99
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 0
m_slotCnt = 1                       m_freeCnt = 7019                    m_freeData = 3321
m_reservedCnt = 0                   m_lsn = (2501578:318516:1)          m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = 1668847350
DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED 

Depois de ler o resultado, notei a informação perto do final e presumi (corretamente) que a página do DCM estaria localizada na página número 6 (bem, 7 na verdade, mas...).

Vamos dar uma olhada:

DBCC PAGE ( 6, 1, 6, 0 );       -- 1. DCM Page

Sim, estas parecem ser as primeiras páginas do DCM:

PAGE: (1:6)


BUFFER:


BUF @0x000002CC7AC80300

bpage = 0x000002CC2D112000          bhash = 0x0000000000000000          bpageno = (1:6)
bdbid = 6                           breferences = 1                     bcputicks = 0
bsampleCount = 0                    bUse1 = 48800                       bstat = 0x9
blog = 0x7a7a7a7a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000002CC2D112000

m_pageId = (1:6)                    m_headerVersion = 1                 m_type = 16
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 99    m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0           Metadata: IndexId = 0               Metadata: ObjectId = 99
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 90
m_slotCnt = 2                       m_freeCnt = 6                       m_freeData = 8182
m_reservedCnt = 0                   m_lsn = (2501605:3132423:42)        m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = -431908416
DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

DIFF_MAP: Header @0x000000B60C7FA064 Slot 0, Offset 96

status = 0x0                        

DIFF_MAP: Extent Alloc Status @0x000000B60C7FA0C2

(1:0)        - (1:24)       =     CHANGED                                
(1:32)       - (1:40)       = NOT CHANGED                                
(1:48)       -              =     CHANGED                                
(1:56)       - (1:80)       = NOT CHANGED                                
(1:88)       -              =     CHANGED                                
(1:96)       - (1:104)      = NOT CHANGED                                
(1:112)      - (1:128)      =     CHANGED                                
(1:136)      - (1:152)      = NOT CHANGED                                
(1:160)      - (1:168)      =     CHANGED                                
(1:176)      -              = NOT CHANGED                                
(1:184)      - (1:192)      =     CHANGED                                
(1:200)      - (1:208)      = NOT CHANGED   
...

No entanto, encontrar a próxima página do DCM foi uma tentativa e erro. Adicionei 4'096'000 páginas às 6 que eu já tinha (o que está errado, deveria ser apenas 512'000 páginas. Obrigado Martin Smith por apontar isso), mas recebi um número de página um pouco maior do que a próxima página do DCM. A próxima página do DCM pode ser encontrada nas informações do cabeçalho.

GAM (1:4089856) = ALLOCATED         SGAM (1:4089857) = NOT ALLOCATED    PFS (1:4092528) = 0x0   0_PCT_FULL
DIFF (1:4089862) = NOT CHANGED      ML (1:4089863) = NOT MIN_LOGGED    

A próxima página do DCM não está documentada nas m_nextPageinformações do cabeçalho, então presumo que ela deva estar na última página bitda primeira página do DCM.

Questões

1. Minha suposição está correta de que o SQL Server detecta a próxima página do DCM como o último bit da primeira página do DCM? Ou o SQL Server tem outros meios de encontrar a segunda, terceira, quarta, etc. páginas do DCM?

2. Não seria mais eficiente documentar a próxima página do DCM nas m_nextPageinformações de cabeçalho da primeira página do DCM?

Estou tentando capturar o valor CDC existente e desabilitar e habilitar novamente para truncamento de partição. Não consegui descobrir como lidar com valores nulos em SQL dinâmico.

Se eu passar diretamente @role_name =NULL, & @filegroup_name = NULL. Está funcionando bem.

-- Enable cdc for that table
    declare @cmd_cdc_enable nvarchar(max)
    SET @cmd_cdc_enable =N'
    exec sys.sp_cdc_enable_table 
    @source_schema = '''+@v_source_schema+''', 
    @source_name = '''+@v_source_table+''',
    @capture_instance = '''+@v_capture_instance +''',
    @supports_net_changes = 1,
    @role_name     =NULL,
    @filegroup_name = NULL,
    @allow_partition_switch = 1

Código de exemplo:

GO
EXEC sys.sp_cdc_enable_db
GO


create schema [C1810429]
go


create table [C1810429].[STYTOTAL_RAW_NEW] (ID int identity primary key, name varchar(200))

-- Enable First time
go
EXEC sys.sp_cdc_enable_table  
    @source_schema = N'C1810429',  
    @source_name   = N'STYTOTAL_RAW_NEW', 
    @capture_instance = 'C1810429_STYTOTAL_RAW_NEW',
    @supports_net_changes = 1 ,
    @role_name     = NULL,  
    @filegroup_name = NULL, 
    @allow_partition_switch = 1



drop table if exists tbl_sp_cdc_help_change_data_capture

create table tbl_sp_cdc_help_change_data_capture (
source_schema sysname null, source_table sysname null,  capture_instance sysname null,  object_id int null, source_object_id int null,  
start_lsn binary(10) null,  end_lsn binary(10) null, supports_net_changes bit null,has_drop_pending bit null,   role_name sysname null, 
index_name sysname null,    filegroup_name sysname null,    create_date datetime null,  index_column_list nvarchar(max) null,   captured_column_list nvarchar(max) null
)

insert into tbl_sp_cdc_help_change_data_capture
Exec sys.sp_cdc_help_change_data_capture @source_schema='C1810429',@source_name='STYTOTAL_RAW_NEW'

-- select * from tbl_sp_cdc_help_change_data_capture



    declare @v_source_schema sysname
    declare @v_source_table sysname 
    declare @v_capture_instance sysname
    declare @v_supports_net_changes int
    declare @v_role_name sysname
    declare @v_filegroup_name sysname

    select @v_source_schema=source_schema,@v_source_table=source_table,@v_capture_instance=capture_instance,@v_supports_net_changes=supports_net_changes,
    @v_role_name=role_name,@v_filegroup_name=[filegroup_name]
    from tbl_sp_cdc_help_change_data_capture

    --select @v_source_schema,@v_source_table,@v_capture_instance,@v_supports_net_changes,@v_role_name,@v_filegroup_name

    /*
    -- disable cdc for that table
    declare @cmd_CDC_disable nvarchar(max)
    SET @cmd_CDC_disable =N'
    exec sys.sp_cdc_disable_table 
    @source_schema = '''+@v_source_schema+''', 
    @source_name = '''+@v_source_table+''',
    @capture_instance = '''+@v_capture_instance+'''

    '
    --print @cmd_CDC_disable
    EXEC sys.sp_executesql @cmd_CDC_disable

    select 'Disabled'

    */
    

    --select @v_source_schema,@v_source_table,@v_capture_instance,@v_supports_net_changes,@v_role_name,@v_filegroup_name

    -- Enable cdc for that table
    declare @cmd_cdc_enable nvarchar(max)
    SET @cmd_cdc_enable =N'
    exec sys.sp_cdc_enable_table 
    @source_schema = '''+@v_source_schema+''', 
    @source_name = '''+@v_source_table+''',
    @capture_instance = '''+@v_capture_instance +''',
    @supports_net_changes = 1,
    @role_name     ='''+@v_role_name  +''',
    @filegroup_name = '''+@v_filegroup_name  +''',
    @allow_partition_switch = 1

    '
    print @cmd_cdc_enable

    EXEC sys.sp_executesql @cmd_cdc_enable

    --select 'Enabled'

Este tópico já foi discutido aqui: Entendendo tamanhos de bloco Mas tenho mais algumas coisas a acrescentar para meu caso de uso.

Geralmente, a maioria database systemsusa um padrão block sizede 8 KB, embora alguns permitam que ele seja modificado. Por outro lado, os modernos operating systemsgeralmente usam um 4 KBtamanho de bloco para file systems. Essa discrepância pode resultar em várias physical I/Osolicitações para preencher um único database page.

Um tamanho de bloco de sistema de arquivo menor beneficia random reads, como index lookups, enquanto tamanhos de bloco maiores são vantajosos para sequential scanse heap fetches. Considerando esses pontos, tenho algumas perguntas:

1. Existe uma prática comum para alinhar o tamanho do bloco do banco de dados com o tamanho do bloco do sistema de arquivos para OLTP?
2. Em um clustered system(por exemplo, SQL Server Availability Groupsou PostgreSQL streaming replication) com um primário e um ou mais secundários, é aceitável ter tamanhos de bloco de sistema de arquivos diferentes ou isso é algo que deve ser sempre evitado?
3. Para analytical databasesou columnar tables, é benéfico usar um tamanho de bloco maior?