none
sqlservr.exe进程占cpu达80%以上,造成系统缓慢 RRS feed

  • 问题

  • sql2000,企业版,web应用,客户反应前台查询缓慢,检测服务器发现,sqlservr.exe进程占cpu达80%以上,然后,开始检查进程和死锁,在企业管理器中,发现有很多进程阻塞,死锁也存在,然后,查看进程死锁的源好像是一个分页存储过程,然后,还有一个系统平台通信用的数据表,

    网上找了一大堆,只说优化,可如何优化是个问题。2天了该问题已经,非常心急啊。

    2013年8月20日 9:10

答案

  • select * from tb_msg_list where userid=1203

    对于这种简单的查询, 如果它不是被阻塞, 确实是执行比较慢的话, 通常是条件列上没有索引, 在条件的列上建立索引就可以了

    通用分页的那个, 跟具体的表和条件有关, 存储过程本身不是问题

    比较简单的方法是在能够比较大量过滤的数据列上建立索引,这些列来自你的查询条件

    2013年8月21日 4:24
  • Don't expect good query performance if table doesn't have any index.
    2013年8月21日 12:24
  • 其实查询阻塞的SQL语句很多的

    --查询阻塞与死锁的sql语句        已放博客园
    --1、
    --排在前两位的等待状态有下面几个:asynch_io_,completion,io_completion,logmgr,writelog,pageiolatch_x
    --这些等待状态意味着有I/O等待
    --如果排在前两位的等待状态以这样开头:LCK_M_?? 说明系统经常有阻塞
    SELECT TOP 2 [wait_type] FROM sys.[dm_os_wait_stats] ORDER BY [wait_time_ms] DESC
    
    
    --2、阻塞发生频率
    EXEC [sys].[sp_helpdb] @dbname = gposdb -- sysname 要查询阻塞的数据库
    SELECT * FROM sys.[dm_db_index_operational_stats](10,NULL,NULL,null) --根据上面的dbid填入第一个参数
    
    --3、开启阻塞事件报告
    EXEC [sys].[sp_configure] @configname = 'blocked process threshold', -- varchar(35)
        @configvalue = 1 -- int
        RECONFIGURE
    
    --4、平均阻塞时间 建议阀值>100ms
    EXEC [sys].[sp_helpdb] @dbname = gposdb -- sysname 要查询阻塞的数据库
    SELECT [row_lock_wait_in_ms],[page_lock_wait_in_ms],[page_latch_wait_in_ms],[page_io_latch_wait_in_ms] FROM sys.[dm_db_index_operational_stats](10,NULL,NULL,null) --根据上面的dbid填入第一个参数

    -- 判断阻塞
    --1. 查看数据库阻塞情况:
    ----------------------------------------Blocked Info----------------------------------
    -- 记录当前阻塞信息
    select t1. resource_type as [lock type] , db_name( resource_database_id ) as [database]   
    , t1. resource_associated_entity_id as [blk object]   
    , t1. request_mode as [lock req]                          -- lock requested   
    , t1. request_session_id as [waiter sid]                      -- spid of waiter   
    , t2. wait_duration_ms as [wait time]         
    ,( select text from sys.dm_exec_requests as r with ( nolock)                  --- get sql for waiter   
    cross apply sys.dm_exec_sql_text (r . sql_handle)     
    where r. session_id = t1 . request_session_id) as waiter_batch   
    ,( select substring (qt . text, r .statement_start_offset / 2,     
    ( case when r. statement_end_offset = - 1 then len( convert (nvarchar ( max), qt. text)) * 2    
    else r. statement_end_offset end - r. statement_start_offset )/2 + 1)     
    from sys.dm_exec_requests as r with (nolock )     
    cross apply sys.dm_exec_sql_text (r . sql_handle) as qt   
    where r. session_id = t1 . request_session_id) as waiter_stmt    --- statement executing now   
    , t2. blocking_session_id as [blocker sid]                --- spid of blocker   
    ,( select text from sys.sysprocesses as p with ( nolock)     --- get sql for blocker   
    cross apply sys.dm_exec_sql_text (p . sql_handle)     
    where p. spid = t2 . blocking_session_id) as blocker_stmt , getdate() time  
    from sys.dm_tran_locks as t1 with (nolock ) , sys.dm_os_waiting_tasks as t2 with (nolock )      
    where t1. lock_owner_address = t2 . resource_address
    
    --------------------------------------------------------------------
    --2. 查看阻塞其他进程的进程(阻塞源头):
    select   t2. blocking_session_id ,COUNT ( 0) counts
    from sys.dm_tran_locks as t1 with (nolock ) , sys.dm_os_waiting_tasks as t2 with (nolock )    
    where t1. lock_owner_address = t2 . resource_address
    group by blocking_session_id
    order by 2
    
    
    
    
    -----------------------------------------------------------------------
    --3. 被阻塞时间最长的进程:
    -- 被阻塞时间最长的 session
    select top 10  t1 .resource_type as [lock type] , db_name( resource_database_id ) as [database]   
    , t1. resource_associated_entity_id as [blk object]   
    , t1. request_mode as [lock req]                          -- lock requested   
    , t1. request_session_id as [waiter sid]                      -- spid of waiter   
    , t2. wait_duration_ms as [wait time]         
    ,( select text from sys.dm_exec_requests as r with ( nolock)                  --- get sql for waiter   
    cross apply sys.dm_exec_sql_text (r . sql_handle)     
    where r. session_id = t1 . request_session_id) as waiter_batch   
    ,( select substring (qt . text, r .statement_start_offset / 2,     
    ( case when r. statement_end_offset = - 1 then len( convert (nvarchar ( max), qt. text)) * 2    
    else r. statement_end_offset end - r. statement_start_offset )/2 + 1)     
    from sys.dm_exec_requests as r with (nolock )     
    cross apply sys.dm_exec_sql_text (r . sql_handle) as qt   
    where r. session_id = t1 . request_session_id) as waiter_stmt    --- statement executing now   
    , t2. blocking_session_id as [blocker sid]                --- spid of blocker   
    ,( select text from sys.sysprocesses as p with ( nolock)     --- get sql for blocker   
    cross apply sys.dm_exec_sql_text (p . sql_handle)     
    where p. spid = t2 . blocking_session_id) as blocker_stmt , getdate() time  
    from sys.dm_tran_locks as t1 with (nolock ) , sys.dm_os_waiting_tasks as t2 with (nolock )      
    where t1. lock_owner_address = t2 . resource_address
    order by t2 .wait_duration_ms desc
    

    SELECT TOP 10
    [session_id],
    [request_id],
    [start_time] AS '开始时间',
    [status] AS '状态',
    [command] AS '命令',
    dest.[text] AS 'sql语句',
    DB_NAME([database_id]) AS '数据库名',
    [blocking_session_id] AS '正在阻塞其他会话的会话ID',
    [wait_type] AS '等待资源类型',
    [wait_time] AS '等待时间',
    [wait_resource] AS '等待的资源',
    [reads] AS '物理读次数',
    [writes] AS '写次数',
    [logical_reads] AS '逻辑读次数',
    [row_count] AS '返回结果行数'
    FROM sys.[dm_exec_requests] AS der
    CROSS APPLY
    sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
    WHERE [session_id]>50 AND DB_NAME(der.[database_id])='gposdb' 
    ORDER BY [cpu_time] DESC

    建议LZ多做笔记,平时收集一下这些SQL语句,下次遇到阻塞,死锁问题就能游刃有余了

    2013年8月23日 13:31

全部回复

  • 既然已经找到存储过程了, 2天了, 怎么也优化出来了吧

    如果经验不足, 可以使用 sql server 的查询引擎优化顾问

    2013年8月20日 12:57
  • Check execution plan of the sp and ensure related tables have proper indices.
    2013年8月20日 13:04
  • 别随便使用引擎优化顾问,那真是玩具中的玩具,MS搞这个工具的想法可能是好的,不过搞出来的东西真是很弱智的结果

    接手过好同个系统,都是对方反复使用引擎优化顾问,导致产生大量的冗余索引(虽然对查询也没啥负作用,但对数据更新影响很大)

    Profiler是正道,再根据数据结构作调整或改写SQL


    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com

    2013年8月21日 1:07
  • Profile是正道, 自己没能力去分析也没用

    对于优化引擎顾问, 工作负荷, 这个东东不准的话, 出来的结果自然是很很有问题的, 当然, 工具肯定不能和人相比, 如果有能力自己优化, 这个工具也最多参考一下而已

    2013年8月21日 1:22
  • 现在找到了消息记录表,tb_msg_list   数据量大概也就58万左右,用于系统用户发送消息,文件,看了下,阻塞的语句很简单,select * from tb_msg_list where userid=1203之类,

    那么针对该表,该如何处理呢,我看了网上发布了一些杀进程的存储过程,但是我想这终究不是解决问题的办法,他那边不断的出现阻塞,我这边就不断的查杀,好像没有尽头,能否帮忙指条路,

    2013年8月21日 3:37
  • Did you check execution plan? Does table have index on userid column?
    2013年8月21日 3:43
  • 看了下,引发阻塞的还有一个分页存储过程,似乎没有看出有什么破绽,代码也不复杂,只好将其贴出,见笑了,

    ======================================================

    ALTER      PROC UP_GetRecordByPage1
     @sqlSelect varchar(500) --要查询的字段
    ,@sqlFrom varchar(200) --表名
    ,@where   varchar(4000)
    ,@order   varchar(500) --排序,如(order by id desc)
    ,@countPerPage int -- 每页数据行数
    ,@toPage int --要转到的页码
    ,@IsCount bit = 0 --是否统计记录数量

    AS

    BEGIN

    -- 根据每页数据行数 和 要转到的页码 得到 数据起止点
    Declare @start int
    Declare @end int
    set @end = @countPerPage * @toPage
    set @start = @countPerPage * (@toPage - 1) + 1

    -- 临时表名称 可随机命名
    Declare @tmpTable varchar(10)
    SET @tmpTable =' #tmp '

    Declare @sqlStr varchar(800)
    IF @IsCount = 0
    BEGIN
    -- 创建数据源到临时表
    SELECT @sqlStr = 'SELECT Identity(int,1,1) AS RowIndex,'
    SELECT @sqlStr = @sqlStr + rtrim(@sqlSelect) + ' INTO  '+ @tmpTable
    SELECT @sqlStr = @sqlStr +' FROM (select top ' + str(@toPage*@countPerPage) + ' ' + rtrim(@sqlSelect) + ' from ' + rtrim(@sqlFrom) + ' where ' +  @where + ' ' + @order + ') a'
    -- 查询临时表 得到所需要的数据
    SELECT @sqlStr = @sqlStr + ' '+'SELECT '+ rtrim(@sqlSelect) +' FROM ' + @tmpTable
    SELECT @sqlStr = @sqlStr + ' WHERE  RowIndex BETWEEN ' + Convert(varchar(10),@start) + " AND " + Convert(varchar(10),@end)
    SELECT @sqlStr = @sqlStr + ' and ' + @where + ' ' + @order
    -- 删除临时表
    SELECT @sqlStr = @sqlStr + ' DROP TABLE '+@tmpTable
    END
    ELSE -- 返回结果数
    BEGIN
     SELECT @sqlStr = 'SELECT COUNT(*) FROM ' + rtrim(@sqlFrom) + ' where ' +  @where
    END
    --print @sqlstr
    EXEC (@sqlStr)


    END


    2013年8月21日 3:43
  • select * from tb_msg_list where userid=1203

    对于这种简单的查询, 如果它不是被阻塞, 确实是执行比较慢的话, 通常是条件列上没有索引, 在条件的列上建立索引就可以了

    通用分页的那个, 跟具体的表和条件有关, 存储过程本身不是问题

    比较简单的方法是在能够比较大量过滤的数据列上建立索引,这些列来自你的查询条件

    2013年8月21日 4:24
  • 这个分页在网上是够流行,在不关注性能的情况下,它是一个灵活的好框架、代码

    但多数程序开发者完成功能就OK了,没留意和关注到后台的性能及当数据量、访问量稍大的情况


    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com

    2013年8月21日 6:53
  • 谢谢大家给予的热情的帮助。

    我看了下数据库版本, Microsoft SQL Server  2000 - 8.00.194 (Intel X86)   Aug  6 2000 00:57:48  
    Copyright (c) 1988-2000 Microsoft Corporation  Enterprise Edition on Windows NT 5.2
    (Build 3790: Service Pack 2)  ,也不知道是不是与这个版本有关,

    所以,第一,准备先打个sp4,第二,按照zjcxc的说法,创建索引;不知道我的理解是否正确,一张几十万的表,没有创建任何索引,同时查询的人又多,每个select都耗时长,所以该表就会引起对其他进程的阻塞,是这样吗?

    2013年8月21日 7:10
  • Don't expect good query performance if table doesn't have any index.
    2013年8月21日 12:24
  • 其实查询阻塞的SQL语句很多的

    --查询阻塞与死锁的sql语句        已放博客园
    --1、
    --排在前两位的等待状态有下面几个:asynch_io_,completion,io_completion,logmgr,writelog,pageiolatch_x
    --这些等待状态意味着有I/O等待
    --如果排在前两位的等待状态以这样开头:LCK_M_?? 说明系统经常有阻塞
    SELECT TOP 2 [wait_type] FROM sys.[dm_os_wait_stats] ORDER BY [wait_time_ms] DESC
    
    
    --2、阻塞发生频率
    EXEC [sys].[sp_helpdb] @dbname = gposdb -- sysname 要查询阻塞的数据库
    SELECT * FROM sys.[dm_db_index_operational_stats](10,NULL,NULL,null) --根据上面的dbid填入第一个参数
    
    --3、开启阻塞事件报告
    EXEC [sys].[sp_configure] @configname = 'blocked process threshold', -- varchar(35)
        @configvalue = 1 -- int
        RECONFIGURE
    
    --4、平均阻塞时间 建议阀值>100ms
    EXEC [sys].[sp_helpdb] @dbname = gposdb -- sysname 要查询阻塞的数据库
    SELECT [row_lock_wait_in_ms],[page_lock_wait_in_ms],[page_latch_wait_in_ms],[page_io_latch_wait_in_ms] FROM sys.[dm_db_index_operational_stats](10,NULL,NULL,null) --根据上面的dbid填入第一个参数

    -- 判断阻塞
    --1. 查看数据库阻塞情况:
    ----------------------------------------Blocked Info----------------------------------
    -- 记录当前阻塞信息
    select t1. resource_type as [lock type] , db_name( resource_database_id ) as [database]   
    , t1. resource_associated_entity_id as [blk object]   
    , t1. request_mode as [lock req]                          -- lock requested   
    , t1. request_session_id as [waiter sid]                      -- spid of waiter   
    , t2. wait_duration_ms as [wait time]         
    ,( select text from sys.dm_exec_requests as r with ( nolock)                  --- get sql for waiter   
    cross apply sys.dm_exec_sql_text (r . sql_handle)     
    where r. session_id = t1 . request_session_id) as waiter_batch   
    ,( select substring (qt . text, r .statement_start_offset / 2,     
    ( case when r. statement_end_offset = - 1 then len( convert (nvarchar ( max), qt. text)) * 2    
    else r. statement_end_offset end - r. statement_start_offset )/2 + 1)     
    from sys.dm_exec_requests as r with (nolock )     
    cross apply sys.dm_exec_sql_text (r . sql_handle) as qt   
    where r. session_id = t1 . request_session_id) as waiter_stmt    --- statement executing now   
    , t2. blocking_session_id as [blocker sid]                --- spid of blocker   
    ,( select text from sys.sysprocesses as p with ( nolock)     --- get sql for blocker   
    cross apply sys.dm_exec_sql_text (p . sql_handle)     
    where p. spid = t2 . blocking_session_id) as blocker_stmt , getdate() time  
    from sys.dm_tran_locks as t1 with (nolock ) , sys.dm_os_waiting_tasks as t2 with (nolock )      
    where t1. lock_owner_address = t2 . resource_address
    
    --------------------------------------------------------------------
    --2. 查看阻塞其他进程的进程(阻塞源头):
    select   t2. blocking_session_id ,COUNT ( 0) counts
    from sys.dm_tran_locks as t1 with (nolock ) , sys.dm_os_waiting_tasks as t2 with (nolock )    
    where t1. lock_owner_address = t2 . resource_address
    group by blocking_session_id
    order by 2
    
    
    
    
    -----------------------------------------------------------------------
    --3. 被阻塞时间最长的进程:
    -- 被阻塞时间最长的 session
    select top 10  t1 .resource_type as [lock type] , db_name( resource_database_id ) as [database]   
    , t1. resource_associated_entity_id as [blk object]   
    , t1. request_mode as [lock req]                          -- lock requested   
    , t1. request_session_id as [waiter sid]                      -- spid of waiter   
    , t2. wait_duration_ms as [wait time]         
    ,( select text from sys.dm_exec_requests as r with ( nolock)                  --- get sql for waiter   
    cross apply sys.dm_exec_sql_text (r . sql_handle)     
    where r. session_id = t1 . request_session_id) as waiter_batch   
    ,( select substring (qt . text, r .statement_start_offset / 2,     
    ( case when r. statement_end_offset = - 1 then len( convert (nvarchar ( max), qt. text)) * 2    
    else r. statement_end_offset end - r. statement_start_offset )/2 + 1)     
    from sys.dm_exec_requests as r with (nolock )     
    cross apply sys.dm_exec_sql_text (r . sql_handle) as qt   
    where r. session_id = t1 . request_session_id) as waiter_stmt    --- statement executing now   
    , t2. blocking_session_id as [blocker sid]                --- spid of blocker   
    ,( select text from sys.sysprocesses as p with ( nolock)     --- get sql for blocker   
    cross apply sys.dm_exec_sql_text (p . sql_handle)     
    where p. spid = t2 . blocking_session_id) as blocker_stmt , getdate() time  
    from sys.dm_tran_locks as t1 with (nolock ) , sys.dm_os_waiting_tasks as t2 with (nolock )      
    where t1. lock_owner_address = t2 . resource_address
    order by t2 .wait_duration_ms desc
    

    SELECT TOP 10
    [session_id],
    [request_id],
    [start_time] AS '开始时间',
    [status] AS '状态',
    [command] AS '命令',
    dest.[text] AS 'sql语句',
    DB_NAME([database_id]) AS '数据库名',
    [blocking_session_id] AS '正在阻塞其他会话的会话ID',
    [wait_type] AS '等待资源类型',
    [wait_time] AS '等待时间',
    [wait_resource] AS '等待的资源',
    [reads] AS '物理读次数',
    [writes] AS '写次数',
    [logical_reads] AS '逻辑读次数',
    [row_count] AS '返回结果行数'
    FROM sys.[dm_exec_requests] AS der
    CROSS APPLY
    sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
    WHERE [session_id]>50 AND DB_NAME(der.[database_id])='gposdb' 
    ORDER BY [cpu_time] DESC

    建议LZ多做笔记,平时收集一下这些SQL语句,下次遇到阻塞,死锁问题就能游刃有余了

    2013年8月23日 13:31