cnn 发表于 2015-8-12 10:21:53

记一次数据库调优过程(IIS发过来SQLSERVER 的FETCH API_CURSOR语句是神马?)

记一次数据库调优过程(IIS发过来SQLSERVER 的FETCH API_CURSOR语句是神马?)
  前几天帮客户优化一个数据库,那个数据库的大小是6G
  这麽小的数据库按道理不会有太大的性能问题的,但是客户反应说CPU占用很高,经常达到80%~90%
  我检查了任务管理器,确实是SQLSERVER占的CPU
  而服务器的内存是16G内存,只占用了7G+
  客户的环境:
  Windows2008R2
  SQLSERVER2005 SP3 64位 企业版
  服务器内存:16G
  CPU:8核
  RDS:阿里云主机
  IIS7.5
  网站使用ASP技术
  

  着手查找原因
  于是就着手检查占用CPU高的原因,检查了很久,发现有一些SQL语句占用CPU很高,而执行的SQL语句如下:

  
  这些是什么语句呢?在msdn上面找不到任何资料,使用下面的SQL语句查看,在字段可以看到是IIS发过来的



SELECT * FROM sys. WHERE SPID>=50
  难道是IIS的bug?然后我又继续在茫茫网海里查找资料,最后终于在paul的博客里找到原因
  文章地址:Hunting down the origins of FETCH API_CURSOR and sp_cursorfetch


  文章大意
  我在调优数据库的时候,使用sqlserver profiler捕获RPC:Completed 事件,可以看到很多类似下面的语句
  exec sp_cursorfetch 180150003,32,1,1
       exec sp_cursorfetch 180150003,32,1,1
       exec sp_cursorfetch 180150003,32,1,1
       exec sp_cursorfetch 180150003,32,1,1
  
  你看到这些语句是从session_id为53的session那里发过来的
  于是用下面语句看一下session_id为53执行的究竟是什么语句



DBCC INPUTBUFFER (53)
  
  而返回的结果是



FETCH API_CURSOR0000000000000004
  
  您很快意识到这跟服务器游标有一定的关系

  
  如果你使用sys.dm_exec_requests 视图或者sys.dm_exec_connections视图来查看session_id53执行了什么语句
  和执行的状态



SELECT t.text
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t
WHERE session_id = 53
  但是返回的结果依然是



FETCH API_CURSOR0000000000000004
  
  那么还有没有其他的视图来帮助我们呢?我们可以使用sys.dm_exec_cursors视图,将spid代入进去



SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text
FROM sys.dm_exec_cursors (53) c
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t

  从结果来看,我们知道语句使用了游标,并且知道游标的属性(scroll locks)和游标创建时间
  并且我们看到执行的SQL语句不像是FETCH API_CURSOR或者sp_cursorfetch,而是



SELECT * FROM dbo.FactResellerSales.
  本人的处理过程
  1、先使用下面的SQL语句找出当前实例下有使用到游标的语句



-- =============================================
-- Author:      <桦仔>
-- Blog:      <http://www.iyunv.com/lyhabc/>
-- Create date: <2014/6/3>
-- Description: <获取当前实例下所有的游标语句>
-- =============================================
DECLARE @spid NVARCHAR(100)
DECLARE @SQL NVARCHAR(MAX)
DECLARE CurSPID CURSOR
FOR
SELECT
FROM    sys.
WHERE    >= 50
OPEN CurSPID
FETCH NEXT FROM CurSPID INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = N'
SELECTcursors.session_id ,
cursors.properties ,
cursors.creation_time ,
cursors.is_open ,
text.text
FROM    sys.dm_exec_cursors (' + @spid + ') cursors
CROSS APPLY sys.dm_exec_sql_text(cursors.sql_handle) text'
EXEC(@SQL)
FETCH NEXT FROM CurSPID INTO @spid
END
CLOSE CurSPID
DEALLOCATE CurSPID
  
  为什麽上面的脚本要使用游标,因为当时我根据paul的脚本来执行的时候,在活动监视器里能看到使用游标的SQL语句,
  但是在SSMS里查询的时候,怎麽也查询不出来,所以才用游标,将使用到游标的语句一网打尽,这里输出的结果要忽略本身这个脚本使用到的游标!!
  
  2、根据输出的结果,发现有几个地方使用了游标,下面只是部分截图

  
  3、把结果拷贝出来,可以发现也是执行的是SELECT 语句

  
  4、因为是ASP程序,没有用到存储过程,于是搜索项目文件,看一下哪个文件有类似的代码

  5、找到结果


  ASP的语法跟VB是很像的,本人觉得非常羞涩
  可以看到server对象创建了一个recordset对象,然后从recordset对象里逐条记录取出来,再做处理,可以看到后续还有
  select case....case...case....
  就是对取出来的记录再做处理
  
  因为ASP是脚本语言,由IIS来执行,所以在SQLSERVER这边可以看到下面语句的program_name字段是IIS



SELECT * FROM sys. WHERE SPID>=50
  
  6、验证一下是否是游标的原因导致CPU高,使用下面的脚本





SELECT * FROM sys.
WHERE ='CPU usage %'   
AND ='SQLServer:Resource Pool Stats'   
AND ='default'                                                

SELECT * FROM sys.
WHERE ='Active cursors'   
AND ='SQLServer:Cursor Manager by Type'   
AND ='_Total'                                                

--建表
USE
GO
CREATE TABLE ActiveCursors
(cntr_value BIGINT,cntr_time DATETIME PRIMARY KEY)
GO
CREATE TABLE CPUUsage
(cntr_value BIGINT,cntr_time DATETIME PRIMARY KEY)
GO

--建作业
DECLARE @DBName NVARCHAR(MAX)
DECLARE @job_name sysname
SET @DBName='xxx'--★Do
SET @job_name='Monitor_CPUUsage_' + @DBName
EXEC msdb.dbo.sp_add_job @job_name=@job_name,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'监控CPU使用率',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa'

--添加监控步骤
DECLARE @job_name SYSNAME
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DBName NVARCHAR(MAX)
SET @DBName='xxx'--★Do
SET @job_name='Monitor_CPUUsage_' + @DBName--★Do
BEGIN
SET @SQL = N'
USE
GO
INSERT INTO CPUUsage(cntr_value,cntr_time)
SELECT cntr_value,GETDATE() FROM sys.
WHERE =''CPU usage %''   
AND =''SQLServer:Resource Pool Stats''   
AND =''default''
'
EXEC msdb.dbo.sp_add_jobstep @job_name = @job_name,
@step_name = N'Monitor', @step_id = 1, @cmdexec_success_code = 0,
@on_success_action = 3, @on_success_step_id = 0, @on_fail_action = 2,
@on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 0,
@os_run_priority = 0, @subsystem = N'TSQL', @command = @SQL,
@database_name = @DBNAME, @flags = 0
END


--创建Monitor作业的调度计划
DECLARE @job_name SYSNAME
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DBName NVARCHAR(MAX)
SET @DBName='xxx'--★Do
SET @job_name='Monitor_CPUUsage_' + @DBName--★Do
--修改作业的执行时间
EXECmsdb.dbo.sp_add_jobschedule@job_name = @job_name, @name=N'Plan',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=2,
@freq_subday_interval=30,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20140105,
@active_end_date=99991231,
@active_start_time=2000,
@active_end_time=235959
EXECmsdb.dbo.sp_add_jobserver@job_name = @job_name, @server_name = N'(local)'


------------------------------------------------------------------------------
--建作业
DECLARE @DBName NVARCHAR(MAX)
DECLARE @job_name sysname
SET @DBName='xxx'--★Do
SET @job_name='Monitor_ActiveCursors_' + @DBName
EXEC msdb.dbo.sp_add_job @job_name=@job_name,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'监控游标使用',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa'

--添加监控步骤
DECLARE @job_name SYSNAME
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DBName NVARCHAR(MAX)
SET @DBName='xxxx'--★Do
SET @job_name='Monitor_ActiveCursors_' + @DBName--★Do
BEGIN
SET @SQL = N'
USE
GO
INSERT INTO ActiveCursors(cntr_value,cntr_time)
SELECT cntr_value,GETDATE()FROM sys.
WHERE =''Active cursors''   
AND =''SQLServer:Cursor Manager by Type''
AND =''_Total''
'
EXEC msdb.dbo.sp_add_jobstep @job_name = @job_name,
@step_name = N'Monitor', @step_id = 1, @cmdexec_success_code = 0,
@on_success_action = 3, @on_success_step_id = 0, @on_fail_action = 2,
@on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 0,
@os_run_priority = 0, @subsystem = N'TSQL', @command = @SQL,
@database_name = @DBNAME, @flags = 0
END


--创建Monitor作业的调度计划
DECLARE @job_name SYSNAME
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DBName NVARCHAR(MAX)
SET @DBName='xxxx'--★Do
SET @job_name='Monitor_ActiveCursors_' + @DBName--★Do
--修改作业的执行时间
EXECmsdb.dbo.sp_add_jobschedule@job_name = @job_name, @name=N'Plan',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=2,
@freq_subday_interval=30,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20140105,
@active_end_date=99991231,
@active_start_time=2000,
@active_end_time=235959
EXECmsdb.dbo.sp_add_jobserver@job_name = @job_name, @server_name = N'(local)'
View Code  上面视图里的字段和 字段跟你的环境会不一样,所以大家要按照自己的环境来修改
  如果是SQLSERVER2005是没有CPU usage %这个counter的,我使用了下面的SQL语句



SELECT SUM() FROM sys. WHERE SPID>=50
  
  7、画折线图
  监控了一天的时间,根据结果使用EXCEL画出折线图


  
  凌晨那段曲线是因为数据库有做清除数据的操作,所以会比较高
  游标跟CPU图虽然说不能完全吻合,但是基本能吻合
  
  解决方法
  1、修改代码
  2、升级到SQL2008,然后使用资源调控器把CPU压下去
  
  最终还是找人修改代码

  总结
  有时候对一些老旧的程序,例如ASP,可能老一代程序员还会,现在的程序员基本都使用ASP.NET
  所以如果可能,还是跟上技术的脚步,不然出问题了,没有人维护就麻烦了
  
  如有不对的地方,欢迎大家拍砖o(&cap;_&cap;)o
页: [1]
查看完整版本: 记一次数据库调优过程(IIS发过来SQLSERVER 的FETCH API_CURSOR语句是神马?)