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

9/1/2015来源:SQL技巧人气:835

记一次数据库调优过程(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语句查看,在[PRogram_name]字段可以看到是IIS发过来的

SELECT * FROM sys.[sysprocesses] 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.textFROM sys.dm_exec_connections cCROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) tWHERE session_id = 53

但是返回的结果依然是

FETCH API_CURSOR0000000000000004

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

SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.textFROM sys.dm_exec_cursors (53) cCROSS 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.cnblogs.com/lyhabc/>-- Create date: <2014/6/3>-- Description: <获取当前实例下所有的游标语句>-- =============================================DECLARE @spid NVARCHAR(100)DECLARE @SQL NVARCHAR(MAX)DECLARE CurSPID CURSORFOR    SELECT  [spid]    FROM    sys.[sysprocesses]    WHERE   [spid] >= 50OPEN CurSPIDFETCH NEXT FROM CurSPID INTO @spidWHILE @@FETCH_STATUS = 0    BEGIN          SET @SQL = N'SELECT  cursors.session_id ,        cursors.properties ,        cursors.creation_time ,        cursors.is_open ,        text.textFROM    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    ENDCLOSE CurSPIDDEALLOCATE 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.[sysprocesses] WHERE SPID>=50

6、验证一下是否是游标的原因导致CPU高,使用下面的脚本

SELECT * FROM sys.[dm_os_performance_counters] WHERE [counter_name]='CPU usage %'   AND [object_name]='SQLServer:Resource Pool Stats'     AND [instance_name]='default'                                                SELECT * FROM sys.[dm_os_performance_counters] WHERE [counter_name]='Active cursors'   AND [object_name]='SQLServer:Cursor Manager by Type'     AND [instance_name]='_Total'                                                --建表USE [msdb]GOCREATE TABLE ActiveCursors(cntr_value BIGINT,cntr_time DATETIME PRIMARY KEY)GOCREATE TABLE CPUUsage(cntr_value BIGINT,cntr_time DATETIME PRIMARY KEY)GO--建作业DECLARE @DBName NVARCHAR(MAX)DECLARE @job_name sysnameSET @DBName='xxx'  --★DoSET @job_name='Monitor_CPUUsage_' + @DBNameEXEC 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 SYSNAMEDECLARE @SQL NVARCHAR(MAX)DECLARE @DBName NVARCHAR(MAX)SET @DBName='xxx'  --★DoSET @job_name='Monitor_CPUUsage_' + @DBName  --★DoBEGIN     SET @SQL = N'USE [msdb]GOINSERT INTO CPUUsage(cntr_value,cntr_time)  SELECT cntr_value,GETDATE() FROM sys.[dm_os_performance_counters] WHERE [counter_name]=''CPU usage %''   AND [object_name]=''SQLServer:Resource Pool Stats''   AND [instance_name]=''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 = 0END  --创建Monitor作业的调度计划DECLARE @job_name SYSNAMEDECLARE @SQL NVARCHAR(MAX)DECLARE @DBName NVARCHAR(MAX)SET @DBName='xxx'  --★DoSET @job_name='Monitor_CPUUsage_' + @DBName  --★Do--修改作业的执行时间EXEC  msdb.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=235959EXEC  msdb.dbo.sp_add_jobserver  @job_name = @job_name, @server_name = N'(local)'--------------------------------------------------------------------------------建作业DECLARE @DBName NVARCHAR(MAX)DECLARE @job_name sysnameSET @DBName='xxx'  --★DoSET @job_name='Monitor_ActiveCursors_' + @DBNameEXEC 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 SYSNAMEDECLARE @SQL NVARCHAR(MAX)DECLARE @DBName NVARCHAR(MAX)SET @DBName='xxxx'  --★DoSET @job_name='Monitor_ActiveCursors_' + @DBName  --★DoBEGIN     SET @SQL = N'USE [msdb]GOINSERT INTO ActiveCursors(cntr_value,cntr_time)  SELECT cntr_value,GETDATE()  FROM sys.[dm_os_performance_counters] WHERE [counter_name]=''Active cursors''   AND [object_name]=''SQLServer:Cursor Manager by Type''  AND [instance_name]=''_Total'''    EXEC msdb.dbo.sp_add_jobstep @job_name = @job_name,        @step_name =