http://symplog.tistory.com/266

https://blogs.msdn.microsoft.com/dbrowne/2013/10/02/creating-a-linked-server-for-oracle-in-64bit-sql-server/

exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1 

exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1 

exec sp_addlinkedserver N'MyOracle', 'Oracle', 'ORAOLEDB.Oracle', N'//172.16.8.119/xe', N'FetchSize=2000', ''

exec master.dbo.sp_serveroption @server=N'MyOracle', @optname=N'rpc out', @optvalue=N'true'

exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle', @useself=N'FALSE', @rmtuser=N'system', @rmtpassword='xxxxxx'     

exec ('select 1 a from dual') at MyOracle

exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1 

exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1 

exec sp_addlinkedserver N'MyOracle', 'Oracle', 'ORAOLEDB.Oracle', N'//192.168.10.80:1521/myora', N'FetchSize=5000', ''

exec master.dbo.sp_serveroption @server=N'MyOracle', @optname=N'rpc out', @optvalue=N'true'

exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle', @useself=N'FALSE', @rmtuser=N'system', @rmtpassword='head1ton' 

exec ('select 1 a from dual') at MyOracle

FROM

ON

JOIN

WHERE

GROUP BY

WITH CUBE | WITH ROLLUP

HAVING

SELECT

DISTINCT

ORDER BY

TOP

응용 프로그램별 권한 설정에서 CLSID가 
{806835AE-FD04-4870-A1E8-D65535358293}
이고 APPID가 
{EE4171E6-C37E-4D04-AF4C-8617BC7D4914}인 COM 서버 응용 프로그램에 대한 로컬 활성화 사용 권한을
응용 프로그램 컨테이너 사용할 수 없음 SID(사용할 수 없음)에서 실행 중인 주소 LocalHost(LRPC 사용)의 
사용자 NT SERVICE\SQLSERVERAGENT SID(S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430)에게 부여하지 않았습니다. 
구성 요소 서비스 관리 도구를 사용하여 이 보안 권한을 수정할 수 있습니다.

(영문)

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID 
{806835AE-FD04-4870-A1E8-D65535358293}
 and APPID 
{EE4171E6-C37E-4D04-AF4C-8617BC7D4914}
 to the user MyDomain\MyLogin SID (S-1-5-21-1688516961-3049443350-111816674-1126) from address LocalHost (Using LRPC). This security permission can be modified using the Component Services administrative tool.

구성요소 서비스 -> 컴퓨터 -> 내컴퓨터 -> DCOM구성에서 Microsoft SQL Server Integration Services 12.0를 찾습니다.

응용 프로그램 ID가 {EE4171E6-C37E-4D04-AF4C-8617BC7D4914} 인지 확인합니다.

속성 -> 보안 -> 시작 및 활성화 권한에서 편집 버튼을 클릭합니다.

추가버튼을 클릭하고 계정을 추가합니다.

위 오류에서 보면 계정은 NT SERVICE\SQLSERVERAGENT가 되어야 겠네요.

로컬 시작과 로컬 활성화 권한을 부여합니다.

확인 버튼을 클릭하고 마무리 합니다.

NT SERVICE\SQLSERVERAGENT 계정 입력방법은 처음 답변했던 내용 대로입니다.

선택할 개체이름을 입력하세요 부분에서 NT SERVICE\SQLSERVERAGENT를 직접 입력해주시면 됩니다.

고급 버튼을 눌러서 검색해봐도 NT SERVICE\SQLSERVERAGENT 계정이 나오지 않습니다. 직접 입력해야 합니다.

USE [master]

GO

CREATE LOGIN [GulsAdmin] WITH PASSWORD=N'!@#dpaqhTld04', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE [Game]

GO

CREATE USER [GulsAdmin] FOR LOGIN [GulsAdmin]

GO

USE [Game]

GO

EXEC sp_addrolemember N'db_owner', N'GulsAdmin'

USE [GameInfo]

GO

CREATE USER [GulsAdmin] FOR LOGIN [GulsAdmin]

GO

USE [GameInfo]

GO

EXEC sp_addrolemember N'db_owner', N'GulsAdmin'

USE [Guild]

GO

CREATE USER [GulsAdmin] FOR LOGIN [GulsAdmin]

GO

USE [Guild]

GO

EXEC sp_addrolemember N'db_owner', N'GulsAdmin'

USE [GuildInfo]

GO

CREATE USER [GulsAdmin] FOR LOGIN [GulsAdmin]

GO

USE [GuildInfo]

GO

EXEC sp_addrolemember N'db_owner', N'GulsAdmin'

USE [Log]

GO

CREATE USER [GulsAdmin] FOR LOGIN [GulsAdmin]

GO

USE [Log]

GO

EXEC sp_addrolemember N'db_owner', N'GulsAdmin'

USE [Login]

GO

CREATE USER [GulsAdmin] FOR LOGIN [GulsAdmin]

GO

USE [Login]

GO

EXEC sp_addrolemember N'db_owner', N'GulsAdmin'

USE [LoginInfo]

GO

CREATE USER [GulsAdmin] FOR LOGIN [GulsAdmin]

GO

USE [LoginInfo]

GO

EXEC sp_addrolemember N'db_owner', N'GulsAdmin'

USE [Rank]

GO

CREATE USER [GulsAdmin] FOR LOGIN [GulsAdmin]

GO

USE [Rank]

GO

EXEC sp_addrolemember N'db_owner', N'GulsAdmin'

USE [Tool]

GO

CREATE USER [GulsAdmin] FOR LOGIN [GulsAdmin]

GO

USE [Tool]

GO

EXEC sp_addrolemember N'db_owner', N'GulsAdmin'

USE [master]

GO

CREATE LOGIN [GulsWeb] WITH PASSWORD=N'qordjqvlftn$#@!', DEFAULT_DATABASE=[Guild], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE [Game]

GO

CREATE USER [GulsWeb] FOR LOGIN [GulsWeb] WITH DEFAULT_SCHEMA = DBO;

GO

USE [Game]

GO

GRANT EXECUTE TO GulsWeb;

GRANT SELECT TO GulsWeb;

USE [GameInfo]

GO

CREATE USER [GulsWeb] FOR LOGIN [GulsWeb] WITH DEFAULT_SCHEMA = DBO;

GO

USE [GameInfo]

GO

GRANT EXECUTE TO GulsWeb;

GRANT SELECT TO GulsWeb;

USE [Guild]

GO

CREATE USER [GulsWeb] FOR LOGIN [GulsWeb] WITH DEFAULT_SCHEMA = DBO;

GO

USE [Guild]

GO

GRANT EXECUTE TO GulsWeb;

GRANT SELECT TO GulsWeb;

USE [GuildInfo]

GO

CREATE USER [GulsWeb] FOR LOGIN [GulsWeb] WITH DEFAULT_SCHEMA = DBO;

GO

USE [GuildInfo]

GO

GRANT EXECUTE TO GulsWeb;

GRANT SELECT TO GulsWeb;

USE [Log]

GO

CREATE USER [GulsWeb] FOR LOGIN [GulsWeb] WITH DEFAULT_SCHEMA = DBO;

GO

USE [Log]

GO

GRANT EXECUTE TO GulsWeb;

GRANT SELECT TO GulsWeb;

USE [Login]

GO

CREATE USER [GulsWeb] FOR LOGIN [GulsWeb] WITH DEFAULT_SCHEMA = DBO;

GO

USE [Login]

GO

GRANT EXECUTE TO GulsWeb;

GRANT SELECT TO GulsWeb;

USE [LoginInfo]

GO

CREATE USER [GulsWeb] FOR LOGIN [GulsWeb] WITH DEFAULT_SCHEMA = DBO;

GO

USE [LoginInfo]

GO

GRANT EXECUTE TO GulsWeb;

GRANT SELECT TO GulsWeb;

USE [Rank]

GO

CREATE USER [GulsWeb] FOR LOGIN [GulsWeb] WITH DEFAULT_SCHEMA = DBO;

GO

USE [Rank]

GO

GRANT EXECUTE TO GulsWeb;

GRANT SELECT TO GulsWeb;

USE [Tool]

GO

CREATE USER [GulsWeb] FOR LOGIN [GulsWeb] WITH DEFAULT_SCHEMA = DBO;

GO

USE [Tool]

GO

GRANT EXECUTE TO GulsWeb;

GRANT SELECT TO GulsWeb;

USE master

GO

--1. tempdb의논리파일이름확인

SELECT name, physical_name, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb')

GO

--2. ALTER DATABASE 사용하여파일위치변경

ALTER DATABASE tempdb

MODIFY FILE(NAME = tempdev, FILENAME = 'C:\TempDB\tempdb.mdf')

ALTER DATABASE tempdb

MODIFY FILE(NAME = templog, FILENAME = 'C:\TempDB\templog.ldf')

GO

--3. Processor만큼파일분할및사이즈변경및파일사이즈, 증가옵션설정

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 20480KB , FILEGROWTH = 10240KB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'C:\TempDB\tempdev2.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'C:\TempDB\tempdev3.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = N'C:\TempDB\tempdev4.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev5', FILENAME = N'C:\TempDB\tempdev5.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev6', FILENAME = N'C:\TempDB\tempdev6.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev7', FILENAME = N'C:\TempDB\tempdev7.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev8', FILENAME = N'C:\TempDB\tempdev8.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )

GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 163840KB )

GO

--4.SQL Server 서비스 재시작.

--5.SQL Server 서비스가 시작된것을확인후 정사 이동 확인 

SELECT name, physical_name, state_desc 

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb')

GO

♬ 하드 디스크 병목 현상

-LogicalDisk\% Free Space 

-PhysicalDisk\% Idle Time

-PhysicalDisk\Avg. Disk Sec/Read

-PhysicalDisk\Avg. Disk Sec/Write

-PhysicalDisk\Avg. Disk Queue Length

-Memory\Cache Bytes

♬ 메모리 병목 현상

-Memory\% Committed Bytes in Use

-Memory\% Available Mbytes

-Memory\Free System Page Table Entries

-Memory\Pool Non-Paged Bytes

-Memory\Pool Paged Bytes

-Memory\Pages per Second

♬ 프로세서 병목 현상

-Processor\% Processor Time

-Processor\% User Time

-Processor\% Interrupt Time

-System\Processor Queue Length

♬ 네트워크 병목 현상

-Network Interface\Bytes Total/Sec

-Network Interface\Output Queue Length

♬ 프로세스 병목 현상

-Process\Handle Count 

-Process\Thread Count

-Process\Private Bytes 

https://technet.microsoft.com/ko-kr/magazine/2008.08.pulse.aspx

성능 관련 기초 이론
※ 모델링 - 시스템에 영향을 미칠 수 있는 요소들을 추출하여 이들의 연관관계를 도식화하는 것
- Queuing Network Model(QNM)
  : 1976년 Jeffrey P. Buzen이 제안, 1978년 Peter J.Denning&Jeffrey P. Buzen에 의해 확장
  : 컴퓨터 시스템의 대기 행렬을 의미하는 큐들의 네트워크
  : 다중 서버에 대한 모델링이 가능
  : 'Operational Law'에 의해 분석

- 단일 서버 QNM
  : 서버가 하나인 경우 사용(2Tier)
  : 사용자가 서버에 Request하는 경우, Queue를 거쳐 서비스 요청
  : Parameters - 부하 강도(사용자의 호출 빈도), 서비스 요구량(사용자 요청을 처리하는 데 소요되는 시간)
  : Operation Law
    → 이용률(Utilization): 전체 측정 시간 대비 서비스 시간의 비율
    → 응답시간(Response Time): 사용자가 서버에서 머물렀던 평균 시간(서비스 시간+대기 시간)
    → 큐 길이(Queue Length): 서버에 머무르고 있는 총 사용자 수(서비스 + 대기)
    → 처리량(Throughtput): 서버가 서비스 처리 종료 후 서버를 출발하는 비율
  : Operation Law의 의미
    → 이용률(Utilization): 대상 서버에 대한 가용 자원 대비에 현재 이용되고 있는 정도
                            0≤이용률≤1, 1이 되면 서버에 병목현상 발생, 부하강도가 증가하면 이용률도 증가
    → 응답시간(Response Time): 사용자가 서비스를 요청하고 받을 때까지 걸리는 시간
                                이용률(부하강도)의 증가에 따라 기하급수적으로 증가
    → 큐 길이(Queue Length): 큐를 점유하고 있는 사용자 수
                              이용률(부하강도)의 증가에 따라 기하급수적으로 증가
    → 처리량(Throughtput): 대상 서버에서 요청 건수의 대비 처리량
                            이용률의 증가에 따라 같이 증가, 이용률이 1이 되면 일정 수준 유지

- 다중 서버 QNM
  : 서버가 여러 개일 경우 사용(3Tier over)
  : 사용자가 서버에 Request하는 경우, Web Server → WAS → DB 서버 등을 거쳐 서비스 요청 및 처리
  : Parameters - 부하 강도(사용자의 호출 빈도), 실행 시간(각 개별 서버 별 요청 처리에 소요되는 시간)
                 ※ 부하 강도는 시스템의 종류에 따라 각각 다르게 정의될 수 있음
                    (ex. TP-Monitor: 요청건수, Batch: 동시 사용자, 웹 기반, C/S 시스템: 동시 사용자 수&평균 Think time 등)
  : Operation Law는 단일 서버 QNM과 유사

- Operation Law
  : QNM에서 서버의 여러 특성들을 파라미터화하여 주요 평가 항목들을 추출할 때 사용하는 주요 공식의 기반
  : 파라미터화 된 기본 항목들은 측정 도구에 의해 측정 가능

  ※ 기본 항목
    → T: 대상 시스템을 모니터링한 총 소요 시간(초)
       A: T시간 동안 시스템에 요청된 총 요청 건수(건)
       C: T시간 동안 시스템에서 처리된 총 처리 건수(건)
       B: 시스템이 요청을 처리하기 위해 사용된 총 서비스 시간(Bysu Time, 초)
       W: 시스템이 요청을 처리하기 위해 사용된 총 응답 시간(서비스 시간 + 대기 시간)
       Z: 사용자가 다음 요청을 준비하기 위해 소요된 Think time(초)
       N: T시간 동안 시스템을 사용하고 있는 사용자 수(명)

  ※ 도출되는 성능 수치 항목
    → 호출비율(Arrival Rate): 단위 시간당 시스템으로 들어오고 있는 건수(λ=A/T)
    → 처리량(Throughtput): 단위 시간당 시스템에서 처리 되고 있는 건수(X=C/T)
    → 이용률(Utilization): 총 소요 시간 대비의 총 서비스 시간 비율(U=B/T)
    → 평균 서비스 시간(Service Time): 요청을 처리하기 위해 소요된 평균 서비스 시간(S=B/C)
    → 응답 시간(Response Time): 요청을 처리하기 위해 소요된 평균 응답 시간(R=W/C)

- Utilization Law
  : U=B/T=(B/C)*(C/T)=S*X
  : 총 소요 시간 대비의 총 서비스 시간 비율 = 단위 시간당 시스템의 처리 건수 * 처리 건당 소요되는 평균 서비스 시간

- Little's Law
  : 서버상의 큐 길이를 설정할 때 참조할 수 있는 공식
  : Q(큐의 길이) = W/T = (W/C)*(C/T) = R*X

- Response Time's Law
  : X(처리량) = 총 호출 건수/총 소요 시간 = N*(T*(R+Z))/T = N*(R+Z)

- Forced-Flow Law
  : 개별 리소스와 전체 시스템 사이의 상관 관계를 보여주는 공식
  : C - 전체 시스템에 대한 처리 건수
    Ck- 개별 리소스에 대한 처리 건수(k번째 리소스)
    Vk- 개별 리소스에 대한 Visit Ratio(k번째 리소스)
    Dk- 개별 리소스에 대한 서비스 요구량(k번째 리소스)

    Vk = Ck/C(k번째 리소스)
    Xk = Vk*X (k번째 리소스에 대한 처리량)
    Uk = Xk*Sk (k번째 리소스에 대한 사용율)
       = X*Vk*Sk
       = X*Dk

- 처리량 상관관계
  : Forced-Flow Law에 의해 도출된 각 리소스에 대한 처리량을 통해 전체 시스템의 처리량을 정의할 수 있음
  : 전체 시스템을 구성하는 n개의 독립 서버의 각 처리량을 Tn이라할 때, 다음과 같이 처리량을 도출할 수 있음
  ※ 전체 Throughput(단, 각각의 서버가 동일한 리소스에 의해 병목을 직면하고 있는 경우)
    T = 1/(1/T1+1/T2+1/T3+1/T4+1/T5+....+1/Tn) = 1/(∑(1/Tn))
  ※ Throughput of Block A&B = 1/(1/a+1/b)

- 병목 지점 분석
  : 임계 지점 부근의 처리량(X)과 응답 시간(R)의 상관 관계
    → X(N)≤min{1/Dmax, N/(D+Z)}
       R(N)≥max{D, NDmax-Z}
       where Dmax: 병목 지점의 서비스 시간
             D=∑Dn
       전제조건: 어떠한 리소스라도 이용률은 1이하이다.
                 N명의 사용자를 처리하고 있는 시스템의 응답 시간은 1명의 사용자를 처리하는 시스템보다 더 빠를 수 없다

  : 처리량의 경우 입계 사용자 수 근방까지 시스템의 용청을 원할하게 처리, 포화 지점 이후 병목 발생 수평을 유지
  : 임계 사용자 수 지점의 처리량은 해당 시스템의 최대 처리량 or 임계 처리량
  : 응답 시간은 임계점 이전은 일정하게 유지, 포화 지점 이후 병목 발생으로 응답시간 증가

===================================================================================================================

각종 공식들에 눈돌아가네요...@_@;

DB와는 직접적인 관련이 없습니다만... 알아두시면 좋으실 듯 해서 두번째 정리본 올립니당~^^ㅋ

출처 : http://cafe.naver.com/prodba/3664

-- 출처 : SQLTAG BOOK2 

CREATE TABLE dbo.TEST_RESOURCE

( COL1 int NOT NULL

, COL2 varchar(100) NULL

, COL3 smallint NOT NULL

, COL4 varchar(10) SPARSE NULL

, COL5 char(10) NOT NULL

, COL6 char(30) NULL

, COL7 varchar(max) NOT NULL

)

GO

INSERT dbo.TEST_RESOURCE

( COL1

, COL2

, COL3

, COL4

, COL5

, COL6

, COL7

)

VALUES

(1,'A',1,'AA','A','A',REPLICATE(CONVERT(varchar(max),'A'),10000)),

(2,'BB',2,'BBB','BB','BB',REPLICATE(CONVERT(varchar(max),'A'),300)),

(3,'CCC',30,'CCCC','CCC','CCC',REPLICATE(CONVERT(varchar(max),'A'),10))

GO

ALTER TABLE dbo.TEST_RESOURCE

ADD CONSTRAINT PK_TEST_RESOURCE

PRIMARY KEY CLUSTERED

( COL3

)

GO

CREATE UNIQUE NONCLUSTERED INDEX UNCIDX_TEST_RESOURCE

ON dbo.TEST_RESOURCE

( COL1

)

GO

-- 1. Partition Id 확인(PK_TEST_RESOURCE)

SELECT %%ROWDUMP%% AS [ROWDUMP]

, sys.fn_GetRowsetIdFromRowDump(%%ROWDUMP%%) AS PARITION_ID

FROM dbo.TEST_RESOURCE

GO

-- 2. Partition Id 확인(UNCIDX_TEST_RESOURCE)

SELECT %%ROWDUMP%% AS [ROWDUMP]

, sys.fn_GetRowsetIdFromRowDump(%%ROWDUMP%%) AS PARITION_ID

FROM dbo.TEST_RESOURCE WITH(INDEX(UNCIDX_TEST_RESOURCE))

GO

-- 3. 가변 길이 칼럼의 행 정보 확인

SELECT %%ROWDUMP%% AS [ROWDUMP]

, RDC.*

FROM dbo.TEST_RESOURCE AS TR

CROSS APPLY

sys.fn_RowDumpCracker(%%ROWDUMP%%) AS RDC

GO

-- 3. File Id, Page Id, Slot Id, KeyHashValue 확인(PK_TEST_RESOURCE)

SELECT %%PHYSLOC%% AS PHYSLOC

, sys.fn_PhysLocFormatter(%%PHYSLOC%%) AS [FILEID:PAGEID:SLOTID]

, PLC.*

, %%LOCKRES%% AS [LOCKRES]

FROM dbo.TEST_RESOURCE AS TR

CROSS APPLY

sys.fn_PhysLocCracker(%%PHYSLOC%%) AS PLC

GO

-- 4. File Id, Page Id, Slot Id, KeyHashValue 확인(UNCIDX_TEST_RESOURCE)

SELECT %%PHYSLOC%% AS PHYSLOC

, sys.fn_PhysLocFormatter(%%PHYSLOC%%) AS [FILEID:PAGEID:SLOTID]

, PLC.*

, %%LOCKRES%% LOCKRES

FROM dbo.TEST_RESOURCE AS TR WITH(INDEX(UNCIDX_TEST_RESOURCE))

CROSS APPLY

sys.fn_PhysLocCracker(%%PHYSLOC%%) AS PLC

GO

--1단계 : 가장 많은 리컴파일 데이터베이스 찾기

CREATE EVENT SESSION [XE_Database_Recompile_Histogram] ON SERVER

ADD EVENT sqlserver.sql_statement_recompile(

ACTION(sqlserver.database_id)

WHERE ([package0].[counter]<(2000)))

ADD TARGET package0.histogram(

SET filtering_event_name = N'sqlserver.sql_statement_recompile'

, slots = (100)

, source = N'sqlserver.database_id')

WITH (

MAX_MEMORY = 4096 KB

, EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS

, MAX_DISPATCH_LATENCY = 5 SECONDS

)

GO

-- 2단계 : 어떤 이유로 리컴파일이 가장 많이 발생하는지 찾기

CREATE EVENT SESSION [XE_Database_Recompile_Histogram_Cause] ON SERVER 

ADD EVENT sqlserver.sql_statement_recompile(

    WHERE (

[package0].[less_than_uint64]([package0].[counter],(2000)) 

AND [package0].[equal_uint64]([sqlserver].[database_id],(54)))) 

ADD TARGET package0.histogram(

SET filtering_event_name=N'sqlserver.sql_statement_recompile'

, source=N'recompile_cause'

, source_type=(0))

WITH (

MAX_MEMORY=4096 KB

, EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS

, MAX_DISPATCH_LATENCY=5 SECONDS

, MAX_EVENT_SIZE=0 KB

, MEMORY_PARTITION_MODE=NONE

, TRACK_CAUSALITY=OFF

, STARTUP_STATE=OFF

)

GO

-- 3단계 : 2단계에서 찾은 원인에 해당하는 쿼리 찾기

CREATE EVENT SESSION [XE_Database_Recompile_Histogram_Cause_Statement] ON SERVER 

ADD EVENT sqlserver.sql_statement_recompile(

    ACTION(sqlserver.sql_text,sqlserver.tsql_stack)

    WHERE (

[package0].[counter]<(2000) AND [sqlserver].[database_id]=(10)

AND [recompile_cause]=(11))) 

ADD TARGET package0.event_file(

SET filename=N'XE_Database_Recompile_Histogram_Cause_Statement'

, max_file_size=(100))

WITH (

MAX_MEMORY=4096 KB

, EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS

, MAX_DISPATCH_LATENCY=5 SECONDS

, MAX_EVENT_SIZE=0 KB

, MEMORY_PARTITION_MODE=NONE

, TRACK_CAUSALITY=OFF

, STARTUP_STATE=OFF

)

GO

-- 시작

ALTER EVENT SESSION [XE_Database_Recompile_Histogram] ON SERVER

STATE = START

GO

ALTER EVENT SESSION [XE_Database_Recompile_Histogram_Cause] ON SERVER

STATE = START

GO

ALTER EVENT SESSION [XE_Database_Recompile_Histogram_Cause_Statement] ON SERVER

STATE = START

GO

-- 종료

ALTER EVENT SESSION [XE_Database_Recompile_Histogram] ON SERVER

STATE = STOP

GO

ALTER EVENT SESSION [XE_Database_Recompile_Histogram_Cause] ON SERVER

STATE = STOP

GO

ALTER EVENT SESSION [XE_Database_Recompile_Histogram_Cause_Statement] ON SERVER

STATE = STOP

GO

-- 삭제

DROP EVENT SESSION [XE_Database_Recompile_Histogram] ON SERVER

GO

DROP EVENT SESSION [XE_Database_Recompile_Histogram_Cause] ON SERVER

GO

DROP EVENT SESSION [XE_Database_Recompile_Histogram_Cause_Statement] ON SERVER

GO

-- 1단계 XQuery를 이용한 분석

SELECT db_name(xnode.xdata.query('.').value('(Slot/value)[1]','varchar(max)')) AS database_name

, xnode.xdata.query('.').value('(Slot/@count)[1]','varchar(max)') as recompile_count

FROM (

SELECT CAST(target_data as xml) as target_data

FROM sys.dm_xe_sessions as s

INNER JOIN sys.dm_xe_session_targets as t

ON s.address = t.event_session_address

WHERE s.name = 'XE_Database_Recompile_Histogram') as a

CROSS APPLY a.target_data.nodes('HistogramTarget/Slot') as xnode (xdata)

GO

-- 2단계 XQuery를 이용한 분석

;WITH cte_xdata_results as (

SELECT xnode.xdata.query('.').value('(Slot/value)[1]','varchar(max)') as recompile_cause

, xnode.xdata.query('.').value('(Slot/@count)[1]','varchar(max)') as recompile_count

FROM (

SELECT CAST(target_data as xml) as target_data

FROM sys.dm_xe_sessions as s

INNER JOIN sys.dm_xe_session_targets as t

ON s.address = t.event_session_address

WHERE s.name = 'XE_Database_Recompile_Histogram_Cause'

) as a

CROSS APPLY a.target_data.nodes('HistogramTarget/Slot') as xnode (xdata)

)

SELECT map_value as statement_recompile_cause

, recompile_count

FROM cte_xdata_results as x

INNER JOIN sys.dm_xe_map_values as mv

ON x.recompile_cause = mv.map_key

WHERE mv.name = 'statement_recompile_cause';

GO

-- 3단계 분석

/* 

tsql_stack 의 handle를 이용해 프로덕션 데이터베이스에서 조회 할 수 있음.(결과가 모두 출력되지 않으면

도구 > 옵션 > 쿼리 결과 > SQL Server > 텍스트로 결과 표시 > 각 열에 표시할 최대 문자 수를 적당하게 변경 후 SSMS 재시작)

*/

SELECT *

FROM sys.dm_exec_sql_text(0x03002E005D05B6215B3BFB00A2A2000001000000000000000000000000000000000000000000000000000000);

;WITH cte_dm_xe_sessions AS (

SELECT xnode.xdata.query('./data').value('(data[@name="recompile_cause"]/text)[1]','varchar(max)') AS recompile_cause

, db_name(xnode.xdata.query('./data').value('(data[@name="source_database_id"]/value)[1]','varchar(max)')) AS [db_name]

, xnode.xdata.query('./data').value('(data[@name="object_type"]/text)[1]','varchar(max)') AS object_type

, xnode.xdata.query('./action').value('(action[@name="sql_text"]/value)[1]','varchar(max)') AS sql_text

, xnode.xdata.query('./action/value/frames') AS tsql_stack_frames

FROM ( SELECT CONVERT(xml,target_data) AS target_data

FROM sys.dm_xe_sessions AS s

INNER JOIN

sys.dm_xe_session_targets AS t

ON s.[address] = t.event_session_address

WHERE s.name = 'XE_Database_Recompile_Histogram_Cause_Statement'

AND target_name = 'ring_buffer'

) AS a

CROSS APPLY

a.target_data.nodes('RingBufferTarget/event') xnode (xdata)

)

SELECT a.db_name

, a.recompile_cause

, a.object_type

, a.sql_text

, [level] AS frame_level

, b.[text]

, SUBSTRING

( b.[text]

, (offsetStart/2)+1

, (( CASE offsetEnd

WHEN -1 THEN DATALENGTH(b.[text])

ELSE offsetEnd

END - offsetStart

)/2)+1

) AS stmt

FROM ( SELECT a.*

, ynode.ydata.query('.').value('(frame/@level)[1]','varchar(max)') AS [level]

, ynode.ydata.query('.').value('(frame/@handle)[1]','varchar(max)') AS handle

, ynode.ydata.query('.').value('(frame/@offsetStart)[1]','varchar(max)') AS offsetStart

, ynode.ydata.query('.').value('(frame/@offsetEnd)[1]','varchar(max)') AS offsetEnd

FROM cte_dm_xe_sessions AS a

CROSS APPLY

a.tsql_stack_frames.nodes('frames/frame') ynode (ydata)

) AS a

OUTER APPLY

sys.dm_exec_sql_text (CONVERT(varbinary(max),handle,1)) b

GO

-- tempdb 의 I/O 관련 장애처리

CREATE DATABASE DBA

GO

USE DBA

GO

-- DROP TABLE dbo.TBL_ANALYSIS_MONITOR_TEMPDB

-- GO

CREATE TABLE dbo.TBL_ANALYSIS_MONITOR_TEMPDB

(

Event_Date datetime -- 로깅 시작

, Event_Idx int identity -- 중복방지 자동증가값

, Task_UserObj_Alloc_Cnt int -- 유저오브젝트 페이지 할당수

, Task_UserObj_Dealloc_Cnt int -- 유저오브젝트 페이지 해제수

, Task_Internal_Alloc_Cnt int -- 내부오브젝트 페이지 할당수

, Task_Internal_Dealloc_Cnt int -- 내부오브젝트 페이지 해제수

, Degree_of_Parallelism smallint -- 병렬처리수준

, Database_ID int -- DB_ID

, User_ID int -- 실행 유저 ID

, SQL_Handle varbinary(64) -- 후에 추적 가능하도록 로깅

, Plan_Handle varbinary(64) -- 후에 추적 가능하도록 로깅

, SQL_Text100 nvarchar(100) -- 핸들 조인 안될경우 대비

, CONSTRAINT CPK__TBL_ANALYSIS_MONITOR_TEMPDB__01 PRIMARY KEY CLUSTERED (Event_Date, Event_Idx)

);

GO

IF object_id('dbo.USP_TEMP_IO_GETTER') IS NULL

EXEC ('CREATE PROC dbo.USP_TEMP_IO_GETTER AS SELECT 1')

GO

ALTER PROCEDURE dbo.USP_TEMP_IO_GETTER

(

@pRoop_Cnt int = 60

)

AS

BEGIN

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @vDate_Time datetime, @vCnt int = 0

WHILE @vCnt < @pRoop_Cnt

BEGIN

SELECT @vDate_Time = GETDATE(), @vCnt += 1;

WITH cteSU AS (

SELECT TOP 10

session_id

, request_id

, SUM(user_objects_alloc_page_count) as [userobj_alloc_cnt]

, SUM(user_objects_dealloc_page_count) as [userobj_dealloc_cnt]

, SUM(internal_objects_alloc_page_count) as [internal_alloc_cnt]

, SUM(internal_objects_dealloc_page_count) as [internal_dealloc_cnt]

, COUNT(session_id) as [degree_of_parallelism]

FROM tempdb.sys.dm_db_task_space_usage

WHERE database_id = 2

AND (user_objects_alloc_page_count > 0 OR internal_objects_alloc_page_count > 0)

AND session_id <> @@spid

AND session_id > 50

GROUP BY session_id, request_id

ORDER BY SUM(user_objects_alloc_page_count)

+ SUM(internal_objects_alloc_page_count) DESC

)

INSERT INTO DBA.dbo.TBL_ANALYSIS_MONITOR_TEMPDB

(Event_Date

, Task_UserObj_Alloc_Cnt

, Task_UserObj_Dealloc_Cnt

, Task_Internal_Alloc_Cnt

, Task_Internal_Dealloc_Cnt

, Degree_of_Parallelism

, Database_ID

, User_ID

, SQL_Handle

, Plan_Handle

, SQL_Text100

)

SELECT GETDATE() AS event_date

, A.userobj_alloc_cnt

, A.userobj_dealloc_cnt

, A.internal_alloc_cnt

, A.internal_dealloc_cnt

, A.degree_of_parallelism

, ER.database_id

, ER.user_id

, ER.sql_handle

, ER.plan_handle

, LEFT(ST.text, 100)

FROM cteSU AS A 

INNER JOIN sys.dm_exec_requests AS ER

ON ER.session_id = A.session_id AND ER.request_id = A.request_id

OUTER APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST

WAITFOR DELAY '00:00:01'

END

END;

GO

SELECT ST.text as [SQL_Text]

, QP.query_plan as [Plan_XML]

, AMT.Max_Task_UserObj_Alloc_Cnt

, AMT.Max_Task_Internal_Alloc_Cnt

, AMT.Database_ID

, AMT.SQL_Text100

FROM (

SELECT TOP 10

SQL_Handle, Plan_Handle, Database_ID

, MAX(Task_UserObj_Alloc_Cnt) as [Max_Task_UserObj_Alloc_Cnt]

, MAX(Task_Internal_Alloc_Cnt) as [Max_Task_Internal_Alloc_Cnt]

, SQL_Text100

FROM DBA.dbo.TBL_ANALYSIS_MONITOR_TEMPDB

GROUP BY SQL_Handle, Plan_Handle, Database_ID, SQL_Text100

ORDER BY MAX(Task_UserObj_Alloc_Cnt) + MAX(Task_Internal_Alloc_Cnt) DESC

) AS AMT 

OUTER APPLY sys.dm_exec_sql_text(AMT.sql_handle) as ST

OUTER APPLY sys.dm_exec_query_plan(AMT.Plan_Handle) as QP

GO

EXEC dbo.USP_TEMP_IO_GETTER;

-- Read I/O

CREATE EVENT SESSION [MONITOR_FILEIO_READ_HISTOGRAM]

ON SERVER

ADD EVENT sqlserver.file_read

( SET collect_path = (1)

WHERE

( -- tempdb 드라이브 path를 지정

[sqlserver].[like_i_sql_unicode_string]([path],N'E:\%')

)

)

ADD TARGET package0.histogram

( SET filtering_event_name = N'sqlserver.file_read'

, source = N'path'

, source_type = (0)

)

WITH

( max_memory = 4096 kb

, event_retention_mode = allow_single_event_loss

, max_dispatch_latency = 30 seconds

, max_event_size = 0 kb

, memory_partition_mode = none

, track_causality = off

, startup_state = off

)

GO

-- Write I/O

CREATE EVENT SESSION [MONITOR_FILEIO_WRITTEN_HISTOGRAM]

ON SERVER

ADD EVENT sqlserver.file_written

( SET collect_path = (1)

WHERE

( -- tempdb 드라이브 path를 지정

[sqlserver].[like_i_sql_unicode_string]([path],N'E:\%')

)

)

ADD TARGET package0.histogram

( SET filtering_event_name = N'sqlserver.file_written'

, source = N'path'

, source_type = (0)

)

WITH

( max_memory = 4096 kb

, event_retention_mode = allow_single_event_loss

, max_dispatch_latency = 30 seconds

, max_event_size = 0 kb

, memory_partition_mode = none

, track_causality = off

, startup_state = off

)

GO

SELECT TOP 10

TSU.session_id

, TSU.request_id

, TSU.task_alloc_cnt

, TSU.task_dealloc_cnt

, EST.text as [query_text]

, EQP.query_plan as [plan_xml]

FROM (

SELECT session_id

, request_id

, SUM(internal_objects_alloc_page_count) as [task_alloc_cnt]

, SUM(internal_objects_dealloc_page_count) as [task_dealloc_cnt]

FROM sys.dm_db_task_space_usage

GROUP BY session_id, request_id --병렬처리 그룹화

) as [TSU]

INNER JOIN sys.dm_exec_requests as [ER]

ON TSU.session_id = ER.session_id

AND TSU.request_id = ER.request_id

OUTER APPLY sys.dm_exec_sql_text (ER.sql_handle) as [EST]

OUTER APPLY sys.dm_exec_query_plan (ER.plan_handle) as [EQP]

WHERE TSU.session_id <> @@spid

ORDER BY TSU.task_alloc_cnt DESC

-- 버전스토오 사용시 TEMPDB 사용량 확인

SELECT TOP 10

SDT.session_id

, SDT.elapsed_time_seconds

, SDT.is_snapshot

, EST.text as query_text

, EQP.query_plan as plan_xml

FROM sys.dm_tran_active_snapshot_database_transactions as [SDT]

LEFT OUTER JOIN sys.dm_exec_requests as [ER]

ON SDT.session_id = ER.session_id

OUTER APPLY sys.dm_exec_sql_text (ER.sql_handle) as [EST]

OUTER APPLY sys.dm_exec_query_plan (ER.plan_handle) as [EQP]

ORDER BY elapsed_time_seconds DESC;

1. SQL Server 메모리의 이해

데이터는 디스크에 저장되고 디스크에서 검색을 하는데 디스크 I/O 작업에 많은 리소스를

소비하게 되므로 SQL Server는 디스크 I/O 작업을 효율적으로 관리하기 위하여 버퍼풀의

메모리 구조를 이용한다.

1) SQL Server 메모리 동작 방식

데이터를 조회하게 되면 SQL Server는 버퍼풀에 데이터가 있는지 확인하고 없을 경우

디스크에서 버퍼풀로 데이터를 복사한 후 데이터를 사용한다.

버퍼풀에 데이터가 있으면 디스크에 액세스 하지 않고 버퍼풀의 데이터를 바로 사용한다.

데이터를 변경하게 되면 트랜잭션 로그에는 변경 내역이 즉시 반영되지만 실질적인

데이터 페이지에는 CHECKPOINT 시점의 최종적으로 수정된 데이터만 디스크에 쓰여져

디스크 I/O를 줄여준다.

SQL Server가 사용하는 메모리는 동적으로 관리된다.

처음 시작될 때 SQL Server는 가동에 필요한 최소한의 메모리 공간만 할당 받아서 사용하게 된다.

SQL Server가 운영되는 동안 메모리가 필요할 경우 시스템의 여유 메모리를 할당 받아 사용하고,

사용이 끝나 다른 곳에서 메모리를 요구할 경우 메모리를 반환한다.

2) 32비트 시스템 메모리 특징

32bit 응용 프로그램에서는 총 4GB의 프로세스 주소 공간을 할당할 수 있다.

이중 2GB는 OS의 커널 모드 주소 공간. 2GB는 사용자 모드 주소 공간으로 사용한다.

메모리 옵션을 사용하여 커널 모드 주소 공간을 1GB로 제한하고, 응용 프로그램에 더 할당하거나

AWE 옵션을 활용하여 사용자 모드 주소 공간을 확장하여 응용 프로그램이 더 많은 메모리를

용할 수 있도록 한다.

3) 메모리 설정을 해야 하는 이유

부하가 많은 시스템에서 I/O가 지속적으로 발생하면 SQL Server는 시스템의 메모리를 계속

요구하며 할당 받아 사용하게 되는데 이는 OS나 다른 응용 프로그램의 동작에 영향을 줄 수 있다. 

반대로 OS나 다른 응응 프로그램에서 많은 공간의 메모리를 사용하고 있다면

SQL Server가 할당 받아 사용할 메모리가 줄어들게 된다.

SQL Server의 버퍼풀 메모리가 부족할 경우 잦은 디스크 I/O를 발생시키고, 쿼리를

실행하는데 필요한 최소 메모리를 얻지 못하고 리소스를 대기하는 동안 오류가 발생한다.

2. 메모리 설정 옵션들

1) 부팅 옵션

3GB

32bit 환경에서 4GB 메모리를 사용 중이라면 boot.ini에 /3GB 옵션을 설정해서 운영체제에

1GB를 할당 하고, SQL Server에 3GB를 할당해서 사용할 수 있다.

그러나 16GB를 초과하는 메모리를 사용할 경우에는 가상메모리 주소 할당을 위해 커널 모드에

2GB 이상의 메모리가 필요하기 때문에 /3GB 옵션은 해제해야 한다.

PAE

32bit 운영체제에서 4GB이상의 물리메모리를 사용할 수 있도록 확장

AWE 설정 시 필수 사항

2) 시스템 옵션

LOCK PAGE IN MEMORY

시스템이 디스크의 가상 메모리로 데이터를 페이징 하지 않도록 방지.

OS에서 메모리 반환 요청을 하지 않는다. 

AWE +  LOCK PAGE IN MEMORY 설정을 하면 할당된 메모리를 해제하지 않기 때문에 OS에 메모리를

뺏기지 않는다. 그래서 max server memory를 지정해 주지 않으면 OS가 메모리 부족을 감지할 경우 

buffer pool 영역 이외의 영역이 non-buffer pool 영역에서 메모리를 빼앗기게 때문에 더 심각한 문제가 

발생할 수 있다. 그래서 max server memory  설정을 해야한다. 

32bit 환경에서 4GB 이상 메모리를 사용해야 하는 경우나 64bit 시스템을 사용할 경우 이 옵션을 반드시 설정 해야한다. 

LARGE SYSTEM CACHE

변경 된 데이터 페이지를 물리적인 메모리에 남겨두는 제한을 설정한다.

시스템 캐시를 사용하게 되면 서버의 성능은 향상되지만 애플리케이션에서

사용할 수 있는 가용메모리는 줄어들게 된다.

응용 프로그램이 좀 더 메모리를 사용할수 있게 해주는 옵션임.

http://sqlsql.tistory.com/82

3) SP_CONFIGURE 옵션

min/max server memory

max server memory 까지 메모리를 확보 후 min server memory 까지 메모리를 해제.

min/max server memory를 같게 하여 SQL Server간 메모리 할당 해제에 따른 리소스 최소화.

AWE

4GB를 초과하는 물리적인 메모리를 32bit 주소 공간에 동적으로 할당하는 역할

/PAE 옵션과 함께 사용

확장된 메모리는 버퍼풀로만 매핑해서 활용

4) Trace flag 옵션

845

Lock Page in Memory를 사용할 수 있도록 하는 옵션

SQL Server 구성관리자 -> SQL Server 서비스 -> SQL Server 속성 -> 고급 -> 시작 매개 변수 마지막에 ;-T845 추가 (맞나?..)

836

Max server memory로 지정된 공간까지 버퍼풀 메모리를 확보

AWE모드에서 작동

834

버퍼풀에 할당된 메모리가 Windows 큰 페이지 할당을 사용하도록 한다.

5) 시스템 별 메모리 옵션 설정하기

32bit SQL Server & 32bit Windows Server

/3GB 옵션으로 OS 1GB, 응용 프로그램 3GB 할당

만약 4GB를 초과하는 메모리를 사용한다면 /PAE 옵션을 통해 4GB 메모리 주소 공간을

최대 64GB까지 확장하고, AWE 옵션으로 SQL Server가 확장된 주소 공간을 버퍼풀

영역으로 사용할 수 있도록 설정.

Lock Page in Memory 설정으로 메모리가 페이징 되는 것을 방지.

max server memory 옵션 설정

16GB를 초과하는 메모리를 사용할 경우 3GB 옵션을 제거

64bit SQL Server & 64bit Windows Server

Lock Page in Memory / Max server memory 만 설정

3GB , PAE , AWE 옵션 필요 없음.

32bit SQL Server & 64bit Windows Server(WOW 모드)

OS가 64bit 이기 때문에 내부적인 메모리의 동작 방식은 64bit 시스템을 따라간다.

그래서 PAE 옵션은 필요하지 않다. 하지만 SQL Server는 32bit 시스템 이므로 AWE 모드를

활성화 한다. Lock Page in Memory / max server memory 옵션도 함께 설정한다.

3. MIN / MAX 메모리 설정하기

데이터 캐시를 위한 버퍼풀 영역의 크기만을 의미

SQL Server 전용 머신이라 할지라도 버퍼풀 이외 개체의 메모리 사용량을 예측하고 적절한

크기를 설정한다. 왜냐하면 SQL Server가 메모리를 확보한 후 해제하지 않아 OS나 다른

애플리케이션에서 메모리 부족으로 문제가 발생 하거나 반대로 SQL Server가 메모리를

확보하지 못해 느린 응답 속도를 낼 수 있다.

1) 메모리 크기 별 권장 메모리 설정

2) 메모리 크기와 사용량 분석

-- 시스템메모리상태보기

-- 버퍼풀이외의공간예측하기

select

       TYPE

       ,SUM(multi_pages_kb) as a

from sys.dm_os_memory_clerks

where multi_pages_kb != 0

group by type

츨처: http://ksewookk.blog.me/100178516895

 LATCH는 목적으로 보면 잠금과 유사하지만 많은 차이점이 있다.

 래치는 SQL SERVER 메모리에서 다른 개체로 부터 페이지 데이터 무결성을 보장하는 객체로 정의할 수 있다.

 (잠금은 트랜잭션을 수행할 때, 데이터를 잠궈 안전하게 보호한다.)

 이는 페이지를 사용할 때 자원을 고립시켜 제어된 액세스를 보장하는 논리적 구조이다.

 잠금과의 차이점은 SQLOS 외부로 노출되지 않는 내부 SQL 서버 메커니즘이다.

 래치는 많은 유형이 있지만 대략 버퍼 래치(BUFFER LATCH), 비버퍼래치(NON BUFFER LATCH) 2종류로 나눌 수 있다.

 버퍼 캐시

  버퍼 래취를 이해하기 위해서는 먼저 버퍼 캐시의 목적을 이해해야 한다. 버퍼 캐시(또는 버퍼풀(BUFFER POOL)이라고 함)는

  데이터 페이지를 보유하는데 사용되는 메모리 영역이다. 버퍼 캐쉬는 많은 여러 버퍼를 포함하고 있으며 버퍼 캐시의 크기는 

  8192바이트(8k)이다. 이 영역은 디스크에서 읽을 수 있는 영역이며 만약 수정이 발생하였을 경우 디스크에 다시 기록한다.

  버퍼풀의 데이터 로딩은 FIFO 를 기초로 하며 캐시에서 오래된 데이터 또는 CHECKPOINT, DBCC DROPCLEANBUFFER로 비울 수 있다.

  (캐시를 비우기 전에 버퍼에 있는 모든 더티 페이지를 기록한다.) 버퍼 캐시의 각 버퍼는 디스크에서 읽은 데이터 페이지의 복사본을

  포함 할 수 있다.

  버퍼 관리자는 해시 테이블(특정 메모리 위치에 각 버퍼에 대한 포인터의 버킷 컬렉션 포함), 버퍼 배열(버퍼 포함) 그리고

  버퍼에 대한 쓰기 등 버퍼캐시의 구성요소를 관리할 책임이 있다. 이것은 메모리 영역에서 데이터를 저장하고 검색하기 위한

  메커니즘으로 데이터베이스 엔진과 메모리 페이지의 인터페이스이다.

  버퍼 래치는 버퍼 캐시의 페이지에 접근 할 때 발생한다. 이들은 잠금과 달리 트랜잭션이 지속되는 동안만 수행하고 더 이상 필요하지

  않을 때 해체된다. 하나의 트랜잭션에 많은 래치가 있으며 래치는 다양한 이류로 발생한다.

  버퍼 래치 중 가장 많이 알려진 종류 중 하나는 PAGE_IO_LATCH wait 이며 이것은 엔진이 I/O 동기화 작업을 기다리고 있는 것으로 완료

  되기 전까지 래치에 반영된다. 이것은 I/O 경합을 나타내는 것으로 DMV sys.dm_os_wait_stats로 모든 버퍼 래치를 볼 수 있다. 

  대기 유형은 sys.dm_exec_requests에서 last_wait_type 컬럼에서 볼 수 있다.

  뷰의 캐시에 있는 버퍼에 대한 정보는 DMV sys.dm_os_buffer_description 에서 확인할 수 있다. 이는 버퍼 캐시에 있는 각 데이터 페이지에 

  대해 하나의 행을 포함한다. Page_ID, Page_Type, Row_Count, free space 등을 보여주며 더티페이지 관해서는 나타내지 않는다.

  PAGE_IO_LATCH wait - 엔진이 I/O 동기화 작업을 기다리고 있는 것으로 완료 되기 전까지 래치에 반영

  DMV sys.dm_os_wait_stats - 모든 버퍼 래치 확인

  DMV sys.dm_os_buffer_descriptors - 버퍼에 대한 정보 확인

  LATCH CLASS 및 LATCH MODE

  버퍼 클래스는 기능에 따라 버퍼 래치와 비버퍼 래치의 여러 종류로 분류할 수 있다.

  버퍼 클래스는 일반적으로 사용된다. DMV는 waiting_requests_count 열을 쿼리하는 경우 대부분의 클래스는 순간 유휴 상태(0)이다.

  NULL LATCH (NL) : 사용하지 않음

  KEEP LATCH (KP) : 래치가 위치하는 동안 버퍼캐시에서 페이지를 유지하기 위한 목적과 참조 카운트를 유지하기 위한 목적.

  SHARED LATCH (SH) : 데이터 페이지를 읽은 요청이 있을 때

  UPDATE LATCH (UP) : EX 래치보다는 가벼움. 업데이트 동안 페이지에 읽기는 허용하나 쓰기 안됨.

  EXCLUSIVE LATCH (EX) : 기록되는 동안 페이지에 대한 액세스 허용하지 않음.

  DESTORY LATCH (DT) : 버퍼를 파괴하고 캐시를 축출하는데 사용.

  래치 대기는 여러 가지 이유로 발생 할 수 있다.

  예를 들어 작은 범위의 페이지에 업데이트가 많을 때 발생한다.

  래치 경합 다루기 - SuperLatch 를 사용한 병렬처리

   SuperLatch라는 버퍼 래치의 경합을 처리 할 수 있는 메커니즘이 있으며

   페이지의 병렬 처리를 가능하기 위해 다른 CPU 스케줄러(NUMA 아키텍쳐)에서 액세스 할 수 있다.

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

http://sqlsql.tistory.com/364

성능 테스트 어플을 작성했는데 0번 core 가 100% 사용해 원인을 찾아보기로 한다. 

1) Windows Performance Recorder 를 이용해 부하상황을 캡처한다. 

2) Windows Performance Analyzer 를 이용해 부하를 분석한다. 

    다음은 분석된 결과이다. 

원인이 뭘까 살펴보니 DPC (Deferred Procedure Call, 인터럽트를 처리하는 소프트웨어 루틴으로 CPU에서 지연된 처리 요청 수치) 가 0번 core 에서만 엄청나게 높았고, 이는 Platform Invoke 프로그램에서 사용하는 owfs_w64.dll 이 원인인 것으로 나타났다. (ndis.sys...)

rss 가 켜져 있는디 왜 한코어만 dpc 처리를 할까...

http://blogs.technet.com/b/mikelag/archive/2010/11/26/processor-0-increased-cpu-utilization.aspx

http://rockball.tistory.com/entry/DPC%EB%9E%80-%EB%AC%B4%EC%97%87%EC%9D%B8%EA%B0%80

http://sqlsql.tistory.com/372

SQLStressCSharp.zip

SQLStressDatabase.zip

SQL Server 의 성능을 테스트 할 수 있음

visual studio 2013 .net framework 4.5 기반으로 작성되어 있음

visual studio 2013은 현재 무료 입니다. (작은회사나, 개인용)

ostress 보다 편리하고, adam 의 test 테스트프로그램 보다 가벼움

hp 의 load runner 와 거의 동일한 테스트가 가능하며 vuser 살 필요없이 이걸로 모든 테스트가 가능하다.

수정하면, 파일만들기, cpu, memory, disk, network 별 다양한 워크로드를 줄 수 있다.

1. SQLStressDatabase 를 다운받아 Config database 를 생성한다.

2. SQLStressCSharp 을 다운받아 CConfig 의 connection string 을 고친다.

3. tConfig 테이블에 Target SQL Server 의 Connection string 을 고친다.

4. SQLStressDatabase 의 perf 데이터베이스를 만들고 cpu, memory procedure 를 만든다.

이후 테스트를 하면 된다. 

[xp_cmdshell 제거하기]

USE master 
GO 
IF OBJECT_ID('[dbo].[xp_cmdshell]') IS NOT NULL BEGIN 
   EXEC sp_dropextendedproc 'xp_cmdshell' 
END 
GO

EXEC sp_addextendedproc 'xp_cmdshell', 'xplog70.dll' 
GO

USE master 
GO

IF OBJECT_ID('[dbo].[xp_cmdshell]') IS NOT NULL BEGIN 
   EXEC sp_dropextendedproc 'xp_cmdshell' 
END 
GO 

[재설치]

sp_dropextendedproc을 해도 해당 DLL이 메모리에 남아 있다.

- 다음 명령어로 동적 연결 라이브러리(DLL)를 메모리에서 내리면 기존 파일이 지워진다.

dbcc xp_cmdshell(free)

USE MASTER

EXEC SP_HELPTEXT XP_CMDSHELL

- 여기서 나타난 DLL 이름을 기억해 둡니다. 이것은 나중에 다시 등록할 때 필요함.

USE MASTER

EXEC SP_DROPEXTENDEDPROC ‘XP_CMDSHELL’

[다시 등록시 ]

USE MASTER

EXEC SP_ADDEXTENDEDPROC ‘XP_CMDSHELL’, ‘XPLOG70.DLL’

- 앞에서 알아냈던 DLL

alter database testdb set recovery simple

traceon(610, -1)

delete....

traceoff(610, -1)

alter database testdb set recovery full

MERGE dbo.wph_WorkplaceFriend T

USING (

SELECT wph_wf_BuildingID, wph_wf_VillageID, wph_wf_UserID, wph_wf_FriendUserID, wph_wf_CoWorkState, wph_wf_ReduceTime 

FROM Admin_DB.dbo.udf_CLR_SplitString_WorkplaceFriend(@WorkplaceFriend_TT)

) S ON T.wph_wf_VillageID = S.wph_wf_VillageID AND T.wph_wf_BuildingID = S.wph_wf_BuildingID

WHEN MATCHED AND S.wph_wf_FriendUserID = 0 THEN

DELETE

WHEN MATCHED AND S.wph_wf_FriendUserID <> 0 THEN

UPDATE SET T.wph_wf_UserID = S.wph_wf_UserID

, T.wph_wf_FriendUserID = S.wph_wf_FriendUserID

, T.wph_wf_CoWorkState = S.wph_wf_CoWorkState

, T.wph_wf_ReduceTime = S.wph_wf_ReduceTime;

declare @OutResult int

EXEC dbo.prAddLogItemTest  '2010-03-01 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보†2010-02-25 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보†2010-02-25 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보†2010-02-25 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보†2010-02-25 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보†2010-02-25 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보†2010-02-25 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보†2010-02-25 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보†2010-02-25 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보†2010-02-25 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보'

        , '|'

        , '†'

        , 10

        , @OutErrorCode =  @OutResult OutPut

print @OutResult

Return                 :  0 (정상처리), -20001 (아이템로그 입력처리 오류)

*/

ALTER PROC [dbo].[prAddLogItemMulti]

    @LogText varchar(max),

    @FieldValue varchar(10),

    @LineValue varchar(10),

    @RowCnt int,

    @OutErrorCode int output

AS

BEGIN

SET NOCOUNT ON 

    DECLARE @LogStr nvarchar(max);

    SELECT @LogStr = 'INSERT INTO dbo.LogItem (LogTime, ServerID, GameServerNo, LogType, ItemID, SerialKey, AccountID, CharName, IPAddr, MapGroupID, MapPosition, ToAccount, ToChar, ToIPAddr, Etc) SELECT ''' + REPLACE(REPLACE(SUBSTRING(@LogText,1,LEN(@LogText)-LEN(@LineValue)),@FieldValue,''','''),@LineValue,''' UNION ALL SELECT ''') + '''';

    EXEC sp_executesql @LogStr;

IF @@ERROR = 0

SET @OutErrorCode = 0;

ELSE

SET @OutErrorCode = -20001;

END

create table test_row_multi_insert (

idx int not null identity(1,1) primary key

, getTime datetime not null

, serverid int not null

, type varchar(2) not null

, name varchar(10) not null

);

/*

alter table test_row_multi_insert

add constraint cl_idx primary key (idx);

*/

/*

create table test_row_multi_insert (

idx int not null identity(1,1) primary key

, getTime datetime not null

, serverid int not null

, type varchar(2) not null

, name varchar(10) not null

, constraint cl_idx primary key (idx)

);

*/

alter procedure test_row_mulit_insert

@rowText nvarchar(max)

, @FieldValue varchar(10)

, @LineValue varchar(10)

, @RowCnt int

, @outErrorCode int output

as

begin

set nocount on;

declare @tmpStr nvarchar(max);

select @tmpStr = 'INSERT INTO dbo.test_row_multi_insert (getTime, serverid, type, name) SELECT ''' + 

REPLACE(REPLACE(SUBSTRING(@rowText, 1, (LEN(@rowText) + 1)-LEN(@LineValue)), @FieldValue, ''','''), @LineValue, ''' UNION ALL SELECT ''') + '''';

exec sp_executesql @tmpStr;

if @@error = 0

set @outErrorCode = 0;

else

set @outErrorCode = -1;

end;

declare @OutResult int

exec dbo.test_row_mulit_insert '2014-11-01 17:11:11|1|A|홍길동†2014-11-02 17:11:11|1|B|백두산†2014-11-03 17:11:11|2|A|태백산'

, '|'

, '†'

, 3

, @outErrorCode = @OutResult output

print @OutResult

select * from test_row_multi_insert;

/* 쿼리 레코드로 인한 대기 시간 증가 */

SELECT TOP 10

 [Wait type] = wait_type,

 [Wait time (s)] = wait_time_ms / 1000,

 [% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 

               / SUM(wait_time_ms) OVER())

FROM sys.dm_os_wait_stats

WHERE wait_type NOT LIKE '%SLEEP%' 

ORDER BY wait_time_ms DESC;

/* 가장 많은 읽기 및 쓰기를 사용하는 데이터베이스 확인 */

SELECT TOP 10 

        [Total Reads] = SUM(total_logical_reads)

        ,[Execution count] = SUM(qs.execution_count)

        ,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

GROUP BY DB_NAME(qt.dbid)

ORDER BY [Total Reads] DESC;

SELECT TOP 10 

        [Total Writes] = SUM(total_logical_writes)

        ,[Execution count] = SUM(qs.execution_count)

        ,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

GROUP BY DB_NAME(qt.dbid)

ORDER BY [Total Writes] DESC;

/* 데이터베이스별 누락된 인덱스 */

SELECT 

    DatabaseName = DB_NAME(database_id)

    ,[Number Indexes Missing] = count(*) 

FROM sys.dm_db_missing_index_details

GROUP BY DB_NAME(database_id)

ORDER BY 2 DESC;

/* 비용이 높은 누락된 인덱스 */

SELECT  TOP 10 

        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 

        , avg_user_impact

        , TableName = statement

        , [EqualityUsage] = equality_columns 

        , [InequalityUsage] = inequality_columns

        , [Include Cloumns] = included_columns

FROM        sys.dm_db_missing_index_groups g 

INNER JOIN    sys.dm_db_missing_index_group_stats s 

       ON s.group_handle = g.index_group_handle 

INNER JOIN    sys.dm_db_missing_index_details d 

       ON d.index_handle = g.index_handle

ORDER BY [Total Cost] DESC;

/* 가장 비용이 높은 사용되지 않은 인덱스 확인 */

-- 필요한 테이블 구조만 작성합니다.

-- 참고: 이 SQL은 다음 단계에서 지정된 데이터베이스 루프 내에 있어야 합니다.

SELECT TOP 1

        DatabaseName = DB_NAME()

        ,TableName = OBJECT_NAME(s.[object_id])

        ,IndexName = i.name

        ,user_updates    

        ,system_updates    

        -- 기타 유용한 필드를 아래에 나열

        --, *

INTO #TempUnusedIndexes

FROM   sys.dm_db_index_usage_stats s 

INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 

    AND s.index_id = i.index_id 

WHERE  s.database_id = DB_ID()

    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0

    AND    user_seeks = 0

    AND user_scans = 0 

    AND user_lookups = 0

    AND s.[object_id] = -999  -- 테이블 구조를 얻기 위한 임시 값

;

-- 서버의 모든 데이터베이스를 대상으로 반복합니다.

EXEC sp_MSForEachDB    'USE [?]; 

-- 테이블이 이미 있는 경우

INSERT INTO #TempUnusedIndexes 

SELECT TOP 100    

        DatabaseName = DB_NAME()

        ,TableName = OBJECT_NAME(s.[object_id])

        ,IndexName = i.name

        ,user_updates    

        ,system_updates    

FROM   sys.dm_db_index_usage_stats s 

INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 

    AND s.index_id = i.index_id 

WHERE  s.database_id = DB_ID()

    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0

    AND    user_seeks = 0

    AND user_scans = 0 

    AND user_lookups = 0

    AND i.name IS NOT NULL    -- HEAP 인덱스 무시

ORDER BY user_updates DESC

;

'

-- 레코드 선택

SELECT TOP 100 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC

-- 임시 테이블 정리

DROP TABLE #TempUnusedIndexes

/* 사용 비용이 높은 인덱스 */

-- 필요한 테이블 구조만 작성합니다.

-- 참고: 이 SQL은 다음 단계에서 지정된 데이터베이스 루프 내에 있어야 합니다.

SELECT TOP 1

        [Maintenance cost]  = (user_updates + system_updates)

        ,[Retrieval usage] = (user_seeks + user_scans + user_lookups)

        ,DatabaseName = DB_NAME()

        ,TableName = OBJECT_NAME(s.[object_id])

        ,IndexName = i.name

INTO #TempMaintenanceCost

FROM   sys.dm_db_index_usage_stats s 

INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 

    AND s.index_id = i.index_id

WHERE s.database_id = DB_ID() 

    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0

    AND (user_updates + system_updates) > 0 -- 활성 행에 대해서만 보고

    AND s.[object_id] = -999  -- 테이블 구조를 얻기 위한 임시 값

;

-- 서버의 모든 데이터베이스를 대상으로 반복합니다.

EXEC sp_MSForEachDB    'USE [?]; 

-- 테이블이 이미 있는 경우

INSERT INTO #TempMaintenanceCost 

SELECT TOP 100

        [Maintenance cost]  = (user_updates + system_updates)

        ,[Retrieval usage] = (user_seeks + user_scans + user_lookups)

        ,DatabaseName = DB_NAME()

        ,TableName = OBJECT_NAME(s.[object_id])

        ,IndexName = i.name

FROM   sys.dm_db_index_usage_stats s 

INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 

    AND s.index_id = i.index_id

WHERE s.database_id = DB_ID() 

    AND i.name IS NOT NULL    -- HEAP 인덱스 무시

    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0

    AND (user_updates + system_updates) > 0 -- 활성 행에 대해서만 보고

ORDER BY [Maintenance cost]  DESC

;

'

-- 레코드 선택

SELECT TOP 100 * FROM #TempMaintenanceCost 

ORDER BY [Maintenance cost]  DESC

-- 임시 테이블 정리

DROP TABLE #TempMaintenanceCost

/* 가장 많이 사용되는 인덱스 확인 */

-- 필요한 테이블 구조만 작성합니다.

-- 참고: 이 SQL은 다음 단계에서 지정된 데이터베이스 루프 내에 있어야 합니다.

SELECT TOP 1

        [Usage] = (user_seeks + user_scans + user_lookups)

        ,DatabaseName = DB_NAME()

        ,TableName = OBJECT_NAME(s.[object_id])

        ,IndexName = i.name

INTO #TempUsage

FROM   sys.dm_db_index_usage_stats s 

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 

    AND s.index_id = i.index_id 

WHERE   s.database_id = DB_ID() 

    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0

    AND (user_seeks + user_scans + user_lookups) > 0 

-- 활성 행에 대해서만 보고

    AND s.[object_id] = -999  -- 테이블 구조를 얻기 위한 임시 값

;

-- 서버의 모든 데이터베이스를 대상으로 반복합니다.

EXEC sp_MSForEachDB    'USE [?]; 

-- 테이블이 이미 있는 경우

INSERT INTO #TempUsage 

SELECT TOP 100

        [Usage] = (user_seeks + user_scans + user_lookups)

        ,DatabaseName = DB_NAME()

        ,TableName = OBJECT_NAME(s.[object_id])

        ,IndexName = i.name

FROM   sys.dm_db_index_usage_stats s 

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 

    AND s.index_id = i.index_id 

WHERE   s.database_id = DB_ID() 

    AND i.name IS NOT NULL    -- HEAP 인덱스 무시

    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0

    AND (user_seeks + user_scans + user_lookups) > 0 -- 활성 행에 대해서만 보고

ORDER BY [Usage]  DESC

;

'

-- 레코드 선택

SELECT TOP 100 * FROM #TempUsage ORDER BY [Usage] DESC

-- 임시 테이블 정리

DROP TABLE #TempUsage

/* 논리적으로 조각난 인덱스 */

SELECT TOP 1 

        DatbaseName = DB_NAME()

        ,TableName = OBJECT_NAME(s.[object_id])

        ,IndexName = i.name

        ,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)

INTO #TempFragmentation

FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 

    AND s.index_id = i.index_id 

WHERE s.[object_id] = -999  -- 테이블 구조를 얻기 위한 임시 값