찐빵의 IT World

-- 1.저장프로시져별 실행수 뽑기 
select db_name(st.dbid) DBName
,object_schema_name(st.objectid,dbid) SchemaName
,object_name(st.objectid,dbid) StoredProcedure
,sum(qs.execution_count) Execution_count
from sys.dm_exec_cached_plans cp
join sys.dm_exec_query_stats qs on cp.plan_handle=qs.plan_handle
cross apply sys.dm_exec_sql_text(cp.plan_handle)st
where DB_Name(st.dbid) is not null and cp.objtype = 'proc'
group by DB_Name(st.dbid),object_schema_name(objectid,st.dbid),object_name(objectid,st.dbid)
order by sum(qs.execution_count) desc


--2. CPU소모량이 많은 저장프로시져 뽑기
select db_name(st.dbid) DBName
,object_schema_name(st.objectid,dbid) SchemaName
,object_name(st.objectid,dbid) StoredProcedure
,sum(qs.execution_count) Execution_count
,sum(qs.total_worker_time) total_cpu_time
,sum(qs.total_worker_time) / (sum(qs.execution_count) * 1.0) avg_cpu_time
from sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle=qs.plan_handle
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where db_name(st.dbid) is not null and cp.objtype='proc'
group by db_name(st.dbid), object_schema_name(objectid,st.dbid), object_name(objectid,st.dbid)
order by sum(qs.total_worker_time) desc

--3. IO량이 많은 저장프로시져 뽑기
select db_name(st.dbid) DBName
,object_schema_name(objectid,st.dbid) SchemaName
,object_name(objectid,st.dbid) StoredProcedure
,sum(execution_count) execution_count
,sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) total_IO
,sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) / sum
(execution_count) avg_total_IO
,sum(qs.total_physical_reads) total_physical_reads
,sum(qs.total_physical_reads) / (sum(execution_count) * 1.0) avg_physical_read
,sum(qs.total_logical_reads) total_logical_reads
,sum(qs.total_logical_reads) / (sum(execution_count) * 1.0) avg_logical_read
,sum(qs.total_logical_writes) total_logical_writes
,sum(qs.total_logical_writes) / (sum(execution_count) * 1.0) avg_logical_writes
from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.plan_handle) st
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
where db_name(st.dbid) is not null and cp.objtype = 'proc'
group by db_name(st.dbid),object_schema_name(objectid,st.dbid), object_name(objectid,st.dbid)
order by sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) desc

--4. 처리시간이 긴 저장프로시져 뽑기
select db_name(st.dbid) DBName
,object_schema_name(objectid,st.dbid) SchemaName
,object_name(objectid,st.dbid) StoredProcedure
,sum(execution_count) execution_count
,sum(qs.total_elapsed_time) total_elapsed_time
,substring(convert(char(23),DATEADD(ms,sum(total_elapsed_time)/1000,0),121),12,23)  total_elapsed_time_ms
,sum(qs.total_elapsed_time) / sum(execution_count) avg_elapsed_time
from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.plan_handle)st
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
where db_name(st.dbid) is not null and cp.objtype = 'proc'
group by db_name(st.dbid),object_schema_name(objectid,st.dbid),object_name(objectid,st.dbid)
order by sum(qs.total_elapsed_time) desc

출처 : http://unions5.tistory.com/m/post/view/id/98

작업을 하다보면 DB를 백업해야 하는 경우가 있습니다. 

보통 EXPORT를 사용하는 것 같은데 종종 에러도 생기고 해서 좀 더 쉬운 방법을 찾아봤습니다.

여기서는 스크립트를 제작해서 간단하게 추출, 주입하는 방법을 소개합니다.

MSSQL 2008을 기준으로 설명하겠습니다.

1. 일단 MSSQL을 킵니다.

이 글은 오라클 기반의 글 입니다.

 1. RBO (Rule Based Optimizer)

머지 않아 소멸될 것이므로 간단한 개념만 소개한다.

RBO는 인덱스 구조나 비교연산자에 따라 순위를 부여하여 이것을 기준으로 최적의 경로를 결정

순위

① ROWID로 1 로우 액세스

② 클러스터 조인에 의한 1 로우 액세스

③ Unique HASH Cluster에 의한 1 로우 액세스

④ Unique INDEX에 의한 1 로우 액세스

⑤ CLUSTER 조인

⑥ Non Unique HASH Cluster Key

⑦ Non Unique Cluster Key

⑧ Non Unique 결합 인덱스

⑨ Non Unique한 컬럼 인덱스

⑩ 인덱스에 의한 범위 처리

⑪ 인덱스에 의한 전체범위처리

⑫ Sort Merge 조인

⑬ 인덱스 컬럼의 MIN, MAX 처리

⑭ 인덱스 컬럼의 ORDER BY

⑮ 전체테이블 스캔

RBO 의 단점

통계정보 무시

RBO 의 장점

옵티마이저의 판단이 매우 규칙적이고 분명하며 사용자가 정확히 예측할 수 있다.

전략적인 인덱스를 구성할 수만 있다면 이 규칙의 보편 타당성이 매우 높다.

 2. CBO(Cost Based Optimizer)

미리 작성해둔 다양한 통계정보를 참조한다. 통계정보에는 테이블의 로우 수와 블록 수, 블록 당 평균 로우 수, 로우의 평균길이, 컬럼별 상수값의 종류, 분포도, 컬럼 내 NULL 값의 수, 클러스터링 팩터, 인덱스의 깊이, 최대 최소값, 리프 블록 수, 가동 시스템의 I/O나 CPU/ 사용정보 등 많은 정보 보유

CBO 의 장점

- 최대의 장점은 현실을 감안한 판단을 할 수 있다(통계정보 기반)

- 통계정보의 관리를 통해 최적화를 제어 할 수 있다

- 옵티마이져를 깊이 이해하고 있지 않더라도 최소한의 성능이 보장된다

쓰다가 일이 생겨서... 추후 업데이트!

 UPDATE

- 조건에 맞는 데이터 변경

UPDATE dbo.Employee
    SET EmpName = N'홍길퉁'
    WHERE EmpID = 'S0001'
GO

- FROM 절을 사용한 조건 지정

UPDATE dbo.Employee
    SET Salary = Salary *0.8
    FROM dbo.Employee e1
    WHERE (SELECT COUNT(*)
        FROM dbo.Vacation
        WHERE EmpID = e1.EmpID) > 2
GO

- 기본적인 INSERT 문

INSERT INTO dbo.Department(DeptID, DeptName, UnitID, StartDate) 
VALUES('PRD', N'상품', 'A', GETDATE()) 
GO

- 열 정보 생략 하여 INSERT 하기

IDENTITY 속성의 열을 제외한 모든 열이거나 나열될 열이 테이블에 포함된 모든열 일때

INSERT INTO dbo.Department 
VALUES('DBA', N'DB관리', 'A', GETDATE())

- 동시에 여러 행 INSERT

INSERT INTO dbo.Department 
VALUES('OPR', N'운영', 'A', GETDATE()), ('CST', N'고객서비스', NULL, GETDATE()) 
GO

- 상위 n개 INSERT

- Vacation과 같은 구조의 SampleVacation 테이블 만들기

SELECT * 
INTO dbo.SampleVacation 
FROM dbo.Vacation 
WHERE 1 = 0 
GO

- Vacation테이블의 상위 5 건만 SampleVacation 테이블에 입력

INSERT TOP(5) 
    INTO dbo.SampleVacation 
    SELECT EmpID, BeginDate, EndDate, Reason, Duration 
        FROM dbo.Vacation 
            ORDER BY BeginDate DESC 
GO

- 저장 프로시저 결과 INSERT

-- 저장 프로시저 만들기
CREATE PROC dbo.usp_GetVacation
        @EmpID char(5)
AS
        SELECT EmpID, BeginDate, EndDate, Duration
            FROM dbo.Vacation
            WHERE EmpID = @EmpID
GO

-- 임시 테이블 만들기
CREATE TABLE #Vacation (
    EmpID char(5),
    BeginDate datetime,
    EndDate datetime,
    Duration int
)
GO

-- 저장 프로시저 결과 INSERT
INSERT INTO #VACATION EXEC dbo.usp_GetVacation 'S0001'
GO

SELECT * FROM #Vacation
GO

- IDENTITY 속성에 INSERT 하기

IDENTITY가 2번인 행을 지웠다고 가정하자.

IDENTITY가 2번인 행을 INSERT 하기 위해서 해야 할 일을 알아보자.

-- 임의의 IDENTITY값 INSERT 하기
SET IDENTITY_INSERT dbo.Vacation ON
GO

INSERT INTO dbo.Vacation(VacationID, EmpID, BeginDate, EndDate, Reason) -- 모든 열 정보를 다 나열해야 한다
VALUES(2, 'S0003', '2007-01-22', '2007-01-08', N'신년 맞이 기분 내기')
GO

SET IDENTITY_INSERT dbo.Vacation OFF
GO

SELECT * FROM dbo.Vacation
GO

 SELECT

- 모든 데이터 가져오기

SELECT * FROM dbo.Employee

- 특정 열의 데이터 가져오기

SELECT EmpID, HireDate, Email 
FROM dbo.Employee 
GO

-특정 행의 데이터만 가져오기

SELECT * 
FROM dbo.Employee 
WHERE EmpID = 'S0005' 
GO

- 특정 열의 특정 행만 가져오기

SELECT EmpID, EmpName, HireDate, EMail 
FROM dbo.Employee 
WHERE EmpID = 'S0005' 
GO

- 비교 연산자 사용

SELECT EmpID, EmpName, HireDate, EMail
FROM dbo.Employee 
WHERE Salary >= 8000

SELECT EmpID, EmpName, HireDate, EMail 
FROM dbo.Employee 
WHERE HireDate < '2007-01-01'

- 문자열 비교

SELECT EmpID, EmpName, HireDate, Email 
FROM dbo.Employee 
WHERE EmpName = '홍길동'

SELECT EmpID, EmpName, HireDate, Email 
FROM dbo.Employee 
WHERE EmpName LIKE '김%'

- 논리 연산자 사용

SELECT EmpID, EmpName, HireDate, EMail 
FROM dbo.Employee 
WHERE HireDate >= '2008-01-01' AND Salary >= 6000

SELECT EmpID, EmpName, HireDate, EMail 
FROM dbo.Employee 
WHERE HireDate >= '2008-01-01' OR Salary >= 6000

SELECT EmpID, EmpName, HireDate, EMail 
FROM dbo.Employee 
WHERE HireDate >= '2008-01-01' AND NOT Salary >= 6000

- 범위 조건 지정

SELECT EmpID, EmpName, HireDate, EMail 
FROM dbo.Employee 
WHERE Salary BETWEEN 6000 AND 8000

SELECT EmpID, EmpName, HireDate, EMail 
FROM dbo.Employee 
WHERE Salary >=6000 AND Salary <= 8000

- 리스트 조건 지정

SELECT EmpID, EmpName, HireDate, EMail 
FROM dbo.Employee 
WHERE DeptID IN ('SYS', 'MKT', 'HRD')

SELECT EmpID, EmpName, HireDate, EMail 
FROM dbo.Employee 
WHERE DeptID = 'SYS' OR DeptID = 'MKT' OR DeptID = 'HRD'

배경

어떤 데이터를 DB에서 직접 고치게 되면, 늘 걱정하는 것 중 하나가 완벽하게 모든 영향받는 테이블들까지

잘 고쳤는가 하고 스스로에게 물어보는 것일게다.

그럴 때면, 해당 컬럼 이름이 포함된 모든 테이블을 뒤지는 게 상책이다.

소스

select b.name, a.name 
from sys.all_columns a, sys.all_objects b
where a.object_id = b.object_id and b.type_desc = 'USER_TABLE'
and a.name = 'AccountID' --// 해당 컬럼 이름을 여기에 넣어서 찾으면 된다.

잡담

sys.all_objects 는 참 유용한 녀석이다. 테이블 뿐만 다음과 같은 타입을 모두 취급하니,

시간되면 천천히 찾아 볼 일이다. ㅎㅎ

CLR_STORED_PROCEDURE
SYSTEM_TABLE
VIEW
SQL_TABLE_VALUED_FUNCTION
DEFAULT_CONSTRAINT
SQL_STORED_PROCEDURE
EXTENDED_STORED_PROCEDURE
AGGREGATE_FUNCTION
USER_TABLE
SERVICE_QUEUE
SQL_INLINE_TABLE_VALUED_FUNCTION
INTERNAL_TABLE
CLR_SCALAR_FUNCTION
SQL_SCALAR_FUNCTION
PRIMARY_KEY_CONSTRAINT
 

출처 : http://blog.daum.net/nextkey/122

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영역의 배치처리실행을 반복하게됩니다.

실행 계획을 텍스트로

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 : 경과시간

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

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