SQL Server命令速查
sqlcmd 连接、实例与文件、T-SQL 查询、索引执行计划、DMV 诊断、事务锁、备份恢复、AlwaysOn 与权限巡检等 233 条命令
共 233 条命令 · 12 类场景 · 点击右侧图标可复制
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
sqlcmd -S 127.0.0.1 -U sa -P "P@ssw0rd" -d master | sqlcmd 连接实例,-S 为服务器地址,-d 指定默认库 | |
sqlcmd -S 127.0.0.1 -E -d master | 使用 Windows 身份验证(-E)连接,无需账号密码 | |
sqlcmd -S 127.0.0.1 -U sa -P "P@ssw0rd" -C -N | 强制加密连接并信任自签证书,新版驱动默认加密必带 -C | |
sqlcmd -S 127.0.0.1 -U sa -P "P@ssw0rd" -Q "SELECT @@VERSION" | 执行单条语句后退出,适合脚本与巡检 | |
sqlcmd -S 127.0.0.1 -U sa -P "P@ssw0rd" -i D:\sql\check.sql -o D:\sql\out.txt | 执行 SQL 文件并输出到文件 | |
sqlcmd -S 127.0.0.1 -U sa -P "P@ssw0rd" -i run.sql -b | -b 遇错返回非零退出码,批处理必须加 | |
:r D:\sql\part2.sql | 在脚本中引入另一个脚本文件 | |
SELECT @@VERSION; | 查看 SQL Server 版本、补丁与操作系统 | |
SELECT SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('ProductLevel'), SERVERPROPERTY('Edition'); | 版本号、SP/CU 级别与版本类型 | |
SELECT @@SERVERNAME AS server_name, @@SERVICENAME AS service_name; | 当前实例名与内部服务名 | |
SELECT SERVERPROPERTY('InstanceDefaultDataPath'), SERVERPROPERTY('InstanceDefaultLogPath'); | 实例默认数据与日志目录 | |
SELECT session_id, login_name, host_name, program_name, status FROM sys.dm_exec_sessions; | 查看当前所有连接与客户端来源 | |
SELECT name, value, value_in_use FROM sys.configurations WHERE name IN ('max server memory (MB)','min server memory (MB)','max degree of parallelism','cost threshold for parallelism'); | 查看关键实例配置的当前值 | |
EXEC sp_configure 'show advanced options', 1; RECONFIGURE; | 打开高级选项,才能修改并行度等参数 | |
EXEC sp_configure 'max server memory (MB)', 24576; RECONFIGURE; | 限制最大内存,避免吃掉操作系统内存 | |
SELECT name, state_desc, startup_type_desc FROM sys.databases ORDER BY name; | 查看所有数据库及在线状态 | |
EXEC xp_readerrorlog 0, 1, N'error'; | 读取错误日志,排查启动与连接失败原因 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
CREATE DATABASE Shop; | 创建数据库,默认继承 model 库配置 | |
CREATE DATABASE Shop ON PRIMARY (NAME = 'Shop', FILENAME = 'D:\data\Shop.mdf', SIZE = 1GB, FILEGROWTH = 256MB) LOG ON (NAME = 'Shop_log', FILENAME = 'D:\log\Shop.ldf', SIZE = 512MB, FILEGROWTH = 256MB); | 显式指定数据与日志文件路径、初始大小与增长量 | |
ALTER DATABASE Shop MODIFY FILE (NAME = 'Shop', SIZE = 10GB, FILEGROWTH = 512MB); | 调整数据文件容量与增长步长 | |
ALTER DATABASE Shop ADD FILE (NAME = 'Shop_data2', FILENAME = 'E:\data\Shop2.ndf', SIZE = 10GB, FILEGROWTH = 512MB) TO FILEGROUP ShopFG; | 新增次要数据文件分散 IO | |
ALTER DATABASE Shop SET RECOVERY FULL; | 切换完整恢复模式,事务日志备份的基础 | |
ALTER DATABASE Shop SET RECOVERY SIMPLE; | 切换简单恢复模式,日志自动截断,无法做时点恢复 | |
ALTER DATABASE Shop SET AUTO_SHRINK OFF; | 关闭自动收缩,避免反复增长收缩造成碎片 | |
ALTER DATABASE Shop SET AUTO_CLOSE OFF; | 关闭自动关闭,生产库必须关闭 | |
ALTER DATABASE Shop SET AUTO_UPDATE_STATISTICS ON; | 保持统计信息自动更新 | |
ALTER DATABASE Shop SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE; | 开启行版本读已提交,减少读写互相阻塞 | |
ALTER DATABASE Shop SET ALLOW_SNAPSHOT_ISOLATION ON; | 启用快照隔离,读操作不再阻塞写 | |
ALTER DATABASE Shop SET SINGLE_USER WITH ROLLBACK IMMEDIATE; | 强制断开其他会话,准备还原或改库名前使用 | |
ALTER DATABASE Shop SET MULTI_USER; | 恢复多用户访问 | |
ALTER DATABASE Shop COLLATE Chinese_PRC_CI_AS; | 修改数据库排序规则(仅对新建对象生效) | |
EXEC sp_helpdb; | 列出所有数据库的大小、所有者与状态 | |
EXEC sp_helpdb 'Shop'; | 查看指定库的文件明细与增长设置 | |
SELECT DB_NAME(database_id), name, type_desc, physical_name, size * 8 / 1024 AS size_mb FROM sys.master_files; | 一次性查看全部实例的文件布局与大小 | |
SELECT name, physical_name, size * 8 / 1024 AS size_mb FROM sys.database_files; | 在当前库中查看数据与日志文件 | |
DBCC SHRINKFILE (N'Shop_log', 1024); | 收缩日志文件到 1GB,需先备份或切换简单模式 | |
DBCC SHRINKDATABASE (Shop, 10); | 收缩整库,仅用于回收一次性大量删除的空白页 | |
DBCC SQLPERF(LOGSPACE); | 查看所有库日志使用率,定位日志暴涨 | |
SELECT name, log_reuse_wait_desc FROM sys.databases; | 日志无法截断时看这一列:BACKUP、REPLICATION、ACTIVE_TRANSACTION 等 | |
DROP DATABASE Shop; | 删除数据库及全部文件(不可恢复) |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
CREATE TABLE dbo.Orders (Id INT IDENTITY(1,1) PRIMARY KEY, OrderNo VARCHAR(32) NOT NULL, Amount DECIMAL(18,2) NOT NULL DEFAULT 0, CreatedAt DATETIME2(3) NOT NULL DEFAULT SYSDATETIME()); | 建表模板:自增主键、金额精度与时间默认值 | |
ALTER TABLE dbo.Orders ADD Remark NVARCHAR(200) NULL; | 新增字段,建议显式写 NULL/NOT NULL | |
ALTER TABLE dbo.Orders ADD CONSTRAINT DF_Orders_Remark DEFAULT N'' FOR Remark; | 为已有字段补默认值约束 | |
ALTER TABLE dbo.Orders ALTER COLUMN Remark NVARCHAR(500) NULL; | 修改字段长度与可空性 | |
EXEC sp_rename 'dbo.Orders.OldName', 'NewName', 'COLUMN'; | 重命名列(不建议在生产高峰期执行) | |
EXEC sp_rename 'dbo.OldTable', 'NewTable'; | 重命名表 | |
ALTER TABLE dbo.Orders ADD CONSTRAINT FK_Orders_Customer FOREIGN KEY (CustomerId) REFERENCES dbo.Customer (Id); | 添加外键约束,注意已有数据必须满足 | |
ALTER TABLE dbo.Orders NOCHECK CONSTRAINT FK_Orders_Customer; | 临时禁用约束,用于批量导入 | |
ALTER TABLE dbo.Orders WITH CHECK CHECK CONSTRAINT FK_Orders_Customer; | 重新校验并启用约束 | |
SELECT * FROM sys.tables ORDER BY name; | 列出当前库所有表 | |
EXEC sp_help 'dbo.Orders'; | 查看表结构、索引、约束与依赖对象 | |
SELECT c.name, t.name AS type_name, c.max_length, c.is_nullable FROM sys.columns c JOIN sys.types t ON c.user_type_id = t.user_type_id WHERE c.object_id = OBJECT_ID('dbo.Orders'); | 以结果集方式查看字段定义,便于导出文档 | |
SELECT name, type_desc, create_date, modify_date FROM sys.objects WHERE type IN ('U','V','P','FN','IF') ORDER BY modify_date DESC; | 按修改时间排查最近变动过的对象 | |
EXEC sp_helptext 'dbo.usp_GetOrders'; | 查看存储过程、视图、函数定义源码 | |
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.usp_GetOrders')); | 以结果集返回对象定义,便于复制 | |
SELECT * FROM sys.sql_modules WHERE definition LIKE '%TmpTable%'; | 全库搜索引用某表/字段的对象,改表前必查 | |
SELECT OBJECT_NAME(referencing_id) AS obj, referenced_entity_name FROM sys.sql_expression_dependencies WHERE referenced_entity_name = 'Orders'; | 查看哪些对象引用了目标表 | |
TRUNCATE TABLE dbo.Stage; | 快速清空表,比 DELETE 快且日志少,需权限且不能有外键引用 | |
SELECT * INTO dbo.Orders_bak FROM dbo.Orders; | 快速复制表结构与数据做备份 | |
DROP TABLE IF EXISTS dbo.Stage; | 安全删除表,不存在时不报错 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
SELECT TOP (100) * FROM dbo.Orders ORDER BY CreatedAt DESC; | 取最新 100 条,TOP 建议加括号 | |
SELECT * FROM dbo.Orders ORDER BY CreatedAt DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY; | 分页查询,必须配合 ORDER BY | |
SELECT COUNT_BIG(*) FROM dbo.Orders; | 大表计数用 COUNT_BIG 避免 INT 溢出 | |
SELECT CustomerId, COUNT(*) AS cnt, SUM(Amount) AS total FROM dbo.Orders WHERE CreatedAt >= DATEADD(DAY, -7, CAST(GETDATE() AS DATE)) GROUP BY CustomerId HAVING SUM(Amount) > 10000; | 分组聚合与 HAVING 过滤,按天边界取整更利于走索引 | |
SELECT o.Id, c.Name FROM dbo.Orders o LEFT JOIN dbo.Customer c ON c.Id = o.CustomerId; | 常用左连接写法,注意连接键类型与索引一致 | |
WITH t AS (SELECT Id, Amount, ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY Amount DESC) AS rn FROM dbo.Orders) SELECT * FROM t WHERE rn = 1; | CTE + 窗口函数取每组第一条 | |
SELECT Id, SUM(Amount) OVER (PARTITION BY CustomerId ORDER BY CreatedAt ROWS UNBOUNDED PRECEDING) AS running_total FROM dbo.Orders; | 累计求和窗口,注意指定 ROWS 框架 | |
SELECT Id, LAG(Amount) OVER (ORDER BY CreatedAt) AS prev_amount, LEAD(Amount) OVER (ORDER BY CreatedAt) AS next_amount FROM dbo.Orders; | 取上一行/下一行做环比分析 | |
SELECT CustomerId, STRING_AGG(OrderNo, ',') WITHIN GROUP (ORDER BY OrderNo) AS order_list FROM dbo.Orders GROUP BY CustomerId; | 行转字符串聚合,SQL Server 2017+ 支持 | |
SELECT * FROM (SELECT CustomerId, Amount FROM dbo.Orders) s PIVOT (SUM(Amount) FOR CustomerId IN ([1],[2],[3])) p; | PIVOT 行转列,IN 中的值必须写死 | |
SELECT * FROM OPENJSON(@json) WITH (id INT '$.id', name NVARCHAR(50) '$.name'); | 解析 JSON 为行集,2016+ 支持 | |
SELECT (SELECT * FROM dbo.Orders FOR JSON PATH, ROOT('data')); | 结果集转 JSON,方便直接给前端 | |
SELECT TRY_CONVERT(DATETIME2, @text) AS dt, TRY_CAST(@text AS INT) AS num; | 安全转换,非法值返回 NULL 而不报错 | |
SELECT IIF(Amount > 1000, N'大额', N'普通') AS flag, FORMAT(CreatedAt, 'yyyy-MM-dd') AS d FROM dbo.Orders; | 条件表达式与日期格式化(FORMAT 较慢,大数据量慎用) | |
UPDATE TOP (1000) dbo.Orders SET Remark = N'batch' WHERE Remark IS NULL; | 分批更新,避免一次性大事务锁表与日志暴涨 | |
DELETE FROM dbo.Orders WHERE CreatedAt < DATEADD(YEAR, -3, GETDATE()); | 按时间清理历史数据,大表建议循环分批 | |
MERGE dbo.Target AS t USING dbo.Source AS s ON t.Id = s.Id WHEN MATCHED THEN UPDATE SET t.Amount = s.Amount WHEN NOT MATCHED THEN INSERT (Id, Amount) VALUES (s.Id, s.Amount); | MERGE 做同步,务必加 HOLDLOCK 并注意并发风险 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
CREATE INDEX IX_Orders_CreatedAt ON dbo.Orders (CreatedAt) INCLUDE (CustomerId, Amount); | 普通索引 + 包含列,减少回表 | |
CREATE UNIQUE INDEX UX_Orders_OrderNo ON dbo.Orders (OrderNo); | 唯一索引,兼作业务唯一性约束 | |
CREATE INDEX IX_Orders_Customer ON dbo.Orders (CustomerId, CreatedAt DESC) WITH (FILLFACTOR = 90, ONLINE = ON); | 组合索引,Online 建索引减少锁表(企业版) | |
CREATE INDEX IX_Orders_Filtered ON dbo.Orders (CreatedAt) WHERE Status = 1; | 筛选索引,只索引热数据,体积更小 | |
ALTER INDEX ALL ON dbo.Orders REBUILD WITH (ONLINE = ON, MAXDOP = 4); | 重建表上所有索引,碎片高时使用 | |
ALTER INDEX IX_Orders_CreatedAt ON dbo.Orders REORGANIZE; | 重组索引,碎片 10%~30% 时优先选择 | |
ALTER INDEX IX_Orders_CreatedAt ON dbo.Orders DISABLE; | 禁用索引,导入前临时提速用 | |
SELECT i.name, s.used_page_count * 8 / 1024 AS size_mb FROM sys.dm_db_partition_stats s JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id WHERE s.object_id = OBJECT_ID('dbo.Orders'); | 查看表与各索引占用空间 | |
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Orders'), NULL, NULL, 'SAMPLED'); | 查看索引碎片率与页数,判断是否需要重建 | |
SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.Orders') AND name IS NOT NULL; | 列出表上的索引定义 | |
EXEC sp_helpindex 'dbo.Orders'; | 简洁查看索引列与类型 | |
SELECT * FROM sys.index_columns WHERE object_id = OBJECT_ID('dbo.Orders') ORDER BY index_id, key_ordinal; | 查看索引键列顺序,排查是否最左前缀不匹配 | |
SET STATISTICS IO ON; SET STATISTICS TIME ON; | SSMS 中开启逻辑读与耗时统计,判断查询开销 | |
SET SHOWPLAN_XML ON; | 只返回估算执行计划不执行语句,脚本化分析用 | |
SET STATISTICS XML ON; | 返回实际执行计划 XML,可保存后用 SSMS 打开 | |
Ctrl + M(SSMS 中开启实际执行计划) | 查看真实行数、算子耗时、是否发生隐式转换与并行 | |
SELECT * FROM sys.dm_exec_query_plan(0x0600...); | 用计划句柄取出图形化执行计划 | |
UPDATE STATISTICS dbo.Orders WITH FULLSCAN; | 全扫描更新统计信息,修复估算严重偏差 | |
DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_CreatedAt'); | 查看统计直方图与更新时间 | |
SET NOCOUNT ON; | 关闭行数提示,减少网络往返,存储过程常规写法 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
SELECT TOP 10 r.session_id, r.status, r.command, r.cpu_time, r.total_elapsed_time, r.blocking_session_id, t.text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.session_id > 50 ORDER BY r.cpu_time DESC; | 查看当前正在执行的语句及 CPU 消耗 | |
SELECT TOP 10 qs.total_worker_time / qs.execution_count AS avg_cpu, qs.execution_count, qs.total_logical_reads, SUBSTRING(t.text, qs.statement_start_offset / 2 + 1, 200) AS stmt FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t ORDER BY avg_cpu DESC; | 按平均 CPU 排序找最耗 CPU 的语句 | |
SELECT qs.sql_handle, qs.plan_handle, qs.total_logical_reads / qs.execution_count AS avg_reads, t.text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t ORDER BY avg_reads DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY; | 按平均逻辑读找 IO 大户 | |
SELECT TOP 10 wait_type, waiting_tasks_count, wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_ms, signal_wait_time_ms FROM sys.dm_os_wait_stats WHERE waiting_tasks_count > 0 ORDER BY wait_time_ms DESC; | 等待类型排名,判断瓶颈在 IO、锁还是 CPU | |
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR); | 清空等待统计,采样一段时间后再看更准确 | |
SELECT * FROM sys.dm_db_missing_index_group_stats ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC; | 缺失索引建议,仅作参考,需结合实际写入成本 | |
SELECT d.statement, d.equality_columns, d.inequality_columns, d.included_columns FROM sys.dm_db_missing_index_details d; | 缺失索引的具体列组合 | |
SELECT * FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t WHERE qs.plan_generation_num > 5 ORDER BY qs.plan_generation_num DESC; | 重编译频繁的语句,检查参数嗅探与临时表用法 | |
SELECT cp.objtype, cp.usecounts, cp.size_in_bytes, st.text FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st ORDER BY cp.size_in_bytes DESC; | 查看计划缓存占用,定位缓存膨胀 | |
SELECT * FROM sys.dm_db_file_space_usage; | tempdb 空间使用情况(在 tempdb 中执行) | |
SELECT session_id, user_objects_alloc_page_count * 8 / 1024 AS user_mb, internal_objects_alloc_page_count * 8 / 1024 AS internal_mb FROM sys.dm_db_session_space_usage ORDER BY user_objects_alloc_page_count DESC; | 按会话看 tempdb 占用,定位谁在撑爆 tempdb | |
SELECT * FROM sys.dm_os_sys_memory; | 操作系统内存状态,判断是否被其他进程挤压 | |
SELECT type, SUM(pages_kb) / 1024 AS mb FROM sys.dm_os_memory_clerks GROUP BY type ORDER BY mb DESC; | SQL Server 内存分配去向,定位缓存占用 | |
SELECT DB_NAME(database_id) AS db, COUNT(*) * 8 / 1024 AS mb FROM sys.dm_os_buffer_descriptors GROUP BY database_id ORDER BY mb DESC; | 缓冲池中各库占用,判断缓存是否被冷库占用 | |
SELECT * FROM sys.dm_io_virtual_file_stats(NULL, NULL) ORDER BY io_stall_read_ms DESC; | 文件级 IO 延迟统计,定位慢盘 | |
SELECT cpu_count, scheduler_count, physical_memory_kb / 1024 AS mem_mb FROM sys.dm_os_sys_info; | 实例可见的 CPU 与物理内存 | |
SELECT * FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'; | 调度器状态与 runnable_tasks_count,判断 CPU 压力 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
BEGIN TRAN; UPDATE dbo.Orders SET Amount = 0 WHERE Id = 1; COMMIT; | 显式事务基本结构,务必成对出现 | |
BEGIN TRY BEGIN TRAN; UPDATE dbo.Orders SET Amount = 1 WHERE Id = 1; COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; THROW; END CATCH; | 标准错误处理模板,CATCH 中回滚并重新抛出 | |
SET XACT_ABORT ON; | 遇运行时错误自动回滚整个事务,批处理强烈建议开启 | |
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; | 脏读,报表查询常用但结果可能不准 | |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; | 默认级别,生产最常用 | |
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; | 快照隔离,读写互不阻塞,需先开启 ALLOW_SNAPSHOT_ISOLATION | |
SET LOCK_TIMEOUT 5000; | 设置锁等待超时(毫秒),避免无限等待 | |
EXEC sp_who2; | 查看所有会话、状态、阻塞者与当前库 | |
SELECT session_id, blocking_session_id, wait_type, wait_time, wait_resource, status, command FROM sys.dm_exec_requests WHERE blocking_session_id <> 0; | 查阻塞关系,blocking_session_id 即源头 | |
DBCC INPUTBUFFER(62); | 查看指定会话最近执行的语句 | |
SELECT * FROM sys.dm_tran_locks WHERE request_status = 'WAIT'; | 查看正在等待的锁与资源 | |
SELECT * FROM sys.dm_tran_active_transactions; | 查看活动事务、开始时间与状态 | |
SELECT * FROM sys.dm_exec_sessions WHERE session_id = 62; | 查看被阻塞会话的登录名与客户端 | |
KILL 62; | 终止会话,先用上面语句确认是业务还是僵尸事务 | |
DBCC OPENTRAN('Shop'); | 查看最老活动事务,判断日志为何不截断 | |
SELECT * FROM sys.fn_dblog(NULL, NULL); | 查看当前日志内容,分析误操作(只读) | |
system_health 会话中的 xml_deadlock_report | 死锁图来源:管理 → 扩展事件 → system_health 会话数据 | |
错误 1205:Transaction was deadlocked on lock resources | 死锁牺牲者,应用需重试并统一加锁顺序 | |
错误 1222:Lock request time out period exceeded | 锁超时,检查长事务与缺失索引导致的扫描 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
BACKUP DATABASE Shop TO DISK = 'D:\bak\Shop_full.bak' WITH INIT, COMPRESSION, CHECKSUM, STATS = 10; | 完整备份,压缩 + 校验和并显示进度 | |
BACKUP DATABASE Shop TO DISK = 'D:\bak\Shop_diff.bak' WITH DIFFERENTIAL, INIT, COMPRESSION; | 差异备份,基于最近一次完整备份 | |
BACKUP LOG Shop TO DISK = 'D:\bak\Shop_log.trn' WITH INIT, COMPRESSION; | 事务日志备份,完整恢复模式下必须定期做 | |
BACKUP LOG Shop TO DISK = 'D:\bak\Shop_tail.trn' WITH NO_TRUNCATE; | 尾部日志备份,灾难恢复前抢救数据 | |
BACKUP DATABASE Shop TO URL = 'https://acct.blob.core.windows.net/bak/Shop.bak' WITH COMPRESSION, CREDENTIAL = 'AzureCred'; | 直接备份到对象存储 | |
RESTORE FILELISTONLY FROM DISK = 'D:\bak\Shop_full.bak'; | 还原前先看逻辑文件名,WITH MOVE 需要用到 | |
RESTORE HEADERONLY FROM DISK = 'D:\bak\Shop_full.bak'; | 查看备份头信息、版本与加密状态 | |
RESTORE VERIFYONLY FROM DISK = 'D:\bak\Shop_full.bak' WITH CHECKSUM; | 校验备份文件可读性,还原演练必做 | |
RESTORE DATABASE Shop FROM DISK = 'D:\bak\Shop_full.bak' WITH MOVE 'Shop' TO 'D:\data\Shop.mdf', MOVE 'Shop_log' TO 'D:\log\Shop.ldf', REPLACE, RECOVERY; | 完整还原并重定位文件,覆盖同名库需 REPLACE | |
RESTORE DATABASE Shop FROM DISK = 'D:\bak\Shop_full.bak' WITH NORECOVERY, REPLACE; | 还原完整备份并保持可继续还原差异与日志 | |
RESTORE DATABASE Shop FROM DISK = 'D:\bak\Shop_diff.bak' WITH NORECOVERY; | 在完整备份之后还原差异备份 | |
RESTORE LOG Shop FROM DISK = 'D:\bak\Shop_log.trn' WITH NORECOVERY; | 按顺序还原日志链,中间不能断 | |
RESTORE LOG Shop FROM DISK = 'D:\bak\Shop_log.trn' WITH STOPAT = '2026-11-01T23:59:59', RECOVERY; | 时点恢复,恢复到误操作之前的时间 | |
RESTORE DATABASE Shop WITH RECOVERY; | 最后一个日志还原完成后使数据库可用 | |
SELECT database_name, type, backup_finish_date, backup_size / 1024 / 1024 AS size_mb, physical_device_name FROM msdb.dbo.backupset b JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id WHERE database_name = 'Shop' ORDER BY backup_finish_date DESC; | 查询备份历史,确认备份链完整 | |
SELECT DB_NAME(database_id) AS db, last_full_backup, last_log_backup FROM (SELECT database_id, MAX(CASE WHEN type = 'D' THEN backup_finish_date END) AS last_full_backup, MAX(CASE WHEN type = 'L' THEN backup_finish_date END) AS last_log_backup FROM msdb.dbo.backupset GROUP BY database_id) t; | 巡检各库最近一次完整与日志备份时间 | |
bcp Shop.dbo.Orders out D:\tmp\orders.txt -c -T -S 127.0.0.1 | 导出表数据为字符文件,-T 使用 Windows 身份验证 | |
bcp Shop.dbo.Orders in D:\tmp\orders.txt -c -T -S 127.0.0.1 -b 10000 | 批量导入数据,-b 控制批大小降低日志压力 | |
BULK INSERT dbo.Orders FROM 'D:\tmp\orders.csv' WITH (FORMAT = 'CSV', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK); | BULK INSERT 导入 CSV,TABLOCK 可最小化日志 | |
SELECT * FROM OPENROWSET(BULK 'D:\tmp\orders.csv', FORMAT = 'CSV', FIRSTROW = 2) AS t; | 不落地直接读取文件内容 | |
CREATE DATABASE Shop ON (NAME = 'Shop', FILENAME = 'D:\data\Shop.mdf') FOR ATTACH; | 附加已有的数据文件(需一并附加日志) | |
EXEC sp_detach_db 'Shop'; | 分离数据库,用于迁移文件位置 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
SELECT * FROM sys.availability_groups; | 查看可用性组列表与集群类型 | |
SELECT ag.name, ar.replica_server_name, ars.role_desc, ars.synchronization_health_desc, ars.operational_state_desc 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 = ars.group_id; | AG 各副本角色与同步健康状态,日常巡检第一句 | |
SELECT ag.name, db.database_name, drs.synchronization_state_desc, drs.log_send_queue_size, drs.redo_queue_size, drs.secondary_lag_seconds FROM sys.dm_hadr_database_replica_states drs JOIN sys.availability_databases_cluster db ON db.group_database_id = drs.group_database_id JOIN sys.availability_groups ag ON ag.group_id = drs.group_id; | 查看发送队列、重做队列与延迟,判断同步是否落后 | |
SELECT * FROM sys.dm_hadr_cluster_members; | 查看 WSFC 节点与仲裁投票状态 | |
SELECT * FROM sys.dm_hadr_availability_group_states; | AG 级别的主副本与失败转移就绪状态 | |
ALTER AVAILABILITY GROUP AG1 FAILOVER; | 手动故障转移(同步提交副本、无数据丢失) | |
ALTER AVAILABILITY GROUP AG1 FORCE_FAILOVER_ALLOW_DATA_LOSS; | 强制故障转移,异步副本可能丢数据,仅在主副本不可用时使用 | |
ALTER DATABASE Shop SET HADR SUSPEND; | 暂停某库的 AG 数据同步,维护时可临时使用 | |
ALTER DATABASE Shop SET HADR RESUME; | 恢复同步,会有一段追赶时间 | |
ALTER AVAILABILITY GROUP AG1 ADD DATABASE Shop; | 把新库加入 AG(需先做完整备份与日志备份) | |
EXEC sp_helppublication; | 在发布库查看发布列表 | |
EXEC sp_helpsubscription; | 查看订阅状态与同步情况 | |
EXEC sp_replmonitorhelppublication; | 复制监控:延迟、未分发命令数 | |
EXEC sp_helparticle; | 查看复制项目(表/字段/存储过程) | |
EXEC sp_helpsubscriberinfo; | 查看订阅端信息与分发代理状态 | |
EXEC sp_help_log_shipping_monitor; | 日志传送监控,查看备份/复制/还原延迟 | |
SELECT * FROM msdb.dbo.log_shipping_monitor_history_detail ORDER BY log_time DESC; | 日志传送历史明细,定位还原中断 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
CREATE LOGIN app_user WITH PASSWORD = 'Str0ng!Passw0rd', CHECK_POLICY = ON, CHECK_EXPIRATION = ON; | 创建 SQL 登录并启用密码策略 | |
CREATE LOGIN [DOMAIN\svc_app] FROM WINDOWS; | 创建 Windows 身份验证登录 | |
CREATE USER app_user FOR LOGIN app_user WITH DEFAULT_SCHEMA = dbo; | 在数据库内为登录创建用户 | |
ALTER ROLE db_datareader ADD MEMBER app_user; | 授予只读角色,比逐表 GRANT 更易维护 | |
ALTER ROLE db_datawriter ADD MEMBER app_user; | 授予写入角色 | |
GRANT SELECT, INSERT, UPDATE ON SCHEMA::dbo TO app_user; | 按架构批量授权,避免逐表授权 | |
GRANT EXECUTE ON dbo.usp_GetOrders TO app_user; | 授予存储过程执行权限 | |
DENY DELETE ON dbo.Orders TO app_user; | 显式拒绝,优先级高于 GRANT | |
REVOKE INSERT ON dbo.Orders FROM app_user; | 撤销已授予的权限 | |
EXEC sp_helprotect @username = 'app_user'; | 查看某用户的权限明细 | |
SELECT dp.name, dp.type_desc, dp.is_disabled FROM sys.database_principals dp WHERE dp.type IN ('S','U','G'); | 查看当前库的用户与角色 | |
SELECT r.name AS role_name, m.name AS member_name FROM sys.database_role_members rm JOIN sys.database_principals r ON r.principal_id = rm.role_principal_id JOIN sys.database_principals m ON m.principal_id = rm.member_principal_id; | 查看角色成员关系 | |
SELECT * FROM sys.server_principals WHERE type IN ('S','U','G'); | 查看实例级登录与服务器角色 | |
ALTER LOGIN app_user DISABLE; | 禁用登录但保留用户与权限 | |
ALTER LOGIN app_user WITH PASSWORD = 'N3w!Passw0rd'; | 修改登录密码 | |
SELECT * FROM sys.sql_logins WHERE is_policy_checked = 0 OR is_expiration_checked = 0; | 巡检弱策略账号,安全检查常用 | |
SELECT * FROM sys.dm_exec_sessions WHERE is_user_process = 1; | 查看当前所有用户会话 | |
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Str0ng!MasterKey'; | TDE 第一步:创建数据库主密钥(在 master 中执行) | |
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate'; | TDE 第二步:创建证书,务必备份证书与私钥 | |
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECert; | TDE 第三步:在业务库创建加密密钥 | |
ALTER DATABASE Shop SET ENCRYPTION ON; | 开启透明数据加密,后台异步加密,注意日志增长 | |
SELECT DB_NAME(database_id) AS db, encryption_state_desc, percent_complete FROM sys.dm_database_encryption_keys; | 查看 TDE 加密进度与状态 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
EXEC msdb.dbo.sp_add_job @job_name = N'NightlyBackup'; | 创建 SQL Agent 作业 | |
EXEC msdb.dbo.sp_add_jobstep @job_name = N'NightlyBackup', @step_name = N'backup', @subsystem = N'TSQL', @command = N'BACKUP DATABASE Shop TO DISK = ''D:\bak\Shop_full.bak'' WITH INIT, COMPRESSION;'; | 添加 T-SQL 步骤,字符串内单引号需转义 | |
EXEC msdb.dbo.sp_add_schedule @schedule_name = N'Daily0300', @freq_type = 4, @freq_interval = 1, @active_start_time = 30000; | 创建每天 03:00 执行的计划 | |
EXEC msdb.dbo.sp_attach_schedule @job_name = N'NightlyBackup', @schedule_name = N'Daily0300'; | 把计划绑定到作业 | |
EXEC msdb.dbo.sp_add_jobserver @job_name = N'NightlyBackup'; | 指定作业在哪个实例执行 | |
EXEC msdb.dbo.sp_start_job @job_name = N'NightlyBackup'; | 手动启动作业 | |
EXEC msdb.dbo.sp_help_job @job_name = N'NightlyBackup'; | 查看作业状态、上次执行结果 | |
EXEC msdb.dbo.sp_stop_job @job_name = N'NightlyBackup'; | 停止正在运行的作业 | |
SELECT j.name, h.step_id, h.step_name, h.run_status, h.run_date, h.run_time, h.message FROM msdb.dbo.sysjobhistory h JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id WHERE h.run_status <> 1 ORDER BY h.run_date DESC, h.run_time DESC; | 查看失败作业记录,run_status 1 表示成功 | |
DBCC CHECKDB('Shop') WITH NO_INFOMSGS, ALL_ERRORMSGS; | 数据库完整性检查,建议每周执行一次 | |
DBCC CHECKTABLE('dbo.Orders') WITH NO_INFOMSGS; | 单表一致性检查,大库可拆分执行 | |
DBCC CHECKDB('Shop') WITH PHYSICAL_ONLY, NO_INFOMSGS; | 只做物理检查,速度快,适合每日巡检 | |
DBCC FREEPROCCACHE; | 清空计划缓存,仅在参数嗅探或升级后必要时使用 | |
DBCC DROPCLEANBUFFERS; | 清空数据缓存,只用于测试环境做冷缓存对比 | |
DBCC UPDATEUSAGE(Shop) WITH COUNT_ROWS; | 修正空间与行数元数据,修复 sp_spaceused 不准 | |
EXEC sp_spaceused; | 查看当前库与主要表的数据/索引空间占用 | |
EXEC sp_updatestats; | 更新全库统计信息,维护窗口常规动作 | |
SELECT name, state_desc, recovery_model_desc, page_verify_option_desc, is_read_committed_snapshot_on FROM sys.databases WHERE database_id > 4; | 一次巡检所有业务库的关键属性 | |
SELECT * FROM sys.dm_os_volume_stats(DB_ID(), NULL); | 查看磁盘容量与剩余空间,提前预警 | |
SELECT * FROM sys.dm_server_services; | 查看各服务运行状态与启动类型 | |
SELECT * FROM sys.dm_server_registry; | 读取实例注册表关键配置项 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
错误 18456:Login failed for user | 账号密码错、登录被禁用、仅 Windows 验证模式或默认库不可用 | |
错误 4060:Cannot open database requested by the login | 默认库被下线/还原中,或用户缺少该库访问权限 | |
错误 9002:The transaction log for database is full | 日志满:先备份日志或切简单模式,检查 log_reuse_wait_desc | |
错误 1105:Could not allocate space for object | 文件组或磁盘已满:查 sys.dm_os_volume_stats 并扩容 | |
错误 1205:Transaction was deadlocked | 死锁牺牲者:统一加锁顺序、缩短事务、补索引减少扫描 | |
错误 1222:Lock request time out period exceeded | 锁等待超时:查 sp_who2 与 sys.dm_tran_locks 找阻塞源 | |
错误 823 / 824:I/O error / logical consistency check failed | 存储层损坏:立即 DBCC CHECKDB,优先从备份恢复并排查硬件 | |
错误 701:There is insufficient system memory | 内存不足:检查 max server memory、查询内存授予与并行度 | |
错误 8645:Timeout waiting for memory resource | 等待内存授予超时:多为大排序/哈希,优化查询或加内存 | |
错误 2627 / 2601:Violation of PRIMARY KEY / UNIQUE constraint | 唯一键冲突:检查并发写入与业务幂等逻辑 | |
错误 547:conflicted with the FOREIGN KEY / CHECK constraint | 外键或检查约束冲突:先补主表数据再写入 | |
错误 208:Invalid object name | 对象不存在或架构前缀缺失:确认默认架构与库上下文 | |
错误 229:The SELECT permission was denied | 权限不足:查 sp_helprotect 与角色成员关系 | |
错误 8152:String or binary data would be truncated | 字段长度不足(2019 起会提示具体列),扩长度或截断数据 | |
错误 241:Conversion failed when converting date and/or time | 隐式转换失败:统一参数类型,避免函数包裹索引列 | |
查询突然变慢但语句没改 | 多为参数嗅探或统计信息过期:更新统计,必要时 OPTION (RECOMPILE) | |
SSMS 连接报证书链错误 | 新版驱动默认加密:勾选“信任服务器证书”或配置有效证书 | |
数据库显示“正在恢复”长时间不结束 | 大库崩溃恢复中:看 ERRORLOG,勿强行重启,评估 RTO |
运维常用组合
sqlcmd -S host -U sa -P pwd -C -Q "SELECT @@VERSION" → 查 sys.databases 状态 → 查 msdb 备份历史sys.dm_exec_requests → sys.dm_exec_query_stats 按 CPU/逻辑读排序 → 实际执行计划 → 补索引或改写法sp_who2 → sys.dm_exec_requests(blocking_session_id) → DBCC INPUTBUFFER → KILL 僵尸会话BACKUP DATABASE WITH COMPRESSION → RESTORE VERIFYONLY → RESTORE FILELISTONLY → RESTORE WITH MOVE, NORECOVERY → RESTORE LOG, RECOVERYsys.master_files 看增长 → DBCC SQLPERF(LOGSPACE) → 备份日志 → DBCC SHRINKFILE 收缩 → 关闭 AUTO_SHRINKsys.dm_hadr_availability_replica_states → sys.dm_hadr_database_replica_states 看 redo_queue_size 与 lag常见问题排查
1. 先看等待类型
sys.dm_os_wait_stats 决定排查方向:PAGEIOLATCH 指向 IO,LCK_ 指向阻塞,CXPACKET 多为并行与统计信息问题
2. 慢查询三步法
实际执行计划看估算行数偏差 → SET STATISTICS IO 看逻辑读 → 检查索引与隐式转换
3. 日志暴涨处理
先查 log_reuse_wait_desc:BACKUP 就补日志备份,ACTIVE_TRANSACTION 找长事务,REPLICATION 查复制延迟
4. 还原前必备份尾部日志
误删数据时先 BACKUP LOG WITH NO_TRUNCATE,再按完整 + 差异 + 日志顺序还原到误操作前
5. 建索引要看写入成本
缺失索引 DMV 只给建议,高频写入表加索引会拖慢 INSERT/UPDATE,需权衡并 INCLUDE 覆盖列
6. 备份没验证等于没备份
定期 RESTORE VERIFYONLY,并做真实还原演练,确认备份链、证书与磁盘路径都可用
相关工具
Linux命令速查
文件、文本、权限、进程、网络、磁盘、systemd与防火墙等 Linux 常用命令速查手册
Docker命令速查
镜像、容器、网络、数据卷、Compose、Swarm、清理与排障等 Docker 常用命令速查手册
Nginx命令速查
服务管理、配置指令、反向代理、HTTPS、限流缓存、日志排查等 Nginx 常用命令速查手册
K8S命令速查
集群上下文、Pod调试、工作负载、Service与Ingress、存储、权限、排障等 kubectl 常用命令速查手册
Windows CMD命令速查
文件目录、网络诊断、进程服务、系统信息、用户权限、注册表、批处理等 Windows CMD 常用命令速查手册
systemctl服务管理速查
服务启停、开机自启、unit文件编写、日志查看、定时器、依赖分析与排障等 systemctl 命令速查手册
关于SQL Server命令速查
在线 SQL Server 命令速查手册,按连接与实例信息、数据库与文件管理、表结构与对象 T-SQL、查询与窗口函数、索引与执行计划、性能诊断 DMV、事务锁与死锁、备份恢复与迁移、AlwaysOn 与复制、安全与权限、作业维护与巡检、常见报错速查等 12 类场景整理 200+ 条常用命令与语句,每条附中文说明与可直接复制的写法,支持关键词搜索、分类折叠和一键复制,打开网页即可查阅。涵盖 sqlcmd 的 -S/-U/-P/-E/-C/-N/-Q/-i/-o/-b 与 :r 脚本引用、@@VERSION 与 SERVERPROPERTY 与 sys.configurations 与 sp_configure、max server memory 与 max degree of parallelism、CREATE DATABASE 与 ALTER DATABASE 文件增长与 RECOVERY FULL/SIMPLE 与 READ_COMMITTED_SNAPSHOT、sys.master_files 与 sys.database_files 与 sp_helpdb、DBCC SHRINKFILE 与 DBCC SQLPERF(LOGSPACE) 与 log_reuse_wait_desc、CREATE TABLE 与 ALTER TABLE 与 sp_rename 与 sp_helptext 与 sys.sql_expression_dependencies、TOP 与 OFFSET FETCH 分页与 CTE 与 ROW_NUMBER/RANK/LAG/LEAD 窗口函数与 STRING_AGG 与 PIVOT 与 OPENJSON 与 FOR JSON 与 TRY_CONVERT 与 MERGE、CREATE INDEX 与 INCLUDE 与筛选索引与 ALTER INDEX REBUILD/REORGANIZE 与 sys.dm_db_index_physical_stats 与 UPDATE STATISTICS 与 DBCC SHOW_STATISTICS、SET STATISTICS IO/TIME 与 SHOWPLAN_XML 与实际执行计划、sys.dm_exec_requests 与 sys.dm_exec_query_stats 与 sys.dm_os_wait_stats 与缺失索引 DMV 与计划缓存与 tempdb 空间与 buffer pool 与 sys.dm_io_virtual_file_stats、BEGIN TRAN 与 TRY CATCH 与 XACT_ABORT 与隔离级别与 sp_who2 与 sys.dm_tran_locks 与 KILL 与死锁 1205/1222、BACKUP DATABASE/LOG 的 COMPRESSION/CHECKSUM/NO_TRUNCATE 与 RESTORE FILELISTONLY/HEADERONLY/VERIFYONLY 与 WITH MOVE/NORECOVERY/STOPAT 时点恢复与 bcp 与 BULK INSERT 与附加分离、AlwaysOn 的 sys.dm_hadr_availability_replica_states 与 redo_queue_size 与 FAILOVER 与复制与日志传送监控、CREATE LOGIN/USER 与角色与 GRANT/DENY 与 sp_helprotect 与 TDE 透明加密、SQL Agent 作业与 DBCC CHECKDB 与 sp_spaceused 与 sp_updatestats 巡检、18456/4060/9002/1105/1205/1222/823/824/701/2627/547/208/8152/241 等报错排查。适用于 SQL Server 数据库日常运维、sqlcmd 脚本化巡检、慢查询与阻塞死锁定位、索引与执行计划优化、备份恢复与灾备演练、AlwaysOn 高可用管理、权限与安全加固,以及 DBA 与运维面试复习。
核心能力
- 涵盖 sqlcmd 的 -S/-U/-P/-E/-C/-N/-Q/-i/-o/-b 与 :r 脚本引用、@@VERSION 与 SERVERPROPERTY 与 sys.configurations 与 sp_configure、max server memory 与 max degree of parallelism、CREATE DATABASE 与 ALTER DATABASE 文件增长与 RECOVERY FULL/SIMPLE 与 READ_COMMITTED_SNAPSHOT、sys.master_files 与 sys.database_files 与 sp_helpdb、DBCC SHRINKFILE 与 DBCC SQLPERF(LOGSPACE) 与 log_reuse_wait_desc、CREATE TABLE 与 ALTER TABLE 与 sp_rename 与 sp_helptext 与 sys.sql_expression_dependencies、TOP 与 OFFSET FETCH 分页与 CTE 与 ROW_NUMBER/RANK/LAG/LEAD 窗口函数与 STRING_AGG 与 PIVOT 与 OPENJSON 与 FOR JSON 与 TRY_CONVERT 与 MERGE、CREATE INDEX 与 INCLUDE 与筛选索引与 ALTER INDEX REBUILD/REORGANIZE 与 sys.dm_db_index_physical_stats 与 UPDATE STATISTICS 与 DBCC SHOW_STATISTICS、SET STATISTICS IO/TIME 与 SHOWPLAN_XML 与实际执行计划、sys.dm_exec_requests 与 sys.dm_exec_query_stats 与 sys.dm_os_wait_stats 与缺失索引 DMV 与计划缓存与 tempdb 空间与 buffer pool 与 sys.dm_io_virtual_file_stats、BEGIN TRAN 与 TRY CATCH 与 XACT_ABORT 与隔离级别与 sp_who2 与 sys.dm_tran_locks 与 KILL 与死锁 1205/1222、BACKUP DATABASE/LOG 的 COMPRESSION/CHECKSUM/NO_TRUNCATE 与 RESTORE FILELISTONLY/HEADERONLY/VERIFYONLY 与 WITH MOVE/NORECOVERY/STOPAT 时点恢复与 bcp 与 BULK INSERT 与附加分离、AlwaysOn 的 sys.dm_hadr_availability_replica_states 与 redo_queue_size 与 FAILOVER 与复制与日志传送监控、CREATE LOGIN/USER 与角色与 GRANT/DENY 与 sp_helprotect 与 TDE 透明加密、SQL Agent 作业与 DBCC CHECKDB 与 sp_spaceused 与 sp_updatestats 巡检、18456/4060/9002/1105/1205/1222/823/824/701/2627/547/208/8152/241 等报错排查
适用场景
适用于 SQL Server 数据库日常运维、sqlcmd 脚本化巡检、慢查询与阻塞死锁定位、索引与执行计划优化、备份恢复与灾备演练、AlwaysOn 高可用管理、权限与安全加固,以及 DBA 与运维面试复习。SQL Server命令速查全程在浏览器中完成,无需安装插件,可放心用于日常办公与排障。
常见问题
- SQL Server命令速查是免费的吗?需要注册或安装吗?
- SQL Server命令速查完全免费,无需注册、无需下载安装任何软件,用浏览器打开网页即可直接使用,也不限制使用次数。
- SQL Server命令速查支持哪些功能?
- 涵盖 sqlcmd 的 -S/-U/-P/-E/-C/-N/-Q/-i/-o/-b 与 :r 脚本引用、@@VERSION 与 SERVERPROPERTY 与 sys.configurations 与 sp_configure、max server memory 与 max degree of parallelism、CREATE DATABASE 与 ALTER DATABASE 文件增长与 RECOVERY FULL/SIMPLE 与 READ_COMMITTED_SNAPSHOT、sys.master_files 与 sys.database_files 与 sp_helpdb、DBCC SHRINKFILE 与 DBCC SQLPERF(LOGSPACE) 与 log_reuse_wait_desc、CREATE TABLE 与 ALTER TABLE 与 sp_rename 与 sp_helptext 与 sys.sql_expression_dependencies、TOP 与 OFFSET FETCH 分页与 CTE 与 ROW_NUMBER/RANK/LAG/LEAD 窗口函数与 STRING_AGG 与 PIVOT 与 OPENJSON 与 FOR JSON 与 TRY_CONVERT 与 MERGE、CREATE INDEX 与 INCLUDE 与筛选索引与 ALTER INDEX REBUILD/REORGANIZE 与 sys.dm_db_index_physical_stats 与 UPDATE STATISTICS 与 DBCC SHOW_STATISTICS、SET STATISTICS IO/TIME 与 SHOWPLAN_XML 与实际执行计划、sys.dm_exec_requests 与 sys.dm_exec_query_stats 与 sys.dm_os_wait_stats 与缺失索引 DMV 与计划缓存与 tempdb 空间与 buffer pool 与 sys.dm_io_virtual_file_stats、BEGIN TRAN 与 TRY CATCH 与 XACT_ABORT 与隔离级别与 sp_who2 与 sys.dm_tran_locks 与 KILL 与死锁 1205/1222、BACKUP DATABASE/LOG 的 COMPRESSION/CHECKSUM/NO_TRUNCATE 与 RESTORE FILELISTONLY/HEADERONLY/VERIFYONLY 与 WITH MOVE/NORECOVERY/STOPAT 时点恢复与 bcp 与 BULK INSERT 与附加分离、AlwaysOn 的 sys.dm_hadr_availability_replica_states 与 redo_queue_size 与 FAILOVER 与复制与日志传送监控、CREATE LOGIN/USER 与角色与 GRANT/DENY 与 sp_helprotect 与 TDE 透明加密、SQL Agent 作业与 DBCC CHECKDB 与 sp_spaceused 与 sp_updatestats 巡检、18456/4060/9002/1105/1205/1222/823/824/701/2627/547/208/8152/241 等报错排查。
- SQL Server命令速查适合哪些场景使用?
- 适用于 SQL Server 数据库日常运维、sqlcmd 脚本化巡检、慢查询与阻塞死锁定位、索引与执行计划优化、备份恢复与灾备演练、AlwaysOn 高可用管理、权限与安全加固,以及 DBA 与运维面试复习。