主页 / dba / 问题 / 167405
Accepted
Sean Long
Asked: 2017-03-17 14:31:56 +0800 CST

获取使用游标但未明确关闭/取消分配它们的活动对象的列表

  • 772

有时我们的开发人员会编写一个使用游标的查询，但不会明确关闭它们。我正在尝试在我的生产数据库中生成一个活动对象列表，这些活动对象使用游标但没有明确关闭/取消分配它们。为此，我编写了一个简单的语句来完成这项工作，但速度非常慢：

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 分钟才能运行。有没有更好的方法来获取我正在寻找的信息？

sql-server sql-server-2014

1 个回答

  1. Best Answer

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

    要进行测试，请在一个窗口中运行：

    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，当打开的游标休眠特定时间时，代理作业将触发警报或电子邮件多少时间。以毫秒为单位。

    希望这可以帮助！

    • 6

相关问题