【1】核心3个DMV
|
DMV |
用处 |
|
Sys.dm_exec_requests |
返回有关在SQL Server中执行的每个请求的信息，包括当前的等待状态 |
|
Sys.dm_exec_sessions |
对于每个通过身份验证的会话都返回相应的一行。此时图是服务器范围的视图。此视图首先可以查到服务器负荷 |
|
Sys.dm_exec_connections |
返回与SQL Server 实例建立的连接有关的信息以及每个连接的详细信息 |
【2】请求状态
（2.1）所有请求状态
status 栏位的值，有这几种 :
Pending, Runnable, Running, Suspended, Sleeping, Dormant,Background, Spinlock
pending (等待)：代表这个 process，既沒有 Thread 可用，也沒有 CPU 可用，正在同时等待这两项系统资源。
runnable，代表这个 process，有 Thread 可用，但沒有 CPU 可用，所以它正在等待 CPU 这项系统资源。
running，代表这个 process，有 Thread 可用，有 CPU 可用。
suspended (暂停)，代表这个 process，正在「等待」別的 process 执行，等待的系统资源可能是 Disk I/O 或数据库的 Lock。
版工注：若这个 process 执行的 SELECT 没加上 NOLOCK 关键字，而別的 process 正在进行「交易」或写入 (会加 Lock)，则这个 SELECT 的 process 就会呈现 “suspended” 的状态。
sleeping，代表这个 process，目前没在做任何事，正在等待进一步的指令。
dormant (暂时搁置)，代表 SQL Server 正在对这个 process 做 reset。
background，代表这个 process 正在 SQL Server 背景执行。 即使你看到有很多 “background” process 正在执行，也不必担心。
spinlock： 自旋锁本质上意味着查询处于一种运行模式，它忙着在cpu中等待自己的回合。essentially means that query is in kind of running mode where it is busy waiting in cpu for its own turn.
相关参考：
running = 会话正在运行一个或多个批 background = 会话正在运行一个后台任务，例如死锁检测 rollback = 会话具有正在处理的事务回滚 pending = 会话正在等待工作线程变为可用 runnable = 会话中的任务在等待,由scheduler来运行的可执行队列中。(重要) spinloop = 会话中的任务正在等待调节锁变为可用。 suspended = 会话正在等待事件（如 I/O）完成。(重要) sleeping = 连接空闲
如果status 上有好几个runnable状态任务，需要认真对待。 cpu负荷过重没有及时处理用户的并发请求
参考转自：https://blog.csdn.net/Ruishine/article/details/116698990
（2.2）多个 runnable 请求如何解决？
如果出现这种状态，证明很多任务可以运行但没在运行。
Sys.dm_exec_requests/sys.sysprocesses的status列，反映了当前所有任务的状态，如果看到好多状态是runnable，那就要严肃对待，正常的SQLServer哪怕非常忙，也不应该经常看到runnable，连running的状态都不应该很多。
如果没有报17883/17884之类的警告，出现非常多的runnable任务可能有两种原因：
（1）、SQLServer CPU使用率接近100%，真的没有足够的cpu来及时处理用户的并发任务。此时应该优化最耗CPU资源的语句或者应用，或者加CPU
（2）、SQLServer CPU使用率并不高，小于50%。这时检查sys.dm_exec_requests的task_state列，会发现很多runnable状态。因为SQLServer除了lock和latch之外，还有一种更轻量级的同步资源：spin lock（自旋锁）。自旋：一些不会发生长时间等待的同步资源，SQLServer会选择让线程在cpu上稍微等待一下，而不会将cpu资源让出来。
可以使用DBCC SQLPERF(SPINLOCKSTATS)查看。
在2005上的64位SQLServer，当内存比较充裕时，会缓存很多执行计划，同事缓存很多执行计划安全上下文。在memory clerk里，用TokenAndPermUserStore表示，当这段内存比较大时，并发用户会容易遇到一种叫MUTEX的自旋锁。可以参考：http://suppot.microsoft.com/kb/927396。这种问题只在安全上下文缓存得太多时才容易发生，所以定期执行一下以下语句有效防止，而且对系统整体性能也没什么坏的影响：
DBCC FREESYSTEMCACHE(TokenAndPermUserStore)
也可以以-T4618和-T4610启动SQLServer，让SQLServer使用另一种缓存管理机制。
据说2008已经改进，不容易出现自旋锁。
【3】请求运行过程
（3.1） 客户端向SQLServer发出请求指令，经过网络层，SQLServer接收到
在这一步中，如果指令比较长，或者比较多，会影响SQLServer接受的速度。
（3.2）SQLServer对收到的指令进行语法、语义检查，编译，生成新的执行计划，或者找到缓存的计划重用：这一步耗费资源的种类比较多：
l CPU：做检查、编译、生成计划都需要计算，这一步耗费CPU资源比较多，尤其是指令复杂的时候。
l 内存：对于非常长的IN子句或者由几万、几十万语句组成，要花费非常大的内存，主要使用stolen内存，对于32位系统来说是很紧张的。一般会出现这些等待情况：CMEMTHREAD/SOS_RESERVEDMEMBLOCKLIST/RESOURCE_SEMAPHORE_QUERY_COMPILE，或者701错误。
l 表上的架构锁（schema lock）：在编译时，要防止对该架构进行修改。如果并发很高，那么会产生阻塞。
l 在SQLServer确认是否有线程的执行计划可用时，要在内存中进行搜索。可能会产生自旋锁。
（3.3）运行指令：
在等到执行计划之后，就进入运行阶段，用到的资源最多。在这一步要做很多事情：
（1） 、SQLServer首先为指令的运行申请内存。
如果同时需要执行很多指令，可能会在内存上遇到困难，通常会见到：RESOURCE_SEMAPHORE_开头的等待状态。
（2） 、如果发现要访问的数据不在内存中。
要讲数据从磁盘读到内存，如果发现内存没有足够的空闲页面存放所有数据，还要做内存整理和paging动作，腾出足够的空间放数据。通常简单的等待状态是：PAGEIOLATCH_X。
（3） 、按执行计划，扫描或者seek内存中的数据页面，讲执行需要处理的记录找出来。这一步需要申请各种各样的锁，以实现事务隔离。通常会引起阻塞，以LCK_开头的那些。
（4） 、指令可能还要做一些连接或者计算工作（sum、max、sort等）
这一步主要使用CPU。
（5） 、根据指令内容、执行计划和数据量，SQLServer可能还会在tempdb创建一些对象，存放临时表、表变量，帮助做join、sort等。
此时有可能出现tempdb瓶颈。
（6） 、如果指令需要修改数据记录，SQLServer会修改内存缓冲区里的页面内容。
由于对象在内存中，不会触发磁盘写入，但由于修改同一页面，容易导致PAGELATCH_X的等待状态。
（7） 、如果指令发生数据修改，在提交事务之前，SQLServer必须将相应的日志记录按照顺序写入日志文件。如果瞬间日志量太大，会出现WRITELOG的等待状态。
（8） 、将结果集返回给客户端：得到结果后，SQLServer会把结果集放到输出缓存中，等客户端把结果集全部取走。指令才结束。如果数据集太大，会导致网络交互太多。此时容易出现：ASYNC_NETWORK_IO等待状态。
以上的动作都要在SQLOS中首先得到一个Worker/thread，然后还要排上scheduler，在CPU上运行。
l SQLServer所有的Worker都在忙自己的事情，就会等待，可以看到等待状态是0x46（UMSTHREAD）。而sys.dm_os_schedulers.work_queue_count的值会不等于0
l 成功拿到worker，但在scheduler又要等待其他Worker，这时看到状态是runnable，而sys.dm_os_schedulers.runnable_tasks_count>1。
l 拿到scheduler，进入running状态，如果非常耗CPU，会出现cpu使用率高的现象。
l 遇到性能问题，查看sys.dm_exec_requests这类DMV对找到问题很有帮助。