sys.dm_tran_locks 를 이용한 잠금 정보 확인

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

 

Sys.dm_tran_locks는 현재 활성 상태인 잠금 관리자 리소스에 대한 정보를 반환 한다. 각 행은 이미 허용된 잠금 또는 허용 대기 중인 잠금에 대해 현재 활성 상태인 잠금 관리자 요청을 나타낸다.

 

select * from sys.dm_tran_locks;

 

 

  • Resource_type : 리소스 유형을 나타낸다. 리소스는 DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT 또는 ALLOCATION_UNIT 중 하나이다.
  • Request_mode : 요청 모드이다. 허용 된 요청의 경우 허용 모드이고 대기 중인 요청의 경우에는 요청 중인 모드가 된다.
  • Request_status : 요청의 현재 상태이다. 가능한 값은 GRANTED, CONVERT, WAIT, LOW_PRIORITY_CONVERT, LOW_PRIORITY_WAIT 또는 ABORT_BLOCKERS이다.
  • resource_associated_entity_id : 리소스가 연결된 데이터베이스 내의 엔터티ID이다. 리소스 유형에 따라 개체 ID, Hobt ID 또는 할당 단위 ID가 될 수 있다.

 

 

Sys.dm_tran_locks, sys.sysprocesses, sys.dm_exec_sql_text 정보를 활용하여 잠금 정보를 좀더 상세히 알아본다. 다음 스크립트를 실행하면 잠금에 대한 정보, 차단된SID, 실행중인 쿼리 등을 보여준다.

SELECT DTL.resource_type,

CASE

WHEN DTL.resource_type IN ('DATABASE', 'FILE', 'METADATA') THEN DTL.resource_type

WHEN DTL.resource_type = 'OBJECT' THEN OBJECT_NAME(DTL.resource_associated_entity_id, SP.[dbid])

WHEN DTL.resource_type IN ('KEY', 'PAGE', 'RID') THEN

(

SELECT OBJECT_NAME([object_id])

FROM sys.partitions

WHERE sys.partitions.hobt_id =

DTL.resource_associated_entity_id

)

ELSE 'Unidentified'

END AS requested_object_name, DTL.request_mode, DTL.request_status,

DEST.TEXT, SP.spid, SP.blocked, SP.status, SP.loginame

FROM sys.dm_tran_locks DTL

INNER JOIN sys.sysprocesses SP

ON DTL.request_session_id = SP.spid

--INNER JOIN sys.[dm_exec_requests] AS SDER ON SP.[spid] = [SDER].[session_id]

CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS DEST

WHERE SP.dbid = DB_ID()

AND DTL.[resource_type] <> 'DATABASE'

ORDER BY DTL.[request_session_id];

 

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/1968/understanding-sql-server-locking/

 

 

2014-10-17 / 강성욱 / http://sqlmvp.kr

 

데이터베이스, 잠금, DB 락, DB 블록킹, sys.dm_tran)locks, sys.dm_exec_sql_text, sys.sysprocesses, SQL Server, MSSQL, DBA, DMV, db lock

페이지 ID로 테이블 이름 찾기

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

손상된 페이지가 있을 때 어느 테이블의 손상인지 확인 하는 방법에 대해서 알아본다. 손상된 페이지가 있을 경우 일반적으로 DBCC CHECKDB를 실행하지만 데이터베이스가 TB 단위인 경우에는 이 문제를 파악하기 위해 몇 시간이 걸릴지도 모르는 일이다. 또 다른 확인 방법으로는 PAGELATCH_EX 대기를 보고 sys.dm_os_waiting_tasks 의 resource_description 정보를 이용하여 테이블 이름을 알아 낼 수도 있다.

 

suspect_pages 테이블의 데이터를 이용하여 테이블 이름을 확인해 보자. Suspect_page 테이블은 SQL Server 2005부터 도입 되었으며 주의 대상이 발생하였을 때 대상 페이지에 대한 정보를 유지 관리하는데 사용되며 복원이 필요한지 여부를 결정하는데 사용 된다.

 

Suspect_pages 테이블에서 손상된 페이지가 있는지 확이 한다.

 

Page_id 를 이용하여 페이지 정보를 확인 한다.

 

 

Metadata: Objectid 정보를 이용하여 오브젝트 네임을 조회 한다.

 

 

[참고자료]

http://www.sqlskills.com/blogs/paul/finding-table-name-page-id/?utm_source=rss&utm_medium=rss&utm_campaign=finding-table-name-page-id

 

2014-10-07 / 강성욱 / http://sqlmvp.kr

 

 

MSSQL, suspect, 주의 대상, 서스펙트, DB복구, DB관리, DBA,SQL Server, dbcc checkdb, pageID, 데이터베이스

컬럼스토어 인덱스 대용량 데이터 로드

Column Store Index Bulk Load Data

 

  • Version : SQL Server 2012, 2014

 

클러스터 컬럼 스토어 인덱스 대용량 데이터 로드

이전 포스팅에서 컬럼스토어 인덱스에 대한 동시성 및 인서트 동시성에 대해서 살펴 보았다.

 

이번 포스트는 대용량 데이터 로드(Bulk load)를 통해 데이터가 삽입될 때 잠금 동작에 대해서 설명한다. 테스트용 기본 테이블을 생성하고 더미 데이터를 생성한다.

Create table t_bulkload (

accountkey int not null,

accountdescription nvarchar (50),

accounttype nvarchar(50),

AccountCodeAlternatekey int

)

go

 

-- Let us prepare the data

-- insert 110K rows into a regular table

 

begin tran

    declare @i int = 0

 

    while (@i < 110000)

    begin

        insert into t_bulkload values (@i, 'description', 'dummy-accounttype', @i*2)

        set @i = @i + 1

    end

 

commit

 

커맨드 창에서 BCP명령을 사용하여 데이터를 파일로 생성한다.

bcp adventureworksDW2012..t_bulkload out c:\t_bulkoad.dat -c -T

 

 

테스트를 위해 만든 테이블을 truncate 하고 클러스터 컬럼스토어 인덱스를 생성한다. 테이블에 행이 없기 때문에 ROWGROUP은 없다.

--truncate the table

Truncate table t_bulkload

 

-- convert row clustered index into clustered columnstore index

CREATE CLUSTERED COLUMNSTORE index t_bulkload_cci on t_bulkload

 

이제 배치 크기를 103000 크기로 하고 대량 데이터를 로드 한다. 그리고 컬럼스토어 RowGroup 정보를 확인한다.

세션 1

세션 2

-- now bulkload the data

begin tran

bulk insert t_bulkload

FROM 'c:\t_bulkoad.dat'

WITH

(

BATCHSIZE = 103000

)

 

 
 

select * from sys.column_store_row_groups where object_id = object_id('t_bulkload')

 

 

위 그림은 두 개의 행 그룹이 있는 것을 보여준다. 첫 번째 행 row_group_id = 0 그룹은 103000행을 압축한다. 이는 BATCHSIZE >= 102400 일때 SQL Server가 직접 행 그룹을 압축하기 때문이다. 직접 행 압축은 행 델타 그룹을 사용하지 않기 때문에 SQL Server 로깅을 최소화 할 수 있다. 그리고 튜플 데이터를 이동 할 필요가 없다.

 

나머지 7천 데이터 파일은 행 델타 그룹 삽입(데이터파일은 110000 행이다)에서 열이 부족하기 때문에 행 그룹이 계속 닫혀 있지 않음을 의미하는 OPEN 상태로 표시되고 있다. 수 백만 행이 표시 되었을 때 백그라운드의 튜플 이동기에 의해 닫히고 압축을 할 것이다.

 

잠금 상태를 살펴 보자. 델타 행 그룹 및 압축 행 그룹 모두에서 X 잠금을 확인 할 수 있다. 행 그룹 레벨에서 잠금 오버헤드를 최소화 한다.

select

request_session_id as spid,

resource_type as rt,

resource_database_id as rdb,

(case resource_type

        WHEN 'OBJECT' then object_name(resource_associated_entity_id)

        WHEN 'DATABASE' then ' '

        ELSE (select object_name(object_id)

                from sys.partitions

                where hobt_id=resource_associated_entity_id)

END) as objname,

resource_description as rd,

request_mode as rm,

request_status as rs

from sys.dm_tran_locks

 

 

 

현재 대용량 데이터 로드가 실행된 상태에서 다른 세션에서 행을 삽입할 경우 어떤 현상이 발생 하는지 살펴보자.

세션 3

세션 4

begin tran

    insert into t_bulkload values (-1, 'single row', 'single row', -1)

 
 

select

request_session_id as spid,

resource_type as rt,

resource_database_id as rdb,

(case resource_type

        WHEN 'OBJECT' then object_name(resource_associated_entity_id)

        WHEN 'DATABASE' then ' '

        ELSE (select object_name(object_id)

                from sys.partitions

                where hobt_id=resource_associated_entity_id)

END) as objname,

resource_description as rd,

request_mode as rm,

request_status as rs

from sys.dm_tran_locks

 

 

한 행을 입력하였지만 현재 행 그룹 row_group_id = 1에 X 잠금을 보유하고 있기 때문에 다음과 같은 새로운 델타 행 그룹에 삽입된 것을 확인 할 수 있다.

 

 

[참고자료]

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2014/07/28/clustered-column-store-index-bulk-loading-the-data.aspx

 

 

2014-09-29 / 강성욱 / http://sqlmvp.kr

 

SQL Server 2012, 컬럼스토어 인덱스, 행 그룹, Column Store Index, 메모리 인덱스, SQL, 데이터베이스, 대용량 데이터베이스, Delta Row Group, Compress Row Group, bulk load, 대용량데이터로드

컬럼스토어 인덱스 INSERT 작업과 동시성

Column Store Index Concurrency with INSERT

 

  • Version : SQL Server 2012, 2014

 

클러스터 컬럼 스토어 INSERT

이전 블로그에 설명한 바와 같이 클러스터된 컬럼스토어 인덱스는 DW 시나리오에서 빠른 쿼리 성능으로 데이터로드에 최적화 되어 있다. 대량의 인서트 작업에서도 DW 쿼리는 커밋되지 않은 읽기(Read Uncommitted) 격리 수준에서 병렬로 데이터를 로드 할 수 있다.

 

데이터가 동시에 인서트 될 때 잠금 동작에 대해서 알아 본다.

Version : SQL Server 2014

CREATE TABLE [dbo].[T_ACCOUNT](

[accountkey] [int] IDENTITY(1,1) NOT NULL,

[accountdescription] [nvarchar](50) NULL

) ON [PRIMARY]

 

-- create a CCI

CREATE CLUSTERED COLUMNSTORE INDEX ACCOUNT_CCI ON T_ACCOUNT

 

 

INSERT Operations

한 행을 인서트하고 잠금을 살펴본다. 참고로 트랜잭션을 커밋하지 않았다.

세션1

세션 2

begin tran

    insert into T_ACCOUNT (accountdescription ) values ('row-1');

 
 

select

    request_session_id as spid,

    resource_type as rt,

    resource_database_id as rdb,

    (case resource_type

        WHEN 'OBJECT' then object_name(resource_associated_entity_id)

        WHEN 'DATABASE' then ' '

        ELSE (select object_name(object_id)

            from sys.partitions

            where hobt_id=resource_associated_entity_id)

    END) as objname,

    resource_description as rd,

    request_mode as rm,

    request_status as rs

from sys.dm_tran_locks

 

다른 세션에서 행을 인서트한다. 그리고 잠금 상태를 확인 한다. 세션 54에서 두 번째 트랜잭션이 같은 RowGroup에 행을 삽입한 것에 유의한다. 동시 인서트로 서로를 차단하지 않고 RowGroup 데이터를 로드 할 수 있다.

세션 2

세션 3

 

begin tran

    insert into T_ACCOUNT (accountdescription ) values ('row-2');

select

    request_session_id as spid,

    resource_type as rt,

    resource_database_id as rdb,

    (case resource_type

        WHEN 'OBJECT' then object_name(resource_associated_entity_id)

        WHEN 'DATABASE' then ' '

        ELSE (select object_name(object_id)

            from sys.partitions

            where hobt_id=resource_associated_entity_id)

    END) as objname,

    resource_description as rd,

    request_mode as rm,

    request_status as rs

from sys.dm_tran_locks

 

 

 

요약하면 CCI에 삽입은 동일한 Delta RowGroup에 인서트 작업과 데이터 로드시 서로를 차단하지 않는다.

 

[참고자료]

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2014/07/27/clustered-column-store-index-concurrency-with-insert-operations.aspx

 

2014-09-25 / 강성욱 / http://sqlmvp.kr

 

SQL Server 2012, 컬럼스토어 인덱스, 행 그룹, Column Store Index, 메모리 인덱스, SQL, 데이터베이스, 대용량 데이터베이스, Delta Row Group, Compress Row Group, 트랜잭션 격리수즌, read uncommitted, transaction isolation level, 스냅샷 격리수준

+ Recent posts