前幾天幫客戶優化一個數據庫，那個數據庫的大小是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 =