조회가 갑자기 느려졌을때

1. exec sp_who2 실행

    --> 누가 sp 실행하고 있는지 확인함

 

2. exec sp_lock 실행

    --> lock 걸린 sp 확인함

          여기서 Mode가 'X' 로 되 있는 부분이 lock 걸린것으로 판단

3. dbcc inputbuff(spid)

    --> 현재 상태를 확인함

 

4. Kill spid

    --> 2번에서 확인한 spid를 넣어서 실행한다.

          이 문장 실행하면 exec sp_lock 에서  Mode가 'X'로 나오던 부분이 전부 없어짐.

 

이렇게 해도 안되면

1. dbcc freeproccache
   dbcc dropcleanbuffers

   --> freeproccache는 계획 캐시를 신중하게 지우려고 사용한다. 예를 들어 계획 캐시를 해제하면 SP가 캐시에서 다시 사용되지 않고 다시 컴파일된다. 이로 인해 쿼리 성능이 일시적으로 갑자기 저하될수 있다.계획 캐시의 삭제된 각 캐시스토어에 대해 SQL Server 오류 로그에 "'DBCC FREEPROCCACHE' 또는 'DBCC FREESYSTEMCACHE' 작업으로 인해 '%s' 캐시스토어(계획 캐시의 일부)에 대한 캐시스토어 플러시가 SQL Server에서 %d번 발견되었습니다"라는 정보 메시지가 있습니다. 이 메시지는 캐시가 해당 시간 간격 내에 플러시되는 동안 5분마다 기록됩니다.

dropcleanbuffers를 사용하면 서버를 종료하고 다시 시작하지 않아도 빈 버퍼 캐시를 하용하여 쿼리를 실행할수 있다. 버퍼 풀에서 빈 버퍼를 삭제하려면 먼저 checkpoint를 사용해 빈 버퍼 캐시를 만든다. 이 과정은 현재 DB에 대한 모든 커밋되지 않은 페이지를 디스크로 기록하고 버퍼를 비운다. 버퍼를 비운후에 위 명령을 실행해 버퍼 풀에서 모든 버퍼를 제거할수 있다.

 

참고사항

1. lock 으로 의심되는 spid 추출하기

select p.status, p.program_name, p.hostname, p.spid, p.blocked, p.cpu, p.physical_io, p.waittime, p.lastwaittype, p.waitresource, p.dbid, p.uid, p.memusage, p.login_time
       ,p.last_batch, p.ecid, p.open_tran, p.sid, p.hostprocess, p.cmd, p.nt_domain, p.nt_username, p.net_address, p.net_library, p.loginame, p.context_info, p.sql_handle,
    p.stmt_start, p.stmt_end
from sys.sysprocesses p
where (status like 'run%' or waittime > 0 or blocked <> 0 or open_tran <> 0 or exists(select * from sys.sysprocesses p1 where p.spid = p1.blocked and p1.spid <> p1.blocked))
  and spid > 50
  and spid <> @@spid
  order by case when status like 'run%' then 0 else 1 end, waittime desc, open_tran desc

2. DISK정리

    dbcc shrinkdatabase('DB_NAME')

    --> 필요없이 비어있는 영역 정리작업을 함