sp_WhoIsActive ( 서버 부하 및 락 관련 )

sp_WhoIsActive

---

• 매번 장애는 발생하는데..
• 실 서비스 때문에 이슈를 파악하기에는 시간이 없고 바로 서버 전환하여 재부팅하기 일수였다.
• 이런 문제를 해결하기 위해 저장하면 좋겠다고 하여서 찾아보니 위와 같은 SP 가 존재하였다.

sp_who2
sp_WhoIsActive
-- https://m.blog.naver.com/stonefly2001/221850646532

/*
Test 용도 - Lock
drop table tblx
go

begin tran 
	create table tblx (idx int)
	waitfor delay '15:55:08.700'
	insert into tblx values ( 1)
commit tran 	

*/

/*
step1) table setting 
exec sp_help whoisactive_log
*/
declare @table_creation_script varchar(max);
exec sp_WhoIsActive @schema = @table_creation_script output , @return_schema = 1;
print @table_creation_script;
go

CREATE TABLE whoisactive_log ( [dd hh:mm:ss.mss] varchar(8000) NULL,[session_id] smallint NOT NULL,[sql_text] xml NULL,[login_name] nvarchar(128) NOT NULL,[wait_info] nvarchar(4000) NULL,[CPU] varchar(30) NULL,[tempdb_allocations] varchar(30) NULL,[tempdb_current] varchar(30) NULL,[blocking_session_id] smallint NULL,[reads] varchar(30) NULL,[writes] varchar(30) NULL,[physical_reads] varchar(30) NULL,[used_memory] varchar(30) NULL,[status] varchar(30) NOT NULL,[open_tran_count] varchar(30) NULL,[percent_complete] varchar(30) NULL,[host_name] nvarchar(128) NULL,[database_name] nvarchar(128) NULL,[program_name] nvarchar(128) NULL,[start_time] datetime NOT NULL,[login_time] datetime NULL,[request_id] int NULL,[collection_time] datetime NOT NULL);
create clustered index cx_whosiactivelog_collection_time on dbo.whoisactive_log([collection_time]);

/*
step2) exec Query
*/
exec sp_WhoIsActive @get_outer_command =1 
	, @output_column_list = '[dd hh:mm:ss.mss][session_id][sql_text][login_name][wait_info][CPU][tempdb_allocations][tempdb_current][blocking_session_id][reads][writes][physical_reads][used_memory][status][open_tran_count][percent_complete][host_name][database_name][program_name][start_time][login_time][request_id][collection_time]' 
	, @destination_table ='TEST.dbo.whoisactive_log'

select * from test.dbo.whoisactive_log

 

참고 : https://github.com/amachanic/sp_whoisactive