업무를 진행하다 보면 테이블의 특정 칼럼에 대해 LOCK 이 걸려 해당 칼럼에 액세스 하지 못하는 경우가 있다. 심한경우 락이 승격되어 테이블 자체에도 액세스 하지 못하는 경우가 생길 수 가 있다. 이럴 경우에 어떤 방식으로 락을 발생시킨 트랜잭션을 잡아내어 대응할 수 있는지 확인해보자. 아래의 데이터를 이용하여 실습을 진행해보자. SELECT * FROM dbo.TBLINSASHTEST WITH(NOLOCK) 위의 테이블에서 트랜잭션 제어어를 사용하여 홍길동의 이름을 변경하는 쿼리를 날려보자. BEGIN TRAN UPDATE dbo.TBLINSASHTEST SET name = N'도로시' WHERE num = 1001 위와 같이 TRANSACTION 을 열고 UPDATE를 해주게 되면 TBLINSATE..
서비스를 제공하는 웹사이트에서 페이징 알고리즘을 쓰지 않는 곳은 없을 것이다. 페이징 알고리즘을 어떤 방법을 적용하느냐에 따라서 엄청난 성능 차이가 있을 수 있다. 예를 들어 아래와 같은 데이터가 있다고 가정해보자. SELECT COUNT(*) FROM dbo.TBLINSA_SH_FINAL WITH(NOLOCK) SELECT * FROM dbo.TBLINSA_SH_FINAL WITH(NOLOCK) 테이블 스키마와 인덱스 구성은 아래와 같다. 페이징 처리에서 정렬 자체를 salary와 ibsaDate로 할 것이기 때문에 아래와 같이 non-clustered index를 구성하였다. clustered index는 insaseq로 고유번호를 지정해두었다. declare @page_num int-- 페이지 번호..
HASH JOIN 이란? 1. 기본 메커니즘 NL 조인은 인덱스를 이용한 조인 방식이므로 인덱스 구성에 따른 성능 차이가 심하다. 또한 인덱스를 아무리 완벽히 구성해도 랜덤 I/O 때문에 대량 데이터 처리에 불리하고 버퍼캐시 히트율에 따라 들쭉날쭉한 성능을 보인다. 소트 머지 조인과 해시 조인은 조인 과정에서 인덱스를 이용하지 않기 때문에 대량 데이터를 조인할 때 NL 조인보다 훨씬 빠르고, 일정한 성능을 보인다. 소트 머지 조인은 항상 양쪽 테이블을 정렬해야 하는데, 해시 조인은 그런 부담도 없다. 일반적으로 HASH JOIN은 아래의 알고리즘을 적용하여 조인을 수행한다. 작은 집합으로 해시 테이블을 생성하고 큰 집합을 읽으면서 해시 테이블을 탐색한다. 해시 테이블을 만드는데 사용되는 집합을 Bulid..
기본 매커니즘 병합(Merge) 조인은 소트 머지(Sort Merge) 조인으로 불리기도 한다. 소트 머지 조인은 조인에 참여하는 두 집합을 조인 키(컬럼) 순서대로 정렬(sort) 한 다음에 병합(merge) 한다. 인덱스에 의해 이미 조인 키 순서대로 정렬된 집합은 정렬 없이 곧바로 조인에 참여하게 된다. 일대다(One-to-Many) 관계를 맺고 있는 두 테이블 간에 병합 조인이 처리되는 과정을 pseudo 코드로 나타내면 아래와 같다. /*일대다 병합 조인 시*/ 양쪽 집합을 조인 키 순서대로 정렬한다. (* 인덱스를 통해 정렬된 집합을 곧바로 얻을 수 있는 쪽은 이 과정이 필요없다.) outer 집합에서 첫 번째 로우 o를 가져온다. inner 집합에서 첫 번째 로우 i를 가져온다. while(..
기본 매커니즘 중첩루프(Nested Loops) 조인은 가장 기본적인 조인 방식이며, 단어가 의미하는 것처럼 반복적으로 안쪽 테이블을 탐색한다. 여기서 바깥쪽에 있는 테이블을 outer table 이라고 부르고 안쪽에 있는 테이블을 inner table 이라고 부른다. 즉, outer table 을 기준으로 inner table 을 반복적으로 탐색하는 방식이라고 생각하면 된다 NL JOIN의 세부처리 1. 결합 대상 테이블 (TABLE A)에서 레코드를 하나씩 반복해가며 스캔. 이 테이블을 구동 테이블(driving table) 또는 외부 테이블(outer table)이라고 부른다. 다른 테이블(TABLE B)는 내부 테이블(inner table)이라고 부른다. 2. 구동 테이블의 레코드 하나마다 내부 ..
인덱스 탐색 과정에 비효율이 있다면 이를 제거하기 위하여 노력해야 한다. 옵티마이저는 인덱스를 수직으로 탐색하고 나서 리프 페이지를 수평으로 읽어 나갈 때 시퀀셜 엑세스 방식을 사용한다. 이 엑세스 방식이 랜덤 엑세스보다 상대적으로 빠르기는 하지만 불필요하게 넓은 범위를 엑세스하면 인덱스 탐색 효율에 문제가 생긴다. 인덱스 탐색 효율을 개선하고자 할 때, 기존 인덱스에 칼럼을 추가하거나 클러스터형 인덱스로 변경하는 등의 방법은 별로 도움되지 않는다. 인덱스 탐색 효율에 큰 영향을 미치는 것은 '인덱스 키 칼럼 순서'와 '검색 조건에 사용된 연산자'이다. 1. 모든 칼럼에 equal 조건을 사용했을 때 아래와 같은 테이블이 존재한다고 가정해보자. ALTER TABLE dbo.TBLINSA_SH_TEST A..
북마크 룩업에 의한 성능 문제를 해결하는 방법 중 하나가 해당되는 컬럼 자체를 클러스터형 인덱스로 만들어주는 것이다. 모든 인덱스를 클러스터형 인덱스로 만들면 좋겠지만, 클러스터형 인덱스는 테이블당 한개만 만들 수 있다. 그렇다면, 이미 클러스터형 인덱스가 존재하는 상태에서 넓은 범위를 액세스하는 또 다른 검색 조건이 성능 문제를 일으킨다면 어떻게 해결해야 할까? 해당 검색조건이 자주 사용되고 빠른 성능을 요구한다면, 북마크 룩업이 아예 발생하지 않도록 필요한 컬럼을 인덱스에 모두 포함하는 방법을 고려할 수 있다. 이 처럼 쿼리 수행에 필요한 컬럼이 모두 포함된 인덱스를 '커버된 인덱스'(Covered index) 라고 부른다. 커버드인덱스를 실습하기 위해서 기존에 테이블의 인덱스를 모두 지워주고 새로운..
인덱스를 효율적으로 액세스하더라도 테이블 랜덤 액세스에 의한 부하가 심각하다면, 해당 인덱스를 클러스터형 인덱스(Clustered Index)로 변경하는 방법을 고려할 수 있다. 클러스터형 인덱스는 인덱스 키 컬럼과 나머지 컬럼을 리프페이지에 같이 저장하기 때문에 테이블 랜덤 액세스를 획기적으로 줄일 수 있다. 단, 클러스터형 인덱스는 넌클러스터형 인덱스의 효율성에 큰 영향을 미치므로 테이블 전체적인 관점에서 사용 여부를 결정해야한다. 좀더 자세한 실습을 위해 기존에 만들어 두었던 인덱스를 제거해보자. 또한 새로운 인덱스는 ibsaDate 컬럼(입사한 날짜)에 걸어주어 WHERE 절에 해당 조건을 필터링 하여 쿼리해보자. DROP INDEX IDX__TBLINSATEST__BUSEO__JIKWI ON d..
북마크 룩업(Bookmark Lookup) 이란, 인덱스 엔트리에 저장된 rid 값으로 테이블 로우를 액세스하는 오퍼레이션이다. 만약 클러스터형 인덱스가 있다면 RID LOOKUP 값 대신 클러스터형 인덱스의 키 값으로 테이블 로우를 찾아가는데(KEY LOOKUP), 이것도 넓게 보면 북마크 룩업이라고 할 수 있다. 북마크 룩업은 랜덤 액세스 방식으로 수행되며 SQL 성능에 큰 영향을 미친다. 필요한 페이지가 메모리 풀의 '버퍼 캐시'에 모두 있더라도, 랜덤 액세스 횟수가 증가하면 SQL 성능은 떨어질 수밖에 없다. 필요한 페이지가 버퍼 캐시에 하나도 없는 경우가 최악인데, 이때는 랜덤 액세스 횟수만큼 물리적 디스크 I/O 가 발생할 수 있다. 아래의 예시를 보면서 이해해보자.(TBLINSATEST 테이..
B-TREE 구조 B-TREE 즉, 균형트리 (BALANCED TREE 줄여서 B-TREE)는 '자료구조'에 나오는 범용적으로 사용되는 데이터의 구조다. 이 구조는 주로 인덱스를 표현할 때와 그 외에서도 많이 사용된다. 이름에서 알 수 있듯이 B-TREE는 균형이 잡힌 트리다. NODE / PAGE 노드(NODE)는 트리 구조에서 데이터가 존재하는 공간이다. 즉, 갈라지는 부분의 '마디'를 뜻한다. 1) 루트노드(ROOT NODE) 가장 상위 노드로서 모든 출발은 해당 루트 노드에서 시작된다. 2) 브랜치 노드(BRANCH NODE) 중간노드라는 의미로 루트노드와 리프노드 사이에 징검다리 역할을 수행한다. 3) 리프노드(LEAP NODE) 제일 잎 노드 말단 노드라는 의미로 마지막에 있는 노드를 말한다...
