DB 튜닝/ ms-sql 튜닝 / DB가 느려졌을때 원인 찾는 법 /

  DB 서버응답이 느려졋다고 

여러 부서에서 연락이 왔다. 

원인을 찾고 해결을 해야 햇다 .

웹 서핑중 찾은 정보들로 여러 부분을 만져 줬다. 

아래는 문제가 될만한  spid 를 찾아주는 쿼리 

-- lock 으로 의심되는 spid 만 추출 

select p.status,  p.program_name, p.hostname

, p.spid, p.blocked, p.kpid, p.cpu, p.physical_io, p.waittype, 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 master.dbo.sysprocesses p

where (status like 'run%' or waittime > 0 OR blocked <> 0 OR open_tran <> 0

OR EXISTS(SELECT * FROM master.dbo.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 

돌리면 아래와 같이 값이 떨어진다.   

result  --> 

status	program_name	hostname	spid	blocked	kpid	cpu	physical_io	waittype	wattime
runnable	Internet Information Services	서버명	70	0	12316	47	499	0x0000	0
runnable	.Net SqlClient Data Provider	컴퓨터명..	118	0	6592	31	0	0x0000	0
runnable	.Net SqlClient Data Provider		118	0	3608	15	0	0x0000	0
runnable	.Net SqlClient Data Provider		118	0	2468	31	0	0x0000	0
runnable	.Net SqlClient Data Provider		118	0	11380	0	0	0x0000	0
suspended	.Net SqlClient Data Provider		118	0	11644	79	3472	0x00BF	3664
suspended	Internet Information Services		70	0	8852	15	76140	0x00BF	82
suspended	.Net SqlClient Data Provider		118	0	14428	0	0	0x00BF	23
suspended	.Net SqlClient Data Provider		118	0	12052	0	0	0x00BF	23
suspended	.Net SqlClient Data Provider		118	0	13264	0	0	0x00BF	21
suspended	.Net SqlClient Data Provider		118	0	6832	0	0	0x00BF	21
suspended	.Net SqlClient Data Provider		118	0	14884	0	0	0x00BF	18
suspended	.Net SqlClient Data Provider		118	0	6120	0	0	0x00BF	18
suspended	.Net SqlClient Data Provider		118	0	13144	0	0	0x00BF	18
suspended	.Net SqlClient Data Provider		118	0	12640	0	0	0x00BF	18
suspended	Internet Information Services		70	0	8172	32	218	0x0042	14
suspended	Internet Information Services		70	0	12264	31	528	0x0042	10
suspended	Internet Information Services		70	0	10972	31	417	0x0042	6

spid 값을 가지고 

dbcc inputbuffer(spid)   를 해보면 

관련자를 확인 할수 있다. 

해당 쿼리를 살펴보고  유저가 만든것이면   적당히 요리조리 처리 하면 된다. 

-- Look 걸린 SPID 검출 (Mode 부분이 X 표시되는 것이 LOCK)

EXEC sp_lock

--Lock 걸린 spid 를 입력하면 현재 돌고 있는 쿼리를 출력해 준다. 

dbcc inputbuffer(spid)

-- Lock 을 유발하는 spid 를 종료시킨다.

kill 78

아래는 원글 사이트 

http://yongandju.tistory.com/74