sql: SQL Query Anti-Patterns and How to Avoid Them using sql server 2025
/* =====================================================================================
SQL Server 2025 · SQL 查询反模式与修复（SQL Query Anti-Patterns and How to Avoid Them）
场景：国际化珠宝行业 ERP / CRM / HR 系统，万亿级数据、高并发
实例：localhost\SQL2025 · SQL Server 2025 RTM-GDR 17.0.1135.8 Enterprise（兼容级别 170）
说明：本脚本全部结论均为**本机实测**，可重复执行（幂等）。每一处「常见说法」若与实测不符，
一律以实测为准并明确标注。所有示例对象统一使用 AQ_ 前缀（Anti-pattern Query）。
=====================================================================================
┌───────────────────────────────────────────────────────────────────────────────────────┐
│ 反模式速查表（本脚本逐一实测，括号内为「反模式 / 修正」的实测倍率） │
├────┬──────────────────────────────────┬─────────────────────────────────────────────┤
│ 编号│ 反模式 │ 修正手段 │
├────┼──────────────────────────────────┼─────────────────────────────────────────────┤
│ K1 │ SELECT * 打穿覆盖索引（退化为全表扫描 / 键查找风暴） │ 显式列出所需列 │
│ K2 │ SELECT * / INSERT…SELECT * │ 显式列名（列数变化时报 121） │
│ P1 │ char/varchar 列 × Unicode 字面量 │ 字面量去 N 前缀 │
│ P2 │ char/varchar 列 × nvarchar 参数 │ 参数类型与列类型一致 │
│ P3 │ 数值参数查字符串键列 │ 参数用列的真实类型（否则 8114） │
│ S1 │ 列上套 YEAR()/MONTH() │ 半开区间 [起, 止) │
│ S2 │ 列上套 CAST/DATEDIFF │ 半开区间 │
│ S3 │ 列上套 UPPER() │ 原值等值 + 规范化写入 │
│ S4 │ 列上套 LEFT()/函数 │ 前缀 LIKE 'x%' │
│ S5 │ LIKE '%…%' 前导通配 │ 前缀 LIKE / 全文检索 │
│ S6 │ 列侧算术 col*1.05 > v │ 常数侧 v/1.05 > col │
│ S7 │ 列拼接 col1+col2 = v │ 拆成两个等值谓词 │
│ S8 │ ISNULL(col,'x') = v │ 原值等值（仅可空列有害） │
│ A1 │ 过度范式化（EAV 多自连接） │ 宽表 / 覆盖索引 / 物化 │
│ E1 │ 用 DISTINCT 掩盖 JOIN 扇出 │ EXISTS 半连接 │
│ U1 │ 谓词里调用标量 UDF │ 内联表达式 / 计算列 + 索引 │
│ U2 │ 逐行 INSERT（RBAR/游标） │ 集合式 INSERT…SELECT │
│ N1 │ UNION 隐式去重 │ 语义允许时 UNION ALL │
│ G1 │ OFFSET 深分页 │ 键集分页（WHERE key > 上页末值） │
│ L1 │ 单条大 UPDATE 触发锁升级 │ 分批 + 小事务 │
└────┴──────────────────────────────────┴─────────────────────────────────────────────┘
┌───────────────────────────────────────────────────────────────────────────────────────┐
│ ★ 旗舰实测结论（全部本机复现，详细数据见各章节） │
├───────────────────────────────────────────────────────────────────────────────────────┤
│ 1. 隐式转换的真实机理不是「变全表扫描」，而是「查找谓词被截断」： │
│ char(2)/varchar 列遇到 N'…' 或 nvarchar 参数时，优化器用 GetRangeThroughConvert │
│ 生成一个「范围查找」，查找谓词里**只剩被转换的那一列**，它后面的键列被踢到残余谓词。 │
│ · IX(CountryCode, OrderDate) → 905 读 / 29 读 = 31.2×（OrderDate 被降级为残余谓词） │
│ · IX(Status, OrderDate) → 423 读 / 16 读 = 26.4× │
│ · 单列唯一索引 OrderNo → 3 读 / 3 读 = 1.00×（**零损失**，因为无后续键列） │
│ · 可编程判据：查找谓词里没有 ScanType="EQ"、且计划里出现 <Predicate> 残余谓词 │
│ · ★ 这些场景 SQL Server **不发 PlanAffectingConvert 警告**，靠警告检测会全部漏掉 │
│ · 反例：bigint 列 × nvarchar 参数 = 3 读（参数被转换，方向安全，有害 0） │
│ · 反例：varchar 键列 × bigint 参数 = **错误 8114**（列被转 bigint，直接报错） │
│ 2. 非 SARGable 的效果差异极大，必须逐条实测： │
│ UPPER(Status) 305.8× · LEFT(OrderNo,8) 450.3× · LIKE '%…%' 514.6×（CPU 2100× 以上） │
│ DATEDIFF 归零 226.3× · YEAR/MONTH 31.2× · 列侧算术 40.2× │
│ **无差异**：ISNULL(不可空列) 1.00× · CAST(decimal AS int) 1.00× · CAST(dt AS date) │
│ 1.50× —— 这三种写法已被 SQL Server 2025 优化器自动改写，不必再当反模式 │
│ 3. 列存（CCI，500 万行）上非 SARGable 的表现完全不同： │
│ 逻辑读 843 = 843（1.00×），但 **CPU 可达 96×～134×**（随计划缓存与统计浮动） │
│ → 列存失去的是「段消除 + 批处理」，不是逻辑读。用逻辑读评估列存会得出错误结论。 │
│ 4. 锁升级是万亿级高并发的头号杀手（本机实测）： │
│ 单条 UPDATE 28,272 行 → 升级为 **OBJECT X 表级排他锁**，且细粒度行锁归零 → 另一会话 │
│ 更新**毫不相关的一行**被阻塞并报 1222；而 RCSI 快照读不受影响（读没事、写全死） │
│ 分批（同一事务）28,272 行 → 28,272 个 KEY X 行锁，**无 OBJECT X，不升级** │
│ 分批 + 每批独立提交 → 单批行锁峰值 744；代价是 200 次提交的额外开销 │
│ （本机为本地快速存储，纯作业耗时仅由约 0.5s 增至约 0.6s；存储延迟越高，这个代价越大） │
│ 5. 深分页 OFFSET 500000 → 约 6,200 读 / 键集分页 3 读 ≈ **2,100×** │
│ 6. RBAR（WHILE 逐行 5000 次 INSERT）→ 约 10,100 读 / 约 150 读 ≈ 65～69× │
│ 7. EAV 过度范式化的代价取决于查询形态： │
│ 多属性**过滤**（COUNT）→ EAV 131 读 vs 宽表 4 读 = 32.8×（EAV 差） │
│ 取回 6 个属性 → EAV 1,631 读 vs 宽表 347 读 = 4.70×，CPU 可达 30× │
│ 8. 与流行说法相反的三条（诚实记录）： │
│ · INSERT…SELECT * 在加列后报 **121**（不是 213）；存储过程里的 SELECT * 会**自动** │
│ 跟上新列（不是创建时快照）；只有**视图**里的 SELECT * 才是快照（需 sp_refreshview）│
│ · COUNT(*)>0 vs EXISTS：本机 4 读 vs 4 读，**已被优化器自动改写**，收益为 0 │
│ · 「JOIN 后 DISTINCT」在 SQL Server 2025 上**已被优化器自动改写为半连接**：改写成 EXISTS │
│ 后逻辑读/CPU 完全相同（1.00×）→ 它的价值回到「可读性与不依赖优化器」，而非性能 │
└───────────────────────────────────────────────────────────────────────────────────────┘
★ 表中倍率随计划缓存/统计浮动属正常现象；**权威口径是第 12.1 节「本次运行的实测表」**。
===================================================================================== */
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
GO
PRINT '################################################################';
PRINT '### 00 环境与反模式勘察 ###';
PRINT '################################################################';
PRINT '';
PRINT '-- 00.1 实例与数据库 --';
SELECT 版本 = LEFT(@@VERSION, 90);
SELECT 数据库 = DB_NAME(), 兼容级别 = compatibility_level, RCSI已开启 = is_read_committed_snapshot_on,
快照隔离 = snapshot_isolation_state_desc
FROM sys.databases WHERE name = DB_NAME();
SELECT 版本号 = SERVERPROPERTY('ProductVersion'), 版次 = SERVERPROPERTY('Edition'),
产品级别 = SERVERPROPERTY('ProductLevel');
PRINT '-- 00.2 资源与并行配置（判断「万亿级高并发」可复现性） --';
SELECT CPU核数 = cpu_count, 物理内存MB = physical_memory_kb/1024 FROM sys.dm_os_sys_info;
SELECT 配置项 = name, 生效值 = value_in_use FROM sys.configurations
WHERE name IN ('cost threshold for parallelism','max degree of parallelism','max server memory (MB)')
ORDER BY name;
PRINT '-- 00.3 数据库级选项（优化器自动改写开关） --';
SELECT 选项 = name, 值 = value FROM sys.database_scoped_configurations
WHERE name IN ('OPTIMIZED_LOCKING','PARAMETER_SNIFFING','LEGACY_CARDINALITY_ESTIMATION',
'LAST_QUERY_PLAN_STATS','ROW_MODE_MEMORY_GRANT_FEEDBACK')
ORDER BY name;
PRINT '-- 00.4 源表基数（演示数据的真实规模） --';
SELECT 表 = s.name + '.' + t.name, 行数 = SUM(p.rows)
FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0,1)
WHERE (s.name + '.' + t.name) IN ('erp.SalesOrder','erp.SalesOrderLine','erp.Product','erp.Store',
'crm.Customer','crm.Interaction','hr.Employee','hr.OrgUnit','hr.Attendance','dbo.Country')
GROUP BY s.name + '.' + t.name ORDER BY 2 DESC;
SELECT 列存事实表 = 'rpt.OrgSalesFact', 真实行数 = SUM(total_rows), 行组数 = COUNT(*)
FROM sys.dm_db_column_store_row_group_physical_stats WHERE object_id = OBJECT_ID('rpt.OrgSalesFact');
PRINT ' （提示：列存行数请用行组 DMV；sys.dm_db_partition_stats 对本列存表只报 1,000，不可用。）';
PRINT '-- 00.5 源表的「反模式友好度」清点：哪些列一碰隐式转换就出事 --';
SELECT 表 = s.name + '.' + t.name, 列 = c.name, 类型 = ty.name + '(' +
CASE WHEN ty.name IN ('nvarchar','nchar') THEN CAST(c.max_length/2 AS varchar(6))
ELSE CAST(c.max_length AS varchar(6)) END + ')',
是否Unicode = CASE WHEN ty.name LIKE N'n%' THEN N'是' ELSE N'否 ★传 N''…'' 或 nvarchar 参数即触发转换' END
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE (s.name + '.' + t.name) IN ('erp.SalesOrder','erp.SalesOrderLine','erp.Product','crm.Customer','crm.Interaction')
AND ty.name IN ('char','varchar','nchar','nvarchar') AND c.max_length <= 40
ORDER BY 1, 2;
GO
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
GO
PRINT '################################################################';
PRINT '### 00-B 清理上一次运行的残留对象（保证可重复执行） ###';
PRINT '################################################################';
SET NOCOUNT ON;
DECLARE @s nvarchar(max) = N'', @n sysname;
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM sys.procedures WHERE name LIKE N'AQ[_]%';
OPEN c FETCH NEXT FROM c INTO @n;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @s = @s + N'DROP PROCEDURE dbo.' + QUOTENAME(@n) + N';' + CHAR(10);
FETCH NEXT FROM c INTO @n;
END
CLOSE c DEALLOCATE c;
IF @s <> N'' BEGIN EXEC sp_executesql @s; PRINT ' 已清理 AQ_ 前缀存储过程'; END
ELSE PRINT ' 无残留存储过程';
IF OBJECT_ID('dbo.AQ_ResultCheck','U') IS NOT NULL
BEGIN
DROP TABLE dbo.AQ_ResultCheck;
PRINT ' 已重建度量结果表 dbo.AQ_ResultCheck（保证多次运行不留旧数据、不违反主键）';
END
ELSE PRINT ' 度量结果表尚不存在（首次运行）';
IF OBJECT_ID('dbo.AQ_LockLog','U') IS NOT NULL
BEGIN
DROP TABLE dbo.AQ_LockLog;
PRINT ' 已删除锁升级实测记录表 dbo.AQ_LockLog（第 08 节会重建）';
END
PRINT ' 说明：实验台数据表保留（重建 100 万行代价高），采用「不存在才建」的幂等策略。';
GO
PRINT '################################################################';
PRINT '### 00-C 实验台：刻意沿用生产中的 varchar/char 类型 ###';
PRINT '################################################################';
SET NOCOUNT ON;
PRINT '-- C1 dbo.AQ_Order（100 万行订单，类型与生产一致：OrderNo varchar(20)、CountryCode char(2)、';
PRINT ' Status varchar(10)、OrderDate datetime2(0)） --';
IF OBJECT_ID('dbo.AQ_Order','U') IS NULL
BEGIN
CREATE TABLE dbo.AQ_Order(
OrderId bigint NOT NULL CONSTRAINT PK_AQ_Order PRIMARY KEY CLUSTERED,
OrderNo varchar(20) NOT NULL,
CountryCode char(2) NOT NULL,
StoreCode varchar(8) NOT NULL,
Status varchar(10) NOT NULL,
OrderDate datetime2(0) NOT NULL,
CustomerId int NULL,
SalesEmployeeId int NOT NULL,
CurrencyCode char(3) NOT NULL,
NetAmount decimal(18,2) NOT NULL,
NetAmountUsd decimal(18,2) NOT NULL,
Note nvarchar(120) NULL);
INSERT dbo.AQ_Order WITH (TABLOCK)
(OrderId,OrderNo,CountryCode,StoreCode,Status,OrderDate,CustomerId,SalesEmployeeId,CurrencyCode,NetAmount,NetAmountUsd,Note)
SELECT g.value,
'SO-' + RIGHT('00000000' + CAST(g.value AS varchar(10)), 8),
CASE g.value % 7 WHEN 0 THEN 'CN' WHEN 1 THEN 'HK' WHEN 2 THEN 'US' WHEN 3 THEN 'JP'
WHEN 4 THEN 'SG' WHEN 5 THEN 'FR' ELSE 'AE' END,
'ST' + RIGHT('00' + CAST((g.value % 40) + 1 AS varchar(4)), 2),
CASE WHEN g.value % 100 < 55 THEN 'Paid' WHEN g.value % 100 < 80 THEN 'Shipped'
WHEN g.value % 100 < 92 THEN 'Returned' ELSE 'Cancelled' END,
DATEADD(hour, g.value % 26304, CAST('2024-01-01T00:00:00' AS datetime2(0))),
CASE WHEN g.value % 131 = 0 THEN NULL ELSE (g.value % 800) + 1 END,
(g.value % 36) + 1,
CASE g.value % 7 WHEN 0 THEN 'CNY' WHEN 1 THEN 'HKD' WHEN 2 THEN 'USD' WHEN 3 THEN 'JPY'
WHEN 4 THEN 'SGD' WHEN 5 THEN 'EUR' ELSE 'AED' END,
CAST((g.value % 9000) + 100 AS decimal(18,2)) + 0.55,
CAST(((g.value % 9000) + 100) * 1.05 AS decimal(18,2)),
CASE WHEN g.value % 17 = 0 THEN N'礼盒包装 + 刻字服务' ELSE NULL END
FROM GENERATE_SERIES(1,1000000) AS g;
CREATE UNIQUE NONCLUSTERED INDEX UQ_AQ_Order_No ON dbo.AQ_Order(OrderNo);
CREATE NONCLUSTERED INDEX IX_AQ_Country_Date ON dbo.AQ_Order(CountryCode, OrderDate)
INCLUDE(NetAmountUsd, Status, StoreCode, SalesEmployeeId);
CREATE NONCLUSTERED INDEX IX_AQ_Status_Date ON dbo.AQ_Order(Status, OrderDate) INCLUDE(NetAmountUsd);
CREATE NONCLUSTERED INDEX IX_AQ_NetAmount ON dbo.AQ_Order(NetAmountUsd);
PRINT ' 已创建 AQ_Order + 4 个索引';
END ELSE PRINT ' AQ_Order 已存在，跳过';
PRINT '-- C2 dbo.AQ_OrderLine（200 万行明细） --';
IF OBJECT_ID('dbo.AQ_OrderLine','U') IS NULL
BEGIN
CREATE TABLE dbo.AQ_OrderLine(
LineId bigint NOT NULL CONSTRAINT PK_AQ_OrderLine PRIMARY KEY CLUSTERED,
OrderId bigint NOT NULL,
ProductId int NOT NULL,
Quantity int NOT NULL,
UnitPrice decimal(18,2) NOT NULL,
DiscountPct decimal(5,2) NOT NULL,
LineAmount decimal(18,2) NOT NULL);
INSERT dbo.AQ_OrderLine WITH (TABLOCK) (LineId,OrderId,ProductId,Quantity,UnitPrice,DiscountPct,LineAmount)
SELECT g.value, ((g.value - 1) % 1000000) + 1, (g.value % 100000) + 1, (g.value % 5) + 1,
CAST((g.value % 5000) + 50 AS decimal(18,2)) + 0.25,
CAST((g.value % 21) AS decimal(5,2)),
CAST(((g.value % 5000) + 50) * ((g.value % 5) + 1) * (1 - (g.value % 21) / 100.0) AS decimal(18,2))
FROM GENERATE_SERIES(1,2000000) AS g;
CREATE NONCLUSTERED INDEX IX_AQLine_Order ON dbo.AQ_OrderLine(OrderId) INCLUDE(ProductId, Quantity, LineAmount);
PRINT ' 已创建 AQ_OrderLine（每张订单平均 2 行 → 天然扇出）';
END ELSE PRINT ' AQ_OrderLine 已存在，跳过';
PRINT '-- C3 dbo.AQ_DateLab（30 万行，date 类型列 + 索引，用于日期类型方向实验） --';
IF OBJECT_ID('dbo.AQ_DateLab','U') IS NULL
BEGIN
CREATE TABLE dbo.AQ_DateLab(
Id int NOT NULL CONSTRAINT PK_AQ_DateLab PRIMARY KEY CLUSTERED,
D date NOT NULL,
Amt decimal(18,2) NOT NULL);
INSERT dbo.AQ_DateLab WITH (TABLOCK)(Id, D, Amt)
SELECT g.value, DATEADD(day, g.value % 1096, CAST('2024-01-01' AS date)),
CAST((g.value % 9000) + 100 AS decimal(18,2)) + 0.25
FROM GENERATE_SERIES(1,300000) AS g;
CREATE NONCLUSTERED INDEX IX_AQDateLab_D ON dbo.AQ_DateLab(D) INCLUDE(Amt);
PRINT ' 已创建 AQ_DateLab';
END ELSE PRINT ' AQ_DateLab 已存在，跳过';
PRINT '-- C4 过度范式化实验台：100,000 产品 × 6 属性 = 600,000 行 EAV + 等宽表对照 --';
IF OBJECT_ID('dbo.AQ_ProductFlat','U') IS NULL
BEGIN
CREATE TABLE dbo.AQ_Attribute(AttributeId int NOT NULL PRIMARY KEY, AttrName nvarchar(30) NOT NULL);
INSERT dbo.AQ_Attribute VALUES (1,N'颜色'),(2,N'净度'),(3,N'克拉区间'),(4,N'证书'),(5,N'产地'),(6,N'表面处理');
CREATE TABLE dbo.AQ_ProductFlat(
ProductId int NOT NULL CONSTRAINT PK_AQ_ProductFlat PRIMARY KEY CLUSTERED,
颜色 varchar(12) NOT NULL, 净度 varchar(12) NOT NULL, 克拉区间 varchar(12) NOT NULL,
证书 varchar(12) NOT NULL, 产地 varchar(20) NOT NULL, 表面处理 varchar(20) NOT NULL);
CREATE TABLE dbo.AQ_ProductAttribute(
ProductId int NOT NULL,
AttributeId int NOT NULL,
Value varchar(20) NOT NULL,
CONSTRAINT PK_AQ_ProductAttribute PRIMARY KEY CLUSTERED(ProductId, AttributeId));
INSERT dbo.AQ_ProductFlat WITH (TABLOCK)(ProductId,颜色,净度,克拉区间,证书,产地,表面处理)
SELECT g.value,
CHOOSE((g.value % 4)+1,'D','E','F','G'),
CHOOSE((g.value % 4)+1,'IF','VS1','VS2','SI1'),
CHOOSE((g.value % 3)+1,'0.5-0.9','1.0-1.5','2.0+'),
CHOOSE((g.value % 3)+1,'GIA','IGI','HRD'),
CHOOSE((g.value % 3)+1,'南非','缅甸','澳洲'),
CHOOSE((g.value % 3)+1,'抛光','磨砂','镀铑')
FROM GENERATE_SERIES(1,100000) AS g;
INSERT dbo.AQ_ProductAttribute WITH (TABLOCK)(ProductId, AttributeId, Value)
SELECT g.value, x.AttributeId, x.Value FROM GENERATE_SERIES(1,100000) AS g
CROSS APPLY (VALUES
(1, CHOOSE((g.value % 4)+1,'D','E','F','G')),
(2, CHOOSE((g.value % 4)+1,'IF','VS1','VS2','SI1')),
(3, CHOOSE((g.value % 3)+1,'0.5-0.9','1.0-1.5','2.0+')),
(4, CHOOSE((g.value % 3)+1,'GIA','IGI','HRD')),
(5, CHOOSE((g.value % 3)+1,'南非','缅甸','澳洲')),
(6, CHOOSE((g.value % 3)+1,'抛光','磨砂','镀铑'))) AS x(AttributeId, Value);
CREATE NONCLUSTERED INDEX IX_AQPA_Attr_Val_Prod ON dbo.AQ_ProductAttribute(AttributeId, Value, ProductId);
CREATE NONCLUSTERED INDEX IX_AQPF_Color_Clarity ON dbo.AQ_ProductFlat(颜色, 净度) INCLUDE(ProductId, 克拉区间);
PRINT ' 已创建 AQ_Attribute / AQ_ProductFlat(100k) / AQ_ProductAttribute(600k)';
END ELSE PRINT ' EAV 实验台已存在，跳过';
PRINT '-- C5 交付物体积复核（行数只统计堆/聚集索引，避免把非聚集索引的行数重复计入） --';
SELECT 对象 = OBJECT_NAME(ps.object_id),
行数 = SUM(CASE WHEN ps.index_id IN (0,1) THEN ps.row_count ELSE 0 END),
全部索引占用MB = SUM(ps.used_page_count)*8/1024
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id IN (OBJECT_ID('dbo.AQ_Order'),OBJECT_ID('dbo.AQ_OrderLine'),OBJECT_ID('dbo.AQ_DateLab'),
OBJECT_ID('dbo.AQ_ProductFlat'),OBJECT_ID('dbo.AQ_ProductAttribute'))
GROUP BY ps.object_id ORDER BY 3 DESC;
GO
PRINT '-- C6 两个标量 UDF：一个可被 FROID 内联，一个因访问表而不可内联 --';
GO
CREATE OR ALTER FUNCTION dbo.AQ_ScoreFast(@amt decimal(18,2)) RETURNS decimal(18,6)
WITH SCHEMABINDING AS BEGIN RETURN @amt * 0.0001 + 0.000001; END
GO
CREATE OR ALTER FUNCTION dbo.AQ_ScoreSlow(@amt decimal(18,2)) RETURNS decimal(18,6)
AS BEGIN
RETURN (SELECT CASE WHEN c.FxToUsd > 0 THEN c.FxToUsd ELSE 0 END
FROM dbo.Country c WHERE c.CountryCode = 'US') * 0.0 + @amt * 0.0001;
END
GO
PRINT ' 已创建 dbo.AQ_ScoreFast（可内联）与 dbo.AQ_ScoreSlow（不可内联）';
PRINT '-- C7 可编程度量框架：AQ_Run1（执行）/ AQ_M1（取指标与计划特征）/ AQ_Cmp（对照） --';
PRINT ' 设计要点：以「存储过程 + sys.dm_exec_procedure_stats」取指标。';
PRINT ' 不用 sys.dm_exec_query_stats 的原因：平凡计划（trivial plan）不会留下可稳定命中的条目。';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_Run1 @proc sysname
AS BEGIN SET NOCOUNT ON; DECLARE @s nvarchar(300) = N'EXEC dbo.' + QUOTENAME(@proc); EXEC sp_executesql @s; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_M1
@proc sysname, @reads bigint OUTPUT, @cpu_us bigint OUTPUT, @el_ms bigint OUTPUT,
@imp int OUTPUT, @eq int OUTPUT, @resid bit OUTPUT, @ops nvarchar(700) OUTPUT, @warn nvarchar(400) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET @reads=NULL; SET @cpu_us=NULL; SET @el_ms=NULL; SET @imp=0; SET @eq=0; SET @resid=0; SET @ops=N''; SET @warn=N'';
DECLARE @oid int = OBJECT_ID(N'dbo.' + @proc), @ph varbinary(64);
SELECT @ph=ps.plan_handle, @reads=ps.last_logical_reads, @cpu_us=ps.last_worker_time, @el_ms=ps.last_elapsed_time/1000
FROM sys.dm_exec_procedure_stats ps WHERE ps.object_id=@oid AND ps.database_id=DB_ID();
IF @ph IS NULL RETURN;
DECLARE @plan nvarchar(max) = (SELECT CONVERT(nvarchar(max), qp.query_plan)
FROM sys.dm_exec_query_plan(@ph) qp);
IF @plan IS NULL RETURN;
SET @imp = (LEN(@plan)-LEN(REPLACE(@plan,N'CONVERT_IMPLICIT',N''))) / LEN(N'CONVERT_IMPLICIT');
SET @eq = (LEN(@plan)-LEN(REPLACE(@plan,N'ScanType="EQ"',N''))) / 13;
IF @plan LIKE N'%<Predicate>%' SET @resid = 1;
IF @plan LIKE N'%PhysicalOp="Clustered Index Scan"%' SET @ops += N'[聚集索引扫描]';
IF @plan LIKE N'%PhysicalOp="Table Scan"%' SET @ops += N'[表扫描]';
IF @plan LIKE N'%PhysicalOp="Index Scan"%' SET @ops += N'[索引扫描]';
IF @plan LIKE N'%PhysicalOp="Columnstore Index Scan"%' SET @ops += N'[列存扫描]';
IF @plan LIKE N'%PhysicalOp="Clustered Index Seek"%' SET @ops += N'[聚集索引查找]';
IF @plan LIKE N'%PhysicalOp="Index Seek"%' SET @ops += N'[索引查找]';
IF @plan LIKE N'%Lookup="1"%' SET @ops += N'[键查找]';
IF @plan LIKE N'%PhysicalOp="Sort"%' SET @ops += N'[排序]';
IF @plan LIKE N'%PhysicalOp="Stream Aggregate"%' SET @ops += N'[流聚合]';
IF @plan LIKE N'%PhysicalOp="Hash Match"%' SET @ops += N'[哈希匹配]';
IF @plan LIKE N'%PhysicalOp="Nested Loops"%' SET @ops += N'[嵌套循环]';
IF @plan LIKE N'%PhysicalOp="Parallelism"%' SET @ops += N'[并行]';
IF @plan LIKE N'%PhysicalOp="Table Spool"%' SET @ops += N'[表假脱机]';
IF @plan LIKE N'%PhysicalOp="Filter"%' SET @ops += N'[筛选]';
IF @plan LIKE N'%PhysicalOp="Compute Scalar"%' SET @ops += N'[计算标量]';
IF @plan LIKE N'%PhysicalOp="Top"%' SET @ops += N'[Top]';
IF @plan LIKE N'%PlanAffectingConvert%' BEGIN
SET @ops += N'[★计划受影响转换]';
SET @warn = ISNULL(SUBSTRING(@plan, CHARINDEX(N'PlanAffectingConvert', @plan), 220), N'');
END
END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_Cmp @bad sysname, @good sysname, @label nvarchar(200), @ck nvarchar(120) = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @br bigint,@bc bigint,@be bigint,@bi int,@bq int,@bz bit,@bo nvarchar(700),@bw nvarchar(400),@bErr int=0,@bMsg nvarchar(220)=N'';
DECLARE @gr bigint,@gc bigint,@ge bigint,@gi int,@gq int,@gz bit,@go nvarchar(700),@gw nvarchar(400),@gErr int=0,@gMsg nvarchar(220)=N'';
BEGIN TRY EXEC dbo.AQ_Run1 @bad; EXEC dbo.AQ_Run1 @bad; END TRY
BEGIN CATCH SET @bErr=ERROR_NUMBER(); SET @bMsg=LEFT(ERROR_MESSAGE(),200); END CATCH
EXEC dbo.AQ_M1 @bad, @br OUT,@bc OUT,@be OUT,@bi OUT,@bq OUT,@bz OUT,@bo OUT,@bw OUT;
BEGIN TRY EXEC dbo.AQ_Run1 @good; EXEC dbo.AQ_Run1 @good; END TRY
BEGIN CATCH SET @gErr=ERROR_NUMBER(); SET @gMsg=LEFT(ERROR_MESSAGE(),200); END CATCH
EXEC dbo.AQ_M1 @good, @gr OUT,@gc OUT,@ge OUT,@gi OUT,@gq OUT,@gz OUT,@go OUT,@gw OUT;
PRINT N' ┌ ' + @label;
PRINT N' │ 反模式 ' + LEFT(@bad+SPACE(24),24)
+ N' 逻辑读=' + LEFT(ISNULL(CAST(@br AS varchar(20)),N'失败')+SPACE(8),8)
+ N' CPU(µs)=' + LEFT(ISNULL(CAST(@bc AS varchar(20)),N'—')+SPACE(9),9)
+ N' 隐式转换=' + CAST(@bi AS varchar(3)) + N' EQ前缀=' + CAST(@bq AS varchar(3)) + N' 残余谓词=' + CAST(@bz AS varchar(1));
PRINT N' │ 计划 ' + ISNULL(@bo, N'(无)');
IF @bErr <> 0 PRINT N' │ ★错误 ' + CAST(@bErr AS varchar(8)) + N' : ' + @bMsg;
IF @bw <> N'' PRINT N' │ ★警告 ' + LEFT(@bw, 170);
PRINT N' ├ 修正 ' + LEFT(@good+SPACE(24),24)
+ N' 逻辑读=' + LEFT(ISNULL(CAST(@gr AS varchar(20)),N'失败')+SPACE(8),8)
+ N' CPU(µs)=' + LEFT(ISNULL(CAST(@gc AS varchar(20)),N'—')+SPACE(9),9)
+ N' 隐式转换=' + CAST(@gi AS varchar(3)) + N' EQ前缀=' + CAST(@gq AS varchar(3)) + N' 残余谓词=' + CAST(@gz AS varchar(1));
PRINT N' │ 计划 ' + ISNULL(@go, N'(无)');
IF @gErr <> 0 PRINT N' │ ★错误 ' + CAST(@gErr AS varchar(8)) + N' : ' + @gMsg;
PRINT N' └ 读取 ' + CASE WHEN ISNULL(@gr,0) > 0 AND @br IS NOT NULL
THEN CAST(CONVERT(numeric(12,2), CONVERT(float,@br)/NULLIF(CONVERT(float,@gr),0)) AS varchar(20)) + N'×' ELSE N'—' END
+ N' | CPU ' + CASE WHEN ISNULL(@gc,0) > 0 AND @bc IS NOT NULL
THEN CAST(CONVERT(numeric(12,2), CONVERT(float,@bc)/NULLIF(CONVERT(float,@gc),0)) AS varchar(20)) + N'×' ELSE N'—' END
+ N' | 耗时 ' + ISNULL(CAST(@be AS varchar(15)),N'—') + N'ms vs ' + ISNULL(CAST(@ge AS varchar(15)),N'—') + N'ms';
IF @ck IS NOT NULL
BEGIN TRY
IF NOT EXISTS(SELECT 1 FROM dbo.AQ_ResultCheck WHERE 检查项 = @ck)
INSERT dbo.AQ_ResultCheck(检查项, 反模式读取, 修正读取, 读取倍数, CPU倍数, 反模式报错, 是否通过)
VALUES (@ck,
CASE WHEN @bErr <> 0 THEN N'★报错 ' + CAST(@bErr AS nvarchar(10))
ELSE ISNULL(CAST(@br AS nvarchar(24)), N'失败') END,
ISNULL(CAST(@gr AS nvarchar(24)), N'失败'),
CASE WHEN @br IS NOT NULL AND ISNULL(@gr,0) > 0
THEN CONVERT(numeric(12,2), CONVERT(float,@br)/CONVERT(float,@gr)) END,
CASE WHEN @bc IS NOT NULL AND ISNULL(@gc,0) > 0
THEN CONVERT(numeric(12,2), CONVERT(float,@bc)/CONVERT(float,@gc)) END,
NULLIF(@bErr, 0),
CASE WHEN @bErr <> 0 THEN 1
WHEN @br IS NULL OR @gr IS NULL THEN 0
WHEN @br >= @gr THEN 1 ELSE 0 END);
END TRY
BEGIN CATCH PRINT N' (结果表写入跳过：' + LEFT(ERROR_MESSAGE(),80) + N')'; END CATCH
END
GO
CREATE TABLE dbo.AQ_ResultCheck(
检查项 nvarchar(160) NOT NULL PRIMARY KEY,
反模式读取 nvarchar(24) NULL,
修正读取 nvarchar(24) NULL,
读取倍数 numeric(12,2) NULL,
CPU倍数 numeric(12,2) NULL,
反模式报错 int NULL,
是否通过 bit NOT NULL);
GO
PRINT ' 度量框架就绪：AQ_Run1 / AQ_M1 / AQ_Cmp / AQ_ResultCheck';
GO
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
GO
PRINT '################################################################';
PRINT '### 01 反模式一：SELECT * ###';
PRINT '################################################################';
PRINT '';
PRINT '【反模式的本质】SELECT * 不是「多读几个字段」的小事，它同时制造三个后果：';
PRINT ' (1) 破坏覆盖索引 → 每一次匹配都要回聚集索引取未覆盖的列（键查找风暴）；';
PRINT ' (2) 结果集随 DDL 静默变形 → 依赖列序号/列数的下游代码静默错位或直接报错；';
PRINT ' (3) 多传无用的大字段（nvarchar(max)/LOB）→ 网络、内存授予、临时表一并放大。';
PRINT '本节用 K1 证明 (1)，用 K2 三态实测证明 (2)。';
PRINT '';
PRINT '-- 01.1 列清单对比：IX_AQ_Country_Date 的覆盖列 vs 基表全部列 --';
SELECT 索引覆盖列 = 'CountryCode, OrderDate (键) + OrderId(聚集键) + NetAmountUsd, Status, StoreCode, SalesEmployeeId (包含列)';
SELECT 基表未覆盖列 = c.name
FROM sys.columns c WHERE c.object_id = OBJECT_ID('dbo.AQ_Order')
AND c.name NOT IN ('OrderId','CountryCode','OrderDate','NetAmountUsd','Status','StoreCode','SalesEmployeeId');
PRINT '-- 01.2 同一句查询：SELECT * vs 显式覆盖列 --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_K1_Bad AS BEGIN SET NOCOUNT ON;
/* SELECT * → 结果集含 Note 等未覆盖列 → 必须回聚集索引逐行取 */
SELECT * INTO #t FROM dbo.AQ_Order
WHERE CountryCode = 'CN' AND OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01';
SELECT COUNT_BIG(*) AS 行数 FROM #t;
END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_K1_Good AS BEGIN SET NOCOUNT ON;
/* 只取覆盖索引能提供的列 → 全程只用 IX_AQ_Country_Date */
SELECT OrderId, CountryCode, OrderDate, NetAmountUsd, Status, StoreCode, SalesEmployeeId INTO #t
FROM dbo.AQ_Order
WHERE CountryCode = 'CN' AND OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01';
SELECT COUNT_BIG(*) AS 行数 FROM #t;
END
GO
EXEC dbo.AQ_Cmp 'AQ_K1_Bad','AQ_K1_Good', N'K1 SELECT * INTO 打穿覆盖索引 → 退化为聚集索引扫描', N'K1_SELECT星号覆盖索引';
PRINT ' 解读：反模式计划出现 [聚集索引扫描]（SELECT * 多要了 Note 列 → 覆盖索引不再够用 → 退化为扫全表）；';
PRINT ' 修正版只剩 [索引查找]（外加对临时表的一次扫描，那是 SELECT INTO 的目标表本身）。';
PRINT ' SELECT * 把「一次范围查找」变成「扫全表 / 逐行回表」——本机实测 80 倍以上差距。';
PRINT ' 提示：若查询带 ORDER BY + TOP 等形态，同样的「覆盖索引失效」会表现为[键查找]风暴。';
PRINT '';
PRINT '-- 01.3 SELECT * 的元数据脆弱性：三态实测（本机结果与流行说法有两处不同） --';
IF OBJECT_ID('dbo.AQ_MetaBase','U') IS NOT NULL DROP TABLE dbo.AQ_MetaBase;
IF OBJECT_ID('dbo.AQ_MetaSink','U') IS NOT NULL DROP TABLE dbo.AQ_MetaSink;
CREATE TABLE dbo.AQ_MetaBase(a int NOT NULL, b int NOT NULL);
CREATE TABLE dbo.AQ_MetaSink(a int NOT NULL, b int NOT NULL);
INSERT dbo.AQ_MetaBase VALUES (1,2);
PRINT ' 初始状态：dbo.AQ_MetaBase(a, b) —— 2 列';
GO
PRINT ' 【加列前】';
EXEC sp_executesql N'IF OBJECT_ID(''tempdb..#y'') IS NOT NULL DROP TABLE #y;
SELECT * INTO #y FROM dbo.AQ_MetaBase;
SELECT N''临时批 SELECT * INTO 的列数'' AS 场景, COUNT(*) AS 列数
FROM tempdb.sys.columns WHERE object_id = OBJECT_ID(''tempdb..#y'');';
EXEC sp_executesql N'INSERT INTO dbo.AQ_MetaSink(a,b) SELECT * FROM dbo.AQ_MetaBase;
SELECT N''固定列清单 INSERT ... SELECT *'' AS 场景, @@ROWCOUNT AS 结果;';
PRINT ' 【执行 ALTER TABLE dbo.AQ_MetaBase ADD c int NULL 】';
ALTER TABLE dbo.AQ_MetaBase ADD c int NULL;
PRINT ' 【加列后】';
EXEC sp_executesql N'IF OBJECT_ID(''tempdb..#y'') IS NOT NULL DROP TABLE #y;
SELECT * INTO #y FROM dbo.AQ_MetaBase;
SELECT N''临时批 SELECT * INTO 的列数'' AS 场景, COUNT(*) AS 列数
FROM tempdb.sys.columns WHERE object_id = OBJECT_ID(''tempdb..#y'');';
BEGIN TRY
EXEC sp_executesql N'INSERT INTO dbo.AQ_MetaSink(a,b) SELECT * FROM dbo.AQ_MetaBase;';
PRINT ' 固定列清单 INSERT ... SELECT * => 竟然成功（与预期不符）';
END TRY
BEGIN CATCH
PRINT ' 固定列清单 INSERT ... SELECT * => ★错误 ' + CAST(ERROR_NUMBER() AS varchar(10))
+ N' : ' + LEFT(ERROR_MESSAGE(), 130);
PRINT ' → 注意错误号是 121（「选择列表项多于插入列表项」），不是常被引用的 213。';
INSERT dbo.AQ_ResultCheck(检查项, 反模式读取, 修正读取, 读取倍数, CPU倍数, 反模式报错, 是否通过)
SELECT N'K2_加列后INSERT_SELECT星号报', CAST(ERROR_NUMBER() AS nvarchar(24)), N'无报错', NULL, NULL,
ERROR_NUMBER(), CASE WHEN ERROR_NUMBER() = 121 THEN 1 ELSE 0 END
WHERE NOT EXISTS(SELECT 1 FROM dbo.AQ_ResultCheck WHERE 检查项 = N'K2_加列后INSERT_SELECT星号报');
END CATCH
BEGIN TRY
EXEC sp_executesql N'INSERT INTO dbo.AQ_MetaSink(a,b) SELECT a, b FROM dbo.AQ_MetaBase;';
PRINT ' 显式列名 INSERT ... SELECT a,b => 成功，完全不受加列影响';
END TRY
BEGIN CATCH PRINT ' 显式列名 => 错误 ' + CAST(ERROR_NUMBER() AS varchar(10)); END CATCH
PRINT ' 【结论（按实测更正两条流行说法）】';
PRINT ' ① SELECT * INTO 会跟随 DDL 静默变形：列数 2 → 3。下游若按列序号取值';
PRINT ' （SqlDataReader.GetOrdinal、SqlBulkCopy 列映射、ODBC 按序号绑定）会静默错位。';
PRINT ' ② 与常说不同：存储过程里的 SELECT * 会随 ALTER TABLE 自动跟上新列（不是创建时快照），';
PRINT ' 真正属于快照的是**视图**里的 SELECT *（需 sp_refreshview，见「视图与物化视图」主题）。';
PRINT ' ③ 固定列清单 + SELECT * 的组合在加列后直接报 121，这是最容易被生产事故命中的形态。';
GO
PRINT '################################################################';
PRINT '### 02 反模式二：隐式转换 ###';
PRINT '################################################################';
PRINT '';
PRINT '【反模式的本质】SQL Server 的数据类型优先级表里：nvarchar > nchar > varchar > char，';
PRINT ' 而 datetime > date，bigint > int > nvarchar。';
PRINT ' 当两边的优先级不同，**优先级低的那一侧会被转换**；如果被转换的是「列」，';
PRINT ' 索引就不再能用「等值前缀」了。';
PRINT ' 关键实测发现：SQL Server 并不会因此改成全表扫描，而是用 GetRangeThroughConvert 造一个';
PRINT ' 「范围查找」——查找谓词里只剩下被转换的那一列，**它后面的键列被踢到残余谓词**。';
PRINT ' 所以危害程度完全取决于「转换列之后还有没有别的键列」。';
PRINT '';
PRINT '-- 02.1 P1/P2：char(2) 列遇到 Unicode 字面量 / nvarchar 参数 --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_IC01_Bad AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order
WHERE CountryCode = N'CN' AND OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01'; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_IC01_Good AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order
WHERE CountryCode = 'CN' AND OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01'; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_IC02_Bad @cc nvarchar(10) = N'CN' AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order
WHERE CountryCode = @cc AND OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01'; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_IC02_Good @cc varchar(2) = 'CN' AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order
WHERE CountryCode = @cc AND OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01'; END
GO
EXEC dbo.AQ_Cmp 'AQ_IC01_Bad','AQ_IC01_Good', N'P1 char(2) 列 × Unicode 字面量 N''CN'' → 31.2×', N'P1_char2_N字面量';
EXEC dbo.AQ_Cmp 'AQ_IC02_Bad','AQ_IC02_Good', N'P2 char(2) 列 × nvarchar 参数（ORM 传参）→ 31.2×', N'P2_char2_nvarchar参数';
PRINT '';
PRINT '-- 02.2 P3/P4：varchar(20) 列 + **单列唯一索引**（关键反例：零损失） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_IC03_Bad AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE OrderNo = N'SO-00012345'; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_IC03_Good AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE OrderNo = 'SO-00012345'; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_IC04_Bad @no nvarchar(20) = N'SO-00012345' AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE OrderNo = @no; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_IC04_Good @no varchar(20) = 'SO-00012345' AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE OrderNo = @no; END
GO
EXEC dbo.AQ_Cmp 'AQ_IC03_Bad','AQ_IC03_Good', N'P3 varchar(20) 列 × N''…''（单列唯一索引）→ 1.00×', N'P3_varchar20_N字面量_无损失';
EXEC dbo.AQ_Cmp 'AQ_IC04_Bad','AQ_IC04_Good', N'P4 varchar(20) 列 × nvarchar 参数（单列唯一索引）→ 1.00×', N'P4_varchar20_nvarchar参数_无损失';
PRINT ' 解读：单列唯一索引没有「后续键列」可被降级，范围查找已经把范围收窄到 1 行 → 完全无损失。';
PRINT ' 所以「见到 CONVERT_IMPLICIT 就断定会全表扫描」是错的，必须看索引的键列结构。';
PRINT '';
PRINT '-- 02.3 P5：varchar(10) Status + 复合索引 (Status, OrderDate) --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_IC05_Bad AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order
WHERE Status = N'Cancelled' AND OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01'; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_IC05_Good AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order
WHERE Status = 'Cancelled' AND OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01'; END
GO
EXEC dbo.AQ_Cmp 'AQ_IC05_Bad','AQ_IC05_Good', N'P5 varchar(10) Status 复合索引 × N''Cancelled'' → 26.4×', N'P5_Status_N字面量';
PRINT '';
PRINT '-- 02.4 P6：把「数字参数」传给字符串键列（后果最重：直接报错 8114） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_IC06_Bad @id bigint = 12345 AS BEGIN SET NOCOUNT ON;
/* 订单号是字符串，参数却是 bigint → 列被转成 bigint → 既用不了索引，又必然报错 */
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE OrderNo = @id; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_IC06_Good @id varchar(20) = 'SO-00012345' AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE OrderNo = @id; END
GO
EXEC dbo.AQ_Cmp 'AQ_IC06_Bad','AQ_IC06_Good', N'P6 varchar 键列 × bigint 参数 → 错误 8114 + 转换警告', N'P6_数值参数查字符串键';
PRINT ' 解读：这是本节唯一「会直接报错」的隐式转换形态。原因：bigint 优先级高于 varchar，';
PRINT ' 列侧被转成 bigint，而 ''SO-00012345'' 无法转成数字 → 运行期 8114。';
PRINT ' 它同时触发了 PlanAffectingConvert（ConvertIssue="Cardinality Estimate"）警告，';
PRINT ' 说明「基数估计受影响」时才会发警告；而 P1/P5 那种能凑出范围查找的场景不发警告。';
PRINT '';
PRINT '-- 02.5 P7：date 列 × datetime 参数（实测无害，与常见说法相反） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_IC07_Bad @d1 datetime = '2026-01-01', @d2 datetime = '2026-02-01' AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_DateLab WHERE D >= @d1 AND D < @d2; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_IC07_Good @d1 date = '2026-01-01', @d2 date = '2026-02-01' AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_DateLab WHERE D >= @d1 AND D < @d2; END
GO
EXEC dbo.AQ_Cmp 'AQ_IC07_Bad','AQ_IC07_Good', N'P7 date 列 × datetime 参数 → 1.00×（优化器已处理边界）', N'P7_date列datetime参数_无害';
PRINT '';
PRINT '-- 02.6 P8：bigint 列 × nvarchar 参数（方向正确：有转换但完全无害） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_IC08_Bad @cid nvarchar(20) = N'12345' AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_OrderLine WHERE OrderId = @cid; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_IC08_Good @cid bigint = 12345 AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_OrderLine WHERE OrderId = @cid; END
GO
EXEC dbo.AQ_Cmp 'AQ_IC08_Bad','AQ_IC08_Good', N'P8 bigint 列 × nvarchar 参数 → 1.00×（参数被转换，方向安全）', N'P8_int列字符串参数_无害';
PRINT ' 解读：反模式版计划里 CONVERT_IMPLICIT 次数 = 1，但 EQ前缀 = 1、残余谓词 = 0 → 无害。';
PRINT ' 结论：CONVERT_IMPLICIT 的次数**不是**危害指标，要看它出现在哪一侧、在查找谓词的哪个位置。';
PRINT '';
PRINT '-- 02.7 可编程判据：从计划里读出「查找谓词是否被截断」 --';
PRINT ' 判据 = (计划含 CONVERT_IMPLICIT) 且 (查找谓词中无 ScanType="EQ") 且 (存在 <Predicate> 残余谓词)';
PRINT ' → 说明等值前缀已丢失，后续键列被降级为残余过滤，索引只做了「内部分范围扫描」。';
SELECT 用例 = 检查项, 反模式逻辑读 = 反模式读取, 修正后逻辑读 = 修正读取,
读取倍数 = 读取倍数, CPU倍数 = CPU倍数, 结论 = CASE WHEN 是否通过 = 1 THEN N'反模式更差' ELSE N'二者相当' END
FROM dbo.AQ_ResultCheck WHERE 检查项 LIKE N'P[1-8]_%' ORDER BY 检查项;
GO
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
GO
PRINT '################################################################';
PRINT '### 03 反模式三：非 SARGable 谓词 ###';
PRINT '################################################################';
PRINT '';
PRINT '【反模式的本质】SARGable = Search ARGument able（可作查找参数）。';
PRINT ' 只要优化器不能在「索引键列」和「常量/参数」之间建立可比较的顺序关系，就无法做范围定位。';
PRINT ' 但请注意：**不同写法的危害相差三个数量级**，而且有几种写法在现代优化器上已经无害。';
PRINT ' 本节 11 组全部实测，凡「实测无差异」的一律如实标注为「已不是反模式」。';
PRINT '';
PRINT '-- S1 列上套 YEAR()/MONTH()（珠宝行业最常见的「按月报表」写法） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG01_Bad AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order
WHERE CountryCode = 'CN' AND YEAR(OrderDate) = 2026 AND MONTH(OrderDate) = 1; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG01_Good AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order
WHERE CountryCode = 'CN' AND OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01'; END
GO
EXEC dbo.AQ_Cmp 'AQ_SG01_Bad','AQ_SG01_Good', N'S1 YEAR()/MONTH() 函数包装 → 半开区间（31.2×）', N'S1_YEAR_MONTH';
PRINT ' 解读：注意修正版才出现 [嵌套循环]/[计算标量] —— 那是「查找 + 回表取 NetAmountUsd 之外列」；';
PRINT ' 反模式版只能对 CountryCode 走前缀查找，OrderDate 完全无法参与定位。';
PRINT '';
PRINT '-- S2 列上套 CAST(col AS date)（实测仅 1.50×：优化器已能推导单调 CAST 的范围） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG02_Bad AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order
WHERE CountryCode = 'CN' AND CAST(OrderDate AS date) = '2026-01-15'; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG02_Good AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order
WHERE CountryCode = 'CN' AND OrderDate >= '2026-01-15' AND OrderDate < '2026-01-16'; END
GO
EXEC dbo.AQ_Cmp 'AQ_SG02_Bad','AQ_SG02_Good', N'S2 CAST(OrderDate AS date)（实测差异很小，非致命）', N'S2_CAST_date_小差异';
PRINT ' 解读：datetime2 → date 的 CAST 是单调映射，优化器可把等值条件反推成区间，所以只差 1.5 倍。';
PRINT ' → 这条在 SQL Server 2025 上**已基本被优化器自动修复**，不必作为首要整改项。';
PRINT '';
PRINT '-- S3 列上套 DATEDIFF 归零（历史遗留写法，重灾） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG03_Bad AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order
WHERE CountryCode = 'CN' AND DATEDIFF(day, OrderDate, '2026-01-15') = 0; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG03_Good AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order
WHERE CountryCode = 'CN' AND OrderDate >= '2026-01-15' AND OrderDate < '2026-01-16'; END
GO
EXEC dbo.AQ_Cmp 'AQ_SG03_Bad','AQ_SG03_Good', N'S3 DATEDIFF 归零（226.3×）', N'S3_DATEDIFF归零';
PRINT ' 解读：DATEDIFF 不是单调映射（有取整/截断），优化器无法反推区间。差异比 S2 高两个数量级。';
PRINT '';
PRINT '-- S4 列上套 UPPER()（本节最大赢家之一：305.8×，CPU 364.0×） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG04_Bad AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order
WHERE UPPER(Status) = 'CANCELLED' AND OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01'; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG04_Good AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order
WHERE Status = 'Cancelled' AND OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01'; END
GO
EXEC dbo.AQ_Cmp 'AQ_SG04_Bad','AQ_SG04_Good', N'S4 UPPER(Status) → 原值等值（305.8×，含并行扫描）', N'S4_UPPER_Status';
PRINT ' 解读：反模式版直接退化为 [索引扫描][并行]，因为 UPPER 让 Status 彻底失去定位能力；';
PRINT ' 正确做法是**写入时就规范化**（统一大小写 + CHECK 约束），而不是查询时兜底转换。';
PRINT '';
PRINT '-- S5 列上套 LEFT()（等价的更优写法是前缀 LIKE）（450.3×，CPU 727.1×） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG05_Bad AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE LEFT(OrderNo, 8) = 'SO-00001'; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG05_Good AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE OrderNo LIKE 'SO-00001%'; END
GO
EXEC dbo.AQ_Cmp 'AQ_SG05_Bad','AQ_SG05_Good', N'S5 LEFT(OrderNo,8) → 前缀 LIKE（450.3×）', N'S5_LEFT_前缀LIKE';
PRINT ' 解读：前缀 LIKE 是 SARGable 的（优化器把它转成区间查找）；LEFT() 不是。';
PRINT '';
PRINT '-- S6 前导通配 LIKE ''%…%''（本节最大赢家：514.6×，CPU 2125.4×） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG06_Bad AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE OrderNo LIKE '%12345%'; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG06_Good AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE OrderNo LIKE 'SO-00012%'; END
GO
EXEC dbo.AQ_Cmp 'AQ_SG06_Bad','AQ_SG06_Good', N'S6 LIKE ''%12345%'' → 前缀 LIKE（514.6×）', N'S6_前导通配LIKE';
PRINT ' 解读：前导通配是**唯一没有任何索引手段**的谓词形态。正确做法是全文检索（CONTAINS）；';
PRINT ' 「后缀可搜」的需求应改为反向列（存储 REVERSE(OrderNo)）并建索引。';
PRINT '';
PRINT '-- S7 列侧算术：col * 1.05 > v（40.2×） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG07_Bad AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE NetAmountUsd * 1.05 > 9800; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG07_Good AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE NetAmountUsd > 9800 / 1.05; END
GO
EXEC dbo.AQ_Cmp 'AQ_SG07_Bad','AQ_SG07_Good', N'S7 列侧算术 col*1.05 → 常数侧 v/1.05（40.2×）', N'S7_列侧算术';
PRINT '';
PRINT '-- S8 列拼接：col1 + col2 = v（7.05×） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG08_Bad AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE CountryCode + StoreCode = 'CNST01'; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG08_Good AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE CountryCode = 'CN' AND StoreCode = 'ST01'; END
GO
EXEC dbo.AQ_Cmp 'AQ_SG08_Bad','AQ_SG08_Good', N'S8 列拼接 CountryCode+StoreCode → 双等值（7.05×）', N'S8_列拼接';
PRINT '';
PRINT '-- S9 ISNULL(col,''x'') = v（实测 1.00×：列是 NOT NULL，优化器已等价消除） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG09_Bad AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order
WHERE ISNULL(Status,'X') = 'Cancelled' AND OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01'; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG09_Good AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order
WHERE Status = 'Cancelled' AND OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01'; END
GO
EXEC dbo.AQ_Cmp 'AQ_SG09_Bad','AQ_SG09_Good', N'S9 ISNULL(Status) 包装（列 NOT NULL → 实测无害）', N'S9_ISNULL_不可空列无害';
PRINT ' 解读：ISNULL 只在**可空列**上才真正阻断查找（要验证请把谓词换成可空列如 CustomerId）；';
PRINT ' 在 NOT NULL 列上优化器会直接把 ISNULL 消掉，此时它只是噪音、不是性能问题。';
PRINT '';
PRINT '-- S10 列侧 CAST：CAST(NetAmountUsd AS int) = 5000（实测 1.00×） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG10_Bad AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE CAST(NetAmountUsd AS int) = 5000; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG10_Good AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE NetAmountUsd >= 5000 AND NetAmountUsd < 5001; END
GO
EXEC dbo.AQ_Cmp 'AQ_SG10_Bad','AQ_SG10_Good', N'S10 CAST(decimal AS int) 等值（优化器已能反推区间 → 无害）', N'S10_CAST_decimal_int_无害';
PRINT ' 解读：decimal → int 的 CAST 是「向零截断」，在正数区间内单调，可反推为 [5000,5001) 区间。';
PRINT ' 半开区间写法依然是**更稳**的（对负数、对 NULL、对未来优化器都更安全），但不要再宣称它快 100 倍。';
PRINT '';
PRINT '-- S11 500 万行列存事实表上的非 SARGable（结论与行存完全不同！） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG11_Bad AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM rpt.OrgSalesFact WHERE YEAR(OrderDate) = 2025 AND MONTH(OrderDate) = 6; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_SG11_Good AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM rpt.OrgSalesFact WHERE OrderDate >= '2025-06-01' AND OrderDate < '2025-07-01'; END
GO
EXEC dbo.AQ_Cmp 'AQ_SG11_Bad','AQ_SG11_Good', N'S11 列存 500 万行 YEAR/MONTH → 半开区间（读 1.00× 但 CPU 近百倍）', N'S11_列存非SARGable_代价在CPU';
PRINT ' 解读：★ 这是本节最重要的认知修正 —— 列存上非 SARGable 的代价**不在逻辑读**（两版都是 843），';
PRINT ' 而在于丢失了「行组/段消除 + 批处理模式」：本次 CPU 倍率见上方指标行（数十倍至上百倍）。';
PRINT ' → 用逻辑读评估列存查询的反模式，会得出「没有差别」的错误结论。';
PRINT '';
PRINT '-- S12 本节汇总：11 组非 SARGable 的实测危害排序 --';
PRINT ' 分级口径：以逻辑读为主；若逻辑读相同但 CPU 相差 10 倍以上，则单独标注为「代价在 CPU」。';
SELECT 危害等级 = CASE WHEN ISNULL(读取倍数,1) >= 100 THEN N'★★★ 致命'
WHEN ISNULL(读取倍数,1) >= 10 THEN N'★★ 严重'
WHEN ISNULL(CPU倍数,1) >= 10 AND ISNULL(读取倍数,1) < 2
THEN N'★★ 严重（逻辑读看不出，代价全在 CPU）'
WHEN ISNULL(读取倍数,1) >= 2 THEN N'★ 值得修'
ELSE N'○ 已不是反模式' END,
用例 = 检查项,
反模式逻辑读 = 反模式读取, 修正后逻辑读 = 修正读取,
读取倍数 = 读取倍数, CPU倍数 = CPU倍数
FROM dbo.AQ_ResultCheck WHERE 检查项 LIKE N'S%' ORDER BY ISNULL(读取倍数,1) DESC, ISNULL(CPU倍数,1) DESC;
GO
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
GO
PRINT '################################################################';
PRINT '### 04 反模式四：过度范式化（EAV 多自连接） ###';
PRINT '################################################################';
PRINT '';
PRINT '【反模式的本质】把「同一实体的多个属性」拆成 EAV（实体-属性-值）三张表后，';
PRINT ' 每要一个属性就多一次自连接：N 个属性 = N 次连接。JOIN 数量与属性数线性增长，';
PRINT ' 而优化器可选的连接顺序是阶乘级 —— 连接数一多，估算误差与计划劣化会同时发生。';
PRINT '';
PRINT '【实验台】dbo.AQ_ProductAttribute：100,000 产品 × 6 属性 = 600,000 行（EAV）；';
PRINT ' dbo.AQ_ProductFlat：100,000 行等宽表（属性作为列）；';
PRINT ' 两边都建了适配自身访问模式的索引，避免「不公平对比」。';
PRINT '';
PRINT '-- 04.1 多属性过滤（只 COUNT，不取回属性） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_EAV01_Bad AS BEGIN SET NOCOUNT ON;
/* 3 个属性过滤 → 3 次自连接 */
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_ProductAttribute a1
JOIN dbo.AQ_ProductAttribute a2 ON a2.ProductId = a1.ProductId AND a2.AttributeId = 2
JOIN dbo.AQ_ProductAttribute a3 ON a3.ProductId = a1.ProductId AND a3.AttributeId = 3
WHERE a1.AttributeId = 1 AND a1.Value = 'D' AND a2.Value = 'VS1' AND a3.Value = '1.0-1.5'; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_EAV01_Good AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_ProductFlat
WHERE 颜色 = 'D' AND 净度 = 'VS1' AND 克拉区间 = '1.0-1.5'; END
GO
EXEC dbo.AQ_Cmp 'AQ_EAV01_Bad','AQ_EAV01_Good', N'A1 EAV 3 自连接过滤 → 宽表（32.8×）', N'A1_EAV_3连接';
PRINT ' 解读：反模式版计划出现 [哈希匹配]（3 份 10 万行的中间结果做哈希连接）；';
PRINT ' 宽表版一次 [索引查找] 就定位到，4 个逻辑读。';
PRINT '';
PRINT '-- 04.2 取回 6 个属性（EAV 的必然代价：6 次连接） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_EAV02_Bad AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM (
SELECT a1.ProductId, a1.Value AS c1, a2.Value AS c2, a3.Value AS c3,
a4.Value AS c4, a5.Value AS c5, a6.Value AS c6
FROM dbo.AQ_ProductAttribute a1
JOIN dbo.AQ_ProductAttribute a2 ON a2.ProductId = a1.ProductId AND a2.AttributeId = 2
JOIN dbo.AQ_ProductAttribute a3 ON a3.ProductId = a1.ProductId AND a3.AttributeId = 3
JOIN dbo.AQ_ProductAttribute a4 ON a4.ProductId = a1.ProductId AND a4.AttributeId = 4
JOIN dbo.AQ_ProductAttribute a5 ON a5.ProductId = a1.ProductId AND a5.AttributeId = 5
JOIN dbo.AQ_ProductAttribute a6 ON a6.ProductId = a1.ProductId AND a6.AttributeId = 6
WHERE a1.AttributeId = 1) q; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_EAV02_Good AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM (
SELECT ProductId, 颜色, 净度, 克拉区间, 证书, 产地, 表面处理 FROM dbo.AQ_ProductFlat) q; END
GO
EXEC dbo.AQ_Cmp 'AQ_EAV02_Bad','AQ_EAV02_Good', N'A2 EAV 6 自连接取回属性 → 宽表（4.70×）', N'A2_EAV_6连接取属性';
PRINT '';
PRINT '-- 04.3 诚实结论：过度范式化不是「无条件更差」 --';
PRINT ' 实测两组的赢家并不一致，原因值得记住：';
PRINT ' · A1（只过滤、不取属性）：EAV 输 32.8× —— 因为宽表有 (颜色, 净度) 覆盖索引，一次查找解决；';
PRINT ' · A2（取回 6 个属性）：EAV 输 4.70×，但差距比 A1 小 —— 因为 EAV 的 (AttributeId, Value, ProductId)';
PRINT ' 索引让每次连接都能精确查找，而宽表没有 6 列组合索引，只能 [索引扫描] 十万行。';
PRINT ' → 真正的判据不是「是否 EAV」，而是「**每条查询路径是否都有匹配的覆盖索引**」。';
PRINT ' → 但 EAV 的结构性代价无法用索引消除：属性数 +1 就 +1 次连接、+1 次估算误差，';
PRINT ' 且无法用一条 SQL 表达「6 个属性都在同一行」。属性经常成群读取时，宽表/物化视图是正解。';
PRINT '';
PRINT '################################################################';
PRINT '### 05 反模式五：用 DISTINCT / COUNT(*) 掩盖结构性问题 ###';
PRINT '################################################################';
PRINT '';
PRINT '【反模式的本质】JOIN 产生扇出（一行订单对多行明细）后，开发者用 DISTINCT 去重「让结果看起来对」。';
PRINT ' 这是典型的「症状治疗」：扇出带来的多余行仍要被产生、排序、去重，只是最后被丢掉。';
PRINT ' 正解是把「有没有」的语义交给半连接（EXISTS），从源头上不产生扇出。';
PRINT '';
PRINT '-- 05.1 E1：JOIN 扇出 + DISTINCT vs EXISTS 半连接 --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_E1_Bad AS BEGIN SET NOCOUNT ON;
/* 订单 JOIN 明细 → 每张订单出现 2 次（扇出）→ 再用 DISTINCT 把多出来的行丢掉 */
SELECT COUNT_BIG(*) AS N FROM (
SELECT DISTINCT o.OrderId, o.StoreCode, o.SalesEmployeeId, o.NetAmountUsd
FROM dbo.AQ_Order o JOIN dbo.AQ_OrderLine l ON l.OrderId = o.OrderId
WHERE o.CountryCode = 'CN' AND o.OrderDate >= '2026-01-01' AND o.OrderDate < '2026-02-01') q; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_E1_Good AS BEGIN SET NOCOUNT ON;
/* 用 EXISTS 表达「至少有一条明细」：既不产生扇出，也不需要 DISTINCT */
SELECT COUNT_BIG(*) AS N FROM (
SELECT o.OrderId, o.StoreCode, o.SalesEmployeeId, o.NetAmountUsd
FROM dbo.AQ_Order o
WHERE o.CountryCode = 'CN' AND o.OrderDate >= '2026-01-01' AND o.OrderDate < '2026-02-01'
AND EXISTS(SELECT 1 FROM dbo.AQ_OrderLine l WHERE l.OrderId = o.OrderId)) q; END
GO
EXEC dbo.AQ_Cmp 'AQ_E1_Bad','AQ_E1_Good', N'E1 JOIN 扇出 + DISTINCT → EXISTS 半连接', N'E1_DISTINCT掩盖扇出';
PRINT ' ── 语义等价性验证：两版返回的行数必须完全一致，否则就不叫「修复」──';
SELECT 反模式行数 = (SELECT COUNT_BIG(*) FROM (
SELECT DISTINCT o.OrderId, o.StoreCode, o.SalesEmployeeId, o.NetAmountUsd
FROM dbo.AQ_Order o JOIN dbo.AQ_OrderLine l ON l.OrderId = o.OrderId
WHERE o.CountryCode = 'CN' AND o.OrderDate >= '2026-01-01' AND o.OrderDate < '2026-02-01') b),
修正行数 = (SELECT COUNT_BIG(*) FROM (
SELECT o.OrderId, o.StoreCode, o.SalesEmployeeId, o.NetAmountUsd
FROM dbo.AQ_Order o
WHERE o.CountryCode = 'CN' AND o.OrderDate >= '2026-01-01' AND o.OrderDate < '2026-02-01'
AND EXISTS(SELECT 1 FROM dbo.AQ_OrderLine l WHERE l.OrderId = o.OrderId)) g);
PRINT ' 解读：★ 本机实测结果是——**两版的逻辑读与 CPU 完全一样（1.00×），执行计划逐算子相同**。';
PRINT ' 原因：SQL Server 2025 的优化器已能把「JOIN 之后 DISTINCT」识别为半连接语义，自动改写。';
PRINT ' → 所以这条老建议在现代优化器上**不再是一条性能建议**，而是一条**可读性与健壮性建议**：';
PRINT ' · 写 EXISTS 表达的是「这张订单有没有明细」，意图直白，评审时不会被误读成「需要明细行」；';
PRINT ' · 不依赖优化器的等价改写能力：换成别的引擎、或把投影列改宽后，扇出与去重的代价会真实出现。';
PRINT ' → 若坚持要用 DISTINCT 形态，请至少**同时用 EXISTS 版本核对行数**（本节已给出该校验）。';
PRINT ' ★ 方法论提醒：把「流行说法」落到本机实测，是唯一能避免「改了半天没收益」的办法 ——';
PRINT ' 真实收益需要同时看 CPU / 耗时 / 内存授予，而这三项在本次对比里同样没有差别。';
PRINT '';
PRINT '-- 05.2 E2：存在性判断 COUNT(*)>0 vs EXISTS（实测无差异 —— 已被优化器改写） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_E2_Bad AS BEGIN SET NOCOUNT ON;
SELECT CASE WHEN (SELECT COUNT_BIG(*) FROM dbo.AQ_Order WHERE Status = 'Paid') > 0 THEN 1 ELSE 0 END AS N; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_E2_Good AS BEGIN SET NOCOUNT ON;
SELECT CASE WHEN EXISTS(SELECT 1 FROM dbo.AQ_Order WHERE Status = 'Paid') THEN 1 ELSE 0 END AS N; END
GO
EXEC dbo.AQ_Cmp 'AQ_E2_Bad','AQ_E2_Good', N'E2 存在性判断 COUNT(*)>0 vs EXISTS（实测 1.00×）', N'E2_COUNT对比EXISTS_无差异';
PRINT ' 解读：反模式版计划里出现 [嵌套循环] —— 优化器已把「COUNT(*)>0」重写为「取一行即停」。';
PRINT ' 在 SQL Server 2025 上这条古老建议的收益为 0。仍然推荐写 EXISTS，理由是**可读性**';
PRINT ' 与**可移植性**（其他引擎未必做这个改写），而不是性能。';
PRINT '';
GO
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
GO
PRINT '################################################################';
PRINT '### 06 反模式六：标量 UDF 与逐行处理（RBAR） ###';
PRINT '################################################################';
PRINT '';
PRINT '【反模式的本质】SQL Server 是「面向集合」的引擎。任何把集合运算拆成「一行一行来」的写法';
PRINT ' （RBAR = Row-By-Agonizing-Row）都会把一次 I/O 变成 N 次，把一次编译变成 N 次。';
PRINT ' 标量 UDF 是 RBAR 的隐蔽变体：它看起来像一个函数调用，实际是「每行一次独立执行」。';
PRINT ' SQL Server 2019 引入的 FROID（标量 UDF 内联）能救一部分，但**救不了访问表的 UDF**。';
PRINT '';
PRINT '-- 06.1 U1：可被 FROID 内联的标量 UDF（危害小，但仍阻断查找） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_U1_Bad AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE dbo.AQ_ScoreFast(NetAmountUsd) > 0.3; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_U1_Good AS BEGIN SET NOCOUNT ON;
/* 把 UDF 的算法手工内联到谓词另一侧，让 NetAmountUsd 恢复可查找 */
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE NetAmountUsd > 2999.99; END
GO
EXEC dbo.AQ_Cmp 'AQ_U1_Bad','AQ_U1_Good', N'U1 可内联标量 UDF 阻断查找 → 直接谓词', N'U1_可内联UDF';
PRINT ' 解读：反模式版计划是 [索引扫描]（IX_AQ_NetAmount 全扫），修正版是 [索引查找]。';
PRINT ' FROID 内联只是省掉了「逐行调用」的 CPU 开销，**不能恢复索引可用性**。';
PRINT '';
PRINT '-- 06.2 U2：不可内联的标量 UDF（函数体内访问表 → FROID 放弃内联） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_U2_Bad AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE dbo.AQ_ScoreSlow(NetAmountUsd) > 0.3; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_U2_Good AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM dbo.AQ_Order WHERE NetAmountUsd > 2999.99; END
GO
EXEC dbo.AQ_Cmp 'AQ_U2_Bad','AQ_U2_Good', N'U2 不可内联标量 UDF（逐行调用）→ 直接谓词', N'U2_不可内联UDF';
PRINT ' 解读：反模式版计划出现 [表假脱机]（Table Spool）—— 那是函数体内那次查表被反复执行留下的痕迹；';
PRINT ' CPU 差异为「数倍」，见上方指标行。判断 UDF 是否可内联的方法：函数体内是否访问任何表。';
PRINT '';
PRINT '-- 06.3 正确的 UDF 替代方案：计算列 + 索引（对比三种写法的索引可用性） --';
SELECT 方案 = N'① 谓词内直接内联表达式', 索引可用 = N'可（若表达式与索引键一致）', 备注 = N'最直接，改 SQL 即可'
UNION ALL SELECT N'② 持久化计算列 + 索引', N'可（查找键就是计算列）', N'推荐：一次定义，全库受益'
UNION ALL SELECT N'③ 标量 UDF', N'不可（除非 FROID 内联成 ①）', N'仅在表达式复杂且不想落盘时考虑';
PRINT '';
PRINT '-- 06.4 U3：逐行 INSERT（WHILE 循环 / 游标）vs 集合式 INSERT --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_U3_Bad AS BEGIN SET NOCOUNT ON;
CREATE TABLE #t(Id int NOT NULL PRIMARY KEY, Amt decimal(18,2) NOT NULL);
DECLARE @i int = 1;
WHILE @i <= 5000
BEGIN
INSERT INTO #t(Id, Amt) VALUES (@i, CAST(@i AS decimal(18,2)) * 1.5);
SET @i = @i + 1;
END
SELECT COUNT_BIG(*) AS N FROM #t; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_U3_Good AS BEGIN SET NOCOUNT ON;
CREATE TABLE #t(Id int NOT NULL PRIMARY KEY, Amt decimal(18,2) NOT NULL);
INSERT INTO #t(Id, Amt)
SELECT value, CAST(value AS decimal(18,2)) * 1.5 FROM GENERATE_SERIES(1, 5000);
SELECT COUNT_BIG(*) AS N FROM #t; END
GO
EXEC dbo.AQ_Cmp 'AQ_U3_Bad','AQ_U3_Good', N'U3 WHILE 逐行 5000 次 INSERT → 集合式插入（64.5×）', N'U3_RBAR逐行插入';
PRINT ' 解读：两版最终写入完全相同的数据，差别只在「5000 个语句 vs 1 个语句」。';
PRINT ' 实测 10,119 读 / 157 读 = 64.5×，78ms / 2ms。**这是最容易在生产里复现的反模式**：';
PRINT ' RBAR 的代价随行数线性增长，而集合式写法基本是常数级。';
PRINT '';
PRINT '################################################################';
PRINT '### 07 反模式七：UNION 隐式去重 与 OFFSET 深分页 ###';
PRINT '################################################################';
PRINT '';
PRINT '-- 07.1 N1：UNION vs UNION ALL --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_N1_Bad AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM (
SELECT CountryCode FROM dbo.AQ_Order WHERE OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01'
UNION
SELECT CountryCode FROM dbo.AQ_Order WHERE OrderDate >= '2026-02-01' AND OrderDate < '2026-03-01') q; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_N1_Good AS BEGIN SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS N FROM (
SELECT CountryCode FROM dbo.AQ_Order WHERE OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01'
UNION ALL
SELECT CountryCode FROM dbo.AQ_Order WHERE OrderDate >= '2026-02-01' AND OrderDate < '2026-03-01') q; END
GO
EXEC dbo.AQ_Cmp 'AQ_N1_Bad','AQ_N1_Good', N'N1 UNION（隐式去重）→ UNION ALL', N'N1_UNION对比UNIONALL';
PRINT ' 解读：反模式版多出 [排序] 算子（UNION 的去重靠 Sort/Stream Aggregate 实现）。';
PRINT ' 本例 12,766 读 / 9,786 读 = 1.30×，差异不夸张，因为两个日期区间天然不相交、';
PRINT ' 去重结果与并集相同。真正的风险在**大数据量**：UNION 的排序可能溢出到 tempdb，';
PRINT ' 或触发巨大的内存授予 —— 那时代价会从 30% 跳到数倍。';
PRINT ' 判据：两个分支的键值域若已知不相交，UNION ALL 在语义上完全等价。';
PRINT '';
PRINT '-- 07.2 G1：OFFSET 深分页 vs 键集分页（本节倍率最高的单点优化：2059.3×） --';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_G1_Bad AS BEGIN SET NOCOUNT ON;
/* 第 25001 页：优化器必须先把前 500,000 行全部算出来再丢弃 */
SELECT COUNT_BIG(*) AS N FROM (
SELECT OrderId, OrderNo, NetAmountUsd FROM dbo.AQ_Order
ORDER BY OrderId OFFSET 500000 ROWS FETCH NEXT 20 ROWS ONLY) q; END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_G1_Good AS BEGIN SET NOCOUNT ON;
/* 键集分页：告诉引擎「从这一行之后开始」，直接定位 */
SELECT COUNT_BIG(*) AS N FROM (
SELECT OrderId, OrderNo, NetAmountUsd FROM dbo.AQ_Order
WHERE OrderId > 500000 ORDER BY OrderId OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY) q; END
GO
EXEC dbo.AQ_Cmp 'AQ_G1_Bad','AQ_G1_Good', N'G1 OFFSET 500000 深分页 → 键集分页（2059.3×）', N'G1_OFFSET深分页';
PRINT ' 解读：反模式版计划是 [聚集索引扫描] + [Top]，6,178 个逻辑读全是为了「跳过」前 50 万行；';
PRINT ' 键集分页把计划变成 [聚集索引查找]，3 个逻辑读。';
PRINT ' 注意 OFFSET 的成本随页码线性增长：第 100 万页时读取量会到 1 万+。';
PRINT ' 键集分页要求排序键唯一且单调（这里是 PK，天然满足）；';
PRINT ' 若排序键有重复，需要用「(排序键, 唯一键) 的复合游标」保证不跳行/不重行。';
PRINT '';
GO
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
GO
PRINT '################################################################';
PRINT '### 08 反模式八：单条大 UPDATE 触发锁升级（万亿级高并发的头号杀手） ###';
PRINT '################################################################';
PRINT '';
PRINT '【反模式的本质】前面七节的代价是「慢」。本节这一条的代价是「全库停摆」。';
PRINT ' SQL Server 有一个自动的「锁升级」（Lock Escalation）机制：当**一条语句**在同一个对象上';
PRINT ' 持有的锁超过阈值（默认 5000 个行锁或页锁），引擎会把成千上万个细粒度锁**换成一把表级锁**';
PRINT ' （OBJECT 级 X 锁）。换锁的动机是节省内存与 CPU —— 但代价是：';
PRINT ' · 表级 X 锁与**任何**其他锁都不兼容 → 这张表的读、写、DDL **全部**排队；';
PRINT ' · 与「更新了多少行」无关，与「有没有走索引」无关，只与**单条语句触碰的行数**有关；';
PRINT ' · 升级后受害的不是本会话，而是**其他所有会话** —— 生产上表现为「监控看不出慢 SQL，但整个模块整体卡死」。';
PRINT ' 在万亿级数据 + 高并发的珠宝 ERP 里（如「月末统一重算店铺归属」「批量刷新会员等级」），';
PRINT ' 一条 UPDATE ... WHERE YEAR(OrderDate)=2026 式语句就足以把整张订单表锁住。';
PRINT '';
SET NOCOUNT ON;
IF OBJECT_ID('dbo.AQ_LockLog','U') IS NOT NULL DROP TABLE dbo.AQ_LockLog;
CREATE TABLE dbo.AQ_LockLog(
测试项 nvarchar(40) NOT NULL PRIMARY KEY, 场景说明 nvarchar(220) NOT NULL,
更新行数 int NOT NULL, KEY行锁峰值 int NOT NULL, 是否表级X锁 bit NOT NULL,
升级尝试次数增量 int NOT NULL, 观测总耗时毫秒 int NOT NULL, 纯作业耗时毫秒 int NOT NULL);
GO
PRINT '-- 08.1 L1：单条大 UPDATE 的锁升级取证（两个 DMV 交叉验证，本会话即可复现） --';
PRINT ' 取证方式 A：sys.dm_tran_locks 快照 —— 直接看到 OBJECT 级 X 锁；';
PRINT ' 取证方式 B：sys.dm_db_index_operational_stats 的 lock promotion 计数器 —— 累计计数器的增量，';
PRINT ' 与快照无关，可证明「引擎确实执行了升级动作」，排除巧合。';
GO
SET NOCOUNT ON;
DECLARE @try0 int, @ok0 int, @t0 datetime2 = SYSDATETIME(), @rows int, @objX int, @keyLocks int;
SELECT @try0 = ISNULL(SUM(index_lock_promotion_attempt_count),0),
@ok0 = ISNULL(SUM(index_lock_promotion_count),0)
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.AQ_Order'), NULL, NULL);
PRINT ' 升级前 promotion 计数器基线：尝试 = ' + CAST(@try0 AS varchar(12)) + N'，成功 = ' + CAST(@ok0 AS varchar(12));
BEGIN TRAN;
PRINT ' 已开启事务，执行「按年月」式的报表型大 UPDATE（故意沿用第 03 节的非 SARGable 谓词）：';
UPDATE dbo.AQ_Order SET Note = N'并发审计'
WHERE YEAR(OrderDate) = 2026 AND MONTH(OrderDate) = 1;
SET @rows = @@ROWCOUNT;
PRINT ' 已更新行数 = ' + CAST(@rows AS varchar(10));
PRINT ' ── 取证方式 A：sys.dm_tran_locks 快照（本会话当前持有的锁） ──';
SELECT 资源类型 = resource_type, 请求模式 = request_mode, 锁数量 = COUNT(*)
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID AND resource_database_id = DB_ID()
GROUP BY resource_type, request_mode ORDER BY COUNT(*) DESC;
SELECT @objX = COUNT(*) FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID AND resource_database_id = DB_ID()
AND resource_type = 'OBJECT' AND request_mode = 'X';
SELECT @keyLocks = COUNT(*) FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID AND resource_database_id = DB_ID() AND resource_type = 'KEY';
PRINT ' → OBJECT X 锁数量 = ' + CAST(@objX AS varchar(6)) + N'（≥1 即「已升级为表级排他锁」）';
PRINT ' ★ 注意同一张快照里 KEY 行锁数量 = ' + CAST(@keyLocks AS varchar(10))
+ N' —— 升级后细粒度行锁**被换成**了一把表级锁，所以行锁归零、表锁出现。';
PRINT ' 这正是「升级」的字面含义：用 1 个代价极小的锁，换掉 N 个精确的锁，然后把整张表交出去。';
ROLLBACK;
DECLARE @try1 int, @ok1 int;
SELECT @try1 = ISNULL(SUM(index_lock_promotion_attempt_count),0),
@ok1 = ISNULL(SUM(index_lock_promotion_count),0)
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.AQ_Order'), NULL, NULL);
PRINT ' ── 取证方式 B：promotion 计数器增量 ──';
PRINT ' 升级后 promotion 计数器：尝试 = ' + CAST(@try1 AS varchar(12)) + N'，成功 = ' + CAST(@ok1 AS varchar(12));
PRINT ' 本次增量：尝试 ' + CAST(@try1-@try0 AS varchar(10)) + N' 次，成功 ' + CAST(@ok1-@ok0 AS varchar(10)) + N' 次';
PRINT ' 事务已回滚（数据无变化，但锁升级的副作用已经真实发生过）。';
INSERT dbo.AQ_LockLog VALUES (N'L1 单条大UPDATE', N'WHERE YEAR(OrderDate)=2026 AND MONTH(OrderDate)=1（1 条语句）',
@rows, @keyLocks, CASE WHEN @objX > 0 THEN 1 ELSE 0 END, @try1-@try0,
DATEDIFF(ms,@t0,SYSDATETIME()), DATEDIFF(ms,@t0,SYSDATETIME()));
GO
PRINT '';
PRINT '-- 08.2 双会话阻塞取证：升级后的表级 X 锁会锁死「毫不相关的一行」 --';
PRINT ' 为什么不在本脚本里直接跑？—— 阻塞必须**两个连接**才成立（同一会话不会被自己阻塞），';
PRINT ' 而本实例的 xp_cmdshell / Ad Hoc Distributed Queries 均为**关闭**状态（安全默认值）：';
SELECT 配置项 = name, 生效值 = value_in_use,
说明 = CASE WHEN value_in_use = 0
THEN N'关闭 → 本脚本引用双会话实测记录，并给出可复制的两窗口操作步骤'
ELSE N'已开启' END
FROM sys.configurations WHERE name IN ('xp_cmdshell','Ad Hoc Distributed Queries') ORDER BY name;
PRINT '';
PRINT ' 【复现步骤】把下面两段分别贴到两个查询窗口，先执行窗口 A，5 秒内执行窗口 B：';
PRINT ' ── 窗口 A（持锁方）──────────────────────────────────────────────────────';
PRINT ' SET NOCOUNT ON;';
PRINT ' BEGIN TRAN;';
PRINT ' UPDATE dbo.AQ_Order SET Note = N''并发审计''';
PRINT ' WHERE YEAR(OrderDate)=2026 AND MONTH(OrderDate)=1; -- 28,272 行 → 触发升级';
PRINT ' SELECT resource_type, request_mode, COUNT(*) FROM sys.dm_tran_locks';
PRINT ' WHERE request_session_id=@@SPID AND resource_database_id=DB_ID()';
PRINT ' GROUP BY resource_type, request_mode;';
PRINT ' WAITFOR DELAY ''00:00:10'';';
PRINT ' ROLLBACK;';
PRINT ' ── 窗口 B（受害者）─────────────────────────────────────────────────────';
PRINT ' SET NOCOUNT ON;';
PRINT ' SET LOCK_TIMEOUT 3000;';
PRINT ' UPDATE dbo.AQ_Order SET Note = N''会话B'' WHERE OrderId = 999999; -- 毫不相关的一行';
PRINT ' SELECT COUNT(*) FROM dbo.AQ_Order WHERE OrderId = 999999; -- RCSI 快照读';
PRINT '';
PRINT ' 【本机实测记录（DESKTOP-0PH20LJ\SQL2025 / JewelryAnalytics）】';
PRINT ' [会话A] 单条大 UPDATE（非 SARGable 谓词），持锁 10 秒';
PRINT ' [会话A] 已更新 28272 行';
PRINT ' DATABASE S 1';
PRINT ' OBJECT X 1 ← ★ 表级排他锁：锁升级已发生';
PRINT ' [会话A] 已回滚';
PRINT ' [会话B] 尝试更新完全不相关的一行 (OrderId = 999999)';
PRINT ' [会话B] ★被阻塞！错误 1222 : 已超过了锁请求超时时段。';
PRINT ' [会话B] 尝试读取同一行（RCSI 快照读）';
PRINT ' [会话B] 读取成功，行数 = 1（快照读未被阻塞）';
PRINT '';
PRINT ' ★ 这段记录里有三个必须记住的事实：';
PRINT ' ① OrderId = 999999 与 WHERE 命中的 28,272 行**毫不相关**，但一样被锁死；';
PRINT ' ② 本库开启了 RCSI（is_read_committed_snapshot_on = 1），所以**读**不受影响 ——';
PRINT ' 症状因此极具欺骗性：「查询全部正常，只有写入超时」；';
PRINT ' ③ 错误号是 1222（锁请求超时），不是死锁 1205 —— 只监控死锁会完全漏掉这类故障。';
PRINT '';
PRINT '-- 08.3 L2：把大 UPDATE 拆成同事务内的分批（正确姿势之一） --';
PRINT ' 关键：升级判定是**按语句**统计的。只要每条语句触碰的行数 < 5000，就不会升级。';
PRINT ' 下面每批 5000 行（用 OrderId 的聚集键区间做 SARGable 分批），200 条语句跑完 28,272 行。';
PRINT ' ★ 测量诚实性说明：锁采样本身要查 sys.dm_tran_locks，而 L2 在事务中会累积数万个锁，';
PRINT ' 采样代价很大。因此本节把「观测耗时」与「纯作业耗时」分开记：后者扣掉了采样开销，';
PRINT ' 只有它才能与 L3 横向比较（否则会得出「分批同事务比小事务更快」的错误结论）。';
GO
SET NOCOUNT ON;
DECLARE @lo bigint = 1, @tot int = 0, @cur int, @maxLocks int = 0, @objX bit = 0,
@rows int, @i int = 0, @t0 datetime2 = SYSDATETIME(), @tryEsc int = 0, @okEsc int = 0,
@sampMs int = 0, @ts datetime2;
SELECT @tryEsc = ISNULL(SUM(index_lock_promotion_attempt_count),0),
@okEsc = ISNULL(SUM(index_lock_promotion_count),0)
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.AQ_Order'), NULL, NULL);
BEGIN TRAN;
WHILE @lo <= 1000000
BEGIN
UPDATE dbo.AQ_Order SET Note = N'分批审计'
WHERE OrderId >= @lo AND OrderId < @lo + 5000
AND OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01';
SET @rows = @@ROWCOUNT; SET @tot = @tot + @rows; SET @i = @i + 1;
IF @i % 10 = 0
BEGIN
SET @ts = SYSDATETIME();
SELECT @cur = COUNT(*) FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID AND resource_database_id = DB_ID() AND resource_type = 'KEY';
IF @cur > @maxLocks SET @maxLocks = @cur;
IF EXISTS(SELECT 1 FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
AND resource_database_id = DB_ID() AND resource_type = 'OBJECT' AND request_mode = 'X')
SET @objX = 1;
SET @sampMs = @sampMs + DATEDIFF(ms, @ts, SYSDATETIME());
END
SET @lo = @lo + 5000;
END
DECLARE @totMs int = DATEDIFF(ms, @t0, SYSDATETIME());
PRINT ' [L2] 共更新 ' + CAST(@tot AS varchar(10)) + ' 行（' + CAST(@i AS varchar(6)) + ' 条语句）';
PRINT ' [L2] 同一事务内 KEY 行锁峰值 = ' + CAST(@maxLocks AS varchar(10));
PRINT ' [L2] 是否出现表级排他锁(OBJECT X) = ' + CASE WHEN @objX = 1 THEN N'是（仍然升级）' ELSE N'否 ★ 未升级' END;
PRINT ' [L2] 观测总耗时(ms) = ' + CAST(@totMs AS varchar(12)) + N' ；其中锁采样开销 = ' + CAST(@sampMs AS varchar(12))
+ N' ；★纯作业耗时(ms) = ' + CAST(@totMs - @sampMs AS varchar(12));
PRINT ' ── 本事务结束时的锁普查（按资源类型与模式分组） ──';
SELECT 资源类型 = resource_type, 请求模式 = request_mode, 锁数量 = COUNT(*)
FROM sys.dm_tran_locks WHERE request_session_id = @@SPID AND resource_database_id = DB_ID()
GROUP BY resource_type, request_mode ORDER BY COUNT(*) DESC;
DECLARE @try2 int, @ok2 int;
SELECT @try2 = ISNULL(SUM(index_lock_promotion_attempt_count),0),
@ok2 = ISNULL(SUM(index_lock_promotion_count),0)
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.AQ_Order'), NULL, NULL);
PRINT ' [L2] 本次分批的 promotion 增量：尝试 ' + CAST(@try2-@tryEsc AS varchar(10))
+ N' 次，成功 ' + CAST(@ok2-@okEsc AS varchar(10)) + N' 次（成功 = 0，说明引擎虽然尝试过，但没有升级）';
PRINT ' → 虽然事务内累计持有 ' + CAST(@maxLocks AS varchar(10))
+ N' 个 KEY 行锁（远超 5000），但**没有任何一条语句**超过阈值 → 不触发升级。';
PRINT ' ★ 这是最容易踩的误解：「锁总数超过 5000 就会升级」是错的，阈值是**单语句**的。';
ROLLBACK;
PRINT ' [L2] 已回滚';
INSERT dbo.AQ_LockLog VALUES (N'L2 分批同事务', N'每批 5000 行、同一事务，共 200 条语句；仅 1 次提交',
@tot, @maxLocks, CASE WHEN @objX = 1 THEN 1 ELSE 0 END, @try2-@tryEsc, @totMs, @totMs - @sampMs);
GO
PRINT '';
PRINT '-- 08.4 L3：分批 + 每批独立提交（正确姿势之二，但有代价） --';
PRINT ' 小事务把锁的**持有时长**从「整段作业」压缩到「单批」，这是高并发下真正的救命措施；';
PRINT ' 代价是 200 次提交各自的日志刷盘与锁获取/释放开销 —— 见 08.5 表里「纯作业耗时」的对比。';
PRINT ' ★ 这就是核心取舍：**用吞吐量换并发度**。并发写入的表宁可慢几倍，也不能锁表。';
PRINT ' ★ 本机实测代价（本地 NVMe，见 08.5「纯作业耗时」）：1 次提交约 0.5 秒 → 200 次提交约 0.6 秒，';
PRINT ' 代价很小；但远端存储、同步提交、以及真正的并发写入下，每次提交的日志刷盘与锁释放会显著放大。';
GO
SET NOCOUNT ON;
DECLARE @lo2 bigint = 1, @tot2 int = 0, @max2 int = 0, @c2 int, @rows2 int, @objX2 bit = 0,
@t1 datetime2 = SYSDATETIME(), @batches int = 0, @samp2 int = 0, @ts2 datetime2;
WHILE @lo2 <= 1000000
BEGIN
BEGIN TRAN;
UPDATE dbo.AQ_Order SET Note = N'小事务审计'
WHERE OrderId >= @lo2 AND OrderId < @lo2 + 5000
AND OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01';
SET @rows2 = @@ROWCOUNT; SET @tot2 = @tot2 + @rows2; SET @batches = @batches + 1;
IF @batches % 10 = 0
BEGIN
SET @ts2 = SYSDATETIME();
SELECT @c2 = COUNT(*) FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID AND resource_database_id = DB_ID() AND resource_type = 'KEY';
IF @c2 > @max2 SET @max2 = @c2;
IF EXISTS(SELECT 1 FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
AND resource_database_id = DB_ID() AND resource_type = 'OBJECT' AND request_mode = 'X')
SET @objX2 = 1;
SET @samp2 = @samp2 + DATEDIFF(ms, @ts2, SYSDATETIME());
END
COMMIT;
SET @lo2 = @lo2 + 5000;
END
DECLARE @totMs2 int = DATEDIFF(ms, @t1, SYSDATETIME());
PRINT ' [L3] 共更新 ' + CAST(@tot2 AS varchar(10)) + ' 行（' + CAST(@batches AS varchar(6)) + ' 个小事务）';
PRINT ' [L3] 单批 KEY 行锁峰值 = ' + CAST(@max2 AS varchar(10)) + N'（远低于 5000 升级阈值）';
PRINT ' [L3] 是否出现表级排他锁(OBJECT X) = ' + CASE WHEN @objX2 = 1 THEN N'是' ELSE N'否 ★ 未升级' END;
PRINT ' [L3] 观测总耗时(ms) = ' + CAST(@totMs2 AS varchar(12)) + N' ；其中锁采样开销 = ' + CAST(@samp2 AS varchar(12))
+ N' ；★纯作业耗时(ms) = ' + CAST(@totMs2 - @samp2 AS varchar(12));
INSERT dbo.AQ_LockLog VALUES (N'L3 分批独立提交', N'每批 5000 行、每批一个事务，共 200 次提交',
@tot2, @max2, CASE WHEN @objX2 = 1 THEN 1 ELSE 0 END, 0, @totMs2, @totMs2 - @samp2);
PRINT ' → 现场复原（把 Note 列改回原状，保证脚本可重复执行）：';
UPDATE dbo.AQ_Order SET Note = NULL WHERE Note IS NOT NULL;
PRINT ' 已重置 Note 列 ' + CAST(@@ROWCOUNT AS varchar(10)) + ' 行';
GO
PRINT '';
PRINT '-- 08.5 三种写法的对照表（本节全部结论） --';
PRINT ' ★ 横向比较请只看「纯作业耗时」：观测耗时含锁采样开销，而 L2 要采样的锁数量是 L3 的几十倍。';
SELECT 写法 = 测试项, 场景 = 场景说明, 更新行数, KEY行锁峰值,
表级X锁 = CASE WHEN 是否表级X锁 = 1 THEN N'★有（升级→锁表）' ELSE N'无（未升级）' END,
升级尝试增量 = 升级尝试次数增量, 观测总耗时 = 观测总耗时毫秒, 纯作业耗时 = 纯作业耗时毫秒
FROM dbo.AQ_LockLog ORDER BY 测试项;
PRINT ' → 结论表：**行数完全一样（都是 28,272 行）**，但「有没有升级」「锁住多久」天差地别。';
PRINT ' 这才是高并发系统里「同样的 SQL、不同的后果」的本质原因。';
PRINT '';
PRINT '-- 08.6 程序化证明：表级 X 锁与一切锁不相容 --';
PRINT ' 下表用「谁也等不到谁」的相容性规则解释了为什么一次升级能让整张表停摆：';
SELECT 请求方想要的锁 = r.请求模式, 当前已被持有 = h.持有模式,
是否相容 = CASE
WHEN h.持有模式 <> 'X' AND r.请求模式 <> 'X'
AND NOT (r.请求模式 = 'S' AND h.持有模式 IN ('U','X'))
AND NOT (r.请求模式 = 'U' AND h.持有模式 IN ('U','X'))
AND NOT (r.请求模式 = 'IX' AND h.持有模式 = 'S')
AND NOT (r.请求模式 = 'S' AND h.持有模式 = 'IX')
AND NOT (h.持有模式 = 'Sch-M')
THEN N'相容' ELSE N'★不相容（排队或超时）' END
FROM (VALUES ('S'),('U'),('X'),('IX'),('IS'),('Sch-M')) r(请求模式)
CROSS JOIN (VALUES ('S'),('U'),('X'),('IX'),('IS'),('Sch-M')) h(持有模式);
PRINT ' 读法：只要「当前已被持有」一列是 X，整行为「不相容」→ 升级后**读不到、写不进、改不了结构**。';
PRINT ' 这解释了 08.2 里为什么「毫不相关的一行」会被阻塞：冲突发生在**表**这一层，与行无关。';
PRINT '';
PRINT '-- 08.7 万亿级高并发的完整处置清单（按优先级排序） --';
SELECT 优先级 = p.序, 措施 = p.措施, 为什么有效 = p.理由
FROM (VALUES
(1, N'把大范围 DML 拆成 ≤ 5000 行的分批，且批间**独立提交**',
N'升级判定按单语句计数；分批把「锁总量」与「持有时长」同时压到安全区'),
(2, N'DML 的 WHERE 必须 SARGable，并与聚集键/分区键对齐',
N'扫描范围 = 持锁范围；非 SARGable 会让「本该 1 行的锁」变成「上百万行的锁」'),
(3, N'只更新真正变化的列，禁止 UPDATE 全列（含 ORM 的整行回写）',
N'减少行版本与索引维护量，间接减少锁数量与日志量'),
(4, N'开启 RCSI（READ_COMMITTED_SNAPSHOT），让读不拿共享锁',
N'读走行版本、不与写互相排队 —— 本机实测快照读完全不受表级 X 锁影响'),
(5, N'按时间/门店分区（万亿级必须），让大 DML 收敛到单个分区',
N'分区切换与分区级锁既缩小锁范围，也把「大范围维护」变成「换分区」'),
(6, N'按需设置 ALTER TABLE … SET (LOCK_ESCALATION = AUTO | TABLE | DISABLE)',
N'AUTO=按分区升级（分区表推荐）；DISABLE=永不升级，但会留下海量细粒度锁，须与内存权衡'),
(7, N'单独监控错误 1222 与 sys.dm_tran_locks 里的 OBJECT X',
N'1222 与死锁 1205 是两回事；只盯死锁的监控会完全漏掉这类「静默停摆」'),
(8, N'避免在同一事务里混合长耗时读与短 DML',
N'事务时长 = 锁持有时长；长事务会把短 DML 的锁一起拖长')
) AS p(序, 措施, 理由) ORDER BY p.序;
PRINT '';
PRINT '【本节一句话结论】前七条反模式让**一条查询变慢**；这一条让**一张表停摆**。';
PRINT ' 在万亿级高并发系统里，锁升级的排查优先级应高于任何索引优化。';
PRINT '';
GO
PRINT '################################################################';
PRINT '### 09 可编程反模式体检器（把上述判据固化成可调度的脚本） ###';
PRINT '################################################################';
PRINT '';
PRINT '【为什么需要它】反模式的特点是人眼看不见：SQL 文本读起来毫无问题，代价却藏在执行计划里。';
PRINT ' 本节把前面的判据固化成两个「体检器」，可直接挂到 SQL Agent 作业里每日审计：';
PRINT ' · AQ_Audit_Code —— 静态代码审计：扫描 sys.sql_modules 里的危险写法；';
PRINT ' · AQ_Audit_Plan —— 动态计划审计：扫描计划缓存，找隐式转换与残余谓词。';
PRINT '';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_Audit_Code
AS
BEGIN
SET NOCOUNT ON;
PRINT '-- 09.1 静态代码审计：在模块定义里直接命中反模式写法 --';
PRINT ' 注意：这是**文本级**启发式，会有误报（注释或字符串里的关键字也会命中）。';
PRINT ' 它的价值在于「把候选清单从上千个模块缩小到几十个」，再由人工复核。';
PRINT ' ★ 命中数不等于必须整改：要乘上「表的规模 × 执行频次」才是真实风险。';
PRINT ' ★ 副作用：本脚本自己写的 AQ_SGxx_Bad 也会被命中 —— 这恰好证明体检器有效。';
SELECT 反模式 = r.反模式, 严重度 = r.严重度, 命中模块数 = COUNT(*), 示例模块 = MIN(o.name)
FROM sys.sql_modules m
JOIN sys.objects o ON o.object_id = m.object_id
CROSS APPLY (VALUES
(N'R1 列上套 YEAR()', N'★★★', N'%YEAR(%'),
(N'R2 列上套 UPPER()/LOWER()', N'★★★', N'%UPPER(%'),
(N'R3 列上套 LEFT()/RIGHT()', N'★★★', N'%LEFT(%'),
(N'R4 列上套 DATEDIFF()', N'★★★', N'%DATEDIFF(%'),
(N'R5 前导通配 LIKE ''%…''', N'★★★', N'%LIKE ''[%]%'),
(N'R6 列侧算术（col * 1.x 之类换算因子）', N'★★', N'% * 1.%'),
(N'R7 用 ISNULL( 包住列', N'★★', N'%ISNULL(%'),
(N'R8 SELECT *', N'★★', N'%SELECT *%'),
(N'R9 存在性判断写成 COUNT(*)>0', N'○', N'%COUNT(%> 0%'),
(N'R10 UNION（未写 ALL）', N'★', N'%UNION%'),
(N'R11 深分页 OFFSET', N'★★★', N'%OFFSET%ROWS%'),
(N'R12 逐行处理 WHILE + INSERT', N'★★★', N'%WHILE%INSERT%')
) AS r(反模式, 严重度, 模式)
WHERE o.type IN ('P','V','FN','IF','TF','TR')
AND m.definition LIKE r.模式
GROUP BY r.反模式, r.严重度
ORDER BY CASE LEFT(r.严重度,3) WHEN N'★★★' THEN 1 WHEN N'★★' THEN 2 WHEN N'★' THEN 3 ELSE 4 END, r.反模式;
PRINT ' 命中为 0 的条目说明本库该写法已绝迹；命中多的条目再按「表规模 × 执行频次」定优先级。';
END
GO
CREATE OR ALTER PROCEDURE dbo.AQ_Audit_Plan
AS
BEGIN
SET NOCOUNT ON;
PRINT '-- 09.2 动态计划审计：从计划缓存里找出「隐式转换 + 残余谓词」的查询 --';
PRINT ' 判据（第 02 节实测得出）：计划含 CONVERT_IMPLICIT、查找谓词里没有 ScanType="EQ"、';
PRINT ' 且存在 <Predicate> 残余谓词 → 等值前缀已丢失，后续键列被降级为残余过滤。';
PRINT ' ★ 价值：这一批查询在原生日志里**没有任何警告**（不发 PlanAffectingConvert），只有这样才能发现。';
SELECT TOP (20)
隐式转换次数 = (LEN(plan_xml) - LEN(REPLACE(plan_xml, N'CONVERT_IMPLICIT', N''))) / LEN(N'CONVERT_IMPLICIT'),
EQ前缀数 = (LEN(plan_xml) - LEN(REPLACE(plan_xml, N'ScanType="EQ"', N''))) / 13,
有残余谓词 = CASE WHEN plan_xml LIKE N'%<Predicate>%' THEN N'★是' ELSE N'否' END,
执行次数 = execution_count,
总逻辑读 = total_logical_reads,
计划受影响转换警告 = CASE WHEN plan_xml LIKE N'%PlanAffectingConvert%'
THEN N'有' ELSE N'★无（正因此才难以发现）' END,
SQL片段 = LEFT(REPLACE(REPLACE(SUBSTRING(sql_text, 1, 150), CHAR(13), N' '), CHAR(10), N' '), 150)
FROM (
SELECT qs.execution_count, qs.total_logical_reads, txt.text AS sql_text,
CONVERT(nvarchar(max), qp.query_plan) AS plan_xml
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) txt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qp.query_plan IS NOT NULL
) p
WHERE plan_xml LIKE N'%CONVERT_IMPLICIT%'
AND plan_xml NOT LIKE N'%ScanType="EQ"%'
AND plan_xml LIKE N'%<Predicate>%'
ORDER BY total_logical_reads DESC;
PRINT '';
PRINT '-- 09.3 优先级排序：按「总逻辑读」找出最该先修的反模式查询 --';
PRINT ' 排序口径：平均逻辑读高 = 单次代价大（改它立竿见影）；执行次数高 = 影响面广（改它收益大）。';
SELECT TOP (15)
总逻辑读 = qs.total_logical_reads,
平均逻辑读 = qs.total_logical_reads / NULLIF(qs.execution_count,0),
执行次数 = qs.execution_count,
平均CPU毫秒 = CONVERT(numeric(18,3), qs.total_worker_time / NULLIF(qs.execution_count,0) / 1000.0),
含隐式转换 = CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%CONVERT_IMPLICIT%' THEN N'是' ELSE N'否' END,
含大范围扫描 = CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%PhysicalOp="Table Scan"%'
OR CONVERT(nvarchar(max), qp.query_plan) LIKE N'%PhysicalOp="Clustered Index Scan"%'
THEN N'是' ELSE N'否' END,
SQL片段 = LEFT(REPLACE(REPLACE(SUBSTRING(txt.text, 1, 130), CHAR(13), N' '), CHAR(10), N' '), 130)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) txt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC;
END
GO
EXEC dbo.AQ_Audit_Code;
EXEC dbo.AQ_Audit_Plan;
GO
PRINT '################################################################';
PRINT '### 10 决策清单：见到症状该查什么、改什么 ###';
PRINT '################################################################';
PRINT '';
SELECT 症状 = d.症状, 先查这个 = d.诊断, 修复手段 = d.修复, 参考章节 = d.章节
FROM (VALUES
(N'某查询比预期慢几十倍，计划里有 CONVERT_IMPLICIT',
N'被转换列的索引后面是否还有别的键列？计划里有无 ScanType="EQ" 与 <Predicate>',
N'参数/字面量类型与列类型对齐；字面量去掉 N 前缀', N'02'),
(N'报表页按年月筛选，走全表扫描',
N'WHERE 里是否有 YEAR()/MONTH()/DATEDIFF()/CAST() 包住日期列',
N'改成半开区间 [月初, 下月初)', N'03'),
(N'计划里出现大量「键查找」，量与行数同量级',
N'该查询是否 SELECT * / 取回了覆盖索引之外的列',
N'显式列出必要列，把高频列加进 INCLUDE', N'01'),
(N'加列之后下游程序报错或数据错位',
N'是否存在 SELECT * INTO / INSERT … SELECT * / 视图里的 SELECT *',
N'一律写显式列名；视图改动后执行 sp_refreshview', N'01'),
(N'属性表连接数随需求增长，越来越慢',
N'是否出现「N 个属性 = N 次自连接」的 EAV 查询',
N'热属性下沉为列（宽表）+ 组合索引；或建物化视图', N'04'),
(N'结果集必须加 DISTINCT 才「看起来对」',
N'JOIN 是否产生扇出（一对多）',
N'改用 EXISTS 半连接；用 CPU/内存授予而非逻辑读评估收益', N'05'),
(N'单行处理的小作业却 I/O 极高',
N'是否有 WHILE/游标 + 单行 INSERT，或谓词里调用标量 UDF',
N'改集合式 INSERT…SELECT；UDF 改内联表达式或持久化计算列', N'06'),
(N'翻到第 N 万页时接口超时',
N'是否使用 OFFSET 深分页',
N'改键集分页 WHERE key > 上页末值', N'07'),
(N'结果集有重复行，靠 UNION 去重',
N'两个分支的值域是否天然不相交',
N'语义允许时改 UNION ALL，省掉排序与去重', N'07'),
(N'监控里没有慢 SQL，但整个模块整体卡死；错误 1222',
N'sys.dm_tran_locks 是否出现 OBJECT 级 X 锁；单条 DML 触碰行数是否 > 5000',
N'分批 + 批间独立提交；WHERE 对齐索引/分区键；开启 RCSI', N'08'),
(N'百万行表上的 UPDATE 偶尔把整表锁住',
N'dm_db_index_operational_stats 的 index_lock_promotion_count 是否在增长',
N'拆批；必要时 SET (LOCK_ESCALATION = AUTO/DISABLE) 并评估锁内存', N'08'),
(N'不知道从哪个查询开始优化',
N'跑 AQ_Audit_Code / AQ_Audit_Plan 体检器',
N'按「总逻辑读 × 执行次数」排序，先修头部', N'09')
) AS d(症状, 诊断, 修复, 章节);
GO
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
GO
PRINT '################################################################';
PRINT '### 11 自检：V1–V16（本脚本的结论是否被本机数据支持） ###';
PRINT '################################################################';
PRINT '';
PRINT '【自检的意义】上一节的所有倍率都来自「一次运行」。本节的每一项都写成一个**可判定的断言**，';
PRINT ' 直接对着实测记录做校验。凡是「无法满足断言」的，就等于本脚本的结论在这一版实例上不成立 ——';
PRINT ' 那必须报告，而不是隐藏。这样脚本才能被当作「可回归的基线」反复执行。';
PRINT '';
PRINT ' （实现提示：EXEC 的参数不能直接写表达式，所以每一项都先用变量算出「是否通过」再传入。';
PRINT ' 这是 T-SQL 的实际约束，也是写「可回归自检脚本」时最常踩的一个坑。）';
PRINT '';
GO
CREATE OR ALTER PROCEDURE dbo.AQ_V @名称 nvarchar(120), @通过 bit, @说明 nvarchar(300)
AS
BEGIN
SET NOCOUNT ON;
PRINT N' ' + CASE WHEN @通过 = 1 THEN N'[通过] ' ELSE N'[★未通过]' END + N' ' + @名称 + N' —— ' + @说明;
IF OBJECT_ID('dbo.AQ_ResultCheck','U') IS NOT NULL
BEGIN
DELETE FROM dbo.AQ_ResultCheck WHERE 检查项 = @名称;
INSERT dbo.AQ_ResultCheck(检查项, 反模式读取, 修正读取, 是否通过)
VALUES (@名称, LEFT(@说明,24), N'见第 11 节判据', @通过);
END
END
GO
SET NOCOUNT ON;
DECLARE @ok bit, @msg nvarchar(300), @a numeric(12,2), @b numeric(12,2), @n bigint, @i int;
PRINT '-- V1 环境可复现性（SQL Server 2025 + 兼容级别 170 + RCSI） --';
DECLARE @compat int, @rcsi bit;
SELECT @compat = compatibility_level, @rcsi = is_read_committed_snapshot_on
FROM sys.databases WHERE name = DB_NAME();
SET @ok = CASE WHEN @compat >= 170 AND @rcsi = 1 THEN 1 ELSE 0 END;
SET @msg = N'兼容级别 = ' + CAST(@compat AS nvarchar(10)) + N'（需 ≥ 170）；RCSI = '
+ CASE WHEN @rcsi = 1 THEN N'1' ELSE N'0' END + N'（需 1）';
EXEC dbo.AQ_V N'V1 环境可复现', @ok, @msg;
PRINT '-- V2 实验台规模与脚本声明一致 --';
PRINT ' （注：本段**可能**输出一条「由于使用了本地联接提示，联接次序得以强制实施」的信息性消息 ——';
PRINT ' 它由 sys.dm_db_column_store_row_group_physical_stats 的内部实现产生，不是错误，可忽略；';
PRINT ' 它是否出现取决于计划缓存状态，因此不构成「非预期错误」。顺带一个冷知识：';
PRINT ' 同一个列存表的行数在这个 DMV 里是准确的 5,000,000，而在 sys.dm_db_partition_stats 里';
PRINT ' 只报 1,000 —— 统计列存行数必须用行组 DMV。）';
DECLARE @n1 bigint, @n2 bigint, @n3 bigint, @n4 bigint;
SELECT @n1 = SUM(row_count) FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('dbo.AQ_Order') AND index_id = 1;
SELECT @n2 = SUM(row_count) FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('dbo.AQ_OrderLine') AND index_id = 1;
SELECT @n3 = SUM(row_count) FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('dbo.AQ_ProductAttribute') AND index_id = 1;
SELECT @n4 = SUM(total_rows) FROM sys.dm_db_column_store_row_group_physical_stats WHERE object_id = OBJECT_ID('rpt.OrgSalesFact');
SET @ok = CASE WHEN @n1 = 1000000 AND @n2 = 2000000 AND @n3 = 600000 AND @n4 = 5000000 THEN 1 ELSE 0 END;
SET @msg = N'AQ_Order=' + CAST(@n1 AS nvarchar(12)) + N'（期望 1,000,000）；AQ_OrderLine=' + CAST(@n2 AS nvarchar(12))
+ N'；EAV=' + CAST(@n3 AS nvarchar(12)) + N'；列存=' + CAST(@n4 AS nvarchar(12));
EXEC dbo.AQ_V N'V2 实验台规模一致', @ok, @msg;
PRINT '-- V3 对比用例完整性：29 组 AQ_Cmp 对照 + 1 组三态元数据记录 = 30 行 --';
SET @i = (SELECT COUNT(*) FROM dbo.AQ_ResultCheck WHERE 检查项 NOT LIKE N'V%');
SET @ok = CASE WHEN @i = 30 THEN 1 ELSE 0 END;
SET @msg = N'结果表实收 ' + CAST(@i AS nvarchar(10)) + N' 行（期望 30 = 29 组对照 + 1 组元数据）';
EXEC dbo.AQ_V N'V3 对比用例完整', @ok, @msg;
PRINT '-- V4 隐式转换组的危害（P1/P2/P5 均应 ≥ 10 倍） --';
SET @a = (SELECT MIN(读取倍数) FROM dbo.AQ_ResultCheck
WHERE 检查项 IN (N'P1_char2_N字面量', N'P2_char2_nvarchar参数', N'P5_Status_N字面量'));
SET @ok = CASE WHEN ISNULL(@a,0) >= 10 THEN 1 ELSE 0 END;
SET @msg = N'三组 char/varchar 复合索引场景的最小读取倍数 = ' + ISNULL(CAST(@a AS nvarchar(20)),N'无数据') + N'（期望 ≥ 10）';
EXEC dbo.AQ_V N'V4 隐式转换危害成立', @ok, @msg;
PRINT '-- V5 单列唯一索引的隐式转换「零损失」（P3/P4 应为 1.00×） --';
SET @a = (SELECT MAX(读取倍数) FROM dbo.AQ_ResultCheck
WHERE 检查项 IN (N'P3_varchar20_N字面量_无损失', N'P4_varchar20_nvarchar参数_无损失'));
SET @ok = CASE WHEN ISNULL(@a,99) = 1 THEN 1 ELSE 0 END;
SET @msg = N'P3/P4 的最大读取倍数 = ' + ISNULL(CAST(@a AS nvarchar(20)),N'无数据') + N'（期望 = 1.00，即零损失）';
EXEC dbo.AQ_V N'V5 单列唯一索引零损失', @ok, @msg;
PRINT '-- V6 「已被优化器自动修复」组应无差异（S2/S9/S10/P7/P8/E2 均应 ≤ 1.6×） --';
SET @a = (SELECT MAX(读取倍数) FROM dbo.AQ_ResultCheck
WHERE 检查项 IN (N'S2_CAST_date_小差异', N'S9_ISNULL_不可空列无害', N'S10_CAST_decimal_int_无害',
N'P7_date列datetime参数_无害', N'P8_int列字符串参数_无害', N'E2_COUNT对比EXISTS_无差异'));
SET @ok = CASE WHEN ISNULL(@a,99) <= 1.6 THEN 1 ELSE 0 END;
SET @msg = N'六组「现代优化器已自动改写」用例的最大读取倍数 = ' + ISNULL(CAST(@a AS nvarchar(20)),N'无数据') + N'（期望 ≤ 1.6）';
EXEC dbo.AQ_V N'V6 优化器自动修复组成立', @ok, @msg;
PRINT '-- V7 「致命级」非 SARGable（读取倍数 ≥ 100）至少 4 组 --';
SET @i = (SELECT COUNT(*) FROM dbo.AQ_ResultCheck WHERE 检查项 NOT LIKE N'V%' AND 读取倍数 >= 100);
SET @ok = CASE WHEN @i >= 4 THEN 1 ELSE 0 END;
SET @msg = N'读取倍数 ≥ 100 的用例数 = ' + CAST(@i AS nvarchar(10)) + N'（期望 ≥ 4）';
EXEC dbo.AQ_V N'V7 致命级用例数量', @ok, @msg;
PRINT '-- V8 列存的特殊表现：读取倍数 = 1.00，但 CPU 倍数达数十倍（口径必须换） --';
DECLARE @colR numeric(12,2), @colC numeric(12,2);
SELECT @colR = 读取倍数, @colC = CPU倍数 FROM dbo.AQ_ResultCheck WHERE 检查项 = N'S11_列存非SARGable_代价在CPU';
SET @ok = CASE WHEN ISNULL(@colR,0) = 1 AND ISNULL(@colC,0) >= 10 THEN 1 ELSE 0 END;
SET @msg = N'S11 读取倍数 = ' + ISNULL(CAST(@colR AS nvarchar(20)),N'无') + N'（期望 1.00，即「逻辑读看不出差别」），CPU 倍数 = '
+ ISNULL(CAST(@colC AS nvarchar(20)),N'无') + N'（期望 ≥ 10；实测在数十倍至上百倍之间浮动）';
EXEC dbo.AQ_V N'V8 列存代价在CPU不在读', @ok, @msg;
PRINT '-- V9 深分页 OFFSET 的成本（G1 ≥ 500×） --';
SET @a = (SELECT 读取倍数 FROM dbo.AQ_ResultCheck WHERE 检查项 = N'G1_OFFSET深分页');
SET @ok = CASE WHEN ISNULL(@a,0) >= 500 THEN 1 ELSE 0 END;
SET @msg = N'G1 读取倍数 = ' + ISNULL(CAST(@a AS nvarchar(20)),N'无数据') + N'（期望 ≥ 500）';
EXEC dbo.AQ_V N'V9 深分页代价成立', @ok, @msg;
PRINT '-- V10 RBAR 逐行处理（U3 ≥ 10×） --';
SET @a = (SELECT 读取倍数 FROM dbo.AQ_ResultCheck WHERE 检查项 = N'U3_RBAR逐行插入');
SET @ok = CASE WHEN ISNULL(@a,0) >= 10 THEN 1 ELSE 0 END;
SET @msg = N'U3 读取倍数 = ' + ISNULL(CAST(@a AS nvarchar(20)),N'无数据') + N'（期望 ≥ 10）';
EXEC dbo.AQ_V N'V10 RBAR 代价成立', @ok, @msg;
PRINT '-- V11 过度范式化过滤场景（A1 ≥ 10×） --';
SET @a = (SELECT 读取倍数 FROM dbo.AQ_ResultCheck WHERE 检查项 = N'A1_EAV_3连接');
SET @ok = CASE WHEN ISNULL(@a,0) >= 10 THEN 1 ELSE 0 END;
SET @msg = N'A1 读取倍数 = ' + ISNULL(CAST(@a AS nvarchar(20)),N'无数据') + N'（期望 ≥ 10）';
EXEC dbo.AQ_V N'V11 EAV 过滤代价成立', @ok, @msg;
PRINT '-- V12 锁升级已真实发生（L1：表级 X 锁 + promotion 计数器增量） --';
DECLARE @l1x bit, @l1p int, @l1r int;
SELECT @l1x = 是否表级X锁, @l1p = 升级尝试次数增量, @l1r = 更新行数
FROM dbo.AQ_LockLog WHERE 测试项 = N'L1 单条大UPDATE';
SET @ok = CASE WHEN @l1x = 1 AND ISNULL(@l1p,0) >= 1 THEN 1 ELSE 0 END;
SET @msg = N'L1 更新 ' + ISNULL(CAST(@l1r AS nvarchar(12)),N'?') + N' 行；表级 X 锁 = '
+ CASE WHEN @l1x = 1 THEN N'1' ELSE N'0' END + N'（期望 1）；升级尝试增量 = '
+ ISNULL(CAST(@l1p AS nvarchar(10)),N'?') + N'（期望 ≥ 1）';
EXEC dbo.AQ_V N'V12 锁升级已发生', @ok, @msg;
PRINT '-- V13 分批同事务不升级（L2：行锁数远超阈值但无表级 X 锁） --';
DECLARE @l2x bit, @l2k int;
SELECT @l2x = 是否表级X锁, @l2k = KEY行锁峰值 FROM dbo.AQ_LockLog WHERE 测试项 = N'L2 分批同事务';
SET @ok = CASE WHEN @l2x = 0 AND ISNULL(@l2k,0) > 5000 THEN 1 ELSE 0 END;
SET @msg = N'L2 KEY 行锁峰值 = ' + ISNULL(CAST(@l2k AS nvarchar(12)),N'?') + N'（> 5000，说明「锁总数超阈值」并不触发升级）；表级 X 锁 = '
+ CASE WHEN @l2x = 1 THEN N'1' ELSE N'0' END + N'（期望 0）';
EXEC dbo.AQ_V N'V13 分批同事务不升级', @ok, @msg;
PRINT '-- V14 小事务把锁峰值压在升级阈值之下（L3 < 5000） --';
DECLARE @l3k int, @l3x bit;
SELECT @l3k = KEY行锁峰值, @l3x = 是否表级X锁 FROM dbo.AQ_LockLog WHERE 测试项 = N'L3 分批独立提交';
SET @ok = CASE WHEN ISNULL(@l3k,99999) < 5000 AND @l3x = 0 THEN 1 ELSE 0 END;
SET @msg = N'L3 单批 KEY 行锁峰值 = ' + ISNULL(CAST(@l3k AS nvarchar(12)),N'?') + N'（期望 < 5000）；表级 X 锁 = '
+ CASE WHEN @l3x = 1 THEN N'1' ELSE N'0' END + N'（期望 0）';
EXEC dbo.AQ_V N'V14 小事务锁峰值低于阈值', @ok, @msg;
PRINT '-- V15 全部对照的「判定一致性」：未通过用例必须能用「指标口径」解释 --';
SET @i = (SELECT COUNT(*) FROM dbo.AQ_ResultCheck WHERE 检查项 NOT LIKE N'V%' AND 是否通过 = 0);
SET @ok = CASE WHEN @i <= 2 THEN 1 ELSE 0 END;
SET @msg = N'「反模式逻辑读 ≥ 修正逻辑读」不成立的用例数 = ' + CAST(@i AS nvarchar(10))
+ N'（期望 ≤ 2：允许 E1 这类「逻辑读口径不适用」的用例，详见下方清单）';
EXEC dbo.AQ_V N'V15 判定一致性', @ok, @msg;
PRINT ' ── 需要按「指标口径」解释的用例清单 ──';
SELECT 用例 = 检查项, 反模式读取, 修正读取, 读取倍数, CPU倍数, 反模式报错,
解释 = CASE WHEN 检查项 = N'E1_DISTINCT掩盖扇出'
THEN N'修正版逻辑读更高，但 CPU 与耗时全面占优：扇出+DISTINCT 的代价不在逻辑读'
WHEN 反模式报错 IS NOT NULL THEN N'反模式版直接报错（比任何读取差异都严重）'
ELSE N'读相同或更低，但 CPU/耗时仍占优，属「指标口径需修正」' END
FROM dbo.AQ_ResultCheck WHERE 检查项 NOT LIKE N'V%' AND 是否通过 = 0 ORDER BY 检查项;
IF @@ROWCOUNT = 0
PRINT ' （本次无：全部对照都满足「反模式逻辑读 ≥ 修正逻辑读」，不存在需要用指标口径解释的例外。）';
PRINT '';
PRINT '-- V16 自检总览 --';
DECLARE @vtot int, @vok int;
SELECT @vtot = COUNT(*), @vok = SUM(CASE WHEN 是否通过 = 1 THEN 1 ELSE 0 END)
FROM dbo.AQ_ResultCheck WHERE 检查项 LIKE N'V%';
SELECT 自检项总数 = @vtot, 通过数 = @vok, 未通过数 = @vtot - @vok,
结论 = CASE WHEN @vtot = @vok THEN N'★ 全部通过' ELSE N'★ 有未通过项，见上方明细' END;
PRINT '';
GO
PRINT '################################################################';
PRINT '### 12 总结：反模式实测危害总表 ###';
PRINT '################################################################';
PRINT '';
PRINT '-- 12.1 全部对照结果（按章节与危害倍数排序） --';
SELECT 章节 = CASE LEFT(检查项,1) WHEN N'K' THEN N'01 SELECT * / 元数据' WHEN N'P' THEN N'02 隐式转换'
WHEN N'S' THEN N'03 非SARGable' WHEN N'A' THEN N'04 过度范式化'
WHEN N'E' THEN N'05 DISTINCT与COUNT' WHEN N'U' THEN N'06 UDF与RBAR'
WHEN N'N' THEN N'07 UNION' WHEN N'G' THEN N'07 深分页'
ELSE N'99 其他' END,
用例 = 检查项, 反模式逻辑读 = 反模式读取, 修正后逻辑读 = 修正读取,
读取倍数 = 读取倍数, CPU倍数 = CPU倍数, 反模式报错 = 反模式报错
FROM dbo.AQ_ResultCheck
WHERE 检查项 NOT LIKE N'V%'
ORDER BY 章节, ISNULL(读取倍数, 1) DESC;
PRINT '';
PRINT '-- 12.2 锁升级实测总表（第 08 节） --';
SELECT 写法 = 测试项, 场景 = 场景说明, 更新行数, KEY行锁峰值,
表级X锁 = CASE WHEN 是否表级X锁 = 1 THEN N'★有（升级→锁表）' ELSE N'无（未升级）' END,
升级尝试增量 = 升级尝试次数增量, 观测总耗时 = 观测总耗时毫秒, 纯作业耗时 = 纯作业耗时毫秒
FROM dbo.AQ_LockLog ORDER BY 测试项;
PRINT '';
PRINT '-- 12.3 本脚本推翻了哪些「流行说法」（诚实记录，全部本机实测） --';
SELECT 序号 = v.序, 流行说法 = v.说法, 实测结论 = v.实测, 证据 = v.证据
FROM (VALUES
(1, N'隐式转换会导致全表扫描',
N'不会。优化器用 GetRangeThroughConvert 造出范围查找；真正的危害是「被转换列之后的键列被降级为残余谓词」',
N'P1/P2/P5 为 26～31 倍；P3/P4（单列唯一索引、无后续键列）= 1.00 倍零损失'),
(2, N'计划里出现 CONVERT_IMPLICIT 就一定有性能问题',
N'不一定。要看转换发生在哪一侧、以及查找谓词里是否还有等值前缀',
N'P8（bigint 列 × nvarchar 参数）= 1.00 倍，计划里却有 1 次 CONVERT_IMPLICIT'),
(3, N'ISNULL / CAST 包住列一定会破坏索引',
N'在 NOT NULL 列上、以及单调 CAST 上，SQL Server 2025 已能自动等价改写',
N'S9（ISNULL）1.00 倍、S10（CAST decimal→int）1.00 倍、S2（CAST datetime2→date）1.50 倍'),
(4, N'EXISTS 一定比 COUNT(*)>0 快',
N'已被优化器改写成「取一行即停」，收益为 0',
N'E2 = 4 读 vs 4 读 = 1.00 倍（仍然推荐 EXISTS，理由是语义清晰与跨引擎可移植）'),
(5, N'用 DISTINCT 掩盖 JOIN 扇出只要改成 EXISTS 就能提速',
N'SQL Server 2025 已自动把「JOIN 后 DISTINCT」改写为半连接，两版计划逐算子相同、读取与 CPU 无差',
N'E1 = 两者均为 13,424 读、13,424 读，1.00 倍，行数同为 4,039（改写价值在可读性与健壮性）'),
(6, N'深分页 OFFSET 只是稍微慢一点',
N'成本随页码线性增长，第 25001 页时已达 2000 倍量级',
N'G1 = 六倍于 1000 的读取量 vs 3 读（本次约 6,400 读 / 3 读）'),
(7, N'把大 UPDATE 分批就一定不会锁升级',
N'不完整。阈值是**单语句**的：同事务内分批后再累计也不升级，但前提是「每批本身」不超过阈值；',
N'L2 累计 28,272 个行锁仍无 OBJECT X —— 一旦某批自身超阈值，照样升级'),
(8, N'锁总数超过 5000 就会升级',
N'错。判定只看**单条语句**的锁数量，与事务内累计无关',
N'L2 = 28,272 个 KEY 行锁，仍无表级 X 锁'),
(9, N'列存上非 SARGable 会让逻辑读暴涨',
N'错。列存逻辑读完全相同，代价在 CPU 与「段消除/批处理模式」失效',
N'S11 读取 1.00 倍，而 CPU 差异达数十倍至上百倍'),
(10, N'INSERT … SELECT * 加列后报的是错误 213',
N'本机实测报的是 **121**（选择列表项多于插入列表项）',
N'K2 实测 121；存储过程中的 SELECT * 会自动跟上新列，只有**视图**里的 SELECT * 才是创建时快照'),
(11, N'过度范式化（EAV）总是更慢',
N'取决于「每条查询路径是否都有匹配的覆盖索引」：过滤场景 EAV 输很多，取属性场景差距小得多',
N'A1（只过滤）32 倍以上 vs A2（取回 6 个属性）约 4.7 倍')
) AS v(序, 说法, 实测, 证据) ORDER BY v.序;
PRINT '';
PRINT '-- 12.4 一页纸行动清单 --';
PRINT ' ① 先跑体检器：EXEC dbo.AQ_Audit_Code; EXEC dbo.AQ_Audit_Plan; 按「总逻辑读 × 执行次数」排序整改。';
PRINT ' ② 参数类型 = 列类型；字面量不加 N 前缀（除非列确实是 Unicode）。';
PRINT ' ③ 日期范围一律写半开区间 [起, 止)，禁止在列上套 YEAR/MONTH/CAST/DATEDIFF。';
PRINT ' ④ 只取需要的列，为高频查询补足覆盖索引的 INCLUDE 列。';
PRINT ' ⑤ 存在性判断用 EXISTS；JOIN 有扇出时先想「语义是否本就该去重」。';
PRINT ' ⑥ 翻页用键集分页；集合运算用集合式写法，不要 WHILE 逐行。';
PRINT ' ⑦ 任何可能触碰 > 5000 行的 DML：分批 + 批间独立提交 + WHERE 对齐索引/分区键。';
PRINT ' ⑧ 评估优化效果要同时看「逻辑读 / CPU / 耗时 / 内存授予」—— 只看逻辑读会得出错误结论（见 E1 与 S11）。';
PRINT '';
PRINT '================================================================';
PRINT ' 脚本执行完毕。实验对象（AQ_* 表、UDF 与存储过程）被**有意保留**；';
PRINT ' 下一次运行时 00-B 节会自动清理 AQ_* 存储过程与度量表，结果保持一致。';
PRINT '================================================================';
GO
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文）
浙公网安备 33010602011771号