SQL Server 常用运维指令手册

适用版本：SQL Server 2017 / 2019 / 2022 / 2025（Windows 与 Linux 均适用） 约定：# root 或 sudo，> Windows 命令行，sqlcmd> sqlcmd 提示符，<xxx> 为占位符。 差异标注：正文用 > **版本差异** 引用块标注跨版本不一致之处。 更新时间：2026-11

目录

1. 版本与支持周期
2. 下载地址与安装
3. 服务管理与启动参数
4. sqlcmd 与客户端连接
5. 实例配置与 sp_configure
6. 登录名、用户与权限
7. 数据库与文件管理
8. 恢复模式与日志管理
9. 备份与恢复
10. 高可用：Always On 与日志传送
11. 日志体系与错误排查
12. 性能诊断：DMV 与 Query Store
13. 锁、阻塞与死锁
14. 空间与容量管理
15. tempdb 与内存管理
16. 升级、迁移与补丁
17. 高频故障速查
18. 危险命令与避坑清单
19. 附录：巡检脚本与 DMV 速查

1. 版本与支持周期

1.1 版本对照

注意：SQL Server 采用"主流支持 5 年 + 扩展支持 5 年"模型。扩展支持期只有安全更新，没有功能修复。2016 已于 2026-07 完全退出支持，仍在使用需尽快升级。

1.2 版本识别

SELECT @@VERSION;
-- Microsoft SQL Server 2025 (RTM-CU10) - 17.0.5035.5 (X64) ...

SELECT SERVERPROPERTY('ProductVersion')  AS product_version,   -- 17.0.5035.5
       SERVERPROPERTY('ProductLevel')    AS patch_level,       -- RTM / SP1 / CU10
       SERVERPROPERTY('Edition')         AS edition,           -- Enterprise / Standard / Express
       SERVERPROPERTY('ProductUpdateLevel') AS cu,             -- CU10
       SERVERPROPERTY('Collation')       AS collation,
       SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_auth_only;

# Linux
/opt/mssql/bin/mssql-conf traceflag 1234 off
cat /var/opt/mssql/mssql.conf

1.3 版本与功能限制

避坑：Standard 版做 Always On 只能 2 个副本且无只读路由，规划高可用前先确认版本，否则方案到实施才发现不可行。

2. 下载地址与安装

2.1 官方入口总表

许可说明：Developer 版功能等同 Enterprise，仅限开发测试；生产必须购买 Standard/Enterprise 授权，或使用 Express（免费但有容量限制）。

2.2 Windows 安装

图形安装要点：

1. 选择 Developer 或 Express 版本。
2. 实例选择：默认实例（MSSQLSERVER，端口 1433）或命名实例（MSSQL$INST01，动态端口）。
3. 服务账户：建议用域账户或虚拟账户 NT Service\MSSQLSERVER，不要用 LocalSystem。
4. 排序规则：Chinese_PRC_CI_AS 或 Latin1_General_100_CI_AS_SC_UTF8（UTF-8 需 2019+）。
5. 认证模式：混合模式（SQL 登录 + Windows 认证），并设置 sa 密码。
6. 数据目录：把 data/log/backup 分盘存放。

静默安装（命令行）：

setup.exe /Q /ACTION=Install /FEATURES=SQLENGINE /INSTANCENAME=MSSQLSERVER ^
  /SECURITYMODE=SQL /SAPWD="Str0ng#Pass2026" ^
  /SQLSVCACCOUNT="NT Service\MSSQLSERVER" /SQLSYSADMINACCOUNTS="BUILTIN\Administrators" ^
  /AGTSVCACCOUNT="NT Service\SQLSERVERAGENT" ^
  /SQLCOLLATION="Chinese_PRC_CI_AS" ^
  /SQLDATADIR="D:\MSSQL\Data" /SQLUSERDBLOGDIR="E:\MSSQL\Log" /SQLBACKUPDIR="F:\MSSQL\Backup" ^
  /TCPENABLED=1 /NPENABLED=0 /IACCEPTSQLSERVERLICENSETERMS

2.3 Linux 安装（RHEL / Ubuntu）

# RHEL 8/9
curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/9/mssql-server-2025.repo
dnf install -y mssql-server
/opt/mssql/bin/mssql-conf setup
systemctl enable --now mssql-server

# Ubuntu 22.04/24.04
curl https://packages.microsoft.com/keys/microsoft.asc | gpg --dearmor -o /usr/share/keyrings/microsoft-prod.gpg
curl -o /etc/apt/sources.list.d/mssql-server.list \
  https://packages.microsoft.com/config/ubuntu/24.04/mssql-server-2025.list
apt update && apt install -y mssql-server
/opt/mssql/bin/mssql-conf setup
systemctl enable --now mssql-server

mssql-conf setup 交互项：版本（Developer/Express/Standard/Enterprise）、语言、sa 密码（需满足复杂度：8 位以上，含大小写、数字、符号）。

# 非交互
MSSQL_PID=Developer ACCEPT_EULA=Y MSSQL_SA_PASSWORD='Str0ng#Pass2026' \
  /opt/mssql/bin/mssql-conf -n setup

# 安装命令行工具
curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/9/prod.repo
dnf install -y mssql-tools18 unixODBC-devel
echo 'export PATH=$PATH:/opt/mssql-tools18/bin' >> /etc/profile.d/mssql.sh

2.4 Docker 部署

docker run -d --name mssql2025 \
  --restart unless-stopped \
  -e ACCEPT_EULA=Y \
  -e MSSQL_SA_PASSWORD='Str0ng#Pass2026' \
  -e MSSQL_PID=Developer \
  -p 1433:1433 \
  -v /data/mssql:/var/opt/mssql \
  mcr.microsoft.com/mssql/server:2025-latest

docker logs -f mssql2025
docker exec -it mssql2025 /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P 'Str0ng#Pass2026' -C

注意：MSSQL_SA_PASSWORD 不满足复杂度要求容器会直接退出，日志里提示密码策略。至少 8 位且包含三类字符。

2.5 安装后必做

-- 1) 确认版本与实例
SELECT @@SERVERNAME, @@VERSION;
SELECT SERVERPROPERTY('InstanceName'), SERVERPROPERTY('IsClustered');

-- 2) 确认认证模式（1 = 仅 Windows，0 = 混合）
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly');

-- 3) 限制最大内存（生产必须，否则会吃光系统内存）
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 28672; RECONFIGURE;

-- 4) 开启远程连接与 TCP 端口（Linux 默认已开）
EXEC xp_readerrorlog 0, 1, N'Server is listening on';

-- 5) 改 sa 密码
ALTER LOGIN sa WITH PASSWORD = 'NewStr0ng#2026';

# 防火墙放行
firewall-cmd --add-port=1433/tcp --permanent && firewall-cmd --reload

3. 服务管理与启动参数

3.1 服务清单

3.2 Windows

Get-Service MSSQLSERVER
Start-Service MSSQLSERVER
Stop-Service MSSQLSERVER
Restart-Service MSSQLSERVER
Set-Service MSSQLSERVER -StartupType Automatic
net start MSSQLSERVER
net stop MSSQLSERVER
sc query MSSQLSERVER

# 命名实例
Get-Service 'MSSQL$INST01'
net start 'MSSQL$INST01'

3.3 Linux

systemctl start mssql-server
systemctl stop mssql-server
systemctl restart mssql-server
systemctl status mssql-server
systemctl enable mssql-server
journalctl -u mssql-server -n 200 --no-pager

3.4 启动参数（跟踪标志）

# Linux：在 mssql.conf 中配置
cat /var/opt/mssql/mssql.conf

[sqlagent]
enabled = true

[memory]
memorylimitmb = 28672

[traceflag]
traceflag0 = 3226
traceflag1 = 1117
traceflag2 = 1118

# Windows：SQL Server 配置管理器 → 启动参数，或用注册表
# 常用跟踪标志：
# 3226  备份成功不写 errorlog（减少日志噪音）
# 1117  数据文件统一增长
# 1118  统一区分配（2016 起默认由混合区分配替代，通常不需要）
# 1222  死锁信息写入 errorlog
# 1204  记录死锁资源与锁（老版本）
# 4199  查询优化器修复全部开启（2016 起默认行为随兼容级别）

建议：生产至少开 3226（备份日志噪音）与 1222（死锁可追溯）。其他标志按实际问题加，不要盲抄网上的"性能优化跟踪标志"清单。

3.5 重启前的检查

-- 有没有正在跑的作业/长事务
SELECT session_id, status, command, wait_type, blocking_session_id, cpu_time, total_elapsed_time
FROM sys.dm_exec_requests WHERE session_id > 50;

-- 有没有未提交事务
SELECT * FROM sys.dm_tran_active_transactions;

-- 检查点，减少重启恢复时间
CHECKPOINT;

4. sqlcmd 与客户端连接

4.1 基本连接

sqlcmd -S localhost -U sa -P 'Str0ng#Pass2026' -C
sqlcmd -S 10.0.0.11,1433 -U app -P 'App#2026' -d appdb -C
sqlcmd -S "tcp:10.0.0.11,1433" -U app -P 'App#2026' -d appdb -N -C
sqlcmd -S localhost -E                       # Windows 集成认证
sqlcmd -S localhost -U sa -P 'xxx' -Q "SELECT @@VERSION"
sqlcmd -S localhost -U sa -P 'xxx' -i script.sql -o out.txt

关键参数：

# 脚本化标准写法
sqlcmd -S localhost -U sa -P "$SA_PWD" -d master -b -r1 -W -s "," -Q "SET NOCOUNT ON; SELECT name FROM sys.databases"

4.2 常用命令（sqlcmd 内）

sqlcmd> :help
sqlcmd> :list
sqlcmd> :r /tmp/other.sql        -- 引入脚本
sqlcmd> :setvar dbname appdb
sqlcmd> :exit
sqlcmd> GO                       -- 批处理结束符（不是分号）

关键：T-SQL 用 GO 分隔批，不是分号。CREATE VIEW、CREATE PROCEDURE 必须是批内第一条语句，前面要有 GO。

4.3 连接排障

# 1) 端口通不通
ss -lntp | grep 1433
telnet 10.0.0.11 1433
nc -zv 10.0.0.11 1433

# 2) 实例在不在
sqlcmd -S localhost -U sa -P 'xxx' -Q "SELECT 1"

# 3) 命名实例的端口（需 SQL Browser）
sqlcmd -S 10.0.0.11\INST01 -U sa -P 'xxx'

# 4) 强制 TCP 并指定端口
sqlcmd -S "tcp:10.0.0.11,1433" -U sa -P 'xxx'

常见错误：

5. 实例配置与 sp_configure

5.1 查看配置

EXEC sp_configure;                                  -- 只显示常用项
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure;                                  -- 显示全部

SELECT name, value, value_in_use, minimum, maximum, is_dynamic, is_advanced
FROM sys.configurations ORDER BY name;

value 是配置值，value_in_use 是当前生效值。is_dynamic=1 的改完 RECONFIGURE 立即生效，0 的需要重启服务。

5.2 修改配置

EXEC sp_configure 'max server memory (MB)', 28672;
RECONFIGURE;                                        -- 动态项立即生效

EXEC sp_configure 'max degree of parallelism', 8;
RECONFIGURE;

EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

注意：RECONFIGURE 只对动态项生效，静态项（如 min server memory）需要 RECONFIGURE WITH OVERRIDE + 重启服务。改完必须验证 value_in_use。

5.3 生产必调参数

-- 查看内存使用
SELECT physical_memory_in_use_kb/1024 AS physical_mb,
       large_page_allocations_kb/1024 AS large_page_mb,
       locked_page_allocations_kb/1024 AS locked_mb,
       total_virtual_address_space_kb/1024 AS vas_mb,
       virtual_address_space_reserved_kb/1024 AS vas_reserved_mb,
       page_fault_count
FROM sys.dm_os_process_memory;

SELECT counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%' AND counter_name IN ('Total Pages','Database pages','Target pages');

6. 登录名、用户与权限

6.1 登录名（实例级）与用户（库级）

-- 实例级登录名
CREATE LOGIN app WITH PASSWORD = 'App#Str0ng2026', CHECK_POLICY = ON;
CREATE LOGIN app FROM WINDOWS;                       -- Windows 账号
ALTER LOGIN app WITH PASSWORD = 'New#2026';
ALTER LOGIN app DISABLE;
ALTER LOGIN app ENABLE;
DROP LOGIN app;

-- 查看
SELECT name, type_desc, is_disabled, create_date, default_database_name
FROM sys.server_principals WHERE type IN ('S','U','G');

-- 库级用户（映射到登录名）
USE appdb;
CREATE USER app FOR LOGIN app;
CREATE USER app FOR LOGIN app WITH DEFAULT_SCHEMA = app;
ALTER USER app WITH DEFAULT_SCHEMA = dbo;
DROP USER app;

-- 包含数据库用户（不依赖实例登录名，适合 AG 故障转移）
ALTER DATABASE appdb SET CONTAINMENT = PARTIAL;
CREATE USER app WITH PASSWORD = 'App#2026';

重要：CREATE LOGIN 是实例级、CREATE USER 是库级。恢复数据库到另一台实例后经常出现"孤儿用户"（SID 对不上），需要用 ALTER USER ... WITH LOGIN = ... 或 sp_change_users_login 修复。

6.2 授权

-- 服务器级角色
ALTER SERVER ROLE sysadmin ADD MEMBER app;
ALTER SERVER ROLE securityadmin ADD MEMBER dba;
ALTER SERVER ROLE dbcreator ADD MEMBER app;
SELECT * FROM sys.server_role_members;

-- 数据库级角色
USE appdb;
ALTER ROLE db_datareader ADD MEMBER report;
ALTER ROLE db_datawriter ADD MEMBER app;
ALTER ROLE db_owner ADD MEMBER dba;
ALTER ROLE db_ddladmin ADD MEMBER deploy;

-- 对象级
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.orders TO app;
GRANT SELECT ON dbo.v_orders TO report;
GRANT EXECUTE ON dbo.p_calc TO app;
DENY DELETE ON dbo.orders TO app;                    -- DENY 优先于 GRANT
REVOKE SELECT ON dbo.orders FROM report;

-- 查看
SELECT * FROM sys.database_permissions WHERE grantee_principal_id = USER_ID('app');
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');

6.3 权限层级

服务器级：登录名 → 服务器角色（sysadmin / securityadmin / dbcreator ...）
数据库级：用户 → 数据库角色（db_owner / db_datareader ...）→ schema → 对象 → 列

注意：SQL Server 的 DENY 优先级高于 GRANT，且 sysadmin 成员绕过所有权限检查。排障时先用 SELECT IS_SRVROLEMEMBER('sysadmin') 确认。

6.4 安全基线

-- 1) 禁用 sa 或改名
ALTER LOGIN sa DISABLE;
-- 或
ALTER LOGIN sa WITH NAME = sysadmin_renamed;

-- 2) 只允许 Windows 认证（若业务允许）
-- SSMS → 服务器属性 → 安全性 → 仅 Windows 身份验证模式

-- 3) 检查空密码/弱密码登录名
SELECT name, is_disabled, is_policy_checked, is_expiration_checked
FROM sys.sql_logins;

-- 4) 强制加密
-- 服务器属性 → 连接 → 强制加密
SELECT session_id, encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID;

-- 5) 审计登录失败
-- SSMS → 安全性 → 审核 → 新建审核 + 服务器审核规范

7. 数据库与文件管理

7.1 库操作

CREATE DATABASE appdb
ON PRIMARY (
  NAME = appdb_data, FILENAME = 'D:\MSSQL\Data\appdb.mdf',
  SIZE = 1GB, FILEGROWTH = 512MB
)
LOG ON (
  NAME = appdb_log, FILENAME = 'E:\MSSQL\Log\appdb.ldf',
  SIZE = 2GB, FILEGROWTH = 512MB
);
GO

ALTER DATABASE appdb SET RECOVERY FULL;
ALTER DATABASE appdb SET AUTO_CLOSE OFF;
ALTER DATABASE appdb SET AUTO_SHRINK OFF;          -- 必须关，否则性能灾难
ALTER DATABASE appdb SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE appdb SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE appdb SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
ALTER DATABASE appdb SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE appdb MODIFY NAME = appdb2;
ALTER DATABASE appdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;   -- 危险
ALTER DATABASE appdb SET MULTI_USER;

DROP DATABASE appdb;                                -- 危险

-- 查看
SELECT name, database_id, state_desc, recovery_model_desc, compatibility_level,
       containment_desc, is_read_only
FROM sys.databases;

SELECT name, state_desc, user_access_desc, recovery_model_desc
FROM sys.databases WHERE database_id = DB_ID('appdb');

7.2 文件与文件组

-- 加数据文件
ALTER DATABASE appdb ADD FILE (
  NAME = appdb_data2, FILENAME = 'D:\MSSQL\Data\appdb2.ndf',
  SIZE = 4GB, FILEGROWTH = 1GB
) TO FILEGROUP [PRIMARY];

-- 加文件组（把大表/归档放到独立文件组）
ALTER DATABASE appdb ADD FILEGROUP fg_archive;
ALTER DATABASE appdb ADD FILE (
  NAME = appdb_arch, FILENAME = 'F:\MSSQL\Archive\appdb_arch.ndf',
  SIZE = 8GB, FILEGROWTH = 1GB
) TO FILEGROUP fg_archive;

-- 迁移表到文件组（重建表）
CREATE UNIQUE CLUSTERED INDEX PK_orders ON dbo.orders(id) WITH (DROP_EXISTING = ON) ON fg_archive;

-- 日志文件
ALTER DATABASE appdb ADD LOG FILE (
  NAME = appdb_log2, FILENAME = 'E:\MSSQL\Log\appdb2.ldf',
  SIZE = 2GB, FILEGROWTH = 512MB
);

-- 查看
SELECT DB_NAME(database_id) AS db, name, type_desc, physical_name,
       size*8/1024 AS size_mb, growth, is_percent_growth, max_size
FROM sys.master_files ORDER BY database_id, file_id;

原则：数据文件与日志文件必须分盘；FILEGROWTH 用固定大小（512MB~1GB），不要用百分比（10% 在大库上会一次增长几十 GB，造成长时间阻塞）。

7.3 表与索引

CREATE TABLE dbo.orders (
  id          BIGINT IDENTITY(1,1) PRIMARY KEY,
  user_id     BIGINT NOT NULL,
  amount      DECIMAL(12,2) NOT NULL DEFAULT 0,
  status      TINYINT NOT NULL DEFAULT 0,
  created_at  DATETIME2(3) NOT NULL DEFAULT SYSDATETIME()
);

CREATE NONCLUSTERED INDEX idx_orders_user ON dbo.orders(user_id, created_at)
  WITH (ONLINE = ON, FILLFACTOR = 90, DATA_COMPRESSION = PAGE);

-- 只改索引（不锁表，企业版支持 ONLINE）
ALTER INDEX idx_orders_user ON dbo.orders REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON dbo.orders REBUILD WITH (ONLINE = ON);

-- 碎片整理建议：10%~30% REORGANIZE，>30% REBUILD
SELECT OBJECT_NAME(ips.object_id) AS table_name, i.name AS index_name,
       ips.avg_fragmentation_in_percent, ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent > 10 AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- 统计信息
UPDATE STATISTICS dbo.orders WITH FULLSCAN;
UPDATE STATISTICS dbo.orders idx_orders_user WITH SAMPLE 30 PERCENT;

-- 在线索引重建需要企业版；标准版会锁表，必须在维护窗口
-- 大表加列（2022+ 支持部分场景在线）
ALTER TABLE dbo.orders ADD remark NVARCHAR(200) NULL;

7.4 分区表

-- 1) 分区函数与方案
CREATE PARTITION FUNCTION pf_month (DATE)
  AS RANGE RIGHT FOR VALUES ('2026-10-01','2026-11-01','2026-12-01');
CREATE PARTITION SCHEME ps_month AS PARTITION pf_month ALL TO ([PRIMARY]);

-- 2) 建分区表
CREATE TABLE dbo.logs (
  id BIGINT IDENTITY, log_date DATE NOT NULL, msg NVARCHAR(1000)
) ON ps_month(log_date);

-- 3) 加分区（SPLIT）
ALTER PARTITION SCHEME ps_month NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION pf_month() SPLIT RANGE ('2027-01-01');

-- 4) 归档：切出分区到独立表（秒级）
CREATE TABLE dbo.logs_202610 (LIKE dbo.logs) ON [PRIMARY];
ALTER TABLE dbo.logs SWITCH PARTITION 2 TO dbo.logs_202610;

-- 5) 合并分区
ALTER PARTITION FUNCTION pf_month() MERGE RANGE ('2026-10-01');

-- 查看
SELECT $PARTITION.pf_month('2026-11-15') AS partition_number;
SELECT * FROM sys.partitions WHERE object_id = OBJECT_ID('dbo.logs');

8. 恢复模式与日志管理

8.1 三种恢复模式

ALTER DATABASE appdb SET RECOVERY FULL;
ALTER DATABASE appdb SET RECOVERY SIMPLE;
SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases;

关键点：切到 FULL 后必须立刻做一次全备，否则日志会一直增长不截断（因为没有日志备份链的起点）。

8.2 日志为什么不截断

SELECT name, log_reuse_wait_desc FROM sys.databases;

-- 空间占用
DBCC SQLPERF(LOGSPACE);

-- 日志文件大小与使用
SELECT DB_NAME(database_id) AS db, name, size*8/1024 AS size_mb,
       FILEPROPERTY(name,'SpaceUsed')*8/1024 AS used_mb
FROM sys.master_files WHERE type_desc = 'LOG';

8.3 日志收缩（谨慎）

-- 1) 确认是 FULL 且有长事务
SELECT log_reuse_wait_desc FROM sys.databases WHERE name='appdb';

-- 2) 做日志备份（截断）
BACKUP LOG appdb TO DISK = 'F:\MSSQL\Backup\appdb_log.trn' WITH COMPRESSION;

-- 3) 收缩到目标大小（不要用 DBCC SHRINKDATABASE）
DBCC SHRINKFILE (appdb_log, 2048);          -- 单位 MB

-- 4) 设合理初始大小与增长
ALTER DATABASE appdb MODIFY FILE (NAME = appdb_log, SIZE = 4GB, FILEGROWTH = 512MB);

危险：DBCC SHRINKDATABASE 会收缩所有文件并产生大量碎片，生产禁用。日志频繁爆满的根因通常是"没做日志备份"或"一次大事务"，收缩只是治标。

9. 备份与恢复

9.1 备份类型

9.2 备份命令

-- 完整备份（带校验与压缩，覆盖旧文件）
BACKUP DATABASE appdb
TO DISK = 'F:\MSSQL\Backup\appdb_full_20261105.bak'
WITH INIT, COMPRESSION, CHECKSUM, STATS = 10,
     NAME = 'appdb full backup';

-- 差异备份
BACKUP DATABASE appdb
TO DISK = 'F:\MSSQL\Backup\appdb_diff_20261105.bak'
WITH INIT, DIFFERENTIAL, COMPRESSION, CHECKSUM;

-- 日志备份
BACKUP LOG appdb
TO DISK = 'F:\MSSQL\Backup\appdb_log_20261105_1200.trn'
WITH INIT, COMPRESSION, CHECKSUM;

-- 备份到多个文件（并行）
BACKUP DATABASE appdb
TO DISK = 'F:\B\appdb_1.bak', DISK = 'G:\B\appdb_2.bak'
WITH INIT, COMPRESSION;

-- 尾日志（数据库损坏时抢救）
BACKUP LOG appdb TO DISK = 'F:\B\tail.trn' WITH NORECOVERY, INIT;

-- 验证备份（不恢复，只校验可读性）
RESTORE VERIFYONLY FROM DISK = 'F:\MSSQL\Backup\appdb_full_20261105.bak' WITH CHECKSUM;

-- 查看备份内容
RESTORE HEADERONLY FROM DISK = 'F:\MSSQL\Backup\appdb_full_20261105.bak';
RESTORE FILELISTONLY FROM DISK = 'F:\MSSQL\Backup\appdb_full_20261105.bak';

-- 备份历史
SELECT database_name, type, backup_start_date, backup_finish_date,
       backup_size/1024/1024 AS size_mb, compressed_backup_size/1024/1024 AS comp_mb,
       physical_device_name
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily mf ON mf.media_set_id = bs.media_set_id
WHERE database_name = 'appdb'
ORDER BY backup_start_date DESC;

9.3 恢复

-- 1) 查看备份中的逻辑文件名
RESTORE FILELISTONLY FROM DISK = 'F:\MSSQL\Backup\appdb_full_20261105.bak';

-- 2) 完整恢复（覆盖现有库）
RESTORE DATABASE appdb
FROM DISK = 'F:\MSSQL\Backup\appdb_full_20261105.bak'
WITH REPLACE, RECOVERY, STATS = 10,
     MOVE 'appdb_data' TO 'D:\MSSQL\Data\appdb.mdf',
     MOVE 'appdb_log'  TO 'E:\MSSQL\Log\appdb.ldf';

-- 3) 恢复到中间状态（准备继续应用日志）
RESTORE DATABASE appdb FROM DISK = '...full.bak' WITH NORECOVERY, REPLACE;
RESTORE DATABASE appdb FROM DISK = '...diff.bak' WITH NORECOVERY;
RESTORE LOG appdb FROM DISK = '...log1.trn' WITH NORECOVERY;
RESTORE LOG appdb FROM DISK = '...log2.trn' WITH RECOVERY;

-- 4) 时间点恢复
RESTORE DATABASE appdb FROM DISK = '...full.bak' WITH NORECOVERY, REPLACE;
RESTORE LOG appdb FROM DISK = '...log.trn'
  WITH RECOVERY, STOPAT = '2026-11-05T10:15:00';

-- 5) 只恢复某张表（恢复到临时库再导）
RESTORE DATABASE appdb_restore FROM DISK = '...full.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'appdb_data' TO 'D:\MSSQL\Data\appdb_restore.mdf',
  MOVE 'appdb_log'  TO 'E:\MSSQL\Log\appdb_restore.ldf';
-- 然后 INSERT INTO appdb.dbo.orders SELECT * FROM appdb_restore.dbo.orders

顺序不能错：全备 → 差异（可选）→ 日志（按 LSN 顺序，一个不能少）→ 最后一次用 WITH RECOVERY。中间任何一步用了 RECOVERY，后续日志就无法再应用。

9.4 备份策略模板

-- 用维护计划或 SQL Agent 作业串起来
-- 作业步骤示例
BACKUP DATABASE appdb TO DISK = 'F:\B\appdb_full_' + FORMAT(GETDATE(),'yyyyMMdd') + '.bak'
WITH INIT, COMPRESSION, CHECKSUM;

9.5 Ola Hallengren 维护脚本（业界标准）

大多数生产环境用 Ola 的脚本统一管理备份、索引与完整性检查：

EXEC master.dbo.DatabaseBackup
  @Databases = 'USER_DATABASES',
  @Directory = 'F:\MSSQL\Backup',
  @BackupType = 'FULL',
  @Verify = 'Y',
  @Compress = 'Y',
  @CheckSum = 'Y',
  @CleanupTime = 720;      -- 保留 30 天

EXEC master.dbo.IndexOptimize
  @Databases = 'USER_DATABASES',
  @FragmentationLow = NULL,
  @FragmentationMedium = 'INDEX_REORGANIZE',
  @FragmentationHigh = 'INDEX_REBUILD_ONLINE',
  @FragmentationLevel1 = 10,
  @FragmentationLevel2 = 30;

10. 高可用：Always On 与日志传送

10.1 方案对比

10.2 Always On 状态查询

-- 可用性组
SELECT ag.name AS ag_name, ag.automated_backup_preference_desc,
       ar.replica_server_name, ar.availability_mode_desc, ar.failover_mode_desc,
       ars.role_desc, ars.synchronization_health_desc, ars.connected_state_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ar.group_id = ag.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ars.replica_id = ar.replica_id;

-- 数据库级同步状态（关键：log_send_queue_size / redo_queue_size）
SELECT ag.name AS ag_name, adc.database_name, drs.synchronization_state_desc,
       drs.log_send_queue_size AS send_queue_kb,
       drs.log_send_rate AS send_rate_kb_s,
       drs.redo_queue_size AS redo_queue_kb,
       drs.redo_rate AS redo_rate_kb_s
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_databases_cluster adc ON adc.group_database_id = drs.group_database_id
JOIN sys.availability_groups ag ON ag.group_id = drs.group_id;

-- 副本延迟
SELECT replica_server_name, last_connect_error_number, last_connect_error_description
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar ON ar.replica_id = ars.replica_id
WHERE ars.last_connect_error_number IS NOT NULL;

10.3 手动切换

-- 主副本上
ALTER AVAILABILITY GROUP AG_APP FAILOVER;

-- 检查是否可切换
SELECT ag.name, ar.replica_server_name, ars.role_desc, ars.synchronization_health_desc,
       ars.is_local
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar ON ar.replica_id = ars.replica_id
JOIN sys.availability_groups ag ON ag.group_id = ar.group_id;

-- 暂停/恢复数据移动（维护副本时）
ALTER AVAILABILITY GROUP AG_APP SUSPEND;
ALTER AVAILABILITY GROUP AG_APP RESUME;

10.4 日志传送

-- 主库配置（备份作业）
BACKUP LOG appdb TO DISK = '\\share\logship\appdb.trn' WITH INIT;

-- 辅助库还原作业
RESTORE LOG appdb FROM DISK = '\\share\logship\appdb.trn' WITH NORECOVERY;

-- 查看历史与延迟
SELECT * FROM msdb.dbo.log_shipping_monitor_primary;
SELECT * FROM msdb.dbo.log_shipping_monitor_secondary;

注意：日志传送的还原作业必须用 WITH NORECOVERY（或 STANDBY 以便只读查询）。用 RECOVERY 后下一个日志就无法应用。

11. 日志体系与错误排查

11.1 错误日志

-- 读取错误日志（0 = 当前，1 = 归档 1，...）
EXEC sp_readerrorlog;
EXEC sp_readerrorlog 0, 1, N'error';
EXEC sp_readerrorlog 0, 1, N'Login failed';
EXEC sp_readerrorlog 0, 1, N'deadlock';

-- 循环所有日志
EXEC sp_cycle_errorlog;              -- 手工轮转
SELECT COUNT(*) FROM sys.dm_os_server_diagnostics_log_configurations;

# Linux 路径
ls /var/opt/mssql/log/
tail -f /var/opt/mssql/log/errorlog

11.2 默认跟踪（配置变更审计）

-- 默认跟踪文件位置
SELECT * FROM sys.traces WHERE is_default = 1;

-- 读最近的对象创建/删除/权限变更
SELECT te.name AS event_name, t.DatabaseName, t.ObjectName, t.LoginName,
       t.StartTime, t.ApplicationName
FROM sys.fn_trace_gettable(
  (SELECT REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 256))
   + 'log.trc' FROM sys.traces WHERE is_default = 1), DEFAULT) t
JOIN sys.trace_events te ON te.trace_event_id = t.EventClass
WHERE te.name IN ('Object:Created','Object:Deleted','Object:Altered')
ORDER BY t.StartTime DESC;

11.3 Extended Events

-- 现成会话：system_health（死锁、严重错误都记在里面）
SELECT xet.name, xes.event_name, xes.package_name
FROM sys.dm_xe_session_events xes
JOIN sys.dm_xe_sessions xet ON xet.address = xes.event_session_address
WHERE xet.name = 'system_health';

-- 读 system_health 里的死锁
SELECT CAST(event_data AS XML) AS deadlock_xml
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report';

-- 建一个慢查询捕获会话
CREATE EVENT SESSION [slow_queries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
  ACTION (sqlserver.sql_text, sqlserver.database_name, sqlserver.username)
  WHERE duration > 5000000                 -- 微秒，即 5 秒
)
ADD TARGET package0.event_file (SET filename = 'F:\XE\slow_queries.xel', max_file_size = 256);
GO
ALTER EVENT SESSION [slow_queries] ON SERVER STATE = START;

-- 读文件目标
SELECT CAST(event_data AS XML).value('(event/@name)[1]','varchar(100)') AS event,
       CAST(event_data AS XML).value('(event/data[@name="duration"]/value)[1]','bigint')/1000000 AS seconds,
       CAST(event_data AS XML).value('(event/action[@name="sql_text"]/value)[1]','varchar(max)') AS sql
FROM sys.fn_xe_file_target_read_file('F:\XE\slow_queries*.xel', NULL, NULL, NULL);

11.4 日志清理

-- 错误日志默认 6 个，循环即可（不要手工删文件）
EXEC sp_cycle_errorlog;

# Linux：logrotate 或按需清理
du -sh /var/opt/mssql/log/*

12. 性能诊断：DMV 与 Query Store

12.1 一眼看现状

SELECT @@SERVERNAME AS server_name,
       sqlserver_start_time,
       DATEDIFF(HOUR, sqlserver_start_time, GETDATE()) AS uptime_hours
FROM sys.dm_os_sys_info;

-- 连接数
SELECT COUNT(*) AS sessions,
       SUM(CASE WHEN status = 'running' THEN 1 ELSE 0 END) AS running,
       SUM(CASE WHEN status = 'sleeping' THEN 1 ELSE 0 END) AS sleeping
FROM sys.dm_exec_sessions WHERE is_user_process = 1;

-- 内存
SELECT physical_memory_in_use_kb/1024 AS used_mb,
       (SELECT cntr_value/1024 FROM sys.dm_os_performance_counters
        WHERE counter_name = 'Target Server Memory (KB)') AS target_mb
FROM sys.dm_os_process_memory;

12.2 当前请求

SELECT r.session_id, r.status, r.command, r.wait_type, r.wait_time, r.blocking_session_id,
       r.cpu_time, r.total_elapsed_time/1000 AS elapsed_sec,
       r.logical_reads, r.reads, r.writes,
       DB_NAME(r.database_id) AS db,
       SUBSTRING(t.text, (r.statement_start_offset/2)+1,
         ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
           ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS current_statement,
       t.text AS full_batch
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50 AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

-- sp_who2 快速版
EXEC sp_who2;
EXEC sp_who2 'active';

12.3 等待统计

SELECT TOP 15 wait_type,
       wait_time_ms/1000 AS wait_sec,
       waiting_tasks_count,
       wait_time_ms/NULLIF(waiting_tasks_count,0) AS avg_wait_ms,
       signal_wait_time_ms/1000 AS signal_sec
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
  'SLEEP_TASK','BROKER_TASK_STOP','SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT',
  'LAZYWRITER_SLEEP','CHECKPOINT_QUEUE','XE_TIMER_EVENT','WAITFOR',
  'BROKER_TO_FLUSH','HADR_FILESTREAM_IOMGR_IOCOMPLETION','DIRTY_PAGE_POLL')
ORDER BY wait_time_ms DESC;

常见等待类型对照：

12.4 高开销 SQL

SELECT TOP 20
  qs.execution_count,
  qs.total_worker_time/1000 AS total_cpu_ms,
  qs.total_worker_time/NULLIF(qs.execution_count,0)/1000 AS avg_cpu_ms,
  qs.total_elapsed_time/1000 AS total_elapsed_ms,
  qs.total_logical_reads,
  qs.total_logical_reads/NULLIF(qs.execution_count,0) AS avg_reads,
  SUBSTRING(t.text, (qs.statement_start_offset/2)+1,
    ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
      ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS statement,
  qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC;

12.5 执行计划与统计 IO

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET SHOWPLAN_ALL OFF;

SELECT * FROM dbo.orders WHERE user_id = 100;
-- 输出：logical reads / physical reads / 编译与执行时间

-- 实际执行计划
SET STATISTICS PROFILE ON;
-- 或 SET STATISTICS XML ON，SSMS 里点"包含实际执行计划"

关键判读：

12.6 Query Store（2016+，强烈建议开）

ALTER DATABASE appdb SET QUERY_STORE = ON;
ALTER DATABASE appdb SET QUERY_STORE (
  OPERATION_MODE = READ_WRITE,
  DATA_FLUSH_INTERVAL_SECONDS = 900,
  INTERVAL_LENGTH_MINUTES = 60,
  MAX_STORAGE_SIZE_MB = 2048,
  QUERY_CAPTURE_MODE = AUTO,
  SIZE_BASED_CLEANUP_MODE = AUTO);

-- 查找回归的查询（计划变差）
SELECT q.query_id, qt.query_sql_text,
       p.plan_id, rs.avg_duration/1000 AS avg_ms, rs.count_executions,
       rs.last_execution_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
WHERE q.query_id IN (
  SELECT query_id FROM sys.query_store_plan
  GROUP BY query_id HAVING COUNT(DISTINCT plan_id) > 1)
ORDER BY rs.last_execution_time DESC;

-- 强制使用某个计划（治计划回归最快的手段）
EXEC sp_query_store_force_plan @query_id = 123, @plan_id = 456;
EXEC sp_query_store_unforce_plan @query_id = 123, @plan_id = 456;

12.7 索引使用分析

-- 从未被使用的索引（写放大元凶）
SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name,
       ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
  ON ius.object_id = i.object_id AND ius.index_id = i.index_id
     AND ius.database_id = DB_ID()
WHERE i.object_id > 100 AND i.type > 1
  AND ISNULL(ius.user_seeks,0) + ISNULL(ius.user_scans,0) + ISNULL(ius.user_lookups,0) = 0
  AND ISNULL(ius.user_updates,0) > 0
ORDER BY ius.user_updates DESC;

-- 缺失索引建议（只作参考，不要全盘照搬）
SELECT TOP 20
  ROUND(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans), 0) AS score,
  s.avg_user_impact, s.user_seeks, s.user_scans,
  d.statement AS table_name, d.equality_columns, d.inequality_columns, d.included_columns
FROM sys.dm_db_missing_index_group_stats s
JOIN sys.dm_db_missing_index_groups g ON g.index_group_handle = s.group_handle
JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
WHERE d.database_id = DB_ID()
ORDER BY score DESC;

注意：缺失索引 DMV 在实例重启后清空，且不懂列顺序与写成本。它给的是"候选"，落地前要自己评估选择性、写放大与索引数量上限（一般单表不超 5~6 个）。

13. 锁、阻塞与死锁

13.1 阻塞定位

-- 当前阻塞链
SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time,
       r.status, r.command, DB_NAME(r.database_id) AS db,
       SUBSTRING(t.text, 1, 200) AS sql
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

-- 阻塞树（递归）
WITH blockers AS (
  SELECT session_id, blocking_session_id, 1 AS level
  FROM sys.dm_exec_requests WHERE blocking_session_id <> 0
  UNION ALL
  SELECT b.session_id, b.blocking_session_id, level + 1
  FROM sys.dm_exec_requests b
  JOIN blockers c ON c.blocking_session_id = b.session_id
  WHERE b.blocking_session_id <> 0 AND level < 10
)
SELECT * FROM blockers ORDER BY level, session_id;

-- 锁明细
SELECT request_session_id, resource_type, resource_database_id,
       DB_NAME(resource_database_id) AS db, resource_description,
       request_mode, request_status
FROM sys.dm_tran_locks
WHERE request_status = 'WAIT';

-- 打开的事务
SELECT tat.transaction_id, tat.name, tat.transaction_begin_time,
       DATEDIFF(SECOND, tat.transaction_begin_time, GETDATE()) AS age_sec,
       ts.session_id, ts.status
FROM sys.dm_tran_active_transactions tat
JOIN sys.dm_tran_session_transactions ts ON ts.transaction_id = tat.transaction_id;

13.2 处理阻塞

-- 先看被阻塞者在等什么，再决定杀谁
EXEC sp_who2 'active';

-- 温和：杀掉语句所在会话（无"只杀语句"的官方命令，只能杀整个会话）
KILL 123;
KILL 123 WITH STATUSONLY;      -- 查看回滚进度

注意：SQL Server 没有 Oracle 的 KILL QUERY 等价物，KILL 就是杀整个会话并回滚事务。大事务回滚可能比执行更久，KILL ... WITH STATUSONLY 可以看进度。

13.3 死锁

-- 1) 开跟踪标志 1222，死锁写入 errorlog
DBCC TRACEON (1222, -1);

-- 2) 从 system_health 读死锁图
SELECT CAST(event_data AS XML) AS deadlock_xml
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report';

-- 3) 从 errorlog 读
EXEC sp_readerrorlog 0, 1, N'deadlock';

死锁的四个常见成因与对策：

-- 开启 RCSI（读不阻塞写），需短暂排他
ALTER DATABASE appdb SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

14. 空间与容量管理

14.1 库与表大小

-- 所有库的大小
SELECT DB_NAME(database_id) AS db,
       SUM(CASE WHEN type_desc = 'ROWS' THEN size END)*8/1024 AS data_mb,
       SUM(CASE WHEN type_desc = 'LOG' THEN size END)*8/1024 AS log_mb
FROM sys.master_files GROUP BY database_id ORDER BY data_mb DESC;

-- 当前库表大小（sp_spaceused）
EXEC sp_spaceused;
EXEC sp_spaceused 'dbo.orders';

-- 精确到表（含索引）
SELECT t.name AS table_name,
       SUM(a.total_pages)*8/1024 AS total_mb,
       SUM(a.used_pages)*8/1024 AS used_mb,
       SUM(a.data_pages)*8/1024 AS data_mb
FROM sys.tables t
JOIN sys.indexes i ON i.object_id = t.object_id
JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id = i.index_id
JOIN sys.allocation_units a ON a.container_id = p.partition_id
GROUP BY t.name ORDER BY total_mb DESC;

-- 磁盘余量
SELECT DISTINCT volume_mount_point, file_system_type,
       total_bytes/1024/1024/1024 AS total_gb,
       available_bytes/1024/1024/1024 AS free_gb
FROM sys.master_files
CROSS APPLY sys.dm_os_volume_stats(database_id, file_id);

14.2 数据文件收缩

-- 先看可回收空间
SELECT DB_NAME(database_id) AS db, name, size*8/1024 AS size_mb,
       FILEPROPERTY(name,'SpaceUsed')*8/1024 AS used_mb
FROM sys.master_files WHERE type_desc = 'ROWS';

-- 收缩单个文件到目标大小
DBCC SHRINKFILE (appdb_data, 8192);          -- 8GB

-- 只收缩到不产生碎片的最小值
DBCC SHRINKFILE (appdb_data, 0, TRUNCATEONLY);

危险：DBCC SHRINKDATABASE 与频繁 SHRINKFILE 会造成严重索引碎片与 IO 抖动，生产上应"一次设足初始大小 + 固定增长"，而不是反复收缩。

14.3 空间增长规划

-- 关闭自动收缩、设置固定增长
ALTER DATABASE appdb SET AUTO_SHRINK OFF;

ALTER DATABASE appdb MODIFY FILE (
  NAME = appdb_data, SIZE = 16GB, FILEGROWTH = 1024MB, MAXSIZE = UNLIMITED);

ALTER DATABASE appdb MODIFY FILE (
  NAME = appdb_log, SIZE = 8GB, FILEGROWTH = 512MB);

14.4 大表归档

-- 分批删除，避免日志暴涨与长时间锁
WHILE 1 = 1
BEGIN
  DELETE TOP (5000) FROM dbo.logs WHERE log_date < '2026-01-01';
  IF @@ROWCOUNT = 0 BREAK;
  CHECKPOINT;                     -- FULL 模式下仍需日志备份配合
  WAITFOR DELAY '00:00:01';
END

-- 或在 SIMPLE 模式下用 SWITCH 分区秒级归档（见 7.4）

15. tempdb 与内存管理

15.1 tempdb 配置

-- 查看
SELECT name, physical_name, size*8/1024 AS size_mb, growth, is_percent_growth
FROM tempdb.sys.database_files;

-- 建议：数据文件数 = CPU 核数（≤8），每个文件等大，固定增长
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 4GB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE (
  NAME = tempdev2, FILENAME = 'T:\MSSQL\tempdb\tempdb2.ndf', SIZE = 4GB, FILEGROWTH = 512MB);

-- 重启后 tempdb 恢复到设定大小（2019+ 可用 MEMORY_OPTIMIZED 元数据）

关键：tempdb 每次重启重建，初始大小要设足（否则每次重启后先经历一轮增长）。文件数超过 8 个对多数场景无收益。

15.2 tempdb 争用

-- PAGELATCH 等待集中在 2:1:1 / 2:1:3 等页
SELECT session_id, wait_type, resource_description, wait_duration_ms
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%' AND resource_description LIKE '2:%';

-- 2019+ 的元数据优化与 tempdb 配置
SELECT name, value_in_use FROM sys.configurations
WHERE name IN ('tempdb metadata memory-optimized');

15.3 内存

-- 各库缓存占用
SELECT COUNT(*) * 8 / 1024 AS cached_mb, DB_NAME(database_id) AS db
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id ORDER BY cached_mb DESC;

-- 内存授予等待
SELECT waiting_tasks_count, wait_time_ms/1000 AS wait_sec
FROM sys.dm_os_wait_stats WHERE wait_type = 'RESOURCE_SEMAPHORE';

-- 清缓存（仅测试环境，生产禁止）
-- DBCC FREEPROCCACHE;
-- DBCC DROPCLEANBUFFERS;

危险：DBCC DROPCLEANBUFFERS 会清空数据缓存，生产执行后所有查询直接打盘，可能引发雪崩。只在性能测试时用。

16. 升级、迁移与补丁

16.1 支持路径

16.2 升级前检查

-- 1) 版本与补丁
SELECT @@VERSION, SERVERPROPERTY('ProductLevel'), SERVERPROPERTY('Edition');

-- 2) 兼容级别（升级后默认仍是旧级别，需手动提升）
SELECT name, compatibility_level FROM sys.databases;

-- 3) 已弃用功能
SELECT * FROM sys.dm_os_performance_counters WHERE object_name LIKE '%Deprecated%';
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure;

-- 4) 升级顾问（Microsoft/Assessment 工具）或 Data Migration Assistant
-- 5) 无效对象与跨库依赖
SELECT * FROM sys.sql_expression_dependencies WHERE is_schema_bound_reference = 0;

# Linux 升级（换仓库版本后）
dnf update -y mssql-server
systemctl restart mssql-server

16.3 升级后

-- 1) 提升兼容级别（先测试！）
ALTER DATABASE appdb SET COMPATIBILITY_LEVEL = 170;   -- 2025 对应 170，2022 对应 160

-- 2) 更新统计信息
EXEC sp_updatestats;

-- 3) 检查 DBCC CHECKDB
DBCC CHECKDB ('appdb') WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- 4) 重建索引（可选，升级后碎片可能变化）
-- 5) 验证 Agent 作业、链接服务器、SSIS 包、备份计划

注意：升级只改引擎版本，不改兼容级别。新版本的优化器行为要等你手动提升 COMPATIBILITY_LEVEL 才启用。提升前务必在测试库跑 Query Store 回归对比。

16.4 迁移（跨实例/跨云）

-- 分离与附加
EXEC sp_detach_db 'appdb';
CREATE DATABASE appdb ON (FILENAME='D:\MSSQL\Data\appdb.mdf'), (FILENAME='E:\MSSQL\Log\appdb.ldf')
FOR ATTACH;

-- 只读打开（用于抢救）
CREATE DATABASE appdb ON (FILENAME='...mdf') FOR ATTACH_REBUILD_LOG;

17. 高频故障速查

17.1 连不上

-- 看详细失败原因
EXEC sp_readerrorlog 0, 1, N'Login failed';

-- 确认认证模式（1=仅 Windows）
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly');

-- 确认账号状态
SELECT name, is_disabled, is_policy_checked FROM sys.sql_logins WHERE name='sa';

# Linux 确认监听
ss -lntp | grep 1433
grep -i 'listening' /var/opt/mssql/log/errorlog

17.2 数据库变成 RECOVERY_PENDING / SUSPECT

SELECT name, state_desc FROM sys.databases;

-- 紧急模式抢救（最后手段）
ALTER DATABASE appdb SET EMERGENCY;
ALTER DATABASE appdb SET SINGLE_USER;
DBCC CHECKDB ('appdb', REPAIR_ALLOW_DATA_LOSS);   -- 可能丢数据
ALTER DATABASE appdb SET MULTI_USER;
ALTER DATABASE appdb SET ONLINE;

危险：REPAIR_ALLOW_DATA_LOSS 会删除损坏页上的数据，只在没有备份、必须救活时用。执行前先复制 mdf/ldf 文件留底。

17.3 日志满 / 无法增长

SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name='appdb';
DBCC SQLPERF(LOGSPACE);

-- 急救（有备份前提下）
BACKUP LOG appdb TO DISK='F:\B\appdb_log_emergency.trn' WITH INIT, COMPRESSION;
DBCC SHRINKFILE (appdb_log, 2048);

根治：FULL 模式下配日志备份作业（15 分钟一次）；或确认业务可容忍后改 SIMPLE。

17.4 阻塞雪崩

EXEC sp_who2 'active';
SELECT session_id, blocking_session_id, wait_type, wait_time, DB_NAME(database_id)
FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;

处理顺序：找到最源头的阻塞会话（blocking_session_id = 0 的那个）→ 判断是长事务还是坏查询 → 联系业务或 KILL。

17.5 CPU 100%

-- TOP CPU 查询
SELECT TOP 10 qs.total_worker_time/1000 AS cpu_ms, qs.execution_count,
       qs.total_worker_time/NULLIF(qs.execution_count,0)/1000 AS avg_cpu_ms,
       SUBSTRING(t.text,1,150) AS sql
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY qs.total_worker_time DESC;

-- 当前在跑的
SELECT session_id, cpu_time, status, command FROM sys.dm_exec_requests ORDER BY cpu_time DESC;

常见原因：并行度过高（MAXDOP=0）、缺失索引导致全表扫、参数嗅探、CXPACKET 倾斜。

17.6 内存不足

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Page life expectancy','Buffer cache hit ratio','Stolen Server Memory (KB)')
  AND object_name LIKE '%Buffer Manager%';

-- 确认 max server memory 是否设置
SELECT name, value_in_use FROM sys.configurations WHERE name = 'max server memory (MB)';

17.7 tempdb 爆满

SELECT SUM(unallocated_extent_page_count)*8/1024 AS free_mb,
       SUM(user_object_reserved_page_count)*8/1024 AS user_obj_mb,
       SUM(internal_object_reserved_page_count)*8/1024 AS internal_mb,
       SUM(version_store_reserved_page_count)*8/1024 AS version_store_mb
FROM tempdb.sys.dm_db_file_space_usage;

-- 谁在占
SELECT session_id, user_objects_alloc_page_count*8/1024 AS user_mb,
       internal_objects_alloc_page_count*8/1024 AS internal_mb
FROM tempdb.sys.dm_db_session_space_usage ORDER BY user_mb DESC;

常见原因：大排序/哈希（worktable）、READ_COMMITTED_SNAPSHOT 的版本存储、长期未提交事务、临时表未释放。

18. 危险命令与避坑清单

18.1 变更前检查清单

-- 1) 确认实例与库
SELECT @@SERVERNAME, DB_NAME(), SERVERPROPERTY('ProductVersion');

-- 2) 备份
BACKUP DATABASE appdb TO DISK='F:\B\pre_change.bak' WITH INIT, COMPRESSION, CHECKSUM;
BACKUP LOG appdb TO DISK='F:\B\pre_change.trn' WITH INIT, COMPRESSION;

-- 3) 看阻塞与长事务
SELECT session_id, blocking_session_id, wait_time FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;
SELECT transaction_id, DATEDIFF(SECOND, transaction_begin_time, GETDATE()) age
FROM sys.dm_tran_active_transactions;

-- 4) 看 AG 延迟
SELECT database_name, log_send_queue_size, redo_queue_size FROM sys.dm_hadr_database_replica_states;

-- 5) 记录基线
SELECT name, compatibility_level, recovery_model_desc FROM sys.databases WHERE name='appdb';

19. 附录：巡检脚本与 DMV 速查

19.1 日常巡检脚本

SET NOCOUNT ON;

PRINT '========== 版本与运行时长 ==========';
SELECT @@SERVERNAME AS server_name, @@VERSION AS version,
       sqlserver_start_time, DATEDIFF(HOUR, sqlserver_start_time, GETDATE()) AS uptime_h;

PRINT '========== 数据库状态 ==========';
SELECT name, state_desc, recovery_model_desc, compatibility_level,
       log_reuse_wait_desc, is_read_only
FROM sys.databases ORDER BY database_id;

PRINT '========== 数据与日志空间 ==========';
SELECT DB_NAME(database_id) AS db,
       SUM(CASE WHEN type_desc='ROWS' THEN size END)*8/1024 AS data_mb,
       SUM(CASE WHEN type_desc='LOG'  THEN size END)*8/1024 AS log_mb
FROM sys.master_files GROUP BY database_id ORDER BY data_mb DESC;

PRINT '========== 磁盘余量 ==========';
SELECT DISTINCT volume_mount_point, total_bytes/1024/1024/1024 AS total_gb,
       available_bytes/1024/1024/1024 AS free_gb,
       CAST(available_bytes*100.0/total_bytes AS DECIMAL(5,1)) AS free_pct
FROM sys.master_files CROSS APPLY sys.dm_os_volume_stats(database_id, file_id);

PRINT '========== 连接与请求 ==========';
SELECT COUNT(*) AS sessions FROM sys.dm_exec_sessions WHERE is_user_process = 1;
SELECT session_id, status, command, wait_type, wait_time, blocking_session_id,
       DB_NAME(database_id) AS db
FROM sys.dm_exec_requests WHERE session_id > 50 AND session_id <> @@SPID;

PRINT '========== 阻塞 ==========';
SELECT session_id, blocking_session_id, wait_type, wait_time
FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;

PRINT '========== 等待 TOP10 ==========';
SELECT TOP 10 wait_type, wait_time_ms/1000 AS wait_sec, waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('SLEEP_TASK','LAZYWRITER_SLEEP','CHECKPOINT_QUEUE',
      'XE_TIMER_EVENT','WAITFOR','BROKER_TASK_STOP','SQLTRACE_BUFFER_FLUSH')
ORDER BY wait_time_ms DESC;

PRINT '========== 内存 ==========';
SELECT physical_memory_in_use_kb/1024 AS used_mb,
       page_fault_count
FROM sys.dm_os_process_memory;
SELECT value_in_use AS max_server_memory_mb FROM sys.configurations
WHERE name = 'max server memory (MB)';

PRINT '========== 近 3 天备份 ==========';
SELECT database_name, type, backup_finish_date,
       backup_size/1024/1024 AS size_mb, compressed_backup_size/1024/1024 AS comp_mb
FROM msdb.dbo.backupset WHERE backup_finish_date > DATEADD(DAY,-3,GETDATE())
ORDER BY backup_finish_date DESC;

PRINT '========== AG 状态 ==========';
SELECT ag.name AS ag_name, ar.replica_server_name, ars.role_desc,
       ars.synchronization_health_desc,
       drs.database_name, drs.log_send_queue_size, drs.redo_queue_size
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ar.group_id = ag.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ars.replica_id = ar.replica_id
LEFT JOIN sys.dm_hadr_database_replica_states drs ON drs.replica_id = ar.replica_id;

PRINT '========== 碎片 TOP10 ==========';
SELECT TOP 10 OBJECT_NAME(ips.object_id) AS table_name, i.name AS index_name,
       ips.avg_fragmentation_in_percent, ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.page_count > 1000 AND ips.avg_fragmentation_in_percent > 10
ORDER BY ips.avg_fragmentation_in_percent DESC;

sqlcmd -S localhost -U sa -P "$SA_PWD" -C -b -i health.sql -o health_$(date +%F).txt

19.2 常用系统存储过程

19.3 关键 DMV 速查

19.4 关键路径与端口

附：与工作区其他手册的配合

• 操作系统层见 CentOS-7-10-常规运维指令手册.md / Ubuntu-常用运维指令手册.md
• 放行端口：firewall-cmd --add-port=1433/tcp --permanent（Linux），Windows 用 New-NetFirewallRule
• Linux 版 SQL Server 依赖 openldap、krb5、openssl，安装前确认系统库版本
• 与其他数据库的核心差异：GO 批处理分隔符、恢复模式决定能否 PITR、max server memory 必须手工设上限、DENY 优先于 GRANT、sysadmin 绕过所有权限检查