찐빵의 IT World

데이터 흐름 태스크에서 오류가 발생했었는데,

오류 화면은 아래와 같다.

IDENTITY 컬럼 관련 오류인데, 아래와 같이 설정을 바꿔주니 정상 동작하였다.

FastLoadKeepIdentity : True 로 변경해야 한다.

IDENTITY 관련된 오류인것 같은데 정확한 원인은 좀 더 알아보아야 할 것 같다.

SQL Server Database Engine 은 두 가지 주요한 구성요소가 있다.

1. Storage Engine

2. Query Processor (Relational Engine)

1. Storage Engine 은 데이터 읽기를 관여한다. 디스크와 메모리 사이에서 데이터 무결성을 유지하면서 그럭저럭 동시성을 최적화한다.

2. Query Processor 는 SQL Server 쿼리들의 최적의 실행을 고안하고, 그 계획을 실행하고, 결과를 반환한다.

Parsing and binding :

쿼리가 구문분석이 되고, 묶인다.

쿼리가 타당하면, 이 단계의 결과물은 논리적 Tree이고, 반드시 실행되어야 하는 쿼리의 논리적 운영을 보여주는 트리안의 각 노드 예를 들면, 특정 테이블을 읽는다던지, 조인을 수행한다던지

논리적 Tree 는

특

바인딩 단계에서

SQL Server 는 object name들이 존재하는지, 관련된 모든 parse tree에 있는 테이블이름과 컬럼이름이 system catalog상 일치하는지 확실히 한다. 이 단계의 결과물을 algebrized Tree라고 하며, 이 것이 Query Optimizer 에게 보내진다.

   N의 의미  

INSERT INTO 테이블명

VALUES

N'값' 에서 N의 의미

저장 프로시저 및 트리거와 같이 서버에서 실행된 코드에 나타나는 유니코드 문자열 상수 앞에는 대문자 N이 와야 합니다. 참조되는 열이 이미 유니코드로 정의된 경우에도 마찬가지입니다. N 접두사가 지정되지 않은 문자열은 데이터베이스의 기본 코드 페이지로 변환되며 특정 문자를 인식하지 못할 수도 있습니다.

예를 들어 이전 예에서 만든 저장 프로시저는 다음과 같은 방법으로 서버에서 실행될 수 있습니다.

EXECUTE Product_Info @name = N'Chain'

N 접두사 사용에 대한 요구 사항은 서버에서 보낸 문자열 상수와 클라이언트에서 보낸 문자열 상수 모두에 적용됩니다.

https://msdn.microsoft.com/ko-kr/library/ms191313.aspx

★ N을 붙이지 않는다면 DB 기본 COLLATION 으로 인식됨

알기 쉽게 표로 나타내는 Collation 과 NVARCHAR + N 의 관계

create table test
(
name1 nvarchar(100),
name2 varchar(100)
)

insert into test 
values (N'臺灣語',N'臺灣語')

select * from test

2행이 결과이며, 데이터 타입이 varchar 일 때는 중국어가 들어가지 않는다.

NVARCHAR로 변경 후, INSERT 문에도 N'중국어' 처럼 작성해야 INSERT 완료됨

이렇게 되는 이유는 DB의 COLLATION 과도 밀접한 관련이 있는데,

아래의 글을 참고하시면 됩니다.

http://police84.tistory.com/119

현재 MS-SQL 서버명을 확인 하는 방법
SELECT @@servername

1. 현재 서버명 삭제
    SP_DROPSERVER '현재의 MSSQL서버명'

2. 새로운 서버명 추가
    SP_ADDSERVER '바꿀서버명', 'local'

3. 서비스 재시작

4. 변경된 서버명 확인
    SELECT @@servername

[MS-SQL]

Primary Key 복합 인덱스

Primary Key 는 NULL을 허용하지 않고, 중복값을 허용하지 않는다는 것은 잘 알고 있을 것이다.

그런데 Primary Key를 2~3개 컬럼으로 잡은 경우의 동작방식에 대해 알게 되어 적어본다.

Primary Key 컬럼을 3개로 잡은 경우

Col1 , Col2 , Col3 컬럼으로 잡고 3개다 int 형식의 컬럼이라고 하면

1 , 2 , 3 의 데이터가 있다면

1 , 2 , 2 의 데이터는 허용하지만

1 , 2 , 3 의 데이터는 허용하지 않는다.

즉, 3개컬럼이 다 합쳐져서 유니크 해야 한다는 뜻이 된다.

Procedure Cache(또는, Plan Cache라고도 함)는 Query Plan이 저장되는 메모리 공간으로 Query Plan이 재사용될 수 있도록 함으로써 쿼리문을 Compile하는데 드는 비용을 최소화하기 위해 사용됩니다. 일반적으로 SP(Stored Procedure) 사용을 권장하는 가장 큰 이유는 SP에 대한 Query Plan의 재사용을 통해 성능 향상을 기대할 수 있기 때문입니다. 반면에, Adhoc 쿼리문은 쿼리문 전체가 동일하지 않은 이상 Query Plan이 재사용되지 못하기 때문에(SQL Server에서 제공하는 '자동 매개변수화(Auto Parameterization)' 기능을 배제했을 때) 실행될 때마다 Compile 비용이 발생하여 성능에 악영향을 미치게 됩니다. 또한, Adhoc 쿼리문을 과도하게 사용하는 환경에서는 Adhoc 쿼리문에 대한 Query Plan들에 의해서 Procedure Cache의 크기가 일정 수준 이상으로 커지는 현상이 발생하기도 하며 이로 인해 사용 가능한 Buffer Pool 메모리 영역의 크기가 감소하여 SQL 서버 전체의 성능이 나빠지는 현상이 발생할 수 있습니다.
 
SQL Server 2005부터는 X64 버전도 지원되어 64-bit 환경이 보편화되었습니다(IA64 버전은 SQL Server 2000부터 지원되었습니다). 64-bit 환경에서는 메모리 주소 공간(Address Space)의 크기가 늘어남에 따라 32-bit 환경에서는 잘 발생하지 않던 현상이 발생하기도 하는데 그 중 대표적인 것이 Procedure Cache의 크기가 수 GB 이상으로 늘어나는 현상입니다(실제 사례 중에는 Procedure Cache의 크기가 10GB를 초과하는 사례도 있었습니다). 이런 현상에 대한 해결책으로 SQL Server 2005 Service Pack 2 이후에는 Procedure Cache의 최대 크기에 대한 제약을 강화하였으며 이를 통해 어느 정도의 성능 개선을 가져왔습니다. 하지만, 이는 완전한 해결책은 아니며 근본적으로 SQL Server를 최적화된 상태에서 운영하려면 Procedure Cache를 최대한으로 활용할 수 있도록 쿼리문을 작성하여 사용해야 합니다.
 
Procedure Cache로 인해 발생하는 성능 문제를 해결하기 위해서는 물론이고, 문제 발생을 미연에 방지하고 SQL Server를 최적화하기 위해 Procedure Cache의 구조와 동작 방식을 이해하는 것은 중요합니다. 특히, SQL Server 2005/2008의 Procedure Cache는 SQL Server 2000과는 현저하게 다르므로 SQL Server 2000의 Procedure Cache에 대해 알고 있었다 해도 새롭게 이해해야 할 필요가 있습니다. 그럼, SQL Server 2005/2008의 Procedure Cache에 대해서 살펴보도록 하겠습니다.
 
SQL Server 2005/2008에서 Procedure Cache 메모리 영역은 다음과 같이 크게 5개의 영역으로 세분화됩니다. 이 중 CACHESTORE_OBJCP와 CACHESTORE_SQLCP가 Compiled Plan(또는, Query Plan)이 저장되는 영역이며 Procedure Cache의 대부분을 차지합니다. CACHESTORE_OBJCP에는 Stored Procedure, Trigger, Function 등의 Compiled Plan이 저장되고 CACHESTORE_SQLCP에는 Adhoc query, Prepared query 등에 대한 Compiled Plan이 저장됩니다.

Procedure Cache 메모리 영역 
   CACHESTORE_OBJCP : Object Plans
   CACHESTORE_SQLCP : SQL Plans
   CACHESTORE_PHDR : Bound Trees
   CACHESTORE_XPROC : Extended Stored Procedures
   CACHESTORE_TEMPTABLES : Temporary Tables & Table Variables
 
CACHESTORE_SQLCP 영역에 Compiled Plan이 저장되는 쿼리문의 유형을 좀더 구체적으로 살펴 보면 다음과 같으며, 이 중 Auto-parameterized queries, sp_executesql procedure, Prepared queries에 대한 Compiled Plan은 'sys.dm_exec_cached_plans' DMV를 통해 조회했을 때 objtype이 ‘Prepared’로 표시되고 나머지 쿼리문 유형들에 대한 Compiled Plan은 objtype이 ‘Adhoc’으로 표시됩니다.

Procedure Cache 메모리 영역 안의
CACHESTORE_SQLCP 영역에 Compiled Plan이 저장되는 쿼리문의 유형 
1. Ad-hoc queries : 하나의 DML(SELECT, INSERT, UPDATE, DELETE)로 이루어진 쿼리
2. Auto-parameterized queries : SQL서버에서 제공하는 Simple Parameterization이나 Forced Parameterization의 적용을 받는 쿼리
3. sp_executesql procedure : sp_executesql을 사용하여 Parametrized된 쿼리
4. Prepared queries : OLEDB나 ODBC에서 제공하는 SQLPrepare/SQLExecute 기능이 사용된 쿼리
5. Batches : 여러 개의 쿼리문이 하나의 Batch로 실행되는 경우
6. Dynamic SQL : EXEC(...)을 통해 실행된 쿼리
 
'sys.dm_os_memory_clerks' DMV를 조회하면 다음과 같이 Procedure Cache를 구성하는 5개의 영역에 대한 메모리 사용 정보를 확인해 볼 수 있습니다. 이런 정보는 'dbcc memorystatus' 실행 결과나 성능 카운터를 통해서도 확인할 수 있으며 이 정보를 통해 메모리를 많이 차지하는 Procedure Cache의 영역을 확인할 수 있습니다.

 
Procedure Cache가 과도하게 커져서 성능 문제가 발생했을 때, SQL Server 2000에서는 'dbcc freeproccache' 명령을 통해 Procedure Cache 전체를 비우는 방법을 사용했었습니다. 이 방법은 Procedure Cache에서 사용하던 메모리를 반환하는 효과는 있었지만 유형에 관계없이 모든 Query Plan이 다시 만들어 져야 하기 때문에 급격한 CPU 사용량 증가 등의 부작용을 유발하였습니다. SQL Server 2005/2008에서는 Procedure Cache를 세분화된 영역별로 비울 수 있는 방법이 추가적으로 제공되어 좀더 유연하게 대처할 수 있습니다. 예를 들어, Adhoc이나 Prepared Query에 대한 Compiled Plan이 저장되는 CACHESTORE_SQLCP 영역을 비우고자 할 때에는 다음의 명령을 실행하면 됩니다.
 
   dbcc freesystemcache('SQL Plans')
 
SQL Server 2005/2008에서 Procedure Cache에 저장된 항목(Entry)들은 SQL Server가 메모리 부족을 경험하지 않는 이상 자동으로 지워지지 않습니다. 이런 이유로 물리적인 메모리가 큰 시스템(특히, 64-bit 시스템)에서는 Procedure Cache가 수십 GB 이상으로 커지는 현상이 발생할 수도 있었으며, 이로 인해 발생하는 성능 저하 현상을 예방하기 위해 SQL Server 2005 Service Pack 2 이후부터는 다음의 공식에 의해 계산된 값이 Procedure Cache의 Soft Limit 값으로 사용되도록 보완되었습니다. 예를 들어, SQL서버의 ‘max server memory’를 100GB로 설정한 시스템일 경우 Procedure Cache의 Soft Limit 값은 10.8GB(4*0.75 + 60*0.1 + 36*0.05)가 됩니다.  Soft Limit 값은 단어가 의미하는 것처럼 Limit 값을 절대로 넘을 수 없는 것(Soft Limit과는 다른 개념으로 Hard Limit이 존재합니다)을 의미하는 것은 아니며 Limit 이상에 도달했을 때 Limit 아래로 내려가도록 조절하겠다는 의미입니다. SQL Server 2005/2008에서는 Procedure Cache 메모리가 Soft Limit에 도달하게 되면 Clock Algorithm에 의해서 Procedure Cache에 저장된 항목들이 제거되고 이를 통해 Procedure Cache에 의해 사용된 메모리를 Soft Limit 이하로 낮추게 됩니다.
 
Procedure Cache Memory Soft Limit = 75% of server memory from 0-4GB + 10% of server memory from 4Gb-64GB + 5% of server memory > 64GB
 
SQL Server 2005/2008에서는 Procedure Cache의 크기를 조절하기 위해 Internal Clock Hand와 External Clock Hand의 두 가지 Clock Algorithm이 사용됩니다. Internal Clock Hand는 일반 사용자 Thread에서 쿼리문 실행을 위해 Procedure Cache를 접근(Lookup, Insert 모두)할 때 조건이 발생하게 되면 실행되며 CacheStore별로 한번에 하나의 Thread에서만 Internal Clock Hand가 실행됩니다. External Clock Hand는 Resource Monitor에서 조건이 발생하게 되면 모든 CacheStore에 대해서 실행되며 각 CacheStore별로 실행되는 Internal Clock Hand와 동시에 실행될 수 있습니다. Internal Clock Hand와 External Clock Hand가 실행되는 조건은 다르지만, 두 경우 모두 동일하게 Cache로부터 비용이 0인 항목(Entry)을 지우는 작업이 수행됩니다(SQL Server 2000에서는 Lazy Writer에 의해 동일한 기능이 수행되었습니다).
 
[Internal Clock Hand]
각 CacheStore 단위(CACHESTORE_OBJCP, CACHESTORE_SQLCP, CACHESTORE_PHDR, CACHESTORE_XPROC, CACHESTORE_TEMPTABLES 별로)에서 다음의 조건 중 하나가 충족되면
해당 CacheStore에서 실행됨.
- 특정 CacheStore의 항목(Entry) 수가 SoftQuota(SQL Server 내부적으로 각 CacheStore별로 정의되어 있는 값으로 64-bit의 경우 CACHESTORE_SQLCP에 대해서 160,036이 지정되어 있음)에 도달
- 특정 CacheStore가 Single Page Allocator(Buffer Pool)에서 할당 받은 Memory가 Procedure Cache의 Soft Limit값의 75%에 도달
- 특정 CacheStore가 Multi Page Allocator(MemToLeave)에서 할당 받은 Memory가 Procedure Cache의 Soft Limit값의 50%에 도달
 
[External Clock Hand]
SQL Server나 시스템에서 메모리 부족이 발생하거나 Procedure Cache 전체 메모리가 Soft Limit에 도달할 때 Resource Monitor(SQL Server의 메모리 사용량을 조절하는 시스템 Task)에 의해서 모든 CacheStore에서 실행됨.
 
'sys.dm_os_memory_cache_clock_hands' DMV를 조회해 보면 다음과 같이 Internal Clock Hand/External Clock Hand가 어떤 CacheStore에서 얼마나 실행되었는지를 확인해 볼 수 있습니다. 다음의 결과에서 External Clock Hand(HAND_EXTERNAL)는 전체 CacheStore에 대해서 실행되기 때문에 모든 CacheStore에 대해 항상 동일한 값을 갖게 됩니다.

Internal Clock Hand/External Clock Hand는 Procedure Cache의 항목을 지울 때 비용을 기준으로 지우게 되며 비용이 0인 항목들을 지우게 됩니다. Clock Hand가 실행될 때, Cache의 항목들을 체크하여 비용이 0인 항목은 제거하고 비용이 0보다 큰 항목은 1/2만큼 감소시킵니다. 1/2만큼 감소된 결과로 비용이 0이 된 항목들(즉, 기존의 비용이 1이었던 항목들)은 다음 번 Clock Hand가 실행될 때 제거 대상이 됩니다.
 
Compiled Plan에 대한 비용은 기본적으로 다음과 같이 Compiled Plan을 만드는 데 소요된 I/O, CPU, 메모리를 기준으로 정해집니다. Cost 값은 아래의 기준에 의해서 각 항목들에 대한 Tick 값을 합산하여 결정되는데, 최대 31의 Tick 값을 가질 수 있습니다. 계산된 Tick 값은 Compiled Plan에 대한 비용 값으로 저장될 때 ‘2의 n승(2n)’으로 계산되어 저장되므로 Compiled Plan은 최대 2147483648(231)의 비용 값을 가질 수 있습니다. 참고적으로, 아래의 기준으로부터 I/O 비용이 가장 큰 비중을 차지(최대 19 ticks)하고 CPU 비용이 그 다음의 비중을 차지(최대 8 ticks)하며 메모리 비용이 제일 작은 비중을 차지(최대 4 ticks)함을 알 수 있습니다.
 
Cost = I/O cost + context switch cost (a measure of CPU cost) + memory cost
- Two I/Os cost 1 tick, with a maximum of 19 ticks.
- Two context switches cost 1 tick, with a maximum of 8 ticks.
- Sixteen memory pages (128 KB) cost 1 tick, with a maximum of 4 ticks.
 
Compiled Plan이 Cache에 저장될 때 type에 따라서 최대 비용이 제한되게 되는데, Adhoc은 비용이 0으로 설정되고 Prepared는 비용이 최대 256으로 제한되며 Proc은 제한 없이 계산된 비용이 그대로 사용됩니다. 이렇게 type별로 비용의 최대값을 다르게 함으로써 Procedure Cache에서 Clock Hand에 의해 Compiled Plan이 지워질 때, Adhoc이 우선적으로 지워지고 Prepared와 Proc의 순서로 지워지도록 합니다.
 
Compiled Plan에 대한 비용 값은 original_cost 값과 current_cost 값으로 설정되는데, Compiled Plan이 Cache에 처음 저장될 때 앞에서 설명 드린 방식에 의해서 계산된 비용 값(original_cost)이 current_cost로 설정됩니다. Clock Hand가 수행될 때마다 current_cost 값은 1/2씩 감소하게 되고 Compiled Plan이 재사용(usecounts가 증가)되게 되면 current_cost 값은 다시 original_cost 값으로 설정됩니다. Adhoc Compiled Plan의 경우에는 Prepared나 Proc과는 달리 original_cost와 current_cost가 0으로 시작하게 되며 재사용될 때마다 original_cost가 1부터 시작하여 2배씩 증가하여 최대 16까지 증가(original_cost가 1, 2, 4, 8, 16의 순으로 증가)하게 됩니다. 따라서, Adhoc의 경우에도 재사용이 많이 되는 Compiled Plan은 original_cost가 16이 될 수 있으며 좀더 오랫동안 Cache에 머물 수 있게 됩니다.
 
다음의 쿼리문을 실행하면 Procedure Cache에 저장된 모든 Compiled Plan에 대한 상세 정보를 확인해 볼 수 있습니다.
 

SELECT text, objtype, refcounts, usecounts, size_in_bytes, disk_ios_count, context_switches_count, 
           pages_allocated_count, original_cost, current_cost 
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
JOIN sys.dm_os_memory_cache_entries e
ON p.memory_object_address = e.memory_object_address 
WHERE cacheobjtype = 'Compiled Plan' AND type in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')

지금까지 SQL Server 2005/2008의 Procedure Cache의 구조와 동작 방식에 대해서 살펴봤습니다. Procedure Cache의 동작 방식을 고려했을 때 Adhoc 쿼리문을 과도하게 사용하는 것은 SQL Server의 성능에 전혀 도움이 되지 않음을 알 수 있습니다. 따라서, 실행 횟수가 많은 쿼리문은 Stored Procedure를 사용하는 것을 우선적으로 고려하고 Stored Procedure 사용이 여의치 않을 경우에는 Prepared 쿼리문을 사용하는 것이 필요합니다. 간혹 Stored Procedure를 여러 개의 쿼리문이 하나의 Batch를 구성할 경우에만 사용하는 것으로 생각하여 단일 쿼리문은 Adhoc 쿼리문으로 처리하는 경우가 많은데 이는 잘못된 생각입니다. 단일 쿼리문이라고 하더라도 Stored Procedure로 처리함으로써 SQL Server의 성능 향상을 가져올 수 있다는 점을 명심하기 바랍니다.
 
SQL Server 2005/2008에서 Procedure Cache에 실행 계획이 저장/재사용/제거되는 방식에 대해서는 다음의 문서로부터 좀더 자세한 내용을 참고할 수 있습니다.
SQL Server 2008 Books Online - Execution Plan Caching and Reuse
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
Plan Cache Internals

출처 : http://blog.daum.net/partsofmemory/28

 실행 계획 캐시(프로시저 캐시)

1. 정의

   SQL Server가 컴파일된 쿼리 실행 계획을 저장하는 저장소

   (성능 향상을 위해 재사용 목적으로 컴파일 된 실행 계획을 저장함)

2. 특징

   - 메모리에 저장

     SQL Server 인스턴스의 서비스 재시작 및 서버 재부팅시 초기화

   - 지속적 변경

     사용 빈도가 낮은 실행 계획 삭제됨

     저장된 실행 계획에 의해 사용되는 데이터베이스 개체 변경시, 해당 개체와 관련된 실행 계획도 삭제됨  

   - 모든 실행 계획이 저장되는 것은 아님

     RECOMPILE 옵션을 포함하는 실행 계획은 캐시에 저장되지 않음

     비용이 0인 실행 계획도 저장안함

   - SQL Server 2005 에서 동적 관리 개체(DMOs, Dynamic Management Objects)가 추가됨

     동적 관리 개체를 통해 실행 계획 캐시에 있는 실행 계획을 SHOW PLAN XML 형식으로 출력가능

3. DMV에서 실행 계획 캐시 확인시 주의사항

   - XQuery 관련 지식 필요

     실행 계획이 SHOW PLAN XML 형식으로 반환되기 때문   

   - 더티 리드 사용

     실행 계획 캐시에 대해 쿼리를 실행하는 경우 최소 트랜잭션 격리수준을 사용 권장

     (TRANSACTION ISOLATION LEVEL READ UNCOMMITTED)

   - 부하 테스트 추천

     운영환경의 실행 계획 캐시 에 대한 쿼리 실행전 시스템 부하에 대한 테스트 권장

     (테스트 환경이 있을 경우 거기서 먼저 해보기 바람)

   - 자동 수집 금지

     실행 계획 캐시의 정보를 반환하는 쿼리는 많은 비용을 소모하기 때문에 자동으로 수집하는 Job을 생성하지 않은것이 좋음

4. 부록 (실행 계획 재사용에 영향을 주는 요소)

   - 저장 프로시저가 수행되는 데이터베이스

   - 트리거 실행 시, 영향 받는 행 수(inserted, deleted 테이블의 행 수 변경에 영향)

   - INSTEAD OF 트리거 ? 0행, 1행 모두 “1-plan” 공유

   - AFTER 트리거 ? 1행 “1-plan”, 0행 or n행 “n-plan” (n>1)

   - Bulk Insert문 실행 계획 캐싱 안됨

   - 8KB 이상의 일괄 처리 명령은 캐싱 안됨

   - SET 옵션

   - 테이블의 소유자 지정 여부

   - sys.syscacheobjects 의 uid

   - CREATE PROCEDURE … WITH RECOMPILE 옵션

   - EXEC (프로시저 명) WITH RECOMPILE 옵션

   - 데이터 변경으로 인한 통계 갱신 발생

   - 임시 테이블 사용시 통계 갱신 발생

   - 오브젝트의 스키마 변경으로 인한 재컴파일

   - 데이터베이스 수준의 변경 작업

   - 재컴파일 임계값 (통계 정보 갱신)

     : 일반 테이블(RT : Recompile Threshold)

       Row수가 500을 넘지 않을 경우 500 Row 이상 변경시

       Row수가 500을 넘을 경우 500 + 전체 Row의 20% 변경시

     : 임시 테이블

       Row수가 500을 넘지 않을 경우 500 Row 이상 변경시

       Row수가 6을 넘을 경우 6 + 전체 Row의 20% 변경시

     : 테이블 변수

       통계 갱신으로 인한 재 컴파일이 발생하지 않음

출처 : http://kalva.tistory.com/entry/실행-계획-캐시프로시저-캐시

-- 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

 데이터베이스 미러링

구성에 필요한 서버들

1) 주(Principal) 서버

주 서버가 장애가 발생했을 경우 주 서버의 역할을 담당할 서버, 즉 미러 서버를 두기 위해 미러링을 한다.

동기화 과정

처음 동기화 : 주 서버의 데이터베이스를 미러 서버에 백업을 받아 복원하는 과정으로 이루어진다

그 이후 : 이후의 데이터 변경은 트랜잭션 로그를 미러 서버로 전달해 복원하는 과정으로 동기화가 유지된다

2) 미러(Mirror) 서버

주 서버 데이터베이스와 동기화된 데이터베이스 복사본을 갖는다

평상시에는 미러 서버의 데이터베이스에 사용자가 접근 할 수 없다

3) 모니터링 서버

자동 복구 모드를 사용하기 위해 필요하다

데이터베이스 미러링 구성

사전준비

1) 로그인 계정과 끝점 생성

미러링에 사용될 끝점(End Point)를 각 서버에 생성 후 이 끝점에 대한 연결 권한이 미러링에 포함된 모든 서버의 SQL Server 서비스 계정에게 주어져야 한다. 마법사를 사용하면 쉽다

2) 데이터베이스 복구 모델 변경

복구모델 = 전체(Full)

3) 데이터베이스 백업과 복원

주 서버에서 전체 백업과 로그 백업을 받아 미러 서버에 마지막 NORECOVERY 옵션을 사용해 복원해서 데이터베이스를 복구 중 상태로 두어야 한다

DB를 새로 구축하는 단계에서 이중화 구성을 하는 거라면 DB구축을 스크립트로 2대 다 하고 주 서버에서 로그백업만 받아서 미러 서버로 NORECOVERY로 복구하여 복원 중... 상태로 놓으려고 했지만, 전체백업을 하지 않으면 로그백업이 안되기 때문에 이 방법은 나만의 상상으로 끝나게 되었다.

4) 서버 단위의 자원 복사

미러링은 DB만 미러링이 되기 때문에 그 이외의 자원은 별도로 복사해야 한다

EX> 로그인 계정

본격적인 구성

1) 주 서버의 데이터베이스 복구 모델 → 전체(Full) 로 변경

ALTER DATABASE AdventureWorks2008 
SET RECOVERY FULL

2) 주 서버의 원본 데이터베이스 백업

-- 전체 백업
BACKUP DATABASE AdventureWorks2008
TO DISK = 'D:\BACKUP\AdventureWorks2008.bak'
WITH INIT -- 백업할때마다 덮어쓰는 옵션
GO

-- 로그 백업
BACKUP LOG AdventureWorks2008
TO DISK = 'D:\BACKUP\AdventureWorks2008_Log.bak'
WITH INIT
GO

3) 데이터베이스 복원 (미러 서버에서)

USE Master
GO

RESTORE DATABASE AdventureWorks2008
    FROM DISK = 'D:\BACKUP\AdventureWorks2008.bak'
    WITH MOVE 'AdventureWorks2008_DATA' TO 'D:\SmartData\AdventureWorks2008.mdf', 
    -- 본인은 한대의 컴퓨터에서 진행해서 경로가 겹쳐서 이동시킴
        MOVE 'AdventureWorks2008_Log' TO 'D:\SmartLog\AdventureWorks2008_Log.ldf',
        NORECOVERY -- 복원 중이 되도록 하는 옵션 지정
GO

RESTORE LOG AdventureWorks2008
    FROM DISK = 'D:\BACKUP\AdventureWorks2008_Log.bak'
    WITH NORECOVERY -- 복원 중이 되도록 하는 옵션 지정
GO

이 과정을 마치면 본인계정\SMART 인스턴스에서 (복원 중...)인 상태의 미러링 된 DB가 보여진다.

4) 데이터베이스 미러링 구성

① 미러링 구성을 위해 미러링에 포함된 주 서버, 미러 서버, 모니터 서버에 끝점(End Point)을 만들고, 이들 SQL Servier의 서비스 계정(?)이 이 끝점을 사용할 수 있도록 권한을 설정해 주어야 한다.

주 서버의 AdventureWorks2008를 우클릭 후 [태스크] - [미러] 를 선택 - [보안구성] 선택

② 안내 페이지가 나오는데 다음

③ 미러링 모니터 서버 포함 여부 선택(자신에게 맞게 사용)

자동 장애조치를 원하는 경우에는 모니터 서버를 포함시켜야 한다. 예를 누르자. 당장 안써도 나중에 추가 가능.

④ 구성할 서버 선택

필요한 경우 미러링 모니터에 서버에 대한 구성은 이후에 추가로 마법사나 쿼리문을 사용해 진행할 수 도 있다.
본인은 미러링 모니터 서버 인스턴스를 체크 해제(인스턴스가 2개뿐이라서ㅋ)

⑤ 주 서버 인스턴스 설정

포트가 기본적으로 5022 / 끝점 이름이 기본적으로 Mirroring [다음]

끝점으로 보내는 데이터 암호화는 체크 되어 있는 채로

⑥ 미러 서버 인스턴스 설정

대역\SMART 인스턴스로 설정하고 [연결] 클릭하여 연결한다.

포트 5023 (한대의 컴퓨터로 연습하는 경우 주 서버와 다르게 설정 / 실제로는 2대이상의 서버 이기 때문에 같게 해도 상관없다)

끝점이름 Mirroring

⑦ 모니터링 서버 인스턴스 설정

한 컴퓨터로 연습할 경우 포트만 1 높여준다

⑧ 서비스 계정 지정

각 서버의 SQL Server 서비스 계정은 미러링에 포함된 모든 서버의 미러링 관련 끝점을 사용할 수 있어야 한다. 즉, 주 서버의 SQL Server 서비스 계정이 미러 서버와 모니터 서버의 로그인 계정으로 등록되고 이들 서버의 끝점에 대한 사용 권한을 가져야 한다.

도메인 계정 : [도메인\서비스계정] 의 형태로

로컬 계정 : [서버이름\서비스계정] 의 형태로

대역에서는 대역내에서 쓰여지는 서비스계정이 있지만, 대역이 아닌 곳에서 서비스 계정을 설정하는 방법은 좀 다르다.

⑨ 마법사 완료

[마침]

성공 성공 뜨면 [닫기]

⑩ 미러링 시작 여부 확인

[미러링 시작]

현재 미러링을 구성하는 환경이 도메인이 아니면 정규화된 도메인 이름이 없다고 나온다. 그래도 [예] 버튼을 누르면 시작된다

⑪ 미러링 구성 완료

설정이 성공적으로 끝나면 데이터베이스 속성 - 미러링 - 아래쪽에 동기화됨: 데이터베이스가 완전히 동기화되었습니다. 라고 나온다

⑫ 데이터베이스 상태 확인

개체 탐색기에서

주 서버 : (주 서버, 동기화됨)

미러 서버 : (미러 서버, 동기화 됨 / 복원 중...)

이라고 뜬다

여기도 봅시다 : http://blog.daum.net/partsofmemory/26