찐빵의 IT World

 ONLINE = ON | OFF

인덱스 작업 중에 쿼리 및 데이터 수정에 기본 테이블과 관련 인덱스를 사용할 수 있는지 여부를 지정합니다. 기본값은 OFF입니다.

ON - 장기 테이블 잠금이 유지되지 않습니다. 따라서 기본 테이블에 대한 쿼리나 업데이트를 계속할 수 있습니다.

OFF - 테이블 잠금이 적용되어 인덱스 작업 중에 테이블을 사용할 수 없습니다.

ONLINE 옵션은 클러스터형 인덱스를 삭제할 때만 지정할 수 있습니다. 자세한 내용은 주의 섹션을 참조하십시오.

출처 : http://technet.microsoft.com/ko-kr/library/ms176118.aspx

 통계

- 검색 조건이 주어질 때 해당 컬럼으로 구성되는 인덱스를 사용할지 여부를 옵티마이저가 판단의 근거로 활용

- 통계는 데이터가 있는 컬럼에 대해 인덱스가 만들어질 때 이 컬럼 값의 히스토그램 및 관련정보를 이용하여 만들어진다.

Sysindex 테이블의 image형식의 statblog컬럼에 통계정보가 저장된다.

데이터베이스 옵션의 auto create statistics와 auto update statistics는 모든 데이터베이스에 대해 자동으로ture로 설정되며 이 옵션들이 설정되어 있으면 인덱스뿐만 아니라 인덱스가 없는 컬럼에 대해서도 필요할 경우 자동으로 통계가 만들어지고 일정 비율(전체행의 약20%)의 데이터가 업데이트 되면통계도 자동으로 업데이트 된다. 

밀도(density)는 1/(인덱스키들의 수)로 정의된다. 즉, 성별은 밀도가 0.5(남자, 여자)이지만 나의 주민등록 번호는 1/5000만 이 된다.(대한민국 인구수를 5천만으로 가정 했을 때).

이때 선택도(selectivity)는 주어진 키 값(또는 키 범위)에 해당하는 행수의 정도를 말하는데 행수가 적을수록 선택도가 높다. 그러나 전체 밀도가낮더라도 특정 키에 대한 선택도는 낮을 수도 있다.

[통계 보기]

DBCC SHOW_STATISTICS문을 사용하여 통계를 확인 할수 있다. 실습에서는 AdventureWorks의 [Person.Address]테이블을 사용 한다.

DBCC SHOW_STATISTICS([PERSON.ADDRESS], PK_ADDRESS_ADDRESSID)

Updated : 최근 통계가 업데이트된 날짜.

Rows : 전체 행 수

Rows Sampled : 샘플링된 행 수 (행 수가 많을 경우 일부를 샘플링하여 사용)

Steps : 히스토그램의 단계 수.고유 인덱스가 아닐 경우 단계수가 많아지며 최대 200단계 이다.

Average Key length : 컬럼 값의 평균 길이. Int형이므로 4가 표시 된다.

All density : 밀도. 밀도는 Steps의 값과 무관하며 일반적으로 (1/ 인덱스 키들의 수) 이다.

RANGE_HI_KEY : 각 단계(step)에서샘플링된 최대 값.

RANGE_ROWS : 각 단계에서RANGE_HI_KEY갑을 제외한 값들의 개수.

EQ_ROWS : 최대값의 개수.

DISTINCT_RANGE_ROWS : RANGE_ROWS 컬럼 값들중 고유한 값의 개수.

수동으로 통계 업데이트

UPDATE STATISTICS 테이블이름 

인덱스 정보 확인하기 

EXEC SP_HELPINDEX [PERSON.ADDRESS]

출처 : http://sqlmvp.kr/140165557766

 SINGLE_USER

이 항목에서는 SQL Server Management Studio 또는 Transact-SQL을 사용하여 SQL Server 2012에서 사용자 정의 데이터베이스를 단일 사용자 모드로 설정하는 방법에 대해 설명합니다. 단일 사용자 모드는 한 번에 하나의 사용자만 데이터베이스에 액세스할 수 있도록 지정하며 일반적으로 유지 관리 동작에 사용됩니다.

주의 사항

 추적 플래그

추적 플래그는 데이터베이스의 기본 설정 사항의 변경이나 숨겨진 서버 내부의 행위들의 관찰을 위해 임시로 설정하는 것이다. 그리고 이 설정값은 사용자가 직접 설정을 해제하거나 SQL Server가 재시작 될때까지 유지된다. 대부분의 추적 플래그는 문서화 되지 않았으며 SQL Server의 버전, 서비스팩, 핫픽스에 따라 그 내용이 바뀔 수 있음을 유념해야 한다.

추적 플래그는 SQL Server의 시작시 -Ttrace# 옵션을 사용하여 설정하거나 DBCC TRACEON 명령어를 이용하여 설정 가능하다. 어느 방법이든지 추적 플래그는 SQL Server가 재시작 하거나 DBCC TRACEOFF 명령을 사용하여 추적 플래그가 해제될 때까지 서버에 설정된 상태로 유지된다.

추적 플래그는 숨겨진 문제나 실제로 서버 내부에서 어떤 일이 일어 났는지 판단할 수 있는 좋은 도구가 될 수 있다. 하지만 문서화 되지 않은 추적 플래그를 실제 운영 환경에 사용하기 전에 신중히 조사할 것을 권고한다.(현재 SQL Server 2000 BOL에는 추적 플래그 230, 1204, 2528, 3205 만이 문서화 되어있다.) 일부 추적 플래그의 현재 속성들은 마이크로소프트의 기술 문서를 참고하면 도움이 될 것이다.

아래의 표는 Transact-SQL Language Reference Guide 에서 옮겨온 것이며, 설명된 속성들은 일부 현재 문서화된(BOL에) 것과 문서화되지 않은 추적 플래그로 SQL Server 7.0 and 2000 에서 사용 가능하다.

출처 : http://cafe.naver.com/cadwebdesign/28

 추적 플래그 시작 매개변수로 지정하는 방법

SQL 구성관리자 - SQL Server Service - SQL Server 인스턴스 - SQL Server(이름) - 속성 - 고급 - 시작매개변수

MSSQL에서 GO 명령은 배치단위를 구분하는 명령으로 사용됩니다.

예를 들어, 아래와 같이 GO로 구분되면 한꺼번에 두 개의 문장을 실행하였다고 하더라도, 두 개의 문장은 별도의 배치로 실행됩니다.

SELECT * FROM 테이블

go

SELECT * FROM 테이블
go

하지만 아래와 같이 GO로 구분하지 않으면, 두 개의 문장은 하나의 배치로 한꺼번에 실행됩니다.

SELECT * FROM 테이블

SELECT * FROM 테이블
go

이를 구분하기 위해서는 SQL 프로파일러를 사용하여 확인해 보시면 되겠습니다.

P.S>

참고로 아래의 구문은 먼저 데이터베이스를 생성하고,

그 다음 생성된 데이터베이스로 데이터베이스 컨텍스트를 변경하는 것입니다.

create database K_League
go

use K_League
go

go를 경계로 일괄처리가 구분되기 때문에.

한쪽에서 정의한 변수를 다른쪽에서는 사용할수 없습니다.

DECLARE @num int

go

SET @num = 10

하시면 에러가 납니다.

SET @num = 10 은 go 위에서 정의 되었는데.

go를 사용함으로써 일괄처리가 끝나고 다시 새로운 일괄처리가 시작되므로써,

변수가 정의가 되지 않은 것입니다.

PS>
 (1) 위 Procedure의 끝부분에 있는 End; 에서 ; 문자는 종결자라고 합니다. 종결자는 Query가 실행되는 하나의Query 단 위의 종료문자에 해당합니다.
 (2) Go뒤에 숫자를 써주게 되면 해당 하는 숫자만큼 Go영역의 배치처리실행을 반복하게됩니다.

Select @@TRANCOUNT : 현재 동작중인 트랜잭션의 갯수

DBCC INPUTBUFFER(spid) : SPID가 마지막으로 사용한 명령문에 대한 정보 SQL Server 2005 Bible 696p.

SP_LOCK : 현재 락 상태를 확인할 수 있다. ( sp_lock [[@spid1 = ] 'spid1' ]  [,[@spid2 = ] 'spid2'] )

exec sp_lock @@spid - @@SPID는 현재 사용자의 프로세스의 서버 프로세스 식별자(SPID) 값을 반환한다.

bible 702p

SP_WHO : 현재 락 상태를 확인 ( SP_WHO [[@login_name = ] 'login' ] )

또한, SP_LOCK 53 과 같이 SPID를 명시하면 특정 SPID의 락 상태를 확인가능

SSMS의 GUI환경에서 락을 관리할 수도 있다. 관리 - 작업모니터를 실행.

DBCC USEROPTIONS : 트랜잭션의 격리 수준을 확인 및 암시적 트랜잭션 모드 확인(impicit_transaction 옵션이 SET이면 ON, 아예 없으면 OFF)

실행 계획을 텍스트로

SET SHOWPLAN_TEXT ON : Stmt항목(쿼리,연산자,인수) 쿼리의 플랜을 텍스트로 보여주고,

SET SHOWPLAN_ALL ON : 실행계획에 대한 모든 정보

실제 실행은 되지 않는다.

실행 후 실행계획 표시

SET STATISTICS PROFILE ON : 성능문제해결을 위해 실행 계획이 필요할 때. SET SHOWPLAN_ALL과 비슷하지만 앞에 2개의 컬럼이 추가된다. ROWS - 실제로 처리된 총 행의 갯수, EXECUTES - 실제 수행된 횟수

EstimateRows(예측 행수) X EstimateExecutions(예측 횟수) = 최적화기가 예측하는 전체 행 수

EstimateRows(예측 행수) X EstimateExecutions(예측 횟수) = 실제 실행 행수  이렇게 비슷 하다면 제대로 예상하고 있는 것

SET STATISTICS IO ON : 디스크 검색 수, 논리적 읽기 수, 물리적 읽기 수

SET STATISTICS TIME ON : 경과시간

행의 이동(Forward Record)(Forwarded Record)

행이 이동하는 경우 : 힙(Heap)에서 가변 컬럼을 현재 값보다 더 큰 크기의 값으로 갱신할 경우

이 때 기존의 페이지에는 더 커진 행의 값에 대한 공간이 없어서 새로운 값을 넣을 수 없으므로 새로운 행을 다른 페이지에서 할당 받아 값을 넣고 기존의 행에서 이 새로운 행을 가리키는 포워딩(forwarding)을 하게 된다. 포워드 된 행은 DBCC PAGE 명령으로 보면 FORWARDED_RECORD 라고 나타나고, 원래 그 행이 있던 자리에는 새로운 포워드 된 행을 가리키는 FORWARDING_STUB 이 나타난다.

"AA"  ->  "kkkkkk"  ->  "ffffffffffffffffffff" 의 경우 "AA" 가 직접 "fffffffffffffff"를 가리키도록 바꾼다.

언제 제자리로 돌아올까?

1. "ffffffffffffffffff" 가 다시 "AA"로 줄어들면 된다. 이동 전의 크기 혹은 그 이하로 줄어들 때

2. DBCC SHRINKDATABASE (책에 될 때도 있고, 안될 때도 있으며 정확히 모르겠다고 함)

3. 클러스터드 인덱스가 없기 때문에 생기는 것이므로 클러스터드 인덱스를 만든다. (좋은 해결책 이라고 함)

 Ref . server 2000/2005 튜닝 / SQL Server 완벽 튜닝 - 정원혁, 손광수 공저. 대림 출판사. 148p

Ghost Record

클러스터드 인덱스에서 행의 이동이 일어나는 경우 Ghost Record가 일어난다.

쿼리가 처리되는 과정은 다음과 같다.

1. 구문 분석(Parsing)

해당 쿼리가 문법적으로 틀리지 않은지 확인. 해당 구문을 SQL 서버가 이해할 수 있는 단위들로 분해하는 과정.

만약 구문이 부정확하다면 여기서 처리를 중단. 이 문장이 일괄 처리(batch) 내에 있다면 일괄 처리 전체를 중단.(Batch abort : Batch 중 하나라도 syntax error가 있다면 전체 batch가 실행되지 않는다.)

2. 표준화(Standardization)

실제로 필요없는 부분들이 제거. 표준화된 쿼리 트리(Standard Query Tree)가 만들어 진다.

3. 최적화(Optimization)

통계나 조각 정보 등을 바탕으로 실행 계획을 만들어 낸다. 쿼리처리에서 매우 중요한 단계

① 쿼리 분석 : 검색 제한자(SARG)인지 조인 조건인지 판단.

② 인덱스 선택 : 분포 통계 정보를 이용하여 인덱스검색이나 테이블 스캔 중의 하나를 선택. 여러 인덱스 중 가장 효율적인 인덱스를 선택

③ 조인 처리 : JOIN, UNION, GROUP BY, ORDER BY 절을 가지고 있는지 확인하여 적절한 작업 순서를 선택

이 단계의 출력은 실행 계획(Execution Plan) 이다.

4. 컴파일(Compilation)

컴파일을 하면 이진 코드가 생성된다. 일반적인 경우에는 컴파일하고 나면 .exe, .dll 등의 이진 파일이 만들어 지는데, SQL Server에서는 그냥 메모리(프로시저 캐시)에만 올린다. 그래서 컴파일 속도가 매우 빠르다.

5. 실행(Execute)

엑세스 루틴으로 가서 실제 처리를 하고 결과를 돌려준다.

이상의 다섯 단계는 단순화한 논리적 절차로 받아들여야 하며, 실제 쿼리 과정은 이보다 훨씬 더 복잡하다.

SELECT 실행 순서 

-문법 순서-

SELECT                            - 1

FROM                               - 2

WHERE                             - 3

GROUP BY                        - 4

HAVING                            - 5

ORDER BY                        - 6

-실행 순서-

FROM                               - 1

WHERE                             - 2

GROUP BY                        - 3

HAVING                             - 4

SELECT                            - 5

ORDER BY                         - 6

해당 데이터가 있는 곳을 찾아가서                   (FROM)

조건에 맞는 데이터만 가져와서                       (WHERE)

원하는 데이터로 가공                                    (GROUP BY)

가공한 데이터에서 조건에 맞는 것만                (HAVING)

뽑아내서                                                     (SELECT)

정렬                                                           (ORDER BY)

실행순서는 문법, 권한 검사 순서이기도 하고,

Alias 등록 순서 이기도 하다.

별칭(Alias)

FROM 절에서 테이블에 Alias를 사용했다면       (FROM Table1 AS T1)

SELECT, ORDER BY 절에서 사용할 수 있고      (SELECT T1.Col1, ORDER BY T1.Col1)

SELECT 절에서 컬럼에 Alias를 사용했다면       (SELECT T1.Col1 AS a)

ORDER BY 절에서 사용할 수 있다.                   (ORDER BY AS a)

ORDER BY절에 T1.a가 안되는 것으로 보아

a는 T1.col1을 대신하는 것 같다.