찐빵의 IT World

 추적 플래그

추적 플래그는 데이터베이스의 기본 설정 사항의 변경이나 숨겨진 서버 내부의 행위들의 관찰을 위해 임시로 설정하는 것이다. 그리고 이 설정값은 사용자가 직접 설정을 해제하거나 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영역의 배치처리실행을 반복하게됩니다.

Visual_BASIC_6_Runtimes_Pack_Release_7.exe

실행시 COMCTL32.OCX 에러가 날 경우 첨부파일을 설치해 주십시오

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을 대신하는 것 같다.

SQL Server Management Studio 의 상단 메뉴

도구 > 옵션 > 텍스트 편집기 > 모든 언어 > 일반

에서 [표시] 부분의 줄번호 체크 해 주시면 됩니다.

그런데 줄번호 표시하면  텍스트 표시영역을 줄이게 되므로,

줄번호가 표시 안되더라도 [Ctrl] + G 키 누르시면 특정 줄번호로 빠르게 이동하실 수 있습니다.

[Ctrl] + G 키로 줄번호 이동은 대부분의 텍스트 편집기에서 표준으로 제공하는 기능입니다.

SQL 서버를 처음 설치하면 시스템이 동작하기 위한 데이터 베이스

- Master 데이터베이스

SQL 서버의 주요한 메타 데이터들이 있다.

SQL Server 인스턴스에 있는 모든 데이터베이스들에 대한 정보와 각 데이터베이스의 파일 정보, 서버 구성 옵션, 계정 등과 같은 중요한 정보들이 저장된다. SQL Server 초기화 정보도 Master에 관리되어 master 데이터베이스를 사용 못하면 SQL Server 서비스 시작을 못한다.

Master 데이터베이스는 SQL Server 2000 시스템의 모든 시스템 수준 정보를 기록합니다. 모든 로그인 계정과 모든 시스템 구성 설정을 기록합니다. 모든 데이터베이스의 존재 유무를 기록하며, 초기화 정보를 기록합니다.
주기적으로 백업되고 잘 관리되어야만 한다.

- MODEL 데이터베이스

새로운 데이터베이스를 생성할 때 이 데이터베이스를 참고하여 만든다.

새로운 데이터베이스를 생성할 때 특별한 지정이 없으면 이 Model 데이터베이스를 베낀다.

Model 데이터베이스를 수정하여 생성할 때 옵션을 변경시킬 수 있다.

- MSDB 데이터베이스

SQL Server 에이전트 서비스가 사용하는 작업 일정 정보, 백업 및 복원 기록 정보, 경고, SSIS 패키지, 복제정보가 저장.

변경사항이 없다면 백업할 필요가 없다.

- TEMPDB 데이터베이스

임시로 사용하는 데이터베이스

시스템이 재 시작할 때 초기화되어 새로 생성된다. 그러므로 이 데이터베이스의 백업은 불필요.

EX) 인덱스나 그룹핑 쿼리 등을 수행할 때 특정 데이터를 정렬한 후 이용해야 한다면, 이러한 작업들은 실제 사용자 데이터베이스에서 이루어지지 않고, Tempdb 데이터베이스에서 1차 작업 후 옮겨지게 된다.