SqlServer教程

sql server请求状态

本文主要是介绍sql server请求状态,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

【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】请求运行过程

参考:https://blog.csdn.net/iteye_6233/article/details/82335833?spm=1001.2101.3001.6650.5&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-5.fixedcolumn&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-5.fixedcolumn

(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对找到问题很有帮助。

这篇关于sql server请求状态的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!