Início / dba / Perguntas / 167405
Accepted
Sean Long
Asked: 2017-03-17 14:31:56 +0800 CST

Obtendo uma lista de objetos ativos que usam cursores, mas não os fecha/desaloca explicitamente

  • 772

Às vezes, nossos desenvolvedores escrevem uma consulta que usa cursores, mas não os fecha explicitamente. Estou tentando gerar uma lista de objetos ativos no meu banco de dados de produção que usam cursores, mas não os fecham/desalocam explicitamente. Para fazer isso, escrevi uma instrução simples que faz o trabalho, mas é extremamente lenta:

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%' )

Atualmente, isso leva algo como 3 minutos para ser executado. Existe uma maneira melhor de obter as informações que estou procurando?

sql-server sql-server-2014

1 respostas

  1. Best Answer

    Em vez de pesquisar todo o texto do procedimento armazenado por esses curingas, provavelmente seria melhor procurar por cursores abertos e seu texto associado. Isso provavelmente tornaria mais fácil localizar nomes de cursores, procs armazenados, etc. escritos por desenvolvedores esquecidos.

    Para testar, execute isso em uma janela:

    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;

    Então, em outra janela, execute isto:

    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;

    Nozes

    Você deve ver o cursor aberto e deixado pendurado pela outra sessão aqui, junto com quaisquer outros cursores abertos.

    Se você precisar monitorar isso (e não tiver uma ferramenta de monitoramento), poderá usar a coluna dormant_duration e pesquisar esse DMV com um trabalho de agente que disparará um alerta ou e-mail quando um cursor aberto estiver inativo por um determinado quantidade de tempo. Isso em milissegundos.

    Espero que isto ajude!

    • 6

relate perguntas