suchen

Heim  >  Artikel  >  Datenbank  >  记一次数据库调优过程（IIS发过来SQLSERVER 的FETCH API

记一次数据库调优过程（IIS发过来SQLSERVER 的FETCH API

WBOY
WBOYOriginal
2016-06-07 15:20:481380Durchsuche

记一次 数据库 调优 过程 （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语句如下：

记一次数据库调优过程（IIS发过来SQLSERVER 的FETCH API

 

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

<span>SELECT</span> <span>*</span> <span>FROM</span> sys.<span>[</span><span>sysprocesses</span><span>]</span> <span>WHERE</span> SPID<span>>=</span><span>50</span>

难道是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执行的究竟是什么语句

<span>DBCC</span> INPUTBUFFER (<span>53</span>)

 

而返回的结果是

 <span>FETCH</span> API_CURSOR0000000000000004

 

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

记一次数据库调优过程（IIS发过来SQLSERVER 的FETCH API

 

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

和执行的状态

<span>SELECT</span> t.<span>text</span>
<span>FROM</span><span> sys.dm_exec_connections c
</span><span>CROSS</span><span> APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t
</span><span>WHERE</span> session_id <span>=</span> <span>53</span>

但是返回的结果依然是

<span>FETCH</span> API_CURSOR0000000000000004

 

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

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

记一次数据库调优过程（IIS发过来SQLSERVER 的FETCH API

从结果来看，我们知道语句使用了游标，并且知道游标的属性（scroll locks）和游标创建时间

并且我们看到执行的SQL语句不像是FETCH API_CURSOR或者sp_cursorfetch，而是

<span>SELECT</span> <span>*</span> <span>FROM</span> dbo.FactResellerSales.

本人的处理过程

1、先使用下面的SQL语句找出当前实例下有使用到游标的语句

<span>--</span><span> =============================================</span><span>
--</span><span> Author:      </span><span>
--</span><span> Blog:        <http:></http:></span><span>
--</span><span> Create date: </span><span>
--</span><span> Description: </span><span>
--</span><span> =============================================</span>
<span>DECLARE</span> <span>@spid</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span>DECLARE</span> <span>@SQL</span> <span>NVARCHAR</span>(<span>MAX</span><span>)

</span><span>DECLARE</span> CurSPID <span>CURSOR</span>
<span>FOR</span>
    <span>SELECT</span>  <span>[</span><span>spid</span><span>]</span>
    <span>FROM</span>    sys.<span>[</span><span>sysprocesses</span><span>]</span>
    <span>WHERE</span>   <span>[</span><span>spid</span><span>]</span> <span>>=</span> <span>50</span>

<span>OPEN</span><span> CurSPID
</span><span>FETCH</span> <span>NEXT</span> <span>FROM</span> CurSPID <span>INTO</span> <span>@spid</span>

<span>WHILE</span> <span>@@FETCH_STATUS</span> <span>=</span> <span>0</span>
    <span>BEGIN</span>  
        <span>SET</span> <span>@SQL</span> <span>=</span> N<span>'</span><span>
SELECT  cursors.session_id ,
        cursors.properties ,
        cursors.creation_time ,
        cursors.is_open ,
        text.text
FROM    sys.dm_exec_cursors (</span><span>'</span> <span>+</span> <span>@spid</span> <span>+</span> <span>'</span><span>) cursors
        CROSS APPLY sys.dm_exec_sql_text(cursors.sql_handle) text</span><span>'</span>
        <span>EXEC</span>(<span>@SQL</span><span>) 

        </span><span>FETCH</span> <span>NEXT</span> <span>FROM</span> CurSPID <span>INTO</span> <span>@spid</span>
    <span>END</span>
<span>CLOSE</span><span> CurSPID
</span><span>DEALLOCATE</span> CurSPID

 

为什麽上面的脚本要使用游标，因为当时我根据paul的脚本来执行的时候，在活动监视器里能看到使用游标的SQL语句，

但是在SSMS里查询的时候，怎麽也查询不出来，所以才用游标，将使用到游标的语句一网打尽，这里输出的结果要忽略本身这个脚本使用到的游标！！

 

2、根据输出的结果，发现有几个地方使用了游标，下面只是部分截图

记一次数据库调优过程（IIS发过来SQLSERVER 的FETCH API

 

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

记一次数据库调优过程（IIS发过来SQLSERVER 的FETCH API

 

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

记一次数据库调优过程（IIS发过来SQLSERVER 的FETCH API

5、找到结果

记一次数据库调优过程（IIS发过来SQLSERVER 的FETCH API

 记一次数据库调优过程（IIS发过来SQLSERVER 的FETCH API

ASP的语法跟VB是很像的，本人觉得非常羞涩

可以看到server对象创建了一个recordset对象，然后从recordset对象里逐条记录取出来，再做处理，可以看到后续还有

select case....case...case....

就是对取出来的记录再做处理

 

因为ASP是脚本语言，由IIS来执行，所以在SQLSERVER这边可以看到下面语句的program_name字段是IIS

<span>SELECT</span> <span>*</span> <span>FROM</span> sys.<span>[</span><span>sysprocesses</span><span>]</span> <span>WHERE</span> SPID<span>>=</span><span>50</span>

 

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

记一次数据库调优过程（IIS发过来SQLSERVER 的FETCH API记一次数据库调优过程（IIS发过来SQLSERVER 的FETCH API 

<span>SELECT</span> <span>*</span> <span>FROM</span> sys.<span>[</span><span>dm_os_performance_counters</span><span>]</span> 
<span>WHERE</span> <span>[</span><span>counter_name</span><span>]</span><span>=</span><span>'</span><span>CPU usage %</span><span>'</span>   
<span>AND</span> <span>[</span><span>object_name</span><span>]</span><span>=</span><span>'</span><span>SQLServer:Resource Pool Stats</span><span>'</span>     
<span>AND</span> <span>[</span><span>instance_name</span><span>]</span><span>=</span><span>'</span><span>default</span><span>'</span>                                                


<span>SELECT</span> <span>*</span> <span>FROM</span> sys.<span>[</span><span>dm_os_performance_counters</span><span>]</span> 
<span>WHERE</span> <span>[</span><span>counter_name</span><span>]</span><span>=</span><span>'</span><span>Active cursors</span><span>'</span>   
<span>AND</span> <span>[</span><span>object_name</span><span>]</span><span>=</span><span>'</span><span>SQLServer:Cursor Manager by Type</span><span>'</span>     
<span>AND</span> <span>[</span><span>instance_name</span><span>]</span><span>=</span><span>'</span><span>_Total</span><span>'</span>                                                


<span>--</span><span>建表</span>
<span>USE</span> <span>[</span><span>msdb</span><span>]</span>
<span>GO</span>
<span>CREATE</span> <span>TABLE</span><span> ActiveCursors
(cntr_value </span><span>BIGINT</span>,cntr_time <span>DATETIME</span> <span>PRIMARY</span> <span>KEY</span><span>)
</span><span>GO</span>
<span>CREATE</span> <span>TABLE</span><span> CPUUsage
(cntr_value </span><span>BIGINT</span>,cntr_time <span>DATETIME</span> <span>PRIMARY</span> <span>KEY</span><span>)
</span><span>GO</span>


<span>--</span><span>建作业</span>
<span>DECLARE</span> <span>@DBName</span> <span>NVARCHAR</span>(<span>MAX</span><span>)
</span><span>DECLARE</span> <span>@job_name</span><span> sysname

</span><span>SET</span> <span>@DBName</span><span>=</span><span>'</span><span>xxx</span><span>'</span>  <span>--</span><span>★Do</span>

<span>SET</span> <span>@job_name</span><span>=</span><span>'</span><span>Monitor_CPUUsage_</span><span>'</span> <span>+</span> <span>@DBName</span>
<span>EXEC</span> msdb.dbo.sp_add_job <span>@job_name</span><span>=</span><span>@job_name</span><span>, 
</span><span>@enabled</span><span>=</span><span>1</span><span>, 
</span><span>@notify_level_eventlog</span><span>=</span><span>0</span><span>, 
</span><span>@notify_level_email</span><span>=</span><span>0</span><span>, 
</span><span>@notify_level_netsend</span><span>=</span><span>0</span><span>, 
</span><span>@notify_level_page</span><span>=</span><span>0</span><span>, 
</span><span>@delete_level</span><span>=</span><span>0</span><span>, 
</span><span>@description</span><span>=</span>N<span>'</span><span>监控CPU使用率</span><span>'</span><span>, 
</span><span>@category_name</span><span>=</span>N<span>'</span><span>Database Maintenance</span><span>'</span><span>, 
</span><span>@owner_login_name</span><span>=</span>N<span>'</span><span>sa</span><span>'</span> 


<span>--</span><span>添加监控步骤</span>
<span>DECLARE</span> <span>@job_name</span><span> SYSNAME
</span><span>DECLARE</span> <span>@SQL</span> <span>NVARCHAR</span>(<span>MAX</span><span>)
</span><span>DECLARE</span> <span>@DBName</span> <span>NVARCHAR</span>(<span>MAX</span><span>)

</span><span>SET</span> <span>@DBName</span><span>=</span><span>'</span><span>xxx</span><span>'</span>  <span>--</span><span>★Do</span>
<span>SET</span> <span>@job_name</span><span>=</span><span>'</span><span>Monitor_CPUUsage_</span><span>'</span> <span>+</span> <span>@DBName</span>  <span>--</span><span>★Do</span>

<span>BEGIN</span> 
    <span>SET</span> <span>@SQL</span> <span>=</span> N<span>'</span><span>
USE [msdb]
GO
INSERT INTO CPUUsage(cntr_value,cntr_time)  
SELECT cntr_value,GETDATE() FROM sys.[dm_os_performance_counters] 
WHERE [counter_name]=</span><span>''</span><span>CPU usage %</span><span>''</span><span>   
AND [object_name]=</span><span>''</span><span>SQLServer:Resource Pool Stats</span><span>''</span><span>   
AND [instance_name]=</span><span>''</span><span>default</span><span>''</span>
<span>'</span>
    <span>EXEC</span> msdb.dbo.sp_add_jobstep <span>@job_name</span> <span>=</span> <span>@job_name</span><span>,
        </span><span>@step_name</span> <span>=</span> N<span>'</span><span>Monitor</span><span>'</span>, <span>@step_id</span> <span>=</span> <span>1</span>, <span>@cmdexec_success_code</span> <span>=</span> <span>0</span><span>,
        </span><span>@on_success_action</span> <span>=</span> <span>3</span>, <span>@on_success_step_id</span> <span>=</span> <span>0</span>, <span>@on_fail_action</span> <span>=</span> <span>2</span><span>,
        </span><span>@on_fail_step_id</span> <span>=</span> <span>0</span>, <span>@retry_attempts</span> <span>=</span> <span>0</span>, <span>@retry_interval</span> <span>=</span> <span>0</span><span>,
        </span><span>@os_run_priority</span> <span>=</span> <span>0</span>, <span>@subsystem</span> <span>=</span> N<span>'</span><span>TSQL</span><span>'</span>, <span>@command</span> <span>=</span> <span>@SQL</span><span>,
        </span><span>@database_name</span> <span>=</span> <span>@DBNAME</span>, <span>@flags</span> <span>=</span> <span>0</span>

<span>END</span>

  


<span>--</span><span>创建Monitor作业的调度计划</span>
<span>DECLARE</span> <span>@job_name</span><span> SYSNAME
</span><span>DECLARE</span> <span>@SQL</span> <span>NVARCHAR</span>(<span>MAX</span><span>)
</span><span>DECLARE</span> <span>@DBName</span> <span>NVARCHAR</span>(<span>MAX</span><span>)

</span><span>SET</span> <span>@DBName</span><span>=</span><span>'</span><span>xxx</span><span>'</span>  <span>--</span><span>★Do</span>
<span>SET</span> <span>@job_name</span><span>=</span><span>'</span><span>Monitor_CPUUsage_</span><span>'</span> <span>+</span> <span>@DBName</span>  <span>--</span><span>★Do</span>

<span>--</span><span>修改作业的执行时间</span>
<span>EXEC</span>  msdb.dbo.sp_add_jobschedule  <span>@job_name</span> <span>=</span> <span>@job_name</span>, <span>@name</span><span>=</span>N<span>'</span><span>Plan</span><span>'</span><span>, 
        </span><span>@enabled</span><span>=</span><span>1</span><span>, 
        </span><span>@freq_type</span><span>=</span><span>4</span><span>, 
        </span><span>@freq_interval</span><span>=</span><span>1</span><span>, 
        </span><span>@freq_subday_type</span><span>=</span><span>2</span><span>, 
        </span><span>@freq_subday_interval</span><span>=</span><span>30</span><span>, 
        </span><span>@freq_relative_interval</span><span>=</span><span>0</span><span>, 
        </span><span>@freq_recurrence_factor</span><span>=</span><span>0</span><span>, 
        </span><span>@active_start_date</span><span>=</span><span>20140105</span><span>, 
        </span><span>@active_end_date</span><span>=</span><span>99991231</span><span>, 
        </span><span>@active_start_time</span><span>=</span><span>2000</span><span>, 
        </span><span>@active_end_time</span><span>=</span><span>235959</span>

<span>EXEC</span>  msdb.dbo.sp_add_jobserver  <span>@job_name</span> <span>=</span> <span>@job_name</span>, <span>@server_name</span> <span>=</span> N<span>'</span><span>(local)</span><span>'</span>





<span>--</span><span>----------------------------------------------------------------------------</span><span>
--</span><span>建作业</span>
<span>DECLARE</span> <span>@DBName</span> <span>NVARCHAR</span>(<span>MAX</span><span>)
</span><span>DECLARE</span> <span>@job_name</span><span> sysname

</span><span>SET</span> <span>@DBName</span><span>=</span><span>'</span><span>xxx</span><span>'</span>  <span>--</span><span>★Do</span>

<span>SET</span> <span>@job_name</span><span>=</span><span>'</span><span>Monitor_ActiveCursors_</span><span>'</span> <span>+</span> <span>@DBName</span>
<span>EXEC</span> msdb.dbo.sp_add_job <span>@job_name</span><span>=</span><span>@job_name</span><span>, 
</span><span>@enabled</span><span>=</span><span>1</span><span>, 
</span><span>@notify_level_eventlog</span><span>=</span><span>0</span><span>, 
</span><span>@notify_level_email</span><span>=</span><span>0</span><span>, 
</span><span>@notify_level_netsend</span><span>=</span><span>0</span><span>, 
</span><span>@notify_level_page</span><span>=</span><span>0</span><span>, 
</span><span>@delete_level</span><span>=</span><span>0</span><span>, 
</span><span>@description</span><span>=</span>N<span>'</span><span>监控游标使用</span><span>'</span><span>, 
</span><span>@category_name</span><span>=</span>N<span>'</span><span>Database Maintenance</span><span>'</span><span>, 
</span><span>@owner_login_name</span><span>=</span>N<span>'</span><span>sa</span><span>'</span> 


<span>--</span><span>添加监控步骤</span>
<span>DECLARE</span> <span>@job_name</span><span> SYSNAME
</span><span>DECLARE</span> <span>@SQL</span> <span>NVARCHAR</span>(<span>MAX</span><span>)
</span><span>DECLARE</span> <span>@DBName</span> <span>NVARCHAR</span>(<span>MAX</span><span>)

</span><span>SET</span> <span>@DBName</span><span>=</span><span>'</span><span>xxxx</span><span>'</span>  <span>--</span><span>★Do</span>
<span>SET</span> <span>@job_name</span><span>=</span><span>'</span><span>Monitor_ActiveCursors_</span><span>'</span> <span>+</span> <span>@DBName</span>  <span>--</span><span>★Do</span>

<span>BEGIN</span> 
    <span>SET</span> <span>@SQL</span> <span>=</span> N<span>'</span><span>
USE [msdb]
GO
INSERT INTO ActiveCursors(cntr_value,cntr_time)  
SELECT cntr_value,GETDATE()  FROM sys.[dm_os_performance_counters] 
WHERE [counter_name]=</span><span>''</span><span>Active cursors</span><span>''</span><span>   
AND [object_name]=</span><span>''</span><span>SQLServer:Cursor Manager by Type</span><span>''</span><span>  
AND [instance_name]=</span><span>''</span><span>_Total</span><span>''</span>
<span>'</span>
    <span>EXEC</span> msdb.dbo.sp_add_jobstep <span>@job_name</span> <span>=</span> <span>@job_name</span><span>,
        </span><span>@step_name</span> <span>=</span> N<span>'</span><span>Monitor</span><span>'</span>, <span>@step_id</span> <span>=</span> <span>1</span>, <span>@cmdexec_success_code</span> <span>=</span> <span>0</span><span>,
        </span><span>@on_success_action</span> <span>=</span> <span>3</span>, <span>@on_success_step_id</span> <span>=</span> <span>0</span>, <span>@on_fail_action</span> <span>=</span> <span>2</span><span>,
        </span><span>@on_fail_step_id</span> <span>=</span> <span>0</span>, <span>@retry_attempts</span> <span>=</span> <span>0</span>, <span>@retry_interval</span> <span>=</span> <span>0</span><span>,
        </span><span>@os_run_priority</span> <span>=</span> <span>0</span>, <span>@subsystem</span> <span>=</span> N<span>'</span><span>TSQL</span><span>'</span>, <span>@command</span> <span>=</span> <span>@SQL</span><span>,
        </span><span>@database_name</span> <span>=</span> <span>@DBNAME</span>, <span>@flags</span> <span>=</span> <span>0</span>

<span>END</span>

  


<span>--</span><span>创建Monitor作业的调度计划</span>
<span>DECLARE</span> <span>@job_name</span><span> SYSNAME
</span><span>DECLARE</span> <span>@SQL</span> <span>NVARCHAR</span>(<span>MAX</span><span>)
</span><span>DECLARE</span> <span>@DBName</span> <span>NVARCHAR</span>(<span>MAX</span><span>)

</span><span>SET</span> <span>@DBName</span><span>=</span><span>'</span><span>xxxx</span><span>'</span>  <span>--</span><span>★Do</span>
<span>SET</span> <span>@job_name</span><span>=</span><span>'</span><span>Monitor_ActiveCursors_</span><span>'</span> <span>+</span> <span>@DBName</span>  <span>--</span><span>★Do</span>

<span>--</span><span>修改作业的执行时间</span>
<span>EXEC</span>  msdb.dbo.sp_add_jobschedule  <span>@job_name</span> <span>=</span> <span>@job_name</span>, <span>@name</span><span>=</span>N<span>'</span><span>Plan</span><span>'</span><span>, 
        </span><span>@enabled</span><span>=</span><span>1</span><span>, 
        </span><span>@freq_type</span><span>=</span><span>4</span><span>, 
        </span><span>@freq_interval</span><span>=</span><span>1</span><span>, 
        </span><span>@freq_subday_type</span><span>=</span><span>2</span><span>, 
        </span><span>@freq_subday_interval</span><span>=</span><span>30</span><span>, 
        </span><span>@freq_relative_interval</span><span>=</span><span>0</span><span>, 
        </span><span>@freq_recurrence_factor</span><span>=</span><span>0</span><span>, 
        </span><span>@active_start_date</span><span>=</span><span>20140105</span><span>, 
        </span><span>@active_end_date</span><span>=</span><span>99991231</span><span>, 
        </span><span>@active_start_time</span><span>=</span><span>2000</span><span>, 
        </span><span>@active_end_time</span><span>=</span><span>235959</span>

<span>EXEC</span>  msdb.dbo.sp_add_jobserver  <span>@job_name</span> <span>=</span> <span>@job_name</span>, <span>@server_name</span> <span>=</span> N<span>'</span><span>(local)</span><span>'</span>
View Code

上面视图里的[object_name]字段和 [instance_name]字段跟你的环境会不一样，所以大家要按照自己的环境来修改

如果是SQLSERVER2005是没有CPU usage %这个counter的，我使用了下面的SQL语句

<span>SELECT</span> <span>SUM</span>(<span>[</span><span>cpu</span><span>]</span>) <span>FROM</span> sys.<span>[</span><span>sysprocesses</span><span>]</span> <span>WHERE</span> SPID<span>>=</span><span>50</span>

 

7、画折线图

监控了一天的时间，根据结果使用EXCEL画出折线图

 记一次数据库调优过程（IIS发过来SQLSERVER 的FETCH API

记一次数据库调优过程（IIS发过来SQLSERVER 的FETCH API

 

凌晨那段曲线是因为数据库有做清除数据的操作，所以会比较高

游标跟CPU图虽然说不能完全吻合，但是基本能吻合

 

解决方法

1、修改代码

2、升级到SQL2008，然后使用资源调控器把CPU压下去

 

最终还是找人修改代码

总结

有时候对一些老旧的程序，例如ASP，可能老一代程序员还会，现在的程序员基本都使用ASP.NET

所以如果可能，还是跟上技术的脚步，不然出问题了，没有人维护就麻烦了

 

如有不对的地方，欢迎大家拍砖o(∩_∩)o 

Stellungnahme：
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel：SqlServer转换为Mysql的一款工具推荐（mss2sql）Nächster Artikel：最近看过的书单（2）

In Verbindung stehende Artikel

Mehr sehen