问题[sql-server](dba)

我有一个查询，它既容易受到参数敏感性的影响，又受表值参数的影响。我很懒，只想用查询提示来解决这个问题。当我懒惰的时候，我可以用 来解决参数敏感性问题OPTION (OPTIMIZE FOR UNKNOWN)。这给了我三个非常紧密相关的问题：

1. 对表值变量OPTION (OPTIMIZE FOR UNKNOWN)有影响吗？
2. 对表值参数OPTION (OPTIMIZE FOR UNKNOWN)有影响吗？
3. 除了 之外，还有哪些查询提示OPTION(RECOMPILE)可用于影响表值参数的基数估计？

我自己的实验和文档都表明，这根本没用。表值参数很容易被参数嗅探，但嗅探到的只是基数，所以OPTION (OPTIMIZE FOR UNKNOWN)应该什么也不做。

至于使用其他表提示的问题，我知道我可以更改对表值参数的操作的估计（例如OPTION (USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES')），但我不认为提示可以影响参数本身的基数估计。

对于行存储表的示例...

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

有多种不同的方法可以从 SQL Server 中的元数据中检索表行数 - 例如以下方法

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') 

执行计划显然显示了正在使用的各种不同对象 - 例如下面的。

• sysrowsets OUTER APPLY OpenRowset(TABLE ALUCOUNT
• sysidxstats 交叉应用 OpenRowSet（表分区计数
• sysidxstats i 交叉应用 OpenRowSet(TABLE INDEXPROP

这是怎么回事？SQL Server 真的在多个地方维护这些元数据吗？如果是这样，哪种方法最可靠？

按照SQL Server DAG 的手动故障转移中的步骤操作

要验证两个可用性组是否已准备好进行故障转移，请使用以下在 SQLCMD 模式下运行的 T-SQL 查询：

: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;

如文章中所示，从全局主节点运行上述查询的结果屏幕截图显示了与 DAG 中每个 AG 的连接。

但是，当我在 DAG 的全局主节点上运行查询时，得到的结果仅来自与转发器的连接。因此，显示的两组结果仅来自与 AG02 的连接。

但是，如果我单独运行这两个查询（均来自全局主数据库），它们将根据情况正确连接到 AG01 或 AG02。

请问您有什么建议可以解释我为什么会出现这种行为吗？

谢谢。

根据我在网上以及本网站找到的信息，我尝试了以下所有方法（1-5）来检索 SQL Server 的最后启动日期：

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'

结果是：

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

问题：我一小时前刚启动了机器（以及随之启动的 SQL Server）。我预期的应该是 2025-04-17 22:38……，而不是 2025-04-13 19:09……。

该 SQL 服务器安装在 VMware 盒中。

在另一个 SQL Server 中，相同的版本和版本，我得到了正确的结果：

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

我不明白为什么虚拟机中的结果会出错。我每次工作时都会启动和关闭主机和虚拟机。

我这里遗漏了什么吗？

• SQL Server 开发版 2019

我正在阅读这篇文章 并做了以下事情：

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
);

尽管表本身是堆，但我还是得到了一个索引假脱机。问题是，这是怎么发生的？为什么我会在堆上得到一个索引假脱机？在上面链接中提到的例子中，没有行，所以没有假脱机，但在这里我却看到了？

我的 T-SQL 脚本出了点问题。我正在思考如何让它输出预期的输出。

这是我的 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

这是我使用表变量的最小、可重复的示例：

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

输出：

SELECT * FROM @Product

输出：

SELECT * FROM @SubProduct

输出：

SELECT * FROM @Orders ORDER BY dt_stamp

输出：

SELECT * FROM @Payment

输出：

SELECT * FROM @SubProductVarKeyValue

输出：

SELECT * FROM @SubProductVarKeyNames

输出：

以下是修改后的脚本示例，与提供的第一个脚本类似：

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

不幸的是，当我美化 JSON 输出时，它生成的内容如下：

[
  {
    "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
  }
]

但是，我需要的输出看起来像这样：

[
  {
    "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
  }
]

注意：是在客户端应用中预先计算的。因此，当重复的被分组amount_to_pay时，它应该成为一个实例。batch_code

用户订单可能会导致上面显示的理想 JSON 输出发生变化。

有人熟悉我正在处理的问题吗？

我尽可能地不愿意实现通用表表达式 (CTE) 方法。

这篇经典的并发安全文章显然是为一次只更新一行而设计的。在我的例子中，我有一个表值输入，并且希望以并发安全的方式更新每一行。我知道这并不总是可行的，但我希望尽可能地接近这一点。MERGE这似乎是一个自然的解决方案，但我并不信任它，而且它确实很容易出现 bug。Michael J. Swart 文章中剩下的两种方法是：

1. 事务内部的锁提示（更新更常见）

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. 事务内部的锁提示（插入更常见的）

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 

我可以调整其中任何一个以使用表变量（例如，我怀疑IF (@@ROWCOUNT = 0)需要完全删除表变量），但是使用表值输入是否明显表明我们应该选择第一个或第二个解决方案？如果不是，那么应该基于什么做出决定？

我有点像‘偶然的 DBA’，所以我可能完全偏离了这里。

我们是本地部署，我们正在讨论我们的测试环境。

我们正计划更换托管公司。

我们将把我们的 AG 转移到新的托管公司

如果新托管公司出现问题，我们希望能够以某种方式快速切换回旧的托管公司。

我们有很多时间表，因此无法通过事务日志进行正常复制。

虽然这是一个测试环境，但有很多测试活动，如果新的托管公司失败，我们不想失去这些活动。（我们可以备份和恢复，但我正在寻找更快的解决方案）

日志传输可以解决这个问题吗？或者您建议其他方法吗？

问候

介绍

我读过很多关于从数据库中删除数据以及使用 shrink 命令的风险的文章。但是，我有机会将数据库迁移到一台停机时间较长的新服务器，因此我打算重置数据库并缩减其大小（以提高性能并节省托管环境中的成本）。

背景

• 现有的SQL Server是2016Web版。
• 新的 SQL Server 2022 标准版。
• 数据库大小~120GB。
• 一些大表以及这些大表上的索引构成了其中的大部分。
• 我只想专注于数据库大小的缩减。对于创建新服务器的其他部分，我感觉还算满意。

拟议流程

停电前

1. 运行使用语法的存储过程DELETE FROM。我已经仔细测试过了。它们会批量删除，并且会在几天内运行。
2. 监视日志文件和日志磁盘以仔细检查是否有足够的空间。
3. 确保在到达停机时间时删除处理已经完成。

停电期间

4. 对数据库进行完整备份并放在安全的地方。
5. 删除最大的索引（我将针对大约 3 或 4 个）。
6. 将数据库置于简单恢复模式。
7. 尽可能缩小日志和数据库文件。
8. 备份数据库。
9. 将备份文件复制到新服务器。
10. 将数据库还原到正确的文件位置
11. 应用已删除的索引。
12. 重建所有索引。
13. 更新统计数据。
14. 将数据库恢复到完整恢复模式。
15. 测试和重启应用程序

首先，由于我正在使用。如果事务块中SET XACT_ABORT ON的任何INSERT或语句发生异常，则它应该终止并回滚整个事务。这意味着，如果中的任何和语句出现错误。那么它应该自动回滚该事务。UPDATEINSERTUPDATEBEGIN TRAN

此外，我正在使用 SQL Server 2017。在我的脚本中，我初始化了一个表变量DECLARE @cust_debugger TABLE (exec_checker VARCHAR(50));，并且由于我的 SQL Server Management Studio IDE 中缺少内置调试功能，因此它在我的脚本中临时实现以调试我的存储过程。

这是我的完整 T-SQL 脚本：

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

每次运行存储过程时，我都会收到此输出（在我的 Express.js API 服务中）：

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

您会注意到表变量@cust_debugger 成功获得了前三个IF @@ROWCOUNT > 0验证部分（即混合了两个INSERT和一条UPDATE语句）。

问题是，为什么最后两段代码执行不正常？但是，由于我SET XACT_ABORT ON之前声明了BEGIN TRAN，所以交易过程中不会出现任何异常。

但是，如果我提取第四组和第五组的部分并排除BEGIN TRAN和的实例SET XACT_ABORT ON（出于测试和调查目的），则脚本如下：

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

正在生成以下输出：

BEGIN TRAN我的问题是，为什么第四组和第五组只有在没有、COMMIT TRAN和的情况下执行才能正确运行ROLLBACK TRAN？

如果最后两组工作正常（如果我提取第四组和第五组的部分并排除BEGIN TRAN和的实例SET XACT_ABORT ON），那么它应该执行该IF @@ROWCOUNT > 0语句。（即，变量也应该增加：）SET @place_order_sbatch_cntr += 1;。

另一方面，我发现Product和SubProduct表定义了TRIGGER，下面是以下TRIGGER函数：

产品触发：

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

子产品触发器：

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

但令人怀疑的是，为什么

如果最后两组工作正常（如果我提取第四组和第五组的部分并排除BEGIN TRAN 和的实例SET XACT_ABORT ON）

尽管禁用了两个触发器。

并重新测试，问题仍然存在。

以下是我为解决我的问题所考虑的内容：

• 建议将复杂的读取（SELECT）过程分离到最后两部分吗？我的意思是，我需要将 SELECT 数据传入临时表或表变量。这样，由于关注点分离的概念，存储的数据最终将通过复杂度较低的 UPDATE 操作进行处理。

• 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?