DB - Sybase Indexes

1. 인덱스가 성능에 미치는 영향 (How indexes affect performance)
. 높은 성능을 위해서는 테이블에 대한 인덱스 설계가 잘 되어야한다
  무분별한 인덱스 추가는 삽입,수정,삭제 작업시 오히려 성능에 악영향을 준다
. Adaptive Server query optimizer는 쿼리마다 분석을 통해 비용이 가장 적은 쿼리 실행계획을 선택한다
. 성능을 높이기 위한 방법
 - 데이터 엑세스할 때 테이블 스캔을 피하기
 - 포인트 쿼리에서 데이터 접근할때 특정값 조건을 넣어서 사용하기
 - 범위검색 쿼리에서 상한, 하한을 지정해서 사용하기
 - 인덱스 커버를 사용해서 데이터 페이지 접근을 피하기
 - 정렬된 데이터를 이용해 정렬작업을 피하거나 비용이 많이드는 작업인 조인등을 피하기
 - 인덱스를 이용해 데이터 고유성을 강화하고 삽입시 저장위치를 랜덤하게 할 수 있음
 - sp_chgattribute 'concurrency_opt_threshold' parameter 이용해 동시성을 좋게할 수 있음
   (sp_chgattribute table_name, "concurrency_opt_threshold", min_page_count)
. select가 오래 걸리거나 조인 시 느리거나 select는 잘 되지만 변경은 잘 안된다면 인덱스가 문제있는 것이다



2. 잘못된 인덱스 증상 (Symptoms of poor indexing)
. 인덱스를 사용하여 성능을 향상시키는 주된 목표는 디스크에서 테이블의 모든 페이지를 읽는 테이블 스캔이나
  디스크에서 데이터 페이지 만 읽는 부분 테이블 스캔을 피하는 것이다
. 600개의 데이터 페이지가 있는 테이블에서 고유 값을 검색하는 쿼리에는 600 개의 물리적 및 논리적 읽기가 필요하다
  인덱스가 데이터 값을 가리키면 동일한 쿼리가 2~3번의 읽기, 200에서 300배의 성능 향상을 만족할 수 있다


** 인덱스 사용되지 않는 경우 (Lack of indexes is causing table scans)
. select 값이나 조인이 너무 길다면 인덱스가 존재하더라도 사용하지 않을 수 있다
. showplan으로 테이블 엑세스 정보 등을 볼 수 있고 테이블 스캔을 한다면 dbcc traceon(302) 사용해서
  해당 이유를 확인할 수 있다, 해당 내용이 잘 보이지 않는다면 쿼리 작성에 문제가 있을 수도 있다
  dbcc traceon(310)를 사용해 더 주의깊게 살펴볼 수 있다


** 인덱스가 충분히 선택적이지 않는 경우 (Index is not selective enough)
. 옵티마이저는 특정행을 구별하기 좋을 경우 인덱스를 사용한다
  여권번호와 같이 특정행을 구별하기 좋으면 사용하지만 sex(M, F) 같은 경우 고유하지 않으므로 사용하지 않을 수 있다


** 범위 쿼리를 지원하지 않는 인덱스 (Index does not support range queries)
. 일반적으로 clustered indexes 및 covering indexes는 range queries 및 많은 행과 일치하는 검색에 우수한 성능을 제공함
  noncovering indexes의 키를 참조하는 range queries는 제한된 수의 행을 반환하는 범위의 인덱스를 사용한다
  그러나 쿼리가 반환하는 행 수가 증가하면 nonclustered index 또는 data-only-locked의 clustered index는 테이블 스캔보다 많은 비용이 든다


** 데이터 변경을 느리게 하는 많은 인덱스 (Too many indexes slow data modification)
. 너무 많은 인덱스는 검색을 빠르게 하지만 데이터 변경 작업 시에 성능을 안좋게한다
. 모든 삽입/삭제 작업은 data-only-locked의 clustered index와 nonclustered index에 영항을 준다
. allpages-locked의 clustered index가 변경되면 nonclustered index도 모두 변경되어야한다


** 인덱스 크기 (Index entries are too large)
. 인덱스 크기를 최대한 작게 만들어야 한다, 인덱스의 총 키 길이를 페이지 크기의 3 분의 1까지 만들 수 있다
  그러나 긴 인덱스는 페이지에 한번에 저장하기 어려우며 인덱스 레벨을 높게된다
  이렇게하면 인덱스 루트에서 리프 페이지로 이동할 페이지 수가 증가하고 쿼리 중에 필요한 디스크 I/O가 증가합다
. 아래 예는 인덱스 사이즈에 다른 레벨을 보여준다

. sp_estspace를 사용해 확인하면 40bytes는 4레벨이 필요하다


** 큰 데이터 및 인덱스에 대한 예외적인 경우 ( Exception for wide data rows and wide index rows)
. wide row로 이뤄진 인덱스는 아래 경우에 유용합니다
 - 테이블의 행 수가 매우 넓어서 데이터 페이지 당 행 수가 매우 적을 때
 - 쿼리 실행이 인덱스 커버링이 사용되어 질 때
 - 쿼리가 충분히 많은 행을 리턴할 때

예 )테이블의 행수가 매우 길고 페이지 당 하나의 행만있는 경우 100개의 행을 반환하는 쿼리는
     100개의 데이터 페이지에 액세스한다
     긴 인덱스 행이 있더라도이 이런 쿼리의 인덱스는 성능을 향상시킬 수 있다

예)인덱스 행이 240바이트 인 경우 인덱스는 페이지 당 8개의 행을 저장하며 쿼리는 12개의 인덱스 페이지에만 액세스한다



3. Index limits and requirements
. 클러스터 인덱스는 키 순으로 데이터가 정렬되므로 테이블당 1개를 만들 수 있다
. 기본적으로 range-, list-, hash-partitioned tables에서는 local index로 생성된다
. nonclustered indexes는 테이블 당 최대 249개 만들 수 있다
. primary key create생성 시 기본적으로 clustered인덱스를 만들고 unique 조건은 nonclustered인덱스를 만듬
. key는 31개 columns까지 생성 가능하고 사이즈는 페이지에 따라 다르다

  Page Size - Max key length
  2048  - 600
  4096  - 1250
  8192  - 2600
  16384 - 5300



4. 인덱스 선택 시 고려할 점 (Choosing indexes)

* 인덱스 선택을 할 때 할 수 있는 질문들
 - 주어진 테이블과 현재 어떤 인덱스가 연관되어 있습니까?
 - 테이블을 사용하는 가장 중요한 프로세스는 무엇입니까?
 - 테이블에서 수행되는 데이터 수정에 대한 작업의 비율은 얼마입니까?
 - 테이블에 클러스터형 인덱스가 생성 되었습니까?
 - 클러스터형 인덱스를 비클러스터형 인덱스로 대체 할 수 있습니까?
 - 인덱스 중에 하나 이상의 중요한 쿼리를 커버할 수 있습니까?
 - 복합 primary key의 고유성을 사용하려면 composite index가 필요합니까?
 - 기존 쿼리에 함수 기반 인덱스를 사용하여 성능을 향상 시킬 수 있습니까?
 - unique 인덱스로 정의 할 수 있는 인덱스는 무엇입니까?
 - 주요 분류 요건은 무엇입니까?
 - 일부 쿼리는 결과 집합의 내림차순 정렬을 사용합니까?
 - 인덱스가 조인 및 참조 무결성 검사를 지원합니까?
 - 인덱싱이 업데이트 유형(직접 또는 지연)에 영향을 줍니까?
 - 커서 위치 지정에 필요한 인덱스는 무엇입니까?
 - dirty reads가 필요할 때 스캔을 지원할 unique 인덱스가 있습니까?
 - unique 인덱스 생성을 위해 IDENTITY columns을 테이블에 추가해야 합니까?

* 인덱스를 얼마나 만들지 고려할 사항
 - 공간 제약
 - 테이블에 대한 엑세스 경로
 - 데이터 modifications 대 select 작업 비율
 - OLTP 대 보고서의 성능 요구 사항
 - 인덱스 변경의 성능 영향도
 - update statistics 얼마나 자주 할 수 있는지


** 인덱스와 논리적인 키 관계 (Index keys and logical keys)
. index key와 logical key는 구별해야 합니다, logical key는 데이터 베이스 설계의 일부분으로서
  primary keys, foreign keys, common keys를 정의 합니다
. index key는 성능상의 이유로 선택합니다
. 흔한 오류는 범위 쿼리나 결과 집합에 상관없이 PK에 클러스터형 인덱스를 만드는 것입니다


** 클러스터 인덱스 지침 (Guidelines for clustered indexes)
. 대부분의 allpages-locked tables은 clustered indexes를 사용하거나
  파티션을 사용해서 heap table의 마지막 페이지에서의 경합을 줄입니다
. 많은 삽입이 필요한 경우 IDENTITY column과 같이 지속적으로 증가하는 clustered index대신
  잠금 경합을 줄이기 위해 임의의 페이지에 삽입을 하는 키를 선택해야 합니다
  primary key는 이 조건에 맞지 않습니다
  data-only-locked tables 보다 allpages-locked tables에서 보통 경합이 더 심합니다
. 클러스터 인덱스는 범위 검색의 인수에 매칭이 될때 우수한 성능을 보입니다
  예) where colvalue >= 5 and colvalue < 10
  allpages-locked tables에서 행은 순서를 유지하고 클러스터된 인덱스 사용시 빠릅니다
  data-only-locked tables에서는 인덱스 생성 시 순서를 유지하지만, 시간이 지나면 클러스터링이 감속합니다
. allpages-locked tables에서 자주 업데이트되는 columns은 clustered indexes에 포함하지 않습니다


** 클러스터 인덱스 선택 시 고려할 점 (Choosing clustered indexes)
. 클러스터 인덱스를 선택하는 경우는 아래와 같습니다
 - primary key, where절에 사용되고 임의의 insert 작업을 하는 경우
 - 아래와 같이 범위별 엑세스가 발생될 때
   col1 between 100 and 200
   col12 > 62 and < 70
 - order by에 사용되는 Columns
 - 자주 변경되지 않는 Columns
 - 조인에 사용되는 Columns

. 여러 선택이 있다면 첫번재로 물리적 순서로 처음 나오는 항목을 선택합니다
  두번째로 범위 쿼리를 찾습니다
  경합으로 인한 “hot spots”이 발생되는지 성능 테스트를 합니다


** 비클러스터형 인덱스 후보 선정 시 고려할 점 (Candidates for nonclustered indexes)
. 클러스터 인덱스 선택이 되지 않은 모든 가능성을 고려합니다
. index covering으로 성능향상이 가능한지 고려합니다
. data-only-locked tables에서 clustered indexes는 leaf레벨이 data레벨 위에 있으므로
  index covering을 사용할 수 있습니다
. allpages-locked tables에서 범위를 벗어난 쿼리는 clustered indexes에서 동작될 수 있지만
  nonclustered indexes에서는 범위 크기에 따라 지원되지 않을 수 있습니다
. 비번한 조회와 중요한 쿼리를 covering하기 위해 복합 인덱스를 고려합니다
. 가장 중요한 쿼리는 포인트 쿼리와 일치되는 검색을 수행할 수 있어야 합니다
. 일치되지 않는 검색도 테이블 스캔을 피할 수 있어야 합니다


** 함수형 인덱스 선택 시 고려할 점 (Choosing function-based indexes)
. 함수기반 인덱스는 특정 legacy 프로그램의 성능을 향상 시키는 옵션을 제공합니다
. 하나 이상의 표현식을 직접 인덱스로 만들 수 있습니다
  각 표현식의 결과값으로 인덱스를 생성하게 됩니다
. 함수나 조작한 결과 값을 컬럼에 저장하고 다른 컬럼 또는 상수와 비교해야할 때
  함수기반 인덱스를 효과적으로 사용할 수 있습니다
. 함수 기반 인덱스의 장점은 기존 쿼리에 사용 된 식과 일치하는 인덱스를 기존 테이블에 간단히 추가 할 수 있다는 것입니다


** 인덱스 선택 (Index selection)
. 자주 사용하는 index와 사용하지 않은 index를 구분합니다
. 모니터링이 가동중이라면 아래와 같은 정보를 확인할 수 있습니다
 * monOpenObjectActivity
 - IndexID : index 식별 id
 - OptSelectCount : 옵티마이저가 사용하기 위해 엑세스한 오브젝트 수
 - LastOptSelectDate : OptSelectCount증가된 마지막 시간
 - UsedCount : 쿼리가 실행될 때 엑세스한 오브젝트 수
 - LastUsedDate : UsedCount증가된 마지막 시간

. OptSelectCount는 이미 컴파일되어 캐시된 경우 실행해도 증가하지 않고
  UsedCount는 실행되면 증가합니다
  exec가 설정되어 있지 않으면 OptSelectCount는 증가하지만 UsedCount는 증가하지 않습니다
. 서버가 재실행되면 모니터링한 값은 없어집니다
// 특정 오브젝트에 대한 모든 인덱스가 옵티마이저가 마지막으로 선택된 시간과 // 실행 중에 실제로 사용 된 시간에 대한 모니터링 테이블을 조회하고 계수를 보고합니다 select DBID, ObjectID, IndexID,

OptSelectCount, LastOptSelectDate, UsedCount, LastUsedDate from monOpenObjectActivity where DBID = db_id("financials_db") and ObjectID = object_id('financials_db..expenses') // 응용 프로그램에서 사용되었

거나 현재 사용되지 않은 모든 인덱스를 표시합니다 select DBID, ObjectID, IndexID, ObjectName = object_name(ObjectID, DBID), LastOptSelectDate, UsedCount, LastUsedDate from monOpenObjectActivity where DBID

= db_id("MY_1253_RS_RSSD") and ObjectID = object_id('MY_1253_RS_RSSD..rs_columns') // 현재 데이터베이스에서 현재 사용되지 않는 모든 인덱스를 표시합니다 select DB = convert(char(20), db_name()), TableName

= convert(char(20), object_name(i.id, db_id())), IndexName = convert(char(20),i.name), IndID = i.indid from master..monOpenObjectActivity a, sysindexes i where a.ObjectID =* i.id and a.IndexID =* i.indid

and (a.UsedCount = 0 or a.UsedCount is NULL) and i.indid > 0 and object_name(i.id, db_id()) not like "sys%" order by 2, 4 asc



** 인덱스 선택 시 추가로 고려할 점 (Other indexing guidelines)
. 인덱스 키를 unique하게 정의하면 옵티마이저는 검색 또는 조인 시에 일치하는 값이 하나만 있음을 알 수 있음
. 데이터베이스 설계 시 참조 무결성을 정의한다면 참조한 columns에 unique index가 있어야 함
. 삽입 작업이 많은 테이블에 인덱스를 만드는 경우 fillfactor를 사용하여 페이지 분할을 임시로 최소화하고
  동시성을 향상 시키며 교착 상태를 최소화한다
. read-only table에 인덱스를 만드는 경우 fillfactor를 100으로 사용하여 테이블 또는 인덱스를 가능한 작게 한다
. key의 크기는 가능한 작게 해야 인덱스 트리가 더 평평해져서 트리 탐색을 더 빠르게 한다
. 적합한 작은 데이터 유형을 사용한다
  숫자형은 문자열보다 약간 빠르다
  가변길이는 고정 길이보다 많은 overhead있으므로 길이 차이가 많지 않다면 고정 길이를 사용한다
  null값 허용하는 문자열 및 이진 형식은 가변 길이 이다
  index key로 사용하는 columns은 가능하면 null 값이 아닌 값으로 한다
. 다른 테이블과 조인 시 데이터 유형이 호환 가능해야 한다
  호환되지 않는다면 인덱스를 사용하지 않을 수 있음


** 비클러스터형 인덱스 선택 시 고려할 점 (Choosing nonclustered indexes)
. 비클러스터형 인덱스 추가할 경우 아래 항목을 고려해야한다
 - 데이터 수정 시간 증가에 비해 검색 시간이 얼마나 단축될지
 - 인덱스가 얼마나 많은 공간을 사용하는지
 - 후보 columns은 얼마나 자주 변경이 있을지
 - index key는 얼마나 선택률이 좋을지
 - index key에 중복값이 얼마나 있을지
. 데이터 수정에 대한 성능 테스트 후에 사용여부를 판단해야 한다

* 데이터 수정을 위한 비용 (Performance price for data modification)
. all locking에서는 nonclustered index는 key값이 삽입/삭제 될 때마다 업데이트가 필요하다
. index keys의 일부만 변경하는 테이블은 해당 인덱스만 업데이트하면 됨
. allpages-locked tables에서 exclusive locks일 경우 트랜젝션 동안 인덱스가 잠기게되어
  경합과 오버헤드가 증가하게 됩니다
. 많은 데이터를 가지는 테이블에서 인덱스 3~4개 정도로 성능에 큰 영향을 미치지는 않음


** 복합인덱스 선택 시 고려할 점 (Choosing composite indexes)
. 인덱스 후보가 하나 이상의 후보로 분석되어진다면 복합 인덱스를 고려해 볼 수 있음
 - 범위 쿼리일 때
 - 그룹화된 columns아니 그룹화된 값이 포함된 Vector(그룹화된) 집계일 경우
 - 높은 수의 중복을 반환하는 쿼리일 때
 - 정렬이 필요한 쿼리일 때
 - 테이블 스캔 쿼리이지만 작은 columns만을 사용하는 쿼리일 때
. 읽기 전용 테이블일 경우 많은 인덱스 사용에 부담이 없다


** 복합인덱스에서 Key순서에 따른 성능 (Key order and performance in composite indexes)
. 인덱스 커버링을 사용할 수 있다면 성능이 매우 좋아진다
// 아래 쿼리에서 au_lname, au_fname, au_id 순으로 인덱스가 생성되 있을 때 select au_id from authors where au_fname = "Eliot" and au_lname = "Wilk" // 아래 경우에는 인덱스 커버링은 되지만 au_id만 사용하므

로 효율적이지 않음 select au_fname, au_lname from authors where au_id = "A1714224678" // 아래 처럼 select 절에 다른 columns이 추가되면 성능이 좋아지지 않음 select au_fname, au_lname, phone from authors

where au_id = "A1714224678"
. 아래 표는 where조건에 따른 성능을 보여준다




** 복합인덱스 장/단점 (Advantages and disadvantages of composite indexes)
. 복합인덱스 장점
 - 인덱스 커버링을 사용할 수 있음
 - 쿼리가 각 검색하는 값을 제공하면 단일 인덱스보다 적은 I/O가 발생됨
 - 여러 속성의 고유값을 강화하는 방법임

. 복합인덱스 사용이 필요한 경우
 - Lookup 테이블
 - 자주 엑세스 되는 경우 columns이 있을 때
 - 벡터 집계 사용이 필요할 때
 - 많은 columns이 있을 때 자주 사용하는 서브집합이 존재할 때

. 복합인덱스 단점
 - 인덱스 페이지 당 항목이 적어지고 인덱스 페이지가 많아짐
 - 복합 인덱스 속성을 업데이트하면 인덱스가 수정됨, 인덱스는 자주 업데이트되지 않아야 함

. 복합인덱스를 피하는 경우
 - data rows와 거의 동일한 index row 일 때
 - where에서 사소한 내용만 사용될 때



5. Techniques for choosing indexes

** 범위 쿼리를 위한 인덱스 (Choosing an index for a range query)
select title from titles where price between $20.00 and $30.00
위 쿼리에서 아래와 같은 테이블의 통계정보가 있을 경우
 - 테이블은 1,000,000 rows가지고 있고, allpages locking임
 - 페이지당 10 rows를 가지고 있고 페이지의 75%는 차있어 테이블에 약 135,000 페이지가 존재
 - price가 $20에서 $30사이인 데이터는 약 19%정도가 존재함

. 인덱스가 없으면 전체 135,000 pages를 모두 스캔함
. price에 클러스터 인덱스가 있다면 첫 20달러를 찾은 후 순차적으로 30달러까지 찾게 됨
  페이지가 약 75%가 찼다면 평균 rows수는 7.5이고 190,000 개의 일치하는 행을 읽으려면
  쿼리가 약 25,300 페이지와 3 또는 4 개의 인덱스 페이지를 읽게됨
. price에 비클러스터 인덱스를 사용하면 leaf수준의 약 19%를 읽어서 약 1,500페이지를 읽어야 함
. price가 임의로 분배되어 있다면 읽어야 하는 페이지가 데이터 페이지 수(190,000)만큼 있을 수 있고
  테이블 검색에는 135,000 페이지 만 있으면되므로 클러스터되지 않은 인덱스는 사용하지 않아도됨
. 선택할 수 있는 다른 경우는 price, title로 구성된 비클러스터 인덱스이다
  첫 20달러를 인덱스 스캔으로 찾은 후 30달러까지 leaf레벨에서 스캔하면서 찾아갈 수 있음
  이 경우 약 35,700개의 leaf페이지가 필요하므로 일치하는 leaf페이지를 스캔하려면이
  index 페이지의 약 19% 또는 약 6,800개의 읽기가 필요하다
. 이 쿼리의 경우 price, title으로 하는 비클러스터형 인덱스가 가장 좋습니다


** 다른 인덱스를 필요로 하는 쿼리 (Adding a point query with different indexing requirements)
. 위 예시에서 이제 title에 대한 조건이 있는 쿼리가 있는 경우
select price from titles where title = "Looking at Leeks"
. 중복된 제목이 거의 없다는 것을 알고 있으므로 이 쿼리는 하나 또는 두개의 결과를 반환한다
. 아래 표는 4가지 인덱스 전략에 대한 분석이다


. price에 대한 범위 쿼리는 4번이 가장 좋음, 1/3번은 16K I/O에서 사용 가능함
. titles에 대한 포인트 쿼리는 1/2/3번이 가장 좋음
. 이 두개의 쿼리는 2개의 인덱스를 만드는 것이 최상의 선택임
. price를 위한 것은 4번
. title을 위한 것은 2번이 최상의 선택임

* 인덱스 선택을 할 때 고려할 사항
 - 각 쿼리의 빈도 및 쿼리가 실행되는 시간은 언제인지
 - 응답 시간은 어느 정도 필요한지
 - 업데이트 응답 시간은 어느 정도 필요하면 인덱스가 하나 이상일때 느려지는지
 - 값의 범위는 일반적인지 또는 자주 사용하는 범위는 얼마인지
 - 대용량 캐시가 있는지, 자체 캐시가 있다면 속도가 매우 빠름
 - 다른 어떤 쿼리 및 검색 조건이 사용되는지
 - 다른 테이블과 자주 조인되는지

DB - sybase Query 측정

1. 모니터링
    1.1 Historical data
         1.1.1 monSysStatement -> v$sql에 대응
         1.1.2 monSysSQLText -> v$sqltext
         1.1.3 monSysPlanText -> v$sqlplan
    1.2 Query
         select S.SPID, S.CPUTIME, S.LINENUMBER, T.SQLTEXT
         from    monProcessStatement S, monProcesSQLText t
         where  s.SPID = T.SPID
         and    S.CPUTIME = (select max(cputime) from monProcessStatement)
     1.3 Locking
          1.3.1 sp_who, sp_lock, sp_familylock을 이용하여 모니터링
                  -> sp_who에 user명을 주면 해당 user것만 모니터링 안주면 전체

     1.4 sysquerymetrics(version 15 이후)
          1.4.1 most expensive statement query
                 -> select lio_avg, qtext from sysquermetrics order by lio_avg
          1.4.2 most frequently used statement query
                 -> select elap_avg, cnt, qtext from sysquerymetrics order by cnt

2. SQL PLAN 확인
    2.1 set showplan on/set showplan off
    2.2 SQL PLAN 메시지 해석
         2.2.1 worktable -> oracle의 temp 영역에 임시 데이터 쓰는 것을 의미
         2.2.2 GETSORTED -> worktable을 만들어서 sorting할 경우에만 메시지가 나타남. index를 이용하여 sorting 한 경우에는 나타나지 않음
        2.2.3 Auxiliary scan descriptors ->  referential integrity check 를 의미
        2.2.4 Positioning -> table이나 index의 leaf level에 access를 시작하는 부분
        2.2.5 Index covering -> base table은 읽지 않고 index scan만으로 끝나는 것
        2.2.6 Matching Index Scans -> or clause or in list 일때 인덱스를 각각 스캔할 경우 나타남
        2.2.7 Dynamic Index -> or 조건일때 해당 row들을 읽어서 중복된 row는 제거한 후 worktable을 만든 다음 그 worktable의 rowid를 access하는 것을 의미한다.
        2.2.8 Reformatting -> 테이블이 크면서 조인 컬럼에 인덱스가 없는 경우 해당 row의 필요컬럼을 worktable에 insert한 후에 join 컬럼에 clustered index를 생성한 다음에 join을 수행한다.
        2.2.9 Log scan -> insert, update, delete 수행에 따라 transaction log에서 data를 읽는 것
        2.2.10 I/O size -> optimizer가 선택한 I/O size
    2.3 dbcc traceon
        -> 10046과 10053 evet trace를 포함
        2.3.1 사용법 -> dbcc traceon(3604, 302) / dbcc traceoff(3604, 302)
                              dbcc traceon(3604, 302, 310) / dbcc traceoff(3604, 302, 310)
               *3604는 client에 log가 생성되게 함. 없으면 error log에 보임
   

3. Object 분석 (Table, Index 확인)
    3.1 table 정보확인 방법
         - sp_help [table] : argument가 없으면 해당 DB의 전체가 보이며 index 정보도 같이 보여줌
         - Sybase Central에서 확인 가능
         - select name from sysobjects where type = "U" 를 통해서는 사용자 table 목록을 확인 가능
    3.2 index 정보확인 방법
         - sp_helpindex [index]
 

4. 통계 정보 확인
   4.1 optdiag를 이용하여 통계정보가 최신 것으로 변경되었는지 확인
   4.2 통계정보의 보관
        4.2.1 systabstats -> table, index의 size, rows count 등이 저장, basic statistics
                - Number of data pages for a table, the number oof leav level pages for an index
                - Number of rows in the table
                - Height of the index
                - Average length of data rows and leaf rows
                - Number of forwared and deleted rows
                - Number of empty pages
        4.2.2 sysstatistics -> specific column의 value가 저장
   4.3 optdiag의 사용법
        optdiag statistics pubtune -Usa -Ppasswd -o pubtune.opt
      -> optdiag statistics <dbname> -U<username> -P<passworkd> -o <outputfile>
        optdiag statistics pubtune..titles -Usa -Ppasswd -o titles.opt
      -> pubtune DB의 titles 라는 table과 그 테이블의 인덱스에 대한 정보를 display

5. Plan 변경
   5.1 optimizer mode
        1. fastfirstrow -> first_rows(n)
        2. allrows_oltp
        3. allrows_mixed -> default mode,
        4. allrows_dss -> all_rows
      example ) select * from A order by A.a plan "(use optgoal allrows_dss)"
   5.2 set forceplan [on|off]
        join order 변경 시 사용가능 그러나 join type 변경은 사용할 수 없음. abstract plan을 이용해야 join order 및 join type 변경이 가능함.
   5.3 Abstract Plans
        -> Oracle의 stored outline과 같은 기능, sysqueryplans에 저장됨
       5.3.1 creating partial plan
             -> partial plan은 특정부분만 plan 수정할 경우
         ex) create plan
              "select t1.c11, t2.c21 from t1, t2, t3
               where t1.c11 = t2.c21 and t.c11 = t3.c31"
              "(i_scan t3_c31_ix t3)"
        5.3.2 abstract plan 사용
               set plan dump [on|off] -> plan capture
               set plan load [on|off] -> abstract plan의 사용
               set plan replace [on|off] -> 기존에 capture된 plan이 있을 경우에 사용
   5.4 Index 변경
     select pub_name, title
     from publishers p, titles t (index date_type)
     where p.pub_id = t.pub_id
     and type = "business"
     and pubdate > "1/1/93"


6. 통계 정보 관리
   6.1 통계정보 생성 및 변경
        update statistics authors(auth_id) with sampling = 5 percent
        update statistics titles (price) -> titles의 price 컬럼에 통계정보 생성
        update statistics titles (price) using 50 values -> titles의 price 컬럼에 50개의 히스토그램 생성
        update all statistics <table_name> -> 모든 column에 통계정보 생성

7. set command -> set autotrace on과 유사함
    7.1 set statistics time ->  set timing on
    7.2 set statistics io
     
8. 참고
    8.1 용어
         8.1.1 MRU와 LRU의 존재는 개발계에서 QUERY 성능 측정시 cache 영향을 최소화하기 위함

MSSQL - 중복 데이터 처리 (삭제 등)

------------------------------------------------------
-- 중복 데이터 조회
------------------------------------------------------
SELECT *
  FROM (
          SELECT some_id
               , ROW_NUMBER() OVER (
                      PARTITION BY some_id ORDER BY some_id ) as dup_idx
            FROM tbl_some_table (NOLOCK)
       ) tb_dup
 WHERE tb_dup.dup_idx > 1;


------------------------------------------------------
-- 중복 데이터 기준으로 중복된 데이터를 다 조회
------------------------------------------------------
SELECT t.*
  FROM (
          SELECT some_id
               , ROW_NUMBER() OVER (
                      PARTITION BY some_id ORDER BY some_id ) as dup_idx
            FROM tbl_some_table (NOLOCK)
       ) tb_dup INNER JOIN tbl_some_table t (NOLOCK)
                        ON t.some_id = tb_dup.some_id
 WHERE tb_dup.dup_idx > 1;


------------------------------------------------------
-- 중복 데이터 삭제
------------------------------------------------------
DELETE tb_dup
  FROM (
          SELECT some_id
               , ROW_NUMBER() OVER (
                      PARTITION BY some_id ORDER BY some_id ) as dup_idx
            FROM tbl_some_table
       ) tb_dup
 WHERE tb_dup.dup_idx > 1;


------------------------------------------------------
-- 여러 컬럼일 경우 중복 데이터 삭제
------------------------------------------------------
DELETE tb_dup
  FROM (
          SELECT some_id, some_nm, some_description
               , ROW_NUMBER() OVER (
                      PARTITION BY some_id, some_nm, some_description
                      ORDER BY some_id, some_nm, some_description ) as dup_idx
            FROM tbl_some_table
       ) tb_dup
 WHERE tb_dup.dup_idx > 1;