데이터베이스 운영을 하면서 lock을 발생시키는 프로세스와 이로 인해 대기하는 프로세스를 확인하고자 하는 경우가 생깁니다. 해당 프로세스에 해당하는 쿼리 또한 정보를 보고 싶어서 한번에 조회가 되도록 쿼리를 작성하여 사용하고 있습니다.
쿼리
create proc [dbo].[sp_mon_blocking]
as
set nocount on
--기초 테이블 생성
create table #a(blocking_spid int,blocking_hostprocess int ,
blocking_sql varchar(8000)
,blockedby_spid int,blockedby_hostprocess int
,blockedby_sql varchar(8000),waittime bigint,
waittype varbinary(50),lastwaittype varchar(100))
--blocking 상황 확인 및 데이터 입력
insert #a(blocking_spid, blocking_hostprocess,
blockedby_spid,waittime,waittype,lastwaittype)
select spid, hostprocess, blocked,waittime,
waittype,lastwaittype from master..sysprocesses where blocked > 0
--데이터가 없으면 여기서 그만!
if @@rowcount = 0
begin
print 'There is no blocking spid.'
return
end
--필요한 변수 선언
declare @spid int
declare @sql_handle binary(20)
declare @object_name varchar(100)
declare @text varchar(8000)
--데이터 수집 커서
declare CurBlocked cursor
for
select distinct blocking_spid from #a
union
select distinct blockedby_spid from #a
open CurBlocked
fetch next from CurBlocked into @spid
while @@fetch_status = 0
begin
select top 1 @sql_handle = sql_handle
from master..sysprocesses where spid = @spid
select @object_name = object_name(objectid),
@text = text from sys.dm_exec_sql_text(@sql_handle)
if @object_name is not null
begin
update #a set blocking_sql = @object_name where blocking_spid = @spid
update #a set blockedby_sql = @object_name,
blockedby_hostprocess = s.hostprocess
from #a a join master..sysprocesses s on a.blockedby_spid = s.spid
where blockedby_spid = @spid
end
else
begin
update #a set blocking_sql = @text where blocking_spid = @spid
update #a set blockedby_sql = @text, blockedby_hostprocess = s.hostprocess
from #a a join master..sysprocesses s on a.blockedby_spid = s.spid
where blockedby_spid = @spid
end
fetch next from CurBlocked into @spid
end
close CurBlocked
deallocate CurBlocked
--잠그는 최 우선 주체가 되는 넘들 입력
insert #a (blocking_spid, blocking_hostprocess, blocking_sql)
select distinct blockedby_spid, blockedby_hostprocess, blockedby_sql from #a
except
select distinct blocking_spid, blocking_hostprocess, blocking_sql from #a
--블로킹 체인을 보여주기 위한 커서
declare CurBlocked cursor
for
select blocking_spid from #a where blockedby_spid is null
open CurBlocked
fetch next from CurBlocked into @spid
while @@fetch_status = 0
begin
WITH Blocking (blocking_spid, blocking_hostprocess,
blocking_sql, blockedby_spid, blockedby_hostprocess,
blockedby_sql,waittime,waittype,lastwaittype, Level)
AS
( select blocking_spid, blocking_hostprocess,
blocking_sql, blockedby_spid, blockedby_hostprocess,
blockedby_sql,waittime,waittype,lastwaittype,
0 AS Level from #a where blocking_spid = @spid
union all
select a.*, Level + 1 from #a a
join Blocking b on a.blockedby_spid = b.blocking_spid
)
select blocking_spid, blocking_hostprocess
,blocking_sql = substring(blocking_sql,PATINDEX('%)[A-z]%', blocking_sql)
+1,8000-(PATINDEX('%)[A-z]%', blocking_sql)+1))
,blockedby_spid, blockedby_hostprocess
,blockedby_sql = substring(blockedby_sql,PATINDEX('%)[A-z]%', blockedby_sql)
+1,8000-(PATINDEX('%)[A-z]%', blockedby_sql)+1))
,waittime,waittype,lastwaittype, Level
from Blocking order by Level
fetch next from CurBlocked into @spid
end
close CurBlocked
deallocate CurBlocked
GO
exec sp_who2 'active' 를 활용하여 현재 실행되고 있는 프로세스의 정보를 조회하는 용도입니다.
구문
CREATE proc [dbo].[sp_active_spid]
as
set nocount on
create table #sp_who2(spid int,Status varchar(100), Login varchar(100),
HostName varchar(200),BlkBy varchar(5) ,DBName varchar(50)
, Command varchar(500), CPUTime bigint, DiskIO bigint, LastBatch varchar(100),
ProgramName varchar(500), spid2 int, REQUESTID int)
--현재 active spid 수집
insert #sp_who2 exec sp_who2 'active'
--시스템 프로세스는 제외
delete #sp_who2 where spid <= 50
--이 프로시저를 실행하는 spid도 제외
delete #sp_who2 where spid = @@spid
--데이터가 없으면 여기서 그만!
if not exists(select * from #sp_who2)
begin
print 'There is no active spid.'
return
end
--블로킹 주체가 되는 spid가 현재 active가 아니더라도 포함 시키자
if exists (select s2.BlkBy from #sp_who2 s1
right join (select BlkBy from #sp_who2 where BlkBy <> ' .') s2
on s1.spid = s2.BlkBy
where s1.spid is null)
begin
declare @spid int
declare curSPID cursor for
select s2.BlkBy from #sp_who2 s1
right join (select BlkBy from #sp_who2 where BlkBy <> ' .') s2
on s1.spid = s2.BlkBy
where s1.spid is null
open curSPID
fetch next from curSPID into @spid
while @@fetch_status = 0
begin
insert #sp_who2 exec sp_who2 @spid
fetch next from curSPID into @spid
end
close curSPID
deallocate curSPID
end
--이제 필요한 정보 조회
select a.spid, BlkBy, a.Status,a.HostName ,hostprocess, Command,
CPUTime, DiskIO, waittime, waittype,lastwaittype, open_tran
,object_name(objectid) as sp_name,
right(d.text, len(d.text)-PATINDEX('%)[A-z]%', d.text)) as sql_stmt
from #sp_who2 as a join master.dbo.sysprocesses as p on a.spid = p.spid
cross apply sys.dm_exec_sql_text(p.sql_handle) as d
order by a.spid
SELECT
a.sid, -- SID
a.serial#, -- 시리얼번호
a.status, -- 상태정보
a.process, -- 프로세스정보
a.username, -- 유저
a.osuser, -- 접속자의 OS 사용자 정보
b.sql_text, -- sql
c.program -- 접속 프로그램
FROM
v$session a,
v$sqlarea b,
v$process c
WHERE
a.sql_hash_value=b.hash_value
AND a.sql_address=b.address
AND a.paddr=c.addr
AND a.status='ACTIVE';
Copyright (c) Microsoft Corporation Enterprise Edition
설명
대용량 데이터베이스를 운영시에 alter index rebuild 구문을 이용하여 데이터 압축을 하는 경우가 발생을 합니다.
기존에는 sys.partitions에 Online Index를 생성하는 동안 rows를 확인하여 진행할 수 있었으나 MS SQL 2016 이상에서는 조회되지 않습니다.
그래서 아래의 방법으로 Alter Index에 대한 진행을 모니터링 하려고 합니다.
사전 설정
SET STATISTICS PROFILE ON
해당 세션에서
ALTER INDEX [인덱스이름] ON [테이블이름]
REBUILD WITH ( DATA_COMPRESSION = PAGE, ONLINE=ON, MAXDOP=8)
진행률 확인
SELECT
node_id,
physical_operator_name,
SUM(row_count) row_count,
SUM(estimate_row_count) AS estimate_row_count,
CAST(SUM(row_count)*100 AS float)/SUM(estimate_row_count)
as estimate_percent_complete
FROM sys.dm_exec_query_profiles
WHERE session_id=<SPID>
GROUP BY node_id,physical_operator_name
ORDER BY node_id desc;
SELECT * FROM M_TABLE_PARTITIONS WHERE TABLE_NAME='테이블명'
HASH 파티션 하기
alter table SAPHANADB.PRCD_ELEMENTS PARTITION BY HASH (KNUMV) PARTITIONS 12;
alter table SAPHANADB.MBEWH PARTITION BY HASH (MATNR) PARTITIONS 12;
alter table SAPHANADB.ACCTCR PARTITION BY HASH (AWREF) PARTITIONS 12;
소요시간
Statement 'alter table SAPHANADB.PRCD_ELEMENTS PARTITION BY HASH (KNUMV) PARTITIONS 12'
successfully executed in 10:06.782 minutes (server processing time: 10:06.779 minutes) - Rows Affected: 0
약 4억건
Statement 'alter table SAPHANADB.MBEWH PARTITION BY HASH (MATNR) PARTITIONS 12'
successfully executed in 24:53.557 minutes (server processing time: 24:53.560 minutes) - Rows Affected: 0
약 10억건
Statement 'alter table SAPHANADB.ACCTCR PARTITION BY HASH (AWREF) PARTITIONS 12'
successfully executed in 11:51.099 minutes (server processing time: 11:51.057 minutes) - Rows Affected: 0
약 5억 6천만건
RANGE RANGE 파티셔닝 하기
ALTER TABLE SAPHANADB.ACDOCA PARTITION BY
RANGE (RBUKRS)
(PARTITION VALUE= 'G111',
PARTITION VALUE= 'R100',
PARTITION VALUE= 'R103',
PARTITION VALUE= 'R106',
PARTITION VALUE= 'R107',
PARTITION OTHERS),
RANGE (GJAHR)
(PARTITION VALUE = '2020',
PARTITION VALUE = '2021',
PARTITION VALUE = '2022',
PARTITION VALUE = '2023',
PARTITION VALUE = '2024',
PARTITION VALUE = '2025',
PARTITION VALUE = '2026',
PARTITION VALUE = '2027',
PARTITION VALUE = '2028',
PARTITION VALUE = '2029',
PARTITION VALUE = '2030',
PARTITION OTHERS)
ALTER TABLE SAPHANADB.CKMLCR PARTITION BY
RANGE (BDATJ)
(PARTITION VALUE = '2019',
PARTITION VALUE = '2020',
PARTITION VALUE = '2021',
PARTITION VALUE = '2022',
PARTITION VALUE = '2023',
PARTITION VALUE = '2024',
PARTITION VALUE = '2025',
PARTITION VALUE = '2026',
PARTITION VALUE = '2027',
PARTITION VALUE = '2028',
PARTITION VALUE = '2029',
PARTITION VALUE = '2030',
PARTITION OTHERS),
RANGE (POPER)
(PARTITION VALUE='001',
PARTITION VALUE='002',
PARTITION VALUE='003',
PARTITION VALUE='004',
PARTITION VALUE='005',
PARTITION VALUE='006',
PARTITION VALUE='007',
PARTITION VALUE='008',
PARTITION VALUE='009',
PARTITION VALUE='010',
PARTITION VALUE='011',
PARTITION VALUE='012',
PARTITION OTHERS)
ALTER TABLE SAPHANADB.CKMLPP PARTITION BY
RANGE (BDATJ)
(PARTITION VALUE = '2019',
PARTITION VALUE = '2020',
PARTITION VALUE = '2021',
PARTITION VALUE = '2022',
PARTITION VALUE = '2023',
PARTITION VALUE = '2024',
PARTITION VALUE = '2025',
PARTITION VALUE = '2026',
PARTITION VALUE = '2027',
PARTITION VALUE = '2028',
PARTITION VALUE = '2029',
PARTITION VALUE = '2030',
PARTITION OTHERS),
RANGE (POPER)
(PARTITION VALUE='001',
PARTITION VALUE='002',
PARTITION VALUE='003',
PARTITION VALUE='004',
PARTITION VALUE='005',
PARTITION VALUE='006',
PARTITION VALUE='007',
PARTITION VALUE='008',
PARTITION VALUE='009',
PARTITION VALUE='010',
PARTITION VALUE='011',
PARTITION VALUE='012',
PARTITION OTHERS)
소요시간
Statement 'ALTER TABLE SAPHANADB.ACDOCA PARTITION BY RANGE (RBUKRS)
(PARTITION VALUE= 'G111', PARTITION VALUE= ...'
successfully executed in 21:00.248 minutes (server processing time: 21:00.110 minutes)
- Rows Affected: 0
4억 7천만
Statement 'ALTER TABLE SAPHANADB.CKMLCR PARTITION BY RANGE (BDATJ)
(PARTITION VALUE = '2019', PARTITION VALUE ...'
successfully executed in 44:35.501 minutes (server processing time: 44:35.510 minutes)
- Rows Affected: 0
11억건
Statement 'ALTER TABLE SAPHANADB.CKMLPP PARTITION BY RANGE (BDATJ)
(PARTITION VALUE = '2019', PARTITION VALUE ...'
successfully executed in 39:36.768 minutes (server processing time: 39:36.776 minutes)
- Rows Affected: 0
11억건
