Sql Server 数据库问题状态修复 生产阻塞查询
一. 数据库问题状态修复
1. 正在还原状态
1. 将数据库还原至'正在还原'状态，不对数据库执行任何操作，不回滚未提交的事务。可以还原其他事务日志：
RESTORE database db with norecovery
2. 修复'正在还原'状态，通过回滚未提交的事务，使数据库处于可以使用的状态。无法还原其他事务日志。
RESTORE database db with recovery
2. 置疑、可疑、脱机、紧急
-- sqlserver:
1.设置数据库状态，重置标志：EXEC sp_resetstatus 'db'
2.设置数据库为紧急模式，脚本：ALTER DATABASE db SET EMERGENCY
3.设置数据库为单用户模式（对数据库检查修复只能在单用户模式下），脚本：ALTER DATABASE db SET SINGLE_USER
4.检查并修复数据库，会提示你修复数据库可能丢失数据，正常应该不会丢失数据库数据，但是数据库日志的完整性将受到破坏，脚本：DBCC CheckDB (db, REPAIR_ALLOW_DATA_LOSS)
或者
DBCC CHECKDB (db, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
5.恢复数据库为多用户模式，脚本：ALTER DATABASE db SET MULTI_USER
6.从紧急模式设置回正常模式：ALTER DATABASE db SET ONLINE
-- 方式2，进一步恢复数据库状态
1.使用以下命令将数据库置于紧急模式，使用此命令后，紧急标记将放置在文件后面：
ALTER DATABASE db SET EMERGENCY
2.将数据库分离并重新连接主数据库
ALTER DATABASE [db] set multi_user
EXEC sp_detach_db db
3.重新附加指定路径下mdf文件，使用此方式，用户将能够创建一个新的日志文件，损坏的文件会自动从数据库中删除：
EXEC sp_attach_single_file_db @DBName = '新数据库名', @physname = N'F:\db.mdf'
3. 主体、镜像、同步、还原等
以上状态一般出现于镜像同步过程中.
# 依次分别在镜像Server和主Server上运行以下命令:
1. 在镜像Server上线运行
ALTER DATABASE [需镜像数据库名]
SET PARTNER = 'TCP://192.168.XXX.XXX:5022';
GO
2. 在主Server上运行
ALTER DATABASE [需镜像数据库名]
SET PARTNER = 'TCP://192.168.XXX.XXX:5022';
GO
--注意:源数据库名和镜像数据库名字要一致.
4. 处于转换状态
步骤：
1.执行语句 查询出所有正在操作的spid
EXEC sp_who2
2.执行语句 spid为上面的spid：**注意** 查出来的为服务器连接所有数据库的信息，只需kill掉无法操作的数据库信息就可以
KILL <SPID>
3.处理后如果处于脱机状态，重新联机即可。
二. SQL等待状态分析
- sys.dm_os_wait_stats可以返回SQL Server启动以来所有等待状态总的等待数和等待时间，是个累积值。
| 列名 | 描述 |
|---|---|
| wait_type | 等待类型的名称。有关详细信息，请参阅"等待类型"。 |
| waiting_tasks_count | 该等待类型的等待数，该计数器在每开始一个等待时便会增加。 |
| wait_time_ms | 该等待类型的总等待时间（毫秒）。该时间包括 signal_wait_time_ms。 |
| max_wait_time_ms | 该等待类型的最长等待时间。 |
| signal_wait_time_ms | 正在等待的线程从收到信号通知到其开始运行之间的时间差。 |
| pdw_node_id | 此分发所在节点的标识符。 |
1. LCK_XX 和 WRITELOG
- LCK_XX
有阻塞发生，经常会看到以“LCK_”开头的等待状态。
结论：大事务大并发，需优化查询。 -
WRITELOG
和磁盘有关的另一个等待状态是WRITELOG，说明任务当前正在等待将日志记录写入日志文件，通常也意味着磁盘写入速度跟不上。
2. CXPACKET
- CXPACKET是指：线程正在等待彼此完成并行处理。什么意思呢？ 当sql server发现一条指令复杂时，会决定用多个线程并行来执行，由于某些并行线程已完成工作，在等待其它并行线程来同步，这种等待就叫CXPACKET。
1.并行处理的优势： 用多个线程来执行一个指令，当sql server发现一条指令复杂时或语句中含有大数据量要处理，此时执行计划会决定用多个线程并行来执行，从而提高整体响应时间，例如一个指令读入100w条记录， 如果用一个线程做 可能需要10秒， 如果10个线程来做 可能只需要1秒，加上线程间同步时间也不过2秒。
2.并行处理的劣势：1是并行线程要等待同步。2是由于这10个线程全力以赴，就有10个对应的cpu，这样别的用户发过来的指令就会受到影响，甚至拿不到cpu来执行。所以对于并发度要求高的需要及时响应的，一般会建议手动设置每个指令的并行线程数。反之可以不设置Max Degree of Parallelism由系统默认去并行或者设少一点并行度。
- 检查处理
(1) 通过实例级别查出CXPACKET的等待时间包括总等时间，平均等待时间，最大等待时间。
(2) 查看并行的前十条语句 (这种查询不建议使用，因为条件是查找含有并行parallel的执行计划，查询响应很慢)。
(3) 找出cpu和i/o耗性能最高的sql语句， 查看执行计划是否有并行处理。
(4) 找出程序中感觉复杂的sql语句，查看执行计划。
(5) 避免或减少白天执行频繁复杂sql，优化sql 建好索引。
(6) 当执行计划发现并不需要用并行执行时，强制sql 使用OPTION ( MAXDOP x) 也不会采用并行执行。
最后考虑调整并行度的开销阈值或降低并行度。设置sql语句级的MAXDOP。如果MAXDOP=1的话，使得一个BATCH只对应一个TASK。如果没有设置MAXDOP，一个BATCH可能会产生多个TASKS，那么TASK之间的协调，等待等等，将是很大的开销。把MAXDOP设小，能同时减少WORKER的使用量。所以，如果我们看到等待类型为CXPACKET的话，那么我们可以设置MAXDOP，减少并行度。
3. PAGEIOLATCH_%
这类等待发生在内存缓冲池中的数据页要与磁盘上数据文件的数据页进行交互时，所以闩锁名称中包含IO。为保证不会有多个用户同时读取/修改内存中的数据页，SQL Server会对内存中的数据页加闩锁，称为PAGEIOLATCH_%。
- 根据不同的等待资源，%的值不同，可能出现以下的等待：
PAGEIOLATCH_NL：Null buffer page I/O latch
PAGEIOLATCH_KP：Keep buffer page I/O latch
PAGEIOLATCH_SH：Shared buffer page I/O latch
PAGEIOLATCH_UP：Update buffer page I/O latch
PAGEIOLATCH_EX：Exclusive buffer page I/O latch
PAGEIOLATCH_DT：Destroy buffer page I/O latch
'等待发生过程示例,这里以最容易发生的PAGEIOLATCH_SH为例，看看这种等待是怎么发生的:'
1）有一个用户请求，必须读取整张X表，由Worker X执行
2）Worker X在表扫描过程中发现要读取page 1:100
3）sqlserver发现page 1:100不在内存的数据缓冲池里
4）sqlserver在数据缓冲池找到一个page的空间，Worker X在上面申请EX模式latch，防止将数据加载入内存前有人读写该page
5）Worker X发起一个异步的IO请求，要求从数据文件中读取page 1:100
6）Worker X需要从内存中读取page 1:100，读取动作需要申请SH模式latch
7）由于Worker X之前申请的EX模式latch还没释放，因此申请SH模式latch的操作将被阻塞（自己阻塞自己）。由于Worker X在等待获取SH模式latch，等待类型即为PAGEIOLATCH_SH
8）当异步IO结束后，系统会通知Worker X，page 1:100已写入内存
9）Worker X释放EX模式latch，此时即可获得SH模式latch
10）Worker X在内存中读page 1:100，读取结束后，进行之后的工作
- 结论
可以看到，当发生PAGEIOLATCH类型的等待时，SQL Server一定是在等待某个I/O动作的完成。如果经常出现这类等待，说明磁盘速度不能满足要求，已经成为SQL Server的瓶颈。
'要强调的是，PAGEIOLATCH等待最常见分两大类'：
-- PAGEIOLATCH_SH
经常发生在用户想要访问一个数据页，但该数据页不在缓存中，SQL Server需要先把该页从磁盘读往内存。如果这个问题经常发生，说明内存很可能不够大，触发了SQL Server做了很多读取页面的工作，进而引发了磁盘读的瓶颈。此时主要是内存有瓶颈，磁盘只是内存压力的副产品。
-- PAGEIOLATCH_EX
经常发生在用户对数据页面做了修改，SQL Server要向磁盘回写时，意味着磁盘写的速度跟不上，和内存没直接关系。
4. PAGELATCH_%
首先注意PAGELATCH_%虽然跟PAGEIOLATCH_%名字很像，但它们的成因是完全不一样的，可以说除了名字像其他没啥像了（类似buffer busy wait和free buffer wait）。
- 成因
SQLServer为了解决在插入数据时物理层的插入冲突，引入了PAGELATCH_%类的latch。当任务要修改page时，必须先申请一个EX的latch，只有得到这个latch，才能修改page内容。
数据页的修改都是在内存中完成，所以时间应该非常短，可以忽略不计，而PAGELATCH只在修改过程中才出现，所以正常生存周期应该很短。如果在数据库中经常遇到，说明：
SQLServer没有明显的内存和磁盘瓶颈。
可能有Hot Page：大量并发语句在修改同一张表，且修改集中在一个或少量几个页面，具体可以通过wait_resource字段看到
- 结论
这种瓶颈无法通过提高硬件配置解决，只能通过修改表设计或者业务逻辑，让修改分散，提高并发性。
- Hot page的缓解方法
对于频繁插入的表，避免在Identity的字段上建聚集索引，使同一时间插入有机会分散到不同的页面上。
如果一定要在Identity的字段上建聚集索引，建议在其他某个列上建若干个分区。
详细参考：
https://support.microsoft.com/en-us/help/4460004/how-to-resolve-last-page-insert-pagelatch-ex-contention-in-sql-server
5. LATCH_%
在sqlserver中，除了buffer latch，其他资源上也会出现latch。正常情况下，这些latch的申请和释放都是很快的，用户应该不会看到相应等待。
- Latch等待常见原因
1）某个先前的任务出现了访问越界异常（Access Violation），SQLServer强制终止了该任务，但是没有完全将它申请的资源释放干净，使某些latch成为孤儿，导致后面任何任务要申请同样的资源都会被阻塞。这类问题容易判断：只要打开sqlserver错误看看之前有没有出现过Access Violation即可，但是一般无法从用户层面解决，只能重启sqlserver服务。
2）同时发生其他资源瓶颈，如内存、线程调用、磁盘等，而latch等待只是一个衍生的等待。
3）当某个数据文件空间用尽，做自动增长的时候，其他任务必须等待，这时也会出现latch资源等待。
4）在一些特殊情况下，可能是SQLServer自己没有处理好，没有使用比较优化的算法（相当于bug），使得用户比较容易遇到等待，一些补丁就曾修复过这类问题。
- 结论
可以看到，一般latch等待都是由其他问题衍生而来。当数据库中出现大量latch等待时，首先要检查SQLServer是否健康运行，看错误日志是否有出现过异常，是否有其他资源瓶颈。另外，将sqlserver升级到最新版本，通常是推荐的做法。
6. ASYNC_NETWORK_IO
当sqlserver要返回结果集给客户端时，会先将结果集填充到输出缓存（output cache），同时网络层会开始将输出缓存里的数据打包，由客户端接收。如果sqlserver返回结果集的速度快过客户端接收速度，当结果集较大时，就会出现输出缓存被填满，sqlserver没地方填充结果集的问题。此时，任务就会进入ASYNC_NETWORK_IO的等待。注意这个等待一般不是数据库的问题，而是客户端由于各种各样的没有及时取走数据，因此调整数据库配置一般不会有大的帮助。
- ASYNC_NETWORK_IO等待常见原因及优化建议
- 1.网络层瓶颈当然是一个可能的原因
是否真有必要返回如此大量的结果集。通常大结果集对应用程序的影响远远大于sqlserver，如果应用要返回上百万千万行结果展示给用户，先崩溃的不会是sqlserver而是应用程序和用户。
- 2.应用程序端的性能问题。其实这才是最常见的问题，sqlserver将结果集打包好发向客户端后，要等客户端确认收到，才会接着发下一个包。如果客户端确认很慢，sqlserver也不得不慢慢发。至于为什么客户端确认很慢，可能是客户端有意只取开头的一部分数据（这就回到前一个问题，是否真有必要返回如此大量的结果集），或者客户端遇到CPU、内存、磁盘等资源瓶颈，运行得很慢
- 3.分布式死锁。常见于长时间的ASYNC_NETWORK_IO等待并且在sql内部造成了阻塞。
7. SOS_SCHEDULER_YIELD
任务的状态可以通过sys.dm_exec_requests的task_state列和sys.sysprocesses的status列查看，如果经常看到很多任务状态是runnable，就要严肃对待了。正常的SQLServer哪怕很忙，也不该经常看到runnable的任务，连running的状态都不应该很多。
- 原因
这个问题最明显的特征就是大量任务处于runnable状态，等待类型为SOS_SCHEDULER_YIELD。如果出现这种状态，说明很多任务可以运行但没在运行，这同样会严重影响sqlserver性能。
- 结论
如果遇到没有报17883/17884之类的警告，出现非常多的runnable任务通常可能有两种原因：
1. SQLServer CPU接近100%
此时真的是没有足够cpu来及时处理用户的并发任务。应该优化最耗CPU资源的语句，或者紧急加CPU。
2. 自旋锁（2008后基本不会有）
8. 内存相关等待
当用户任务申请内存暂时申请不到时，会出现一些特殊的等待状态：
CMEMTHREAD
在一个时间点，只有一个连接能往一块缓存区申请/释放内存，当多个用户想要同时往同一块缓存区申请/释放内存时，只有一个连接能成功，其他的必须等待，等待的事件就是CMEMTHREAD。
这种等待通常只发生在并发特别高的sqlserver里，这些并发的连接通常大量使用每次执行都需编译的动态t-sql语句。
--解决方法
看能否降低并发量
修改客户端行为，使用存储过程或参数化sql以减少编译量，增加执行计划重用度
SOS_RESERVEDMEMBLOCKLIST
如果sql语句包含大量参数、或者in子句包含大量值，它的执行计划可能超过8KB，需要申请multi-page区域来存储，当申请暂时不能得到满足时，等待的就是这个事件。这个问题更常见于32位机器，因为32位机器multi-page区域很小而且远小于buffer pool，如果multi-page有内存压力而buffer pool没有，并不会触发lazy writer刷数据清理内存。
--解决方法
避免使用包含大量参数、或者in子句包含大量值的sql，这能从根本上解决问题
定期运行DBCC FREEPROCCACHE语句，手动清除缓存的执行计划。能暂时缓解，在生产环境谨慎操作。
扩展multi-page区域大小或使用64位机器，能延迟问题发生时间
RESOURCE_SEMAPHORE_QUERY_COMPLIE
如果sql或存储过程过于复杂，编译所需的内存可能超乎你的想象。sqlserver为编译内存设了一个上限，当在编译的sql使用内存达到这个上限后，后面的语句只能等前面的语句编译完释放内存后才能继续编译，此时等待的事件就是RESOURCE_SEMAPHORE_QUERY_COMPLIE。
--解决方法
修改客户端行为，使用存储过程或参数化sql以减少编译量，增加执行计划重用度
避免使用过于复杂的sql，降低编译需要的内存量
定期运行DBCC FREEPROCCACHE语句，手动清除缓存的执行计划。能暂时缓解，在生产环境谨慎操作。
SQLTRACE_%
追踪：对于繁忙的SQLServer，开启SQL Trace很可能对性能产生负面影响。如果经常出现这种等待，除非迫不得已，否则应该立刻停止SQL Trace。
9. 总结各阶段常见等待
- 总结一下一个用户请求在其生命周期中大致会经过哪些阶段，以及在各阶段可能需要等待的资源。
1. 客户端发出请求指令，经过网络层，SQL Server接收到
如果指令比较长或者比较多，客户端发指令的快慢就会影响SQLServer接收的速度，网络传输速度也会有影响。
对第一条，将上百个小指令合并成几个大的批处理可能提升性能；
对第二条，把大的批处理/语句写成存储过程，减少网络传输，也会对性能有所帮助。
2. SQL Server进行检查、编译，生成执行计划或找到缓存的计划重用
这一步耗费资源的种类比较多：
-- CPU：
做检查、编译、生成计划都需要计算，这一步耗费CPU资源比较多，尤其是sql复杂需要大量计算时。
-- 内存：
对于非常长sql，或者由几万几十万语句组成的批处理，要耗费大量内存。如果内存紧张，一般就会出现前面提到的内存等 待，或者直接报701错误。
-- 表上的架构锁（schema lock）：
在编译时，要防止对架构进行修改。如果大量用户并发做编译操作或者有操作申请了级别很高的锁，编译时可能遇到阻塞。
确认缓存中是否有执行计划可用时，要在内存中进行搜索，可能会产生自旋锁。
3. 运行指令
得到执行计划后就进入运行阶段，通常这步耗时最长，用到的资源也最多。在这一步要做很多事情：
-- SQLServer首先为指令的运行申请内存。
需要的内存大小与sql的长度、复杂度有关，如果同时需要执行很多复杂sql，可能新sql在申请内存时就会遇到上面所说的 内存等待。
-- 如果发现要访问的数据不在内存中。
要将数据从磁盘读到内存，如果发现内存没有足够的空闲页面存放所有数据，通常的等待状态是：PAGEIOLATCH_%。
-- 按照执行计划scan或seek内存中的数据页面，找定需要的记录。
这一步需要申请各种各样的锁，以实现事务隔离，可能会引起阻塞，遇到LCK_%相关等待。
-- 指令可能还要做一些连接或者计算工作（sum、max、sort等）
这一步主要使用CPU。如果执行计划不够优化、计算量庞大，而又没有等待其他资源，通常CPU使用率会较高。
-- 根据指令内容、执行计划和数据量，可能还会在tempdb创建临时对象
此时有可能出现tempdb瓶颈。
-- 如果指令需要修改数据记录，SQLServer会修改内存缓冲区里的页面内容。
由于对象在内存中，不会触发磁盘写入，若大量修改同一页面，容易导致hot page，出现PAGELATCH_%等待。
-- 在提交事务之前，SQLServer必须将相应的日志记录按照顺序写入日志文件，此时可能出现WRITELOG等待。
-- 将结果集返回给客户端，此时容易出现ASYNC_NETWORK_IO等待
4.以上动作都要先在sqlos中拿到Worker（Thread），这个Worker能还要排上scheduler，才能在CPU上运行：
-- 如果所有的Worker都在忙，没有空闲Worker，那么任务就要等待一个Worker出现。此时等待状态是THREADPOOL。
而sys.dm_os_schedulers.work_queue_count的值会不等于0。
-- 任务拿到worker，但scheduler正在运行其他Worker，任务会进入等待队列。
此时任务状态是runnable，而sys.dm_os_schedulers.runnable_tasks_count>1。
-- 任务拿到scheduler，进入running状态，如果非常耗CPU，会出现CPU使用率高的现象。
总之，从任务当前等待状态可以大概知道它当前运行到哪一步，也可以分析出sqlserver可能存在的资源瓶颈。在遇到性能问题时，先查看sys.dm_exec_requests这类DMV中各连接的状态，对定位问题很有帮助。
三. 常见阻塞查询
1.常用查询
# 阻塞和死锁查询
DECLARE @spid INT,
@bl INT,
@intTransactionCountOnEntry INT,
@intRowcount INT,
@intCountProperties INT,
@intCounter INT
CREATE TABLE #tmp_lock_who
(
id INT IDENTITY(1, 1),
spid SMALLINT,
bl SMALLINT
)
INSERT INTO #tmp_lock_who ( spid, bl )
SELECT 0, blocked
FROM(SELECT*
FROM sys.sysprocesses
WHERE blocked > 0
) a
WHERE NOT EXISTS(SELECT*
FROM(SELECT*
FROM sys.sysprocesses
WHERE blocked > 0
) b
WHERE a.blocked = spid )
UNION
SELECT spid, blocked
FROM sys.sysprocesses
WHERE blocked > 0
-- 找到临时表的记录数
SELECT @intCountProperties =COUNT(*), @intCounter = 1
FROM #tmp_lock_who
IF @intCountProperties = 0
SELECT N'现在没有阻塞和死锁信息'ASmessage
-- 循环开始
WHILE @intCounter <= @intCountProperties
BEGIN
-- 取第一条记录
SELECT @spid = spid, @bl = bl
FROM #tmp_lock_who
WHERE Id = @intCounter
BEGIN
IF @spid = 0
SELECT N'引起数据库死锁的是: '+CAST(@bl AS VARCHAR(10))
+ N'进程号,其执行的SQL语法如下'
ELSE
SELECT N'进程号SPID：'+CAST(@spid AS VARCHAR(10))
+ N'被进程号SPID：'+CAST(@bl AS VARCHAR(10)) N'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
END
-- 循环指针下移
SET @intCounter = @intCounter + 1
END
DROP TABLE #tmp_lock_who
# 查询锁
SELECT * FROM sys.sysprocesses WHERE blocked>0
# 查询死锁
select *
from master..SysProcesses
where db_Name(dbID) = '数据库名称'
and spId <> @@SpId
and dbID <> 0
and blocked >0;
“Spid”是SQL Server进程ID号，由SQL Server分配给每个新连接。它从一开始就是全球独一无二的。它被视为master..sysprocesses的spid列、sp\u who的spid列，或者在SQL Administrator中选择Manage/System时的“Proc ID”列。
“Kpid”是内核进程ID。在SQL Server for Windows NT下，这是线程ID号，也称为“ID thread”，在创建线程时由Windows NT分配。线程ID号是唯一标识线程的系统范围标识符，可通过调用Win32 API GetCurrentThreadID（）获得。
2.阻塞查询 sp_lock
# 执行 exec sp_lock
# 查看 spid 为 416 的进程
DBCC INPUTBUFFER(416)
spid 是指进程ID，这个过滤掉了系统进程，只展示了用户进程spid>50。
dbid 指当前实例下的哪个数据库 , 使用DB_NAME() 函数来标识数据库
type 请求锁住的模式
mode 锁的请求状态
GRANT：已获取锁。
CNVRT：锁正在从另一种模式进行转换，但是转换被另一个持有锁（模式相冲突）的进程阻塞。
WAIT：锁被另一个持有锁（模式相冲突）的进程阻塞。
# 总结：
当mode 不为GRANT状态时， 需要了解当前锁的模式，以及通过进程ID查找当前sql 语句
例如当前进程ID是416，且mode状态为WAIT 时，查看方式 DBCC INPUTBUFFER(416)
'用sp_lock查询显示的信息量很少，也很难看出谁被谁阻塞。所以当数据库版本为2005及以上时不建议使用。'
3.阻塞查询 dm_tran_locks
SELECT
t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address;
上面查询只显示有阻塞的会话， 关注blocking_session_id 也就是被阻塞的会话ID，同样使用DBCC INPUTBUFFER来查询sql语句.
4.阻塞查询 sys.sysprocesses (重点常用)
# 通用查询语句
select * FROM sys.sysprocesses WITH(NOLOCK)
# 修改部分细节便于查看
SELECT spid,kpid,blocked,waittime,
lastwaittype,DB_NAME(dbid)AS DB,waitresource,
open_tran,hostname,[program_name],
hostprocess,loginame,[status]
FROM sys.sysprocesses WITH(NOLOCK)
where [status]<>'sleeping'
# 关键字段说明:
spid 会话ID（进程ID），SQL内部对一个连接的编号，一般来讲小于50
kpid 线程ID
blocked: 阻塞的进程ID， 值大于0表示阻塞, 值为本身进程ID表示io操作
waittime:当前等待时间（以毫秒为单位）。
open_tran: 进程的打开事务数
hostname:建立连接的客户端工作站的名称
program_name 应用程序的名称。
hostprocess 工作站进程 ID 号。
loginame 登录名。
----------------------------
# [status]
running = 会话正在运行一个或多个批
background = 会话正在运行一个后台任务，例如死锁检测
rollback = 会话具有正在处理的事务回滚
pending = 会话正在等待工作线程变为可用
runnable = 会话中的任务在等待,由scheduler来运行的可执行队列中。(#重要)
spinloop = 会话中的任务正在等待调节锁变为可用。
suspended = 会话正在等待事件（如 I/O）完成。(#重要)
sleeping = 连接空闲
wait resource 格式为 fileid:pagenumber:rid 如(5:1:82)
----------------------------
# kpid与waittime
kpid>0, waittime=0 处于运行状态
kpid>0, waittime>0 需要等待某个资源，才能继续执行,一般会是suspended（等待io）
kpid=0, waittime=0 但它还是阻塞的源头，查看open_tran>0 事务没有及时提交。
如果blocked>0，但waittime时间很短，说明阻塞时间不长，不严重
如果status 上有好几个runnable状态任务，需要认真对待。 cpu负荷过重没有及时处理用户的并发请求
## 优点:
sys.sysprocesses 能显示会话进程有多少， 等待时间， open_tran有多少事务， 阻塞会话是多少. 整体内容更为详细。同样使用DBCC INPUTBUFFER(spid)来查询sql语句.
# WITH(NOLOCK)
语句执行时不发出共享锁，允许脏读 ，等于 READ UNCOMMITTED事务隔离级别 。nolock确实在查询时能提高速度，但它并不是没有缺点的，起码它会引起脏读、只适用与select查询语句。 在一些不需要考虑脏读的场合会用到，例如当用户在论坛发广告贴时删除其所有发帖，这个查询就不怕脏读，全删，或者漏一个正在发的都不是问题。
5.DBCC INPUTBUFFER 查询具体命令
# 语法
DBCC INPUTBUFFER (spid)
显示从客户端发送到 Microsoft® SQL Server™ 的最后一个语句。
参数spid是 sp_who 系统存储过程的输出中所显示的用户连接系统进程 ID (SPID)。
结果集 DBCC INPUTBUFFER 返回包含如下列的行集。
# 例如:
当缓冲区中的最后事件是 DBCC INPUTBUFFER(11) 时，DBCC INPUTBUFFER 将返回以下结果集。
------------ ---------- ------------------------
|EventType | Parameters| EventInfo |
------------ ---------- ------------------------
|RPC Event | 0 | DBCC INPUTBUFFER (11)|
------------ ---------- ------------------------
# 说明
EventType 事件类型，例如：RPC、语言或无事件。
Parameters 0 = 文本
1- n = 参数
EventInfo 对于 RPC 的 EventType，EventInfo 仅包含过程名。对于语言或无事件的 EventType，仅显示事件的头 255 个字符。
6.查询阻塞 sp_who2
# 显示当前用户请求的阻塞信息
EXEC sp_who2
通过下面的语句可以将sp_who2的显示信息导入到一张临时表里．也可以导入到一张永久表里．便于我们过滤一些不相关的内容.
CREATE TABLE aaa(SPID INT,Status VARCHAR(255),
Login VARCHAR(255),HostName VARCHAR(255),
BlkBy VARCHAR(255),DBName VARCHAR(255),
Command VARCHAR(255),CPUTime INT,
DiskIO INT,LastBatch VARCHAR(255),
ProgramName VARCHAR(255),SPID2 INT,
REQUESTID INT);
INSERT INTO aaa EXEC sp_who2;
SELECT * FROM aaaa
WHERE DBName <> 'master'
ORDER BY LastBatch;
7.动态性能视图DMV(推荐)
动态性能视图展示了更多更丰富的信息,帮助DBA快速诊断"当前"的阻塞现象. 它还能捕获诸如SQL语句和执行当前SQL语句已经使用的CPU时间, 内存大小,运行总时间,逻辑读数等.
运行如下语句:
use [master]
GO
SELECT
DB_NAME(Blocked.database_id) AS 'database',
Blocked.Session_ID AS 'blocked SPID',
Blocked_SQL.TEXT AS 'blocked SQL',
Waits.wait_type AS 'wait resource',
Blocking.Session_ID AS 'blocking SPID',
Blocking_SQL.TEXT AS 'blocking SQL',
sess.status AS 'blocking status',
sess.total_elapsed_time AS 'blocking elapsed time',
sess.logical_reads AS 'blocking logical reads',
sess.memory_usage AS 'blocking memory usage',
sess.cpu_time AS 'blocking cpu time',
sess.program_name AS 'blocking program',
GETDATE() AS 'timestamp'
FROM sys.dm_exec_connections AS Blocking
INNER JOIN sys.dm_exec_requests AS Blocked ON Blocked.Blocking_Session_ID = Blocking.Session_ID
INNER JOIN sys.dm_os_waiting_tasks AS Waits ON waits.Session_ID = Blocked.Session_ID
INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = Blocking.Session_ID
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS Blocking_SQL
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS Blocked_SQL
GO
其他DMV
SqlServer有关锁和内存使用的DMV
--查看连接当前数据库的SPID所加的锁
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT DB_NAME(resource_database_id) AS DatabaseName
, request_session_id
, resource_type
, CASE
WHEN resource_type = 'OBJECT'
THEN OBJECT_NAME(resource_associated_entity_id)
WHEN resource_type IN ('KEY', 'PAGE', 'RID')
THEN (SELECT OBJECT_NAME(OBJECT_ID)
FROM sys.partitions p
WHERE p.hobt_id = l.resource_associated_entity_id)
END AS resource_type_name
, request_status
, request_mode
FROM sys.dm_tran_locks l
WHERE request_session_id !=@@spid
ORDER BY request_session_id
--查看没关闭事务的空闲Session
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT es.session_id, es.login_name, es.host_name, est.text
, cn.last_read, cn.last_write, es.program_name
FROM sys.dm_exec_sessions es
INNER JOIN sys.dm_tran_session_transactions st
ON es.session_id = st.session_id
INNER JOIN sys.dm_exec_connections cn
ON es.session_id = cn.session_id
CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) est
LEFT OUTER JOIN sys.dm_exec_requests er
ON st.session_id = er.session_id
AND er.session_id IS NULL
--查看被阻塞的语句和它们的等待时间
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
Waits.wait_duration_ms / 1000 AS WaitInSeconds
, Blocking.session_id as BlockingSessionId
, DB_NAME(Blocked.database_id) AS DatabaseName
, Sess.login_name AS BlockingUser
, Sess.host_name AS BlockingLocation
, BlockingSQL.text AS BlockingSQL
, Blocked.session_id AS BlockedSessionId
, BlockedSess.login_name AS BlockedUser
, BlockedSess.host_name AS BlockedLocation
, BlockedSQL.text AS BlockedSQL
, SUBSTRING (BlockedSQL.text, (BlockedReq.statement_start_offset/2) + 1,
((CASE WHEN BlockedReq.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), BlockedSQL.text)) * 2
ELSE BlockedReq.statement_end_offset
END - BlockedReq.statement_start_offset)/2) + 1)
AS [Blocked Individual Query]
, Waits.wait_type
FROM sys.dm_exec_connections AS Blocking
INNER JOIN sys.dm_exec_requests AS Blocked
ON Blocking.session_id = Blocked.blocking_session_id
INNER JOIN sys.dm_exec_sessions Sess
ON Blocking.session_id = sess.session_id
INNER JOIN sys.dm_tran_session_transactions st
ON Blocking.session_id = st.session_id
LEFT OUTER JOIN sys.dm_exec_requests er
ON st.session_id = er.session_id
AND er.session_id IS NULL
INNER JOIN sys.dm_os_waiting_tasks AS Waits
ON Blocked.session_id = Waits.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle)
AS BlockingSQL
INNER JOIN sys.dm_exec_requests AS BlockedReq
ON Waits.session_id = BlockedReq.session_id
INNER JOIN sys.dm_exec_sessions AS BlockedSess
ON Waits.session_id = BlockedSess.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL
ORDER BY WaitInSeconds
--查看超过30秒等待的查询
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
Waits.wait_duration_ms / 1000 AS WaitInSeconds
, Blocking.session_id as BlockingSessionId
, Sess.login_name AS BlockingUser
, Sess.host_name AS BlockingLocation
, BlockingSQL.text AS BlockingSQL
, Blocked.session_id AS BlockedSessionId
, BlockedSess.login_name AS BlockedUser
, BlockedSess.host_name AS BlockedLocation
, BlockedSQL.text AS BlockedSQL
, DB_NAME(Blocked.database_id) AS DatabaseName
FROM sys.dm_exec_connections AS Blocking
INNER JOIN sys.dm_exec_requests AS Blocked
ON Blocking.session_id = Blocked.blocking_session_id
INNER JOIN sys.dm_exec_sessions Sess
ON Blocking.session_id = sess.session_id
INNER JOIN sys.dm_tran_session_transactions st
ON Blocking.session_id = st.session_id
LEFT OUTER JOIN sys.dm_exec_requests er
ON st.session_id = er.session_id
AND er.session_id IS NULL
INNER JOIN sys.dm_os_waiting_tasks AS Waits
ON Blocked.session_id = Waits.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle)
AS BlockingSQL
INNER JOIN sys.dm_exec_requests AS BlockedReq
ON Waits.session_id = BlockedReq.session_id
INNER JOIN sys.dm_exec_sessions AS BlockedSess
ON Waits.session_id = BlockedSess.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL
WHERE Waits.wait_duration_ms > 30000
ORDER BY WaitInSeconds
--buffer中缓存每个数据库所占的buffer
SET TRAN ISOLATION LEVEL READ UNCOMMITTED
SELECT
ISNULL(DB_NAME(database_id), 'ResourceDb') AS DatabaseName
, CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2))
AS [Size (MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY DatabaseName
--当前数据库中每个表所占缓存的大小和页数
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
OBJECT_NAME(p.[object_id]) AS [TableName]
, (COUNT(*) * 8) / 1024 AS [Buffer size(MB)]
, ISNULL(i.name, '-- HEAP --') AS ObjectName
, COUNT(*) AS NumberOf8KPages
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
INNER JOIN sys.indexes i ON p.index_id = i.index_id
AND p.[object_id] = i.[object_id]
ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
AND p.[object_id] > 100
GROUP BY p.[object_id], i.name
ORDER BY NumberOf8KPages DESC
--数据库级别等待的IO
SET TRAN ISOLATION LEVEL READ UNCOMMITTED
SELECT DB_NAME(database_id) AS [DatabaseName]
, SUM(CAST(io_stall / 1000.0 AS DECIMAL(20,2))) AS [IO stall (secs)]
, SUM(CAST(num_of_bytes_read / 1024.0 / 1024.0 AS DECIMAL(20,2)))
AS [IO read (MB)
, SUM(CAST(num_of_bytes_written / 1024.0 / 1024.0 AS DECIMAL(20,2)))
AS [IO written (MB)
, SUM(CAST((num_of_bytes_read + num_of_bytes_written)
/ 1024.0 / 1024.0 AS DECIMAL(20,2))) AS [TotalIO (MB)
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
GROUP BY database_id
ORDER BY [IO stall (secs)] DESC
--按文件查看IO情况
SET TRAN ISOLATION LEVEL READ UNCOMMITTED
SELECT DB_NAME(database_id) AS [DatabaseName]
, file_id
, SUM(CAST(io_stall / 1000.0 AS DECIMAL(20,2))) AS [IO stall (secs)]
, SUM(CAST(num_of_bytes_read / 1024.0 / 1024.0 AS DECIMAL(20,2)))
AS [IO read (MB)]
, SUM(CAST(num_of_bytes_written / 1024.0 / 1024.0 AS DECIMAL(20,2)))
AS [IO written (MB)]
, SUM(CAST((num_of_bytes_read + num_of_bytes_written)
/ 1024.0 / 1024.0 AS DECIMAL(20,2))) AS [TotalIO (MB)]
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
GROUP BY database_id, file_id
ORDER BY [IO stall (secs)] DESC
