Perguntas[sql-server](dba)

Tenho uma consulta propensa à sensibilidade de parâmetros e que sofre com seu parâmetro com valor de tabela. Sou preguiçoso e só quero resolver isso com dicas de consulta. Quando estou preguiçoso, consigo resolver a sensibilidade de parâmetros com OPTION (OPTIMIZE FOR UNKNOWN). Isso me gera três perguntas muito relacionadas:

1. Tem algum impacto em variáveisOPTION (OPTIMIZE FOR UNKNOWN) ​​com valor de tabela ?
2. Tem algum impacto nos parâmetrosOPTION (OPTIMIZE FOR UNKNOWN) com valor de tabela ?
3. Quais outras dicas de consulta podem ser usadas para influenciar estimativas de cardinalidade para parâmetrosOPTION(RECOMPILE) com valores de tabela ?

Tanto meus próprios experimentos quanto a documentação sugerem que essa é uma causa perdida. Parâmetros com valor de tabela estão sujeitos à detecção de parâmetros, mas tudo o que é detectado é a cardinalidade, então OPTION (OPTIMIZE FOR UNKNOWN)não deve fazer nada.

Quanto à questão de usar outras dicas de tabela, sei que posso alterar a estimativa para operações no parâmetro com valor de tabela (por exemplo, OPTION (USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES')), mas não acho que as dicas possam influenciar a estimativa de cardinalidade para o parâmetro em si.

Para um exemplo de tabela rowstore...

CREATE TABLE T(Id INT PRIMARY KEY, C1 INT NULL, C2 INT NULL);

Existem vários métodos diferentes para recuperar contagens de linhas de tabela de metadados no SQL Server - como o abaixo

SELECT SUM(rows) 
FROM sys.partitions
WHERE object_id = object_id('dbo.T') AND index_id <= 1;

SELECT SUM(row_count) 
FROM sys.dm_db_partition_stats
WHERE object_id = object_id('dbo.T') AND index_id <= 1;

SELECT SUM(rows)
FROM sys.sysindexes
WHERE id = object_id('dbo.T') AND indid <= 1;

SELECT OBJECTPROPERTYEX(object_id('dbo.T'), 'Cardinality') 

Os planos de execução aparentemente mostram uma variedade de objetos diferentes sendo usados ​​- como o abaixo.

• sysrowsets OUTER APPLY OpenRowset(TABLE ALUCOUNT
• sysidxstats CROSS APPLY OpenRowSet(TABELA PARTITIONCOUNTS
• sysidxstats i CROSS APPLY OpenRowSet(TABLE INDEXPROP

O que está acontecendo aqui? O SQL Server realmente mantém esses metadados em vários lugares? Se sim, qual é o método mais confiável?

Seguindo as etapas em Failover manual do SQL Server DAG

Para verificar se ambos os Grupos de Disponibilidade estão prontos para failover, use a consulta T-SQL abaixo, executada no Modo SQLCMD:

:CONNECT TDPRD071  
SELECT ag.name, drs.database_id, db_name(drs.database_id) as database_name,  
       drs.group_id, drs.replica_id, drs.last_hardened_lsn  
FROM sys.dm_hadr_database_replica_states drs  
INNER JOIN sys.availability_groups ag ON drs.group_id = ag.group_id;

:CONNECT TDDR071  
SELECT ag.name, drs.database_id, db_name(drs.database_id) as database_name,  
       drs.group_id, drs.replica_id, drs.last_hardened_lsn  
FROM sys.dm_hadr_database_replica_states drs  
INNER JOIN sys.availability_groups ag ON drs.group_id = ag.group_id;

A captura de tela dos resultados da execução das consultas acima no primário global, conforme mostrado no artigo, mostra a conexão com cada AG no DAG.

No entanto, quando executo as consultas no primário global do meu DAG, os resultados que obtenho são apenas da conexão com o encaminhador. Portanto, os dois conjuntos de resultados exibidos são apenas da conexão com o AG02.

Entretanto, se eu executar as duas consultas individualmente (ambas do primário global), elas se conectarão corretamente ao AG01 ou AG02, conforme o caso.

Alguma sugestão sobre o motivo de eu estar tendo esse comportamento?

obrigado.

Pelo que consegui encontrar na web, e neste site também, tentei todos os seguintes procedimentos (1 a 5) para recuperar a última data em que o SQL Server foi iniciado:

SELECT 0 as Id, SYSDATETIME() as DT
union
SELECT 1, sqlserver_start_time FROM sys.dm_os_sys_info  
union
SELECT 2, login_time FROM sys.dm_exec_sessions WHERE session_id = 1
union
SELECT 3, start_time from sys.traces where is_default = 1
union
SELECT 4, crdate FROM sysdatabases WHERE name='tempdb'
union
SELECT 5, create_date FROM sys.databases WHERE name = 'tempdb'

O resultado é:

0   2025-04-17 23:38:26.9364166
1   2025-04-13 19:09:42.2000000
2   2025-04-13 19:09:44.3166667
3   2025-04-13 19:09:46.5600000
4   2025-04-13 19:09:51.0900000
5   2025-04-13 19:09:51.0900000

O problema: iniciei a máquina (e o SQL Server com ela) há apenas uma hora. Eu esperava algo como 2025-04-17 22:38... e não 2025-04-13 19:09.

Este servidor SQL está instalado em uma caixa VMware.

Em outro SQL Server, mesma edição e versão, tenho resultados corretos:

Id  DT
0   2025-04-18 14:34:49.3442637
1   2025-04-18 14:34:03.4333333
2   2025-04-18 14:34:05.1233333
3   2025-04-18 14:34:09.7400000
4   2025-04-18 14:34:14.6100000
5   2025-04-18 14:34:14.6100000

Não consigo entender por que tenho resultados errados na máquina virtual. Eu inicio e desligo a máquina host e a VM convidada sempre que trabalho.

Estou esquecendo de alguma coisa aqui?

• SQL Server Dev Edition 2019

Eu estava lendo isso e fiz o seguinte:

go
create table fiirst (
col1 int,
col2 int
);
create table seecond(
col1 int,
col2 int
);
with
n1(c) as (select 0 union all select 0 ),
n2(c) as ( select 0 from n1 as t1 cross join n1 as t2),
n3(c) as ( select 0 from n2 as t1 cross join n2 as t2),
n4(c) as (select 0 from n3 as t1 cross join n3 as t2),
ids(id) as (select ROW_NUMBER() over (order by (select null)) from n4)
insert into fiirst(col1,col2)
select id,id 
from ids;
with
n1(c) as (select 0 union all select 0 ),
n2(c) as ( select 0 from n1 as t1 cross join n1 as t2),
n3(c) as ( select 0 from n2 as t1 cross join n2 as t2),
n4(c) as (select 0 from n3 as t1 cross join n3 as t2),
ids(id) as (select ROW_NUMBER() over (order by (select null)) from n4)
insert into seecond(col1,col2)
select id,id
from ids;
----Craig Freedman's query

select *
from fiirst
where fiirst.col1 > (
    select min(seecond.col1)
    from seecond
    where seecond.col2 < fiirst.col2
);

E eu obtive um spool de índice , mesmo a tabela sendo um heap. A questão é: como isso aconteceu? Por que eu obtenho um spool de índice em um heap? No exemplo mencionado no link acima, não havia linhas, então não havia spools, mas aqui eu os vejo?

Estou com um problema com meu script T-SQL. Estou pensando e trabalhando em como produzir a saída esperada dele.

Aqui está meu script T-SQL:

DECLARE @user_id VARCHAR(50) = 'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL'; -- FOR TESTING PURPOSES ONLY
DECLARE @status_t TABLE (status VARCHAR(50));
INSERT INTO @status_t VALUES ('PENDING'),('APPROVED'),('PROCESSING')

SELECT
    orders.batch_code, -- FOR DEMONSTRATION PURPOSES ONLY
    oa_prod.nested_data AS nested_prod,
    cnb.amount_to_pay
FROM
    [testDB].[dbo].[Orders] AS orders
RIGHT OUTER JOIN
    [testDB].[dbo].[Payment] AS cnb
ON
    orders.order_id = cnb.order_id
LEFT OUTER JOIN
    [testDB].[dbo].[StatusRef] AS stat_ref
ON
    orders.stat_ref_id = stat_ref.stat_ref_id
CROSS APPLY( -- OR OUTER APPLY
    SELECT
        orders.batch_code,
        orders.order_id,
        prod.prod_name,
        pv_kn.key_name,
        pv_kv.value,
        CASE WHEN
            orders.prod_id IS NOT NULL
        THEN
            prod.disc_price
        WHEN
            orders.prod_var_id IS NOT NULL
        THEN
            prod_var.disc_price
        END AS disc_price,
        orders.quantity
    FROM
        [testDB].[dbo].[Product] AS prod
    RIGHT OUTER JOIN
        [testDB].[dbo].[SubProduct] AS prod_var
    ON
        prod.prod_id = prod_var.prod_id
    LEFT OUTER JOIN
        [testDB].[dbo].[SubProductVarKeyValue] AS pv_kv
    ON
        prod_var.prod_var_id = pv_kv.prod_var_id
    LEFT OUTER JOIN
        [testDB].[dbo].[SubProductVarKeyNames] AS pv_kn
    ON
        pv_kv.pv_key_name_id = pv_kn.pv_key_name_id
    WHERE       
        prod.prod_id = orders.prod_id
        OR prod_var.prod_var_id = orders.prod_var_id
    FOR JSON PATH,
    INCLUDE_NULL_VALUES
) AS oa_prod(nested_data) -- it's a syntax analyzer lint conflict ONLY
WHERE
    orders.disable = 0
    AND cnb.disable = 0
    AND orders.user_id = @user_id
    AND stat_ref.com_usd_wrds IN (SELECT status FROM @status_t)
ORDER BY
    orders.dt_stamp DESC,
    orders.batch_code ASC -- To prevent batch_code from being separated
FOR JSON PATH,
INCLUDE_NULL_VALUES

Aqui está meu exemplo mínimo e reproduzível usando variáveis ​​de tabela:

DECLARE @StatusRef TABLE(
    stat_ref_id VARCHAR(50) PRIMARY KEY NOT NULL,
    com_usd_wrds NVARCHAR(100) NOT NULL
);

DECLARE @Product TABLE(
    prod_id VARCHAR(50) PRIMARY KEY NOT NULL,
    prod_name VARCHAR(200) NOT NULL,
    stock INT NOT NULL,
    disc_price DECIMAL(12, 2) NOT NULL
);

DECLARE @SubProduct TABLE(
    prod_var_id VARCHAR(50) PRIMARY KEY NOT NULL,
    stock INT NOT NULL,
    disc_price DECIMAL(12, 2) NOT NULL,
    prod_id VARCHAR(50) NOT NULL
);

DECLARE @Orders TABLE(
    order_id VARCHAR(50) PRIMARY KEY NOT NULL,
    batch_code VARCHAR(50) NULL,
    quantity INT NOT NULL,
    stat_ref_id VARCHAR(50) NOT NULL,
    disable BIT DEFAULT (0) NOT NULL,
    dt_stamp DATETIME NOT NULL,
    prod_id VARCHAR(50) NULL,
    prod_var_id VARCHAR(50) NULL,
    user_id VARCHAR(50) NOT NULL
);

DECLARE @Payment TABLE(
    amount_to_pay DECIMAL(14, 2) NOT NULL,
    order_id VARCHAR(50) NOT NULL,
    disable BIT DEFAULT (0) NOT NULL
);

DECLARE @SubProductVarKeyValue TABLE(
    value VARCHAR(100) NOT NULL,
    prod_var_id VARCHAR(50) NOT NULL,
    pv_key_name_id VARCHAR(50) NOT NULL
);

DECLARE @SubProductVarKeyNames TABLE(
    pv_key_name_id VARCHAR(50) PRIMARY KEY NOT NULL,
    key_name VARCHAR(100) NOT NULL
);

INSERT INTO @StatusRef
VALUES
(
    'STAT-REF-1001', -- stat_ref_id
    'PENDING' -- com_usd_wrds
),
(
    'STAT-REF-1002', -- stat_ref_id
    'APPROVED' -- com_usd_wrds
),
(
    'STAT-REF-1003', -- stat_ref_id
    'PROCESSING' -- com_usd_wrds
);

INSERT INTO @Product
VALUES
(
    'PROD-ID-1001', -- prod_id
    'iPhone', -- prod_name
    0, -- stock | dependent to @SubProduct
    0.00 -- disc_price | dependent to @SubProduct
),
(
    'PROD-ID-1002', -- prod_id
    'Samsung', -- prod_name
    0, -- stock | dependent to @SubProduct
    0.00 -- disc_price | dependent to @SubProduct
),
(
    'PROD-ID-1003', -- prod_id
    'Nokia', -- prod_name
    75, -- stock
    33150.00 -- disc_price
),
(
    'PROD-ID-1004', -- prod_id
    'Google', -- prod_name
    100, -- stock
    53509.00 -- disc_price
),
(
    'PROD-ID-1005', -- prod_id
    'Sony', -- prod_name
    0, -- stock | dependent to @SubProduct
    0.00 -- disc_price | dependent to @SubProduct
),
(
    'PROD-ID-1006', -- prod_id
    'Lenovo', -- prod_name
    0, -- stock | dependent to @SubProduct
    0.00 -- disc_price | dependent to @SubProduct
);

INSERT INTO @SubProduct
VALUES
(
    'PROD-VAR-ID-1', -- prod_var_id
    25, -- stock
    45809.00, -- disc_price
    'PROD-ID-1001' -- prod_id
),
(
    'PROD-VAR-ID-2', -- prod_var_id
    50, -- stock
    40209.00, -- disc_price
    'PROD-ID-1002' -- prod_id
),
(
    'PROD-VAR-ID-3', -- prod_var_id
    0, -- stock | dependent to @Product
    0.00, -- disc_price | dependent to @Product
    'PROD-ID-1003' -- prod_id
),
(
    'PROD-VAR-ID-4', -- prod_var_id
    0, -- stock | dependent to @Product
    0.00, -- disc_price | dependent to @Product
    'PROD-ID-1004' -- prod_id
),
(
    'PROD-VAR-ID-5', -- prod_var_id
    125, -- stock
    25809.00, -- disc_price
    'PROD-ID-1005' -- prod_id
),
(
    'PROD-VAR-ID-6', -- prod_var_id
    150, -- stock
    49100.00, -- disc_price
    'PROD-ID-1006' -- prod_id
);

INSERT INTO @SubProductVarKeyValue
VALUES
(
    'new', -- value
    'PROD-VAR-ID-1', -- prod_var_id
    'PVKN-ID-1' -- pv_key_name_id
),
(
    'new', -- value
    'PROD-VAR-ID-2', -- prod_var_id
    'PVKN-ID-1' -- pv_key_name_id
),
(
    'new', -- value
    'PROD-VAR-ID-5', -- prod_var_id
    'PVKN-ID-1' -- pv_key_name_id
),
(
    'new', -- value
    'PROD-VAR-ID-6', -- prod_var_id
    'PVKN-ID-1' -- pv_key_name_id
)

INSERT INTO @SubProductVarKeyNames
VALUES
(
    'PVKN-ID-1', -- pv_key_name_id
    'Condition' -- key_name
)

INSERT INTO @Orders
(
    order_id,
    batch_code,
    quantity,
    stat_ref_id,
    dt_stamp,
    prod_id,
    prod_var_id,
    user_id
)
VALUES
(
    'ORDER-2025-04-11-B71D0E2F5D8C', -- order_id
    NULL, -- batch_code
    1, -- quantity
    'STAT-REF-1003', -- stat_ref_id
    '2025-04-14 10:17:20.963', -- dt_stamp
    NULL, -- prod_id
    'PROD-VAR-ID-1', -- prod_var_id
    'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
    'ORDER-2025-04-11-D95EB033CA40', -- order_id
    'BGUID-2025-04-11-6D81B58FAE94', -- batch_code
    2, -- quantity
    'STAT-REF-1001', -- stat_ref_id
    '2025-04-13 09:17:20.963', -- dt_stamp
    NULL, -- prod_id
    'PROD-VAR-ID-2', -- prod_var_id
    'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
    'ORDER-2025-04-11-7F04EFA2BB60', -- order_id
    'BGUID-2025-04-11-6D81B58FAE94', -- batch_code
    2, -- quantity
    'STAT-REF-1001', -- stat_ref_id
    '2025-04-13 09:17:20.963', -- dt_stamp
    'PROD-ID-1003', -- prod_id
    NULL, -- prod_var_id
    'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
    'ORDER-2025-04-10-3F03EAA47686', -- order_id
    'BGUID-2025-04-10-20239FD2059F', -- batch_code
    1, -- quantity
    'STAT-REF-1002', -- stat_ref_id
    '2025-04-12 08:17:20.963', -- dt_stamp
    'PROD-ID-1004', -- prod_id
    NULL, -- prod_var_id
    'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
    'ORDER-2025-04-10-F4A89E2C4A30', -- order_id
    'BGUID-2025-04-10-20239FD2059F', -- batch_code
    1, -- quantity
    'STAT-REF-1002', -- stat_ref_id
    '2025-04-12 08:17:20.963', -- dt_stamp
    NULL, -- prod_id
    'PROD-VAR-ID-5', -- prod_var_id
    'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
    'ORDER-2025-04-08-31BD887341FA', -- order_id
    NULL, -- batch_code
    1, -- quantity
    'STAT-REF-1001', -- stat_ref_id
    '2025-04-11 07:17:20.963', -- dt_stamp
    NULL, -- prod_id
    'PROD-VAR-ID-6', -- prod_var_id
    'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL' -- user_id
);

INSERT INTO @Payment
(
    amount_to_pay,
    order_id
)
VALUES
(
    45809.00, -- amount_to_pay
    'ORDER-2025-04-11-B71D0E2F5D8C' -- order_id
),
(
    146718.00, -- amount_to_pay
    'ORDER-2025-04-11-D95EB033CA40' -- order_id
),
(
    146718.00, -- amount_to_pay
    'ORDER-2025-04-11-7F04EFA2BB60' -- order_id
),
(
    79318.00, -- amount_to_pay
    'ORDER-2025-04-10-3F03EAA47686' -- order_id
),
(
    79318.00, -- amount_to_pay
    'ORDER-2025-04-10-F4A89E2C4A30' -- order_id
),
(
    49100.00, -- amount_to_pay
    'ORDER-2025-04-08-31BD887341FA' -- order_id
);

SELECT * FROM @StatusRef

SAÍDA:

SELECT * FROM @Product

SAÍDA:

SELECT * FROM @SubProduct

SAÍDA:

SELECT * FROM @Orders ORDER BY dt_stamp

SAÍDA:

SELECT * FROM @Payment

SAÍDA:

SELECT * FROM @SubProductVarKeyValue

SAÍDA:

SELECT * FROM @SubProductVarKeyNames

SAÍDA:

Aqui está o exemplo modificado do script que é semelhante ao primeiro fornecido:

DECLARE @user_id VARCHAR(50) = 'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL'; -- FOR TESTING PURPOSES ONLY
DECLARE @status_t TABLE (status VARCHAR(50));
INSERT INTO @status_t VALUES ('PENDING'),('APPROVED'),('PROCESSING')

SELECT
    orders.batch_code, -- FOR DEMONSTRATION PURPOSES ONLY
    oa_prod.nested_data AS nested_prod,
    cnb.amount_to_pay
FROM
    @Orders AS orders
RIGHT OUTER JOIN
    @Payment AS cnb
ON
    orders.order_id = cnb.order_id
LEFT OUTER JOIN
    @StatusRef AS stat_ref
ON
    orders.stat_ref_id = stat_ref.stat_ref_id
CROSS APPLY( -- OR OUTER APPLY
    SELECT
        orders.batch_code,
        orders.order_id,
        prod.prod_name,
        pv_kn.key_name,
        pv_kv.value,
        CASE WHEN
            orders.prod_id IS NOT NULL
        THEN
            prod.disc_price
        WHEN
            orders.prod_var_id IS NOT NULL
        THEN
            prod_var.disc_price
        END AS disc_price,
        orders.quantity
    FROM
        @Product AS prod
    RIGHT OUTER JOIN
        @SubProduct AS prod_var
    ON
        prod.prod_id = prod_var.prod_id
    LEFT OUTER JOIN
        @SubProductVarKeyValue AS pv_kv
    ON
        prod_var.prod_var_id = pv_kv.prod_var_id
    LEFT OUTER JOIN
        @SubProductVarKeyNames AS pv_kn
    ON
        pv_kv.pv_key_name_id = pv_kn.pv_key_name_id
    WHERE       
        prod.prod_id = orders.prod_id
        OR prod_var.prod_var_id = orders.prod_var_id
    FOR JSON PATH,
    INCLUDE_NULL_VALUES
) AS oa_prod(nested_data) -- it's a syntax analyzer lint conflict ONLY
WHERE
    orders.disable = 0
    AND cnb.disable = 0
    AND orders.user_id = @user_id
    AND stat_ref.com_usd_wrds IN (SELECT status FROM @status_t)
ORDER BY
    orders.dt_stamp DESC
    --orders.batch_code ASC -- To prevent batch_code from being separated
FOR JSON PATH,
INCLUDE_NULL_VALUES

Infelizmente, quando embelezo a saída JSON, isso é o que ele gera:

[
  {
    "batch_code": null,
    "nested_prod": [
      {
        "batch_code": null,
        "order_id": "ORDER-2025-04-11-B71D0E2F5D8C",
        "prod_name": "iPhone",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 45809,
        "quantity": 1
      }
    ],
    "amount_to_pay": 45809
  },
  {
    "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
    "nested_prod": [
      {
        "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
        "order_id": "ORDER-2025-04-11-D95EB033CA40",
        "prod_name": "Samsung",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 40209,
        "quantity": 2
      }
    ],
    "amount_to_pay": 146718
  },
  {
    "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
    "nested_prod": [
      {
        "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
        "order_id": "ORDER-2025-04-11-7F04EFA2BB60",
        "prod_name": "Nokia",
        "key_name": null,
        "value": null,
        "disc_price": 33150,
        "quantity": 2
      }
    ],
    "amount_to_pay": 146718
  },
  {
    "batch_code": "BGUID-2025-04-10-20239FD2059F",
    "nested_prod": [
      {
        "batch_code": "BGUID-2025-04-10-20239FD2059F",
        "order_id": "ORDER-2025-04-10-3F03EAA47686",
        "prod_name": "Google",
        "key_name": null,
        "value": null,
        "disc_price": 53509,
        "quantity": 1
      }
    ],
    "amount_to_pay": 79318
  },
  {
    "batch_code": "BGUID-2025-04-10-20239FD2059F",
    "nested_prod": [
      {
        "batch_code": "BGUID-2025-04-10-20239FD2059F",
        "order_id": "ORDER-2025-04-10-F4A89E2C4A30",
        "prod_name": "Sony",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 25809,
        "quantity": 1
      }
    ],
    "amount_to_pay": 79318
  },
  {
    "batch_code": null,
    "nested_prod": [
      {
        "batch_code": null,
        "order_id": "ORDER-2025-04-08-31BD887341FA",
        "prod_name": "Lenovo",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 49100,
        "quantity": 1
      }
    ],
    "amount_to_pay": 49100
  }
]

No entanto, preciso que a saída seja algo como isto:

[
  {
    "batch_code": null,
    "nested_prod": [
      {
        "batch_code": null,
        "order_id": "ORDER-2025-04-11-B71D0E2F5D8C",
        "prod_name": "iPhone",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 45809,
        "quantity": 1
      }
    ],
    "amount_to_pay": 45809
  },
  {
    "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
    "nested_prod": [
      {
        "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
        "order_id": "ORDER-2025-04-11-D95EB033CA40",
        "prod_name": "Samsung",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 40209,
        "quantity": 2
      },
      {
        "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
        "order_id": "ORDER-2025-04-11-7F04EFA2BB60",
        "prod_name": "Nokia",
        "key_name": null,
        "value": null,
        "disc_price": 33150,
        "quantity": 2
      }
    ],
    "amount_to_pay": 146718
  },
  {
    "batch_code": "BGUID-2025-04-10-20239FD2059F",
    "nested_prod": [
      {
        "batch_code": "BGUID-2025-04-10-20239FD2059F",
        "order_id": "ORDER-2025-04-10-3F03EAA47686",
        "prod_name": "Google",
        "key_name": null,
        "value": null,
        "disc_price": 53509,
        "quantity": 1
      },
      {
        "batch_code": "BGUID-2025-04-10-20239FD2059F",
        "order_id": "ORDER-2025-04-10-F4A89E2C4A30",
        "prod_name": "Sony",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 25809,
        "quantity": 1
      }
    ],
    "amount_to_pay": 79318
  },
  {
    "batch_code": null,
    "nested_prod": [
      {
        "batch_code": null,
        "order_id": "ORDER-2025-04-08-31BD887341FA",
        "prod_name": "Lenovo",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 49100,
        "quantity": 1
      }
    ],
    "amount_to_pay": 49100
  }
]

Observação: O amount_to_payfoi pré-calculado no aplicativo cliente. Portanto, ele deve se tornar uma instância única quando batch_codes duplicados forem agrupados.

Os pedidos do usuário podem causar variações na saída JSON desejada mostrada acima.

Alguém está familiarizado com o problema que estou enfrentando?

Na medida do possível, prefiro não implementar a abordagem Common Table Expression (CTE).

Este artigo clássico sobre segurança de concorrência foi claramente projetado para realizar o upsert de apenas uma linha por vez. Na minha situação, tenho uma entrada com valor de tabela e quero realizar o upsert de cada linha de forma segura em termos de concorrência. Sei que isso nem sempre é possível, mas quero chegar o mais perto possível. MERGEParece uma solução natural, mas desconfio dela e, na verdade, estou em uma situação em que é propensa a bugs . As duas abordagens restantes no artigo de Michael J. Swart são:

1. Dentro de uma transação com dicas de bloqueio (atualização mais comum)

CREATE PROCEDURE s_AccountDetails_Upsert ( @Email nvarchar(4000), @Etc nvarchar(max) )
AS 
SET XACT_ABORT ON;
BEGIN TRAN
 
  UPDATE TOP (1) dbo.AccountDetails WITH (UPDLOCK, SERIALIZABLE)
     SET Etc = @Etc
   WHERE Email = @Email;
 
  IF (@@ROWCOUNT = 0)
  BEGIN      
      INSERT dbo.AccountDetails ( Email, Etc )
      VALUES ( @Email, @Etc );
  END 
COMMIT

2. Dentro de uma transação com dicas de bloqueio (inserir mais comum)

CREATE PROCEDURE s_AccountDetails_Upsert ( @Email nvarchar(4000), @Etc nvarchar(max) )
AS 
SET XACT_ABORT ON;
BEGIN TRAN
 
  INSERT dbo.AccountDetails ( Email, Etc )
  SELECT @Email, @Etc
  WHERE NOT EXISTS (
      SELECT *
      FROM dbo.AccountDetails WITH (UPDLOCK, SERIALIZABLE)
      WHERE Email = @Email
  )
 
  IF (@@ROWCOUNT = 0)
  BEGIN      
      UPDATE TOP (1) dbo.AccountDetails
      SET Etc = @Etc
      WHERE Email = @Email;
  END 
COMMIT 

Eu poderia adaptar qualquer uma delas para usar variáveis ​​de tabela (por exemplo, suspeito que IF (@@ROWCOUNT = 0)precise ser totalmente removida), mas o uso de uma entrada com valor de tabela torna óbvio que devemos preferir a primeira ou a segunda solução? Se não, com base em quê a decisão deve ser tomada?

Sou meio que um "DBA acidental", então posso estar totalmente enganado.

Estamos no local e estamos falando sobre nosso ambiente de teste.

Estamos planejando mudar de empresa de hospedagem.

Vamos mover nosso AG para a nova empresa de hospedagem

Gostaríamos de poder retornar rapidamente para a antiga empresa de hospedagem, caso algo não esteja funcionando na nova.

Temos muitas tabelas temporais, então a replicação normal via log de transações não é possível.

Embora seja um ambiente de teste, há muita atividade de teste que não queremos perder se uma nova empresa de hospedagem falhar. (Poderíamos fazer backup e restaurar, mas eu estava procurando uma solução mais rápida)

O logshipping pode ser uma solução para isso ou há outra maneira que você sugeriria?

cumprimentos

Introdução

Tenho lido muitos artigos sobre exclusão de bancos de dados e os perigos de usar o comando shrink. No entanto, tenho a oportunidade de mover um banco de dados para um novo servidor com uma grande janela de inatividade, então quero redefinir o banco de dados e reduzir seu tamanho (para aumentar o desempenho e economizar custos em um ambiente hospedado).

Fundo

• O SQL Server existente é a Web Edition 2016.
• Nova edição Standard do SQL Server 2022.
• Tamanho do banco de dados ~120 GB.
• Algumas tabelas grandes compõem a maior parte disso, assim como os índices nessas tabelas grandes.
• Quero me concentrar apenas na redução do tamanho do banco de dados. Estou confortável com as outras partes da criação de um novo servidor.

Processo proposto

Antes da interrupção

1. Execute procedimentos armazenados que usam DELETE FROMsintaxe. Eu os testei cuidadosamente. Eles agrupam as exclusões e serão executados ao longo de alguns dias.
2. Monitore os arquivos de log e o disco de log para verificar se há espaço suficiente.
3. Garanta que o processamento de exclusão seja concluído antes de chegarmos à janela de interrupção.

Durante a interrupção

4. Faça um backup completo do banco de dados e coloque-o em um local seguro.
5. Elimine os maiores índices (há cerca de 3 ou 4 que eu vou focar).
6. Coloque o banco de dados no modo de recuperação simples.
7. Reduza os arquivos de log e de banco de dados o máximo possível.
8. Faça backup do banco de dados.
9. Copie o arquivo de backup para o novo servidor.
10. Restaurar o banco de dados para os locais de arquivo corretos
11. Aplique os índices que foram removidos.
12. Reconstrua todos os índices.
13. Atualizar estatísticas.
14. Retorne o banco de dados ao modelo de recuperação completa.
15. Testando e reiniciando aplicativos

Em primeiro lugar, como estou usando SET XACT_ABORT ON. Se ocorrer uma exceção em qualquer uma das instruções " INSERTou UPDATE" dentro do bloco de transação, ele deve encerrar e reverter toda a transação . Ou seja, se houver um erro em qualquer uma das instruções " INSERTe UPDATE" dentro do BEGIN TRAN. Então ele deve fazer o ROLLBACK da transação automaticamente .

Além disso, estou usando o SQL Server 2017. No meu script, inicializei uma variável de tabela DECLARE @cust_debugger TABLE (exec_checker VARCHAR(50));e ela está temporariamente implementada no meu script para depurar meu procedimento armazenado devido à falta de recursos de depuração integrados no meu IDE do SQL Server Management Studio.

Aqui está meu script T-SQL completo:

SET XACT_ABORT ON; -- To automatically rollback the entire transaction if an exception is encountered
                    
BEGIN TRAN;

DECLARE @cust_debugger TABLE (exec_checker VARCHAR(50)); -- custom debugger

SELECT TOP 1
    @d_pay_method_id = pay_method_id
FROM
    [testDB].[dbo].[Payment]
WHERE
    method_name LIKE '%Bank Transfer%';
            
SELECT TOP 1
    @d_stat_ref_id = stat_ref_id
FROM
    [testDB].[dbo].[StatusRef]
WHERE
    com_usd_wrds = 'PENDING';

SET @d_batch_code = 'BGUID' + '-' + CONVERT(VARCHAR,GETDATE(),23)
    + '-' + SUBSTRING(CONVERT(VARCHAR(12),REPLACE(CONVERT(VARCHAR(64), NEWID()), '-', '')), 1, 13);--DATETIME2(4)-&-12-RANDOM-CODE

INSERT INTO -- first batch
    [testDB].[dbo].[Orders]
(
    order_id,
    batch_code,
    quantity,
    cart_id,
    user_id,
    pay_method_id,
    stat_ref_id
)
OUTPUT
    INSERTED.order_id,
    INSERTED.cart_id
INTO
    @order_id_tbl
SELECT
    'ORDER' + '-' + CONVERT(VARCHAR,GETDATE(),23)
        + '-' + SUBSTRING(CONVERT(VARCHAR(12),REPLACE(CONVERT(VARCHAR(64), NEWID()), '-', '')), 1, 13) AS order_id,--DATE-&-12-RANDOM-CODE
    @d_batch_code AS batch_code,
    cart.quantity,
    cart.cart_id,
    @user_id AS user_id,
    @d_pay_method_id AS pay_method_id,
    @d_stat_ref_id AS stat_ref_id
FROM
    [testDB].[dbo].[PinkBasket] AS cart
LEFT OUTER JOIN
    [testDB].[dbo].[StatusRef] AS stat_ref
ON
    cart.stat_ref_id = stat_ref.stat_ref_id
WHERE
    cart.user_id = @user_id
    AND cart.disable = 0
    AND cart.is_selected = 1
    AND cart.is_purchased = 0
    AND cart.is_wishlisted = 0
    AND stat_ref.com_usd_wrds = 'PENDING';

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_fbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('first set');

END

INSERT INTO -- first batch
    [testDB].[dbo].[Payment]
(
    cnb_pay_id,
    pay_ref_no,
    amount_to_pay,
    order_id
)
SELECT
    'PGUID' + '-' + REPLACE(REPLACE(REPLACE(CAST(sysdatetime() AS DATETIME2(4)), ' ', '-'), ':', '-'), '.', '-')
        + '-' + SUBSTRING(CONVERT(VARCHAR(12),REPLACE(CONVERT(VARCHAR(64), NEWID()), '-', '')),1,13), --DATETIME2(4)-&-12-RANDOM-CODE
    @pay_ref_no,
    @amount_to_pay,
    tempData.order_id
FROM
    @order_id_tbl AS tempData;

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_fbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('second set');

END

SELECT TOP 1
    @d_stat_ref_id = stat_ref_id
FROM
    [testDB].[dbo].[StatusRef]
WHERE
    com_usd_wrds = 'ORDERED';

UPDATE -- first batch
    [testDB].[dbo].[PinkBasket]
SET
    stat_ref_id = @d_stat_ref_id,
    is_purchased = 1
WHERE
    cart_id IN
        (
            SELECT
                tempData.cart_id
            FROM
                @order_id_tbl AS tempData
        );

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_fbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('third set');

END

UPDATE
    prod
SET
    prod.stock = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN prod.stock ELSE prod.stock - nested_ref.quantity END,
    prod.availability = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[Product] AS prod
INNER JOIN
(
    SELECT
        cart.prod_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod.prod_id = nested_ref.prod_id
WHERE
    prod.disable = 0
    AND prod.availability = 1
    AND prod.is_draft = 0;

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_sbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('fourth set');

END

UPDATE
    prod_var
SET
    prod_var.stock = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN prod_var.stock ELSE prod_var.stock - nested_ref.quantity END,
    prod_var.availability = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[SubProduct] AS prod_var
INNER JOIN
(
    SELECT
        cart.prod_var_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_var_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod_var.prod_var_id = nested_ref.prod_var_id
WHERE
    prod_var.disable = 0
    AND prod_var.availability = 1
    AND prod_var.is_reserved = 1;

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_sbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('fifth set');

END

IF (@place_order_fbatch_cntr + @place_order_sbatch_cntr) >= 4 --@place_order_fbatch_cntr should be 3, and @place_order_sbatch_cntr should be greater than or equal to 1

BEGIN

    COMMIT TRAN;

    SELECT @successful AS json_data;

END

ELSE

BEGIN

    ROLLBACK TRAN;

    SELECT(SELECT exec_checker FROM @cust_debugger FOR JSON PATH, INCLUDE_NULL_VALUES) AS json_data; -- debugging purposes only

END

Cada vez que executei meu procedimento armazenado, recebi esta saída (no meu serviço de API Express.js):

spOutput = "[{\"exec_checker\":\"first set\"},{\"exec_checker\":\"second set\"},{\"exec_checker\":\"third set\"}]"

Você notará que a variável de tabela @cust_debugger obteve com sucesso as três primeiras seções de IF @@ROWCOUNT > 0validação (ou seja, com uma mistura de duas INSERTe uma UPDATEinstrução).

O problema é: por que as duas últimas seções não são executadas corretamente? Mas, como declarei SET XACT_ABORT ONantes BEGIN TRAN, não há exceção durante o processo de transação.

Entretanto, se eu extrair a parte do quarto e quinto conjuntos e excluir as instâncias de BEGIN TRANe SET XACT_ABORT ON(para fins de teste e investigação), aqui está o seguinte script:

DECLARE @cust_debugger TABLE (exec_checker VARCHAR(50)); -- custom debugger

UPDATE
    prod
SET
    prod.stock = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN prod.stock ELSE prod.stock - nested_ref.quantity END,
    prod.availability = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[Product] AS prod
INNER JOIN
(
    SELECT
        cart.prod_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod.prod_id = nested_ref.prod_id
WHERE
    prod.disable = 0
    AND prod.availability = 1
    AND prod.is_draft = 0;

IF @@ROWCOUNT > 0
BEGIN

    INSERT INTO @cust_debugger VALUES ('fourth set');

END

UPDATE
    prod_var
SET
    prod_var.stock = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN prod_var.stock ELSE prod_var.stock - nested_ref.quantity END,
    prod_var.availability = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[SubProduct] AS prod_var
INNER JOIN
(
    SELECT
        cart.prod_var_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_var_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod_var.prod_var_id = nested_ref.prod_var_id
WHERE
    prod_var.disable = 0
    AND prod_var.availability = 1
    AND prod_var.is_reserved = 1;

IF @@ROWCOUNT > 0
BEGIN

    INSERT INTO @cust_debugger VALUES ('fifth set');

END

SELECT * FROM @cust_debugger

A seguinte saída está sendo gerada:

Minha pergunta é: por que o quarto e o quinto conjuntos funcionam corretamente somente quando executados sem BEGIN TRAN, COMMIT TRAN, e ROLLBACK TRAN?

Se esses dois últimos conjuntos estiverem funcionando corretamente (se eu extrair a parte do quarto e quinto conjuntos e excluir as instâncias de BEGIN TRANe SET XACT_ABORT ON), então ele deverá executar a IF @@ROWCOUNT > 0instrução. (ou seja, a variável também deverá ser incrementada: SET @place_order_sbatch_cntr += 1;).

Por outro lado, descobri que as tabelas Producte SubProducttêm um TRIGGER definido, aqui estão as seguintes funções TRIGGER:

GATILHO do produto:

ALTER TRIGGER [dbo].[ProductReferentialIntegrityInsteadOfDelete]
   ON  [dbo].[Product]
   INSTEAD OF DELETE
AS 
BEGIN

    SET NOCOUNT ON;

    -- Insert statements for trigger here
    IF EXISTS (
        SELECT
            1
        FROM
            testImgDB.dbo.ProductImg prodImg
        JOIN
            deleted d
        ON
            prodImg.prod_id = d.prod_id
    )
    BEGIN
        RAISERROR ('Cannot delete, as related records exist in testImgDB.dbo.ProductImg.', 16, 1);
        ROLLBACK;
    END
    ELSE
    BEGIN
        DELETE FROM
            testDB.dbo.Product
        WHERE
            prod_id IN (SELECT prod_id FROM deleted);
    END
END

GATILHO do subproduto:

ALTER TRIGGER [dbo].[SubProductReferentialIntegrityInsteadOfDelete]
   ON  [dbo].[SubProduct]
   INSTEAD OF DELETE
AS 
BEGIN

    SET NOCOUNT ON;

    -- Insert statements for trigger here
    IF EXISTS (
        SELECT
            1
        FROM
            testImgDB.dbo.SubProductImg prodImg
        JOIN
            deleted d
        ON
            prodImg.prod_var_id = d.prod_var_id
    )
    BEGIN
        RAISERROR ('Cannot delete, as related records exist in testImgDB.dbo.SubProductImg.', 16, 1);
        ROLLBACK;
    END
    ELSE
    BEGIN
        DELETE FROM
            testDB.dbo.SubProduct
        WHERE
            prod_var_id IN (SELECT prod_var_id FROM deleted);
    END
END

Ainda assim, é questionável por que,

se esses dois últimos conjuntos estiverem funcionando corretamente (se eu extrair a parte do quarto e quinto conjuntos e excluir as instâncias de BEGIN TRAN e SET XACT_ABORT ON)

Apesar de desabilitar os dois TRIGGERs.

e após novos testes, o problema permanece.

Aqui está o que tenho pensado para resolver meu caso:

• É recomendável separar o processo de leitura complexa (SELECT) para minhas duas últimas seções? Ou seja, preciso passar os dados do SELECT para uma tabela temporária ou variável de tabela. Então, esses dados armazenados serão eventualmente processados ​​com o UPDATE com menor complexidade devido ao conceito de separação de responsabilidades.

• I want to know how to review the processing of my script where the issue arises. Such as if I could review why the UPDATE process doesn't update, then I could also decide what measures of response I needed to take.

• How to check for Locks / Deadlocks / Blocking, hoping that it might cause the unexpected outcomes.

Here are my assumptions that might be causing the conflicts:

• For the Product and SubProduct tables, there are triggers in each of the two tables. But it is defined with INSTEAD OF DELETE. Could it affect the process? Ahhh, because UPDATE statements are also deleting a record, right? The first thing it does is DELETE, and the second is INSERT. Because that's how UPDATE works under the hood of SQL Server, right? I forgot to look at it; it seems it was because of the TRIGGER.

• According to what I've observed. When you create a TRIGGER. You can use the INSERTED table and the DELETED table (if it's handling an UPDATE statement). When you UPDATE a table, it will delete the record first, then it will be stored in the DELETED table, then the DELETED table will pass the data into the INSERTED table and use the argument that is passed to the UPDATE statement instead. Take note, I'm not sure how SQL Server processes the DELETED table and INSERTED table when the UPDATE triggers.

• Since I am using a test server. Here's the screenshot of the test server specifications:

• Is 8GB RAM and 178 GB of storage insufficient for testing purposes?

Does anyone know why this is happening?