十分紧急：sys.processes系统表有一条恐怖信息

Question

ＳＱＬ对ＣＰＵ的使用，或者可能造成大规模的ＣＰＵ资源的浪费，有下列一些主要的原因：
 １）存储过程执行方案的重复 "re-compilation",这在性能监测器里可以观察，如果是这种原因，就要使用trace来查找有问题的存储过程，改掉造成recompilation的语句。
 ２）ＳＱＬ语句执行方案的 compilation 是不是用到　cache，还是每次都要重新　compile，这通过比较 batch/sec 和 compilations/sec可以看到，综合 cache plan hit ratio (不是buffer cache,而是plan cache)可以有一个比较好的结论。如果符合这种情况，需要把语句尽量作成参数化，这样会有很大的提升。
 ３）过多的Ｉ／Ｏ，造成ＣＰＵ资源的浪费，那么调优的手段就是找出造成大量Ｉ／Ｏ的语句，从索引，和语法角度来减低系统的消耗。
 ４）过多的连接，切断，可以考虑怎么使用Connection Pooling来减低连接需要的资源消耗

Answer 1

可能是我表达不清楚，其实我主要想弄懂的是从sys.processes里面查出来的信息是否对我的服务器有影响。
那台服务器CPU高的原因可能是动态SQL导致的吧，里面上百个存储过程，几乎用的董事动态SQL，每次都要compile，不高才怪

Answer 2

可能是我表达不清楚，其实我主要想弄懂的是从sys.processes里面查出来的信息是否对我的服务器有影响。
那台服务器CPU高的原因可能是动态SQL导致的吧，里面上百个存储过程，几乎用的董事动态SQL，每次都要compile，不高才怪也不一定。但如果你的存储过程，比如有200个存储过程，在1秒内，都会运行一次（平均来说），那么cpu就肯定会很高了，就像你说的编译次数太多了，而编译是很消耗cpu的。但如果你的存储过程的运行频率不是很高，那么有可能是个别的语句，运行时间很长，而且可能是对大的表进行了全表扫描，过着是做一些group by ，sum等计算，那么也有可能是导致cpu高的原因。

Answer 3

另外，我原来的公司，如果运行一个报表，代码在200-300行左右把，cpu马上升到99%，这个代码中引用了1个上亿数据的表，另外还有多个几千万级别的表

Answer 4

用以下SQL查出最耗CPU的语句..SELECT TOP 50
       qs.total_worker_time/qs.execution_count as [Avg CPU Time],
       SUBSTRING(qt.text,qs.statement_start_offset/2, 
(case when qs.statement_end_offset = -1 
      then len(convert(nvarchar(max), qt.text)) * 2 
 else qs.statement_end_offset end -qs.statement_start_offset)/2) as query_text,
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid 
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Avg CPU Time] DESC

Answer 5

可能是我表达不清楚，其实我主要想弄懂的是从sys.processes里面查出来的信息是否对我的服务器有影响。
那台服务器CPU高的原因可能是动态SQL导致的吧，里面上百个存储过程，几乎用的董事动态SQL，每次都要compile，不高才怪也不一定。但如果你的存储过程，比如有200个存储过程，在1秒内，都会运行一次（平均来说），那么cpu就肯定会很高了，就像你说的编译次数太多了，而编译是很消耗cpu的。但如果你的存储过程的运行频率不是很高，那么有可能是个别的语句，运行时间很长，而且可能是对大的表进行了全表扫描，过着是做一些group by ，sum等计算，那么也有可能是导致cpu高的原因。
嗯

Answer 6

其实我发这个帖子的目的就是想知道在sys.[sysprocesses] 系统视图里，出现了我截图中的一条信息，是否有影响
问题是那个截图中的CPU还在不断的飙升
我通过dbcc 查看那个spid的命令，就一条：begin tran 
这些是怎么回事呢？长此下去会对我的数据库造成影响吗？

Answer 7

DBCC INPUTBUFFER(spid)看看是干嘛的

Answer 8

版主终于来了，感谢感谢已经看过了，命令就一条：begin tran 其他没了
我重新把问题描述下，方便你看：
我在我的数据库中执行下面的语句SELECT 
    spid,waittype,waittime,lastwaittype,waitresource,cpu,
    physical_io,memusage,login_time,last_batch,open_tran,cmd 
FROM 
    sys.[sysprocesses] 
WHERE 
    [spid]>50 AND [dbid]='5' and status='sleeping' 
order by cpu desc得到的结果，其中有这么一条：然后我执行dbcc inputbuffer(135) ，发现这个spid就仅仅是打开了一个事务，结果如下：我们的数据库连接用的是连接池，按道理说，sleeping状态的进程，其CPU应该不会持续增长的，但是这个进程的CPU却一直在增长。我在我电脑上实验了各种情况，都无法模拟出图中的现象。
不晓得是不是程序员搞出来的什么bug 

Answer 9

1）从等待类型看 没有等待任何资源
2）看看hostname 和program_name (从哪里执行，什么客户端引起的)
3）cpu还在持续增长吗

Answer 10

你可以看到截图中的cmd是awaiting command，我怀疑是不是有人打开个begin tran然后搞些什么死循环之类的。如果已经很紧急，建议直接kill 135

Answer 11

不紧急的话，研究一下，执行这个，然后上图      SELECT    DTAT.transaction_id ,
                DTAT.[name] ,
                DTAT.transaction_begin_time ,
                CASE DTAT.transaction_type
                  WHEN 1 THEN 'Read/write'
                  WHEN 2 THEN 'Read-only'
                  WHEN 3 THEN 'System'
                  WHEN 4 THEN 'Distributed'
                END AS transaction_type ,
                CASE DTAT.transaction_state
                  WHEN 0 THEN 'Not fully initialized'
                  WHEN 1 THEN 'Initialized, not started'
                  WHEN 2 THEN 'Active'
                  WHEN 3 THEN 'Ended' -- only applies to read-only transactions 
                  WHEN 4 THEN 'Commit initiated'-- distributed transactions only 
                  WHEN 5 THEN 'Prepared, awaiting resolution'
                  WHEN 6 THEN 'Committed'
                  WHEN 7 THEN 'Rolling back'
                  WHEN 8 THEN 'Rolled back'
                END AS transaction_state ,
                CASE DTAT.dtc_state
                  WHEN 1 THEN 'Active'
                  WHEN 2 THEN 'Prepared'
                  WHEN 3 THEN 'Committed'
                  WHEN 4 THEN 'Aborted'
                  WHEN 5 THEN 'Recovered'
                END AS dtc_state
      FROM      sys.dm_tran_active_transactions DTAT
                INNER JOIN sys.dm_tran_session_transactions DTST ON DTAT.transaction_id = DTST.transaction_id
      WHERE     [DTST].[is_user_transaction] = 1
      ORDER BY  DTAT.transaction_begin_time

Answer 12

对的呀，我也看到没有等待任何资源。
是从程序端那边执行的，我就怀疑是程序的问题，但是我首先要排查数据库问题呀，昨天还有今天我在测试机上做了十几个模拟实验了，都得不到截图中那样的结果。
CPU还在持续飙升，已经有：1339889  这么多了，还在涨

Answer 13

不紧急，我就是想弄出原因到底是程序的问题，还是数据库的问题，如果可以排除数据库问题的话，我就直接联系程序的boss了，让他们以后写程序规范点。呵呵

Answer 14

select * from sys.dm_exec_connections  where session_id =135

Answer 15

版主的这个语句什么作用啊，我收集下，以后方便使用。
已经执行，结果如下图：
这个脚本用到什么dmv，你就去查对应的解释

Answer 16

换一个：这里脚本你加个where条件筛选135那个sessionidSELECT  DTST.[session_id] ,
        DES.[login_name] AS [Login Name] ,
        DB_NAME(DTDT.database_id) AS [Database] ,
        DTDT.[database_transaction_begin_time] AS [Begin Time] ,  
 -- DATEDIFF(ms,DTDT.[database_transaction_begin_time], GETDATE()) AS [Durationms],  
        CASE DTAT.transaction_type
          WHEN 1 THEN 'Read/write'
          WHEN 2 THEN 'Read-only'
          WHEN 3 THEN 'System'
          WHEN 4 THEN 'Distributed'
        END AS [Transaction Type] ,
        CASE DTAT.transaction_state
          WHEN 0 THEN 'Not fully initialized'
          WHEN 1 THEN 'Initialized, not started'
          WHEN 2 THEN 'Active'
          WHEN 3 THEN 'Ended'
          WHEN 4 THEN 'Commit initiated'
          WHEN 5 THEN 'Prepared, awaiting resolution'
          WHEN 6 THEN 'Committed'
          WHEN 7 THEN 'Rolling back'
          WHEN 8 THEN 'Rolled back'
        END AS [Transaction State] ,
        DTDT.[database_transaction_log_record_count] AS [Log Records] ,
        DTDT.[database_transaction_log_bytes_used] AS [Log Bytes Used] ,
        DTDT.[database_transaction_log_bytes_reserved] AS [Log Bytes RSVPd] ,
        DEST.[text] AS [Last Transaction Text] ,
        DEQP.[query_plan] AS [Last Query Plan]
FROM    sys.dm_tran_database_transactions DTDT
        INNER JOIN sys.dm_tran_session_transactions DTST ON DTST.[transaction_id] = DTDT.[transaction_id]
        INNER JOIN sys.[dm_tran_active_transactions] DTAT ON DTST.[transaction_id] = DTAT.[transaction_id]
        INNER JOIN sys.[dm_exec_sessions] DES ON DES.[session_id] = DTST.[session_id]
        INNER JOIN sys.dm_exec_connections DEC ON DEC.[session_id] = DTST.[session_id]
        LEFT JOIN sys.dm_exec_requests DER ON DER.[session_id] = DTST.[session_id]
        CROSS APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST
        OUTER APPLY sys.dm_exec_query_plan(DER.[plan_handle]) AS DEQP
ORDER BY DTDT.[database_transaction_log_bytes_used] DESC; 
-- ORDER BY [Duration ms] DESC;

Answer 17

对的呀，我也看到没有等待任何资源。
是从程序端那边执行的，我就怀疑是程序的问题，但是我首先要排查数据库问题呀，昨天还有今天我在测试机上做了十几个模拟实验了，都得不到截图中那样的结果。
CPU还在持续飙升，已经有：1339889  这么多了，还在涨
首先你要去了解下
程序里有没有和用户交互的事务方式
比如：打开一个事务 然后等待用户输入信息 然后才能提交
这个时候用户可能迟迟没有输入，可能用户离开了，出差了等等 一直挂那里 处于未提交的状态
如果是这样 这样的情况程序设计的禁忌 不允许的

Answer 18

对的呀，我也看到没有等待任何资源。
是从程序端那边执行的，我就怀疑是程序的问题，但是我首先要排查数据库问题呀，昨天还有今天我在测试机上做了十几个模拟实验了，都得不到截图中那样的结果。
CPU还在持续飙升，已经有：1339889  这么多了，还在涨
首先你要去了解下
程序里有没有和用户交互的事务方式
比如：打开一个事务 然后等待用户输入信息 然后才能提交
这个时候用户可能迟迟没有输入，可能用户离开了，出差了等等 一直挂那里 处于未提交的状态
如果是这样 这样的情况程序设计的禁忌 不允许的

程序和数据库的交互是一直都有的，但是具体怎么导致的还不知道，待我排查了数据库问题之后，会找程序的boss谈这个事情

Answer 19

这句话执行没有返回值。
select *
from sys.dm_tran_session_transactions
where session_id=135
得到
transaction_id
然后用DBA_Huangzj 加上条件查查看sys.dm_tran_active_transactions  
where transaction_ID=

Answer 20

我在order by之前加：where DTST.session_id=135
没有结果。

Answer 21

我在order by之前加：where DTST.session_id=135
没有结果。那不加where条件，全部返回

Answer 22

这句话执行没有返回值。
select *
from sys.dm_tran_session_transactions
where session_id=135
得到
transaction_id
然后用DBA_Huangzj 加上条件查查看sys.dm_tran_active_transactions  
where transaction_ID=sys.dm_tran_active_transactions  
where transaction_ID= 这个里头也没有找到结果

Answer 23

我在order by之前加：where DTST.session_id=135
没有结果。那不加where条件，全部返回
不加任何条件，也没有结果呢