GeekIt

SQL Server命令速查

sqlcmd 连接、实例与文件、T-SQL 查询、索引执行计划、DMV 诊断、事务锁、备份恢复、AlwaysOn 与权限巡检等 233 条命令

共 233 条命令 · 12 类场景 · 点击右侧图标可复制

命令 / SQL说明复制
sqlcmd -S 127.0.0.1 -U sa -P "P@ssw0rd" -d mastersqlcmd 连接实例,-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, RECOVERY
空间治理sys.master_files 看增长 → DBCC SQLPERF(LOGSPACE) → 备份日志 → DBCC SHRINKFILE 收缩 → 关闭 AUTO_SHRINK
AG 巡检sys.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,并做真实还原演练,确认备份链、证书与磁盘路径都可用

共 233 条 SQL Server 命令 · 示例中的库名 Shop 与路径请替换为实际环境

关于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 与运维面试复习。