かけがえのない愛

 1.     데이터베이스 파일과 로그 파일은 별도의 드라이브로 분리한다.

è  IO를 분산하여 병목현상을 감소시킨다.

2.     Ad Hoc 쿼리 (동적 쿼리) : 정적 쿼리로 대체

è  코드의 실행 시점에 SQL 문이 동적으로 구성되고 실행되는 쿼리

è  문제점 : 실행 시마다 컴파일을 반복하게 됨으로 Cache 재사용을 저해함으로써 CPU, Memory 등 여러가지 문제 발생

è  SP의 이점

n  실행 계획 Caching 을 통한 성능 이득

n  Network Traffic 최소화

n  출력 Parameter, Return 값 사용

n  Ownership Chain 을 통한 권한 처리, SQL Injection 차단 등의 보안 기능

n  업무 논리의 캡슐화, 모듈화

n  SQLXML 3.0 이후 릴리스에서 XML WebService 노출 기능

è  SP 안에 동적 쿼리 형태로 작성 하지 않는다.

3.     인덱스 생성 Guide

è  where절에서 자주 사용되는 컬럼

è  between A and B (클러스터인덱스가 유리) : 범위 쿼리 문에서는 클러스터 인덱스가

유리 하다.

è  order by가 항상 사용되는 컬럼

è  join으로 자주 사용되는 컬럼

è  100만건 중에 10개 조회 OR 1000개 조회와 같이 찾는 것이 적은 컬럼 : 중복 데이터가 많은 (조회되는 것이 많은) 컬럼은 인덱스에 좋은 영향은 아님.

è  인덱스로 인해 얻는 손해 : H/W 용량 증가, DML(Data Manipulation Language : INSERT, UPDATE, DELETE) Performance 저하 (특히 INSERT)

4.     쿼리 Guide

è  WHERE 절에 가공 컬럼 비교 금지 : WHERE REPLACE(USER_ID, ‘ ’, ‘’) = ‘11111’ 와 같이 컬럼을 가공하게 되면 인덱스를 사용하지 못한다. - INSERT 시 USER_ID에는 ‘ ‘ 이 INSERT 되지 않도록 하고, 조건절은 WHERE USER_ID = ‘11111’

è  WHERE 절에서 LIKE 사용 시 ‘%’ 로 시작하는 비교 금지 : NAME LIKE '%홍길동%' 와 같이 LIKE 시 ‘%’로 시작하는 비교는 TABLE SCAN 한다. - 해당 TABLE의 데이터 건수가 소량이고 일정량을 유지한다면 시스템 Capacity내에서 사용 가능하나 일정량의 데이터가 주기적으로 증가하고 해당 쿼리가 빈번히 사용되는 쿼리라면 '%홍길동%' 기능을 '홍길동%' 으로 바꾸던가 시스템 Capacity 를 고려하여 데이터를 삭제하여야 한다.

è  SELECT 절에 사용자 정의 함수 사용 : 무조건 사용하지 않는다. – join 으로 변경

è  SELECT 절에 하위쿼리 사용 : 무조건 사용하지 않는다. – join 으로 변경

è  잠금 문제 : SELECT 쿼리에 with (nolock) 혹은 with (readuncommitted) Hint 사용 – Commit 되지 않은 데이터를 읽어 나중에 없어지거나 변경될 수 있으나 회계와 같은 중요한 데이터가 아니라면 반드시 사용한다.

SQL Server 의 기본 격리수준은 read committed 이다. 그래서 읽을 때는 공유 잠금이 유지되어, 테이블을 SELECT 할 경우 INSERT, UPDATE등은 BLOCK 된다. 이럴 경우 SELECT 시 DB의 성능이 떨어지게 된다.

각 구문에 적용을 해야 하는 불편함을 해결하는 방법은 SP 상단에 아래 문구를 삽입한다.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

è  Ad Hoc 쿼리 : 정적 쿼리로 변경 (SP)

è  SELECT * 을 사용하는 것은 피한다. : 사용하지 않는 데이터를 호출하는 것만으로도 이미 많은 부하가 생긴다. 특히 text 타입의 데이터를 호출하는 경우는 그 정도가 심해진다. - 필요한 컬럼만 SELECT 한다.

è  COUNT(*)을 사용하라. : COUNT(컬럼)으로 호출하는 경우가 있다. 이 경우 해당 컬럼의 NULL값을 제외한 COUNT를 가져오게 된다. NULL값을 일일이 체크하면 호출 속도가 저하되게 된다. NULL을 체크해야 하는 경우가 아닌 대부분의 경우 COUNT(*)을 사용한다. COUNT(*)는 NULL값의 경우도 모두 count에 추가하지만 그로 인해 성능의 저하가 많이 줄어든다.

è  1 row 만 필요하다면 TOP 1을 사용한다.

è  커서 및 임시테이블의 내용을 최대한 자제하라. : 커서보다는 임시 테이블이, 임시테이블 보다는 테이블 변수를 사용하는 것이 성능에 좋다.

è  뷰의 사용을 줄여라. : 직접 쿼리가 단계를 줄이므로 가급적 뷰를 사용하지 않는다.

è  JOIN을 사용하는 경우 INNER JOIN을 되도록 사용하라.
. 동일한 효과를 가지는 쿼리를 작성할 경우 INNER JOIN이 아닌 LEFT OUTER JOIN을 쓰는 경우가 있다. (습관적으로?) 확연히 속도가 차이가 나므로 INNER JOIN을 사용하는 것이 좋다.

è  반드시 인덱스를 사용하도록 한다. : 실행 계획을 통해 인덱스의 사용 유무와 올바른 인덱스를 사용하는지 확인한다.

è  SP에서 데이터 형식 우선 순위에 따른 형식 변환이 일어나지 않도록 컬럼 타입과 동일한 타입의 파라미터를 사용 한다.

@searchColumnA NVARCHAR(12)

SELECT * FROM A WITH (READUNCOMMITTED)

WHERE columnA = @searchColumnA

-       columnA 의 타입이 VARCHAR 이면 NVARCHAR 의 우선순위가 높아 아래와 같이 CONVERT 가 발생하여 Performance 를 저하 시킨다.

SELECT * FROM A WITH (READUNCOMMITTED) WHERE CONVERT(NVARCHAR(12), columnA)

è  set nocount on 을 먼저 실행하라 : 불필요한 메시지를 표시하지 않는다.

5.     예상 실행 계획을 자주 확인하라.

실행계획의 내용은 꼼꼼히 따져봐야 한다. 튜닝의 시작은 성능 분석이다.

(그래픽 실행 계획 아이콘)

6.     Index를 타는지 항상 체크하라.

Index를 활용하지 않은 검색은 데이터가 많으면 많을수록 성능은 급격히 떨어진다.

7.     Clustered Index Seek를 항상 체크하라.

Clustered Index Scan을 타는 것 만으로도 속도는 향상이 되지만 완전하진 않다.
Clustered Index column의 일정 구간을 타는 Seek여야 한다.

일반 Non Clustered Index의 경우는 Clustered를 찾기 위해 해당 column의 Clustered Index 정보를 호출해야 하는 부담이 생긴다.

Clustered Index와 Non Clustered Index의 Index 구조의 차이 : (클러스터형 인덱스 구조) , (비클러스터형 인덱스 구조)

8.     기타…..

è  Index 설정시 DESC 정렬을 해야 빠른가 ?

그렇지 않다.
오름차순이건 내림차순이건 Index가 걸려있으면 조건에 따라 Clustered Index를 찾아 가게 된다.

è  Clustered Index Seek를 타면 무조건 빠른가 ?

그렇지 않다.

è  Non Clustered Index column은 무조건 마지막엔 Clustered Index Column을 조회하는가 ?

그렇지 않다.

è  Clustered Index 는 Table 당 하나만 존재 한다.

è  Table로부터 하나 혹은 소수의 Row들을 리턴하는 SQL Query인 경우에 Nonclustered Index가 적합하고, 많은 수의 Row들을 리턴하는 것이 필요한 Query들의 경우에는 Clustered Index가 더 적합하다.

WITH DEPTINFO(DeptCode, UpperDeptCode) AS
(         
	SELECT DeptCode, UpperDeptCode
    FROM dbo.tb_DeptList
    WHERE DeptCode ='A001'
    UNION ALL
    SELECT A.DeptCode, A.UpperDeptCode
    FROM DEPTINFO AS A
    JOIN dbo.tb_DeptList AS B
    ON A.DeptCode = B.UpperDeptCode  
)
SELECT DeptCode, UpperDeptCode
FROM DEPTINFO

Windows 방화벽은 기본적으로 Windows Server 2008 R2 설치시 대부분의 포트 및 응용 프로그램에 대한 접근을 보안상의 목적으로 막아져 있다.
그러다보니, SQL Server 2008의 원격 접속이 근본적으로 차단되어져 있는 것이다.

이에 Windows Server 2008 R2에 SQL Server 2008의 원격 접속을 허용하는 과정을 아래 단계를 거쳐서 진행하면 된다.

1. Windows Server 2008 R2에서 [시작]-[모든 프로그램]-[관리 도구]-[서버 관리자]를 실행한다.
아래 그림과 같이 구성 항목의 하위 항목인 [고급 보안이 포함된 Windows 방화벽] 항목을 선택하고,
[인바인드 규칙]에 마우스 오른쪽 버튼을 클릭하여 [새 규칙] 메뉴를 클릭한다.
인바인드 규칙은 원격에서 접속하는 포트 및 프로그램에 대한 설정을 하나 추가할 수 있는 마법사를 실행시켜준다.

4. 작업항목에서 [연결 허용] 항목을 선택하여, 위에서 지정한 포트를 열어둘 수 있다.

5. 프로필 설정에서는 3가지 옵션이 있는데, 원격 인터넷을 통한 접속을 허용하고자한다면, [공용] 항목을 포함해서 선택하면된다.    

6. 이름란에는 기억하기 편하고, 추후 Windows Firewall에서 손쉽게 규칙을 적용 또는 해제하기 위한 이름과 설명을 입력한다.

7. 인바운드 규칙을 모두 적용하고 나면 아래 화면처럼,
서버관리자의 인바운드 규칙이 새롭게 적용된 [SQL Server 기본 포트]라는 항목이 추가되어져 있는것을 알 수 있다.  
     

이렇게 함으로써,
원격서버에서 Windows Server 2008 R2 기반에 SQL Server 2008에 접속할 수 있는 최소한의 처리 단계를 구축할 수 있다.

부장 홍길동을 필두로 하여 트리 구조로 만들어져 있는것을 볼 수 있다.

위 트리 구조를 데이터로 간단하게 표현하면 아래와 같다.

쿼리문
WITH EMP (사원번호, 직함, 성명, 소속사번) AS
(
     SELECT 사원번호, 직함, 성명, 소속사번
     FROM    사원
     WHERE  사원번호 = ''002'' -- 김길동
     UNION ALL
     SELECT  T1.사원번호, T1.직함, T1.성명, T1.소속사번
     FROM    사원 T1 INNER JOIN EMP T2 ON T2.사원번호 = T1.소속사번
)
SELECT *
FROM EMP;

실행 결과
002              차장       김길동      001
003              과장       고길동      002
004              대리       서길동      003
005              대리       안길동      003
006              과장       이길동      002
007              대리       안길동      006
008              사원       최길동      006