获取使用游标但未显式关闭/释放它们的活动对象列表

Question

有时，我们的开发人员会编写一个使用游标的查询，但不会显式地关闭它们。我试图在生产数据库中生成一个使用游标但没有显式关闭/释放它们的活动对象列表。要做到这一点，我编写了一个简单的语句来完成这项工作，但是非常慢：

select distinct name, 
            definition
from   SYS.SQL_MODULES 
   inner join SYS.OBJECTS O 
           on SQL_MODULES.OBJECT_ID = O.OBJECT_ID 
where  SQL_MODULES.DEFINITION like '%open%'
   and SQL_MODULES.DEFINITION like '%declare % cursor%'
   and ( SQL_MODULES.DEFINITION not like '%close%' 
          or SQL_MODULES.DEFINITION not like '%deallocate%' )

目前，这大约需要3分钟才能运行。有什么更好的方法来获取我想要的信息吗？

Answer 1

与其搜索所有存储过程文本中的这些通配符，不如查找打开的游标及其相关文本。这可能会使定位游标名称、存储过程等变得更容易，这些都是由健忘的开发人员编写的。

若要进行测试，请在一个窗口中运行以下操作：

SET NOCOUNT ON; 

IF OBJECT_ID('tempdb..#commands') IS NOT NULL
   BEGIN
         DROP TABLE #commands;
   END;

CREATE TABLE #commands
(
  ID INT IDENTITY(1, 1)
         PRIMARY KEY CLUSTERED,
  Command NVARCHAR(2000)
);
DECLARE @CurrentCommand NVARCHAR(2000);

INSERT INTO #commands ( Command )
    SELECT 'SELECT 1';

DECLARE result_cursor CURSOR
FOR
        SELECT Command
            FROM #commands;

OPEN result_cursor;
FETCH NEXT FROM result_cursor INTO @CurrentCommand;
WHILE @@FETCH_STATUS = 0
      BEGIN 

            EXEC (@CurrentCommand);

            FETCH NEXT FROM result_cursor INTO @CurrentCommand;
      END;

--CLOSE result_cursor;
--DEALLOCATE result_cursor;

然后在另一个窗口运行以下命令：

SELECT dec.session_id, dec.cursor_id, dec.name, dec.properties, dec.creation_time, dec.is_open, dec.is_close_on_commit,
        dec.fetch_status, dec.worker_time, dec.reads, dec.writes, dec.dormant_duration, dest.text
    FROM sys.dm_exec_cursors (0) AS dec
    CROSS APPLY sys.dm_exec_sql_text(dec.sql_handle) AS dest
    WHERE dec.is_open = 1;

您应该看到游标在这里与任何其他打开的游标一起被另一个会话打开并被挂起。

如果您需要监视这些(而且您没有监控工具)，您可以使用dormant_duration列并轮询使用代理作业的DMV，当打开的游标休眠一定时间时，该作业会触发警报或电子邮件。以毫秒为单位。

希望这能有所帮助！