MSSQL 2012 Shared Memory Provider 间歇性报错及数据库锁死咨询

报错原因说明 #

你遇到的A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)报错属于服务半挂的典型表现：你手动终止进程时，已经陷入hung状态的SQL Server核心进程直接断开了和SSMS的本地连接，并非操作失误导致。 #

优先级排查步骤 #

一、先留存现场证据（重启前必须做，否则丢失故障根因线索） #

• 提取SQL Server运行日志：进入默认路径C:\Program Files\Microsoft SQL Server\MSSQLxx.你的实例名\MSSQL\Log，复制最近7天的ERRORLOG文件留存，日志中会记录服务崩溃、IO异常、死锁触发的准确时间和关联对象
• 执行内置锁查询语句，定位阻塞源，无需杀进程即可查看持有锁的会话信息：

SELECT 
    request_session_id AS 会话ID,
    OBJECT_NAME(resource_associated_entity_id) AS 锁定表名,
    request_type AS 锁类型,
    request_mode AS 锁模式,
    request_status AS 锁状态
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('你的业务数据库名')

• 打开系统性能监视器，添加以下计数器持续采样30分钟，确认异常触发时的指标特征：
  • SQL Server:Locks\Number of Deadlocks/sec（每秒死锁数）
  • PhysicalDisk% Disk Time（磁盘使用率）
  • Process\sqlservr% Processor Time（SQL服务CPU占用）
  • SQL Server:General Statistics\User Connections（用户连接数）

二、底层环境问题排查 #

• 匹配故障首次出现时间，核对近一周是否有以下变更：Windows系统补丁更新、SQL Server小版本升级、业务功能上线、定时任务新增、服务器磁盘迁移/扩容、防火墙规则调整
• 检查磁盘空间：业务数据库、tempdb所在磁盘剩余空间需至少保留10%以上；确认tempdb无不合理的增长上限，单次增长大小建议设为1GB，避免频繁触发增长锁
• 核对SQL Server内存配置：32G内存的服务器，SQL服务最大内存建议设为28G以内，8G内存的服务器建议设为6G以内，预留足够内存给系统进程和连接调度，内存不足会导致SQL服务主动断开连接甚至半挂
• 确认SQL Server网络配置：打开SQL Server配置管理器，检查共享内存、TCP/IP协议均处于启用状态，TCP端口为固定端口，无动态端口冲突

三、业务侧锁问题排查 #

• 开启扩展事件捕获死锁图（性能影响远小于传统跟踪标记），死锁图会直接展示互斥会话执行的SQL语句、持有的锁资源、等待的锁资源，可直接定位到问题业务代码
• 排查长事务未提交场景：多数累积性锁死都是业务代码开启事务后，遇到异常未主动回滚，长时间持有表/行锁，后续访问同表的会话持续阻塞，最终拖垮整个服务
• 检查近期新增的查询逻辑：是否存在SELECT * FROM 表 WITH(TABLOCKX)这类强制排他表锁的写法，或者UPDATE/DELETE语句未走索引导致全表扫描，大量行锁升级为表锁引发全局阻塞

内容的提问来源于stack exchange，提问作者Scott