记一次 数据库 调优 过程 （IIS发 过来 SQLSERVER 的FETCH API_CURSOR语句是神马？） 前几天帮客户优化一个 数据库 ，那个 数据库 的大小是6G 这麽小的 数据库 按道理不会有太大的性能问题的，但是客户反应说CPU占用很高，经常达到 80%~90% 我检查了任务管理
记一次 数据库 调优 过程 （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 事件，可以看到很多类似下面的语句
你看到这些语句是从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:
-- Create date:
-- Description:
-- =============================================
DECLARE @spid NVARCHAR ( 100 )
DECLARE @SQL NVARCHAR ( MAX )
DECLARE CurSPID CURSOR
FOR
SELECT [ spid ]
FROM sys. [ sysprocesses ]
WHERE [ spid ] >= 50
OPEN CurSPID
FETCH NEXT FROM CurSPID INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = N '
SELECT cursors.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. [ 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 ]
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 [msdb]
GO
INSERT 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 = 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
-- 修改作业的执行时间
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 = 235959
EXEC msdb.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 [msdb]
GO
INSERT 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 = 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
-- 修改作业的执行时间
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 = 235959
EXEC msdb.dbo.sp_add_jobserver @job_name = @job_name , @server_name = N ' (local) '
View Code
上面视图里的[object_name]字段和 [instance_name]字段跟你的环境会不一样，所以大家要按照自己的环境来修改
如果是SQLSERVER2005是没有CPU usage %这个counter的，我使用了下面的SQL语句
SELECT SUM ( [ cpu ] ) FROM sys. [ sysprocesses ] WHERE SPID >= 50
7、画折线图
监控了一天的时间，根据结果使用EXCEL画出折线图
凌晨那段曲线是因为 数据库 有做清除数据的操作，所以会比较高
游标跟CPU图虽然说不能完全吻合，但是基本能吻合
解决方法
1、修改代码
2、升级到SQL2008，然后使用资源调控器把CPU压下去
最终还是找人修改代码
总结
有时候对一些老旧的程序，例如ASP，可能老一代程序员还会，现在的程序员基本都使用ASP.NET
所以如果可能，还是跟上技术的脚步，不然出问题了，没有人维护就麻烦了
如有不对的地方，欢迎大家拍砖o(∩_∩)o
查看更多关于记一次数据库调优过程（IIS发过来SQLSERVER的FETCHAPI的详细内容...