함수 통계 정보 확인 (sys.dm_exec_function_stats)

 

  • Version : SQL Server 2016

 

SQL Server에서 쿼리를 사용할 때 함수를 사용하는 경우가 있다. 함수를 사용할 때 함수에 대한 성능을 확인하기 위해서 일반적으로 함수의 개별문을 실행하였다. 이번 포스트에서는 SQL Server 2016에서 새롭게 제공된 sys.dm_exec_function_stats DMV를 사용하여 함수에 대한 통계 정보를 확인해 본다.

 

sys.dm_exec_function_stats는 모든 스칼라 함수 및 인메모리, CLR 스칼라 함수에 대한 통계 정보를 제공한다. 이 기능은 모든 스칼라 함수에 대한 캐시된 실행 계획을 반환한다. 인모메리 기능의 통계를 볼 때 논리적 물리적 IO에대한 칼럼 정보는 0으로 나타나 정보를 확인할 수 없지만 쿼리에 대한 실행 횟수는 조회할 수 있다.

 

sys.dm_exec_function_stats에 대한 기능을 실습을 통해 알아본다. 데이터베이스는 AdventureWorks2014를 사용하였으며 dbo.ufnGetProductListPrice, dbo.ufnGetStock 함수를 호출한다.

USE AdventureWorks2014

GO

 

SELECT

    OH.PurchaseOrderNumber ,

    dbo.ufnGetProductListPrice(OD.ProductID, OH.OrderDate) ListPrice ,

    OD.UnitPRice ,

    OD.OrderQty ,

    OD.LineTotal ,

    dbo.ufnGetStock(OD.ProductID) RemainingStock

FROM Sales.SalesOrderHeader OH

    INNER JOIN Sales.SalesOrderDetail OD ON OH.SalesOrderID = OD.SalesOrderID

GO

 

아래 쿼리의 경우 dbo.ufnGetContacInformation 테이블 반환 함수에 대한 캐시목록은 작성하지만 앞에서 설명한것과 같이 sys.dm_exec_function_stats에서는 레코드를 표시하지 않는다.

USE AdventureWorks2014

GO

 

SELECT *

FROM Sales.SalesOrderHeader OH

CROSS APPLY dbo.ufnGetContactInformation(OH.SalesPersonID)

GO

 

아래 스크립트는 sys.dm_exec_function_stats를 사용하여 함수의 실행 통계를 확인할 수 있다. 함수에 대한 이름 뿐만 아니라 기본적인 통계 그리고 함수의 구문을 확인할 수 있다.

USE MASTER

GO

 

SELECT DB_NAME(database_id) + '.' +

        OBJECT_SCHEMA_NAME(OBJECT_ID, database_id) +

'.' + OBJECT_NAME(OBJECT_ID, database_id)

        AS Function_Name,

QS.last_execution_time ,

QS.max_worker_time ,

QS.max_physical_reads ,

QS.max_logical_reads ,

QS.max_logical_writes ,

T.Text

FROM sys.dm_exec_function_stats QS

CROSS APPLY sys.dm_exec_sql_text(sql_handle) T

 

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4084/troubleshoot-sql-server-function-performance-with-the-sysdmexecfunctionstats-dmv/

 

2015-11-24 / 강성욱 / http://sqlmvp.kr

 

SQL Server, MS SQL, SQL 2016, 함수 통계 확인, 쿼리튜닝, DB 튜닝, sys.dm_exec_function_stats

'SQL Server > SQL Server Tip' 카테고리의 다른 글

.NET 4.6.1에 변경된 Multisubnet 기본 수신기 동작  (0) 2015.12.04
SQL Server 2014 온라인 인덱스 리빌드와 잠금 우선순위 옵션  (0) 2015.11.26
NULL 데이터가 포함된 데이터 사용 시 주의점  (0) 2015.11.20
통계정보와 실제 데이터 분포 확인하기  (0) 2015.11.17
SQL Server Failover Cluster 설치시 네트워크 이름으로 인한 설치 오류  (0) 2015.11.12

RESOURCE_GOVERNOR_IDLE과 쿼리 성능

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

이 글은 CSS SQL Server Engineers에 기재된 내용으로 원문을 읽고 해석한 것으로 필자의 이해력을 기반으로 기술하였습니다. 기술적 오류 또는 번역의 오류가 포함될 수 있으니 반드시 원문을 참고 바랍니다.

 

쿼리의 실행이 느릴 때 SQL Nexus(http://sqlnexus.codeplex.com/) 에서 다음과 같은 대기 유형을 캡처 했다. 대기 유형에서 RESOURCE_GOVERNOR_IDLE가 매우 높게 나타는것을 확인 하였다.

 

이 대기 유형은 CPU CAP 실행에 관련한 것이었다(CAP_CPU_PERCENT). CAP_CPU_PERCENT를 사용하면 SQL Server는 CPU pool에서 CPU CAP을 초과하지 않는 것을 보장한다. 만약 CPU_CAP_PERCENT를 10%로 설정한 경우 SQL Server는 CPU pool의 10%를 사용하는 것을 보장한다. SQL Server는 풀에게 부여되지 않는 퀀텀(quantum)을 차지하기 위해 실행 가능한 큐에 유휴 소비자(Idle Consumer)를 삽입한다. 유휴소비자가 기다리는 동안 RESOURCE_GOVERNOR_IDLE이 유휴소비자 퀀텀이 있음을 나타내기 시작했다. 여기에 특정 리소스 풀에 대한 실행 가능한 큐와 CAP_CPU_PERCENT 구성 없이 어떻게 보이는지에 대한 것이다.

 

 

대기 유형은 Sys.dm_os_ring_buffers에서 볼 수 있을 뿐만 아니라 sys.dm_os_ring_buffers 항목에서도 볼 수 있다.

select * from sys.dm_os_ring_buffers

where ring_buffer_type ='RING_BUFFER_SCHEDULER' and record like '%SCHEDULER_IDLE_ENQUEUE%'

 

<Record id = "139903" type ="RING_BUFFER_SCHEDULER" time ="78584090"><Scheduler address="0x00000002F0580040"><Action>SCHEDULER_IDLE_ENQUEUE</Action><TickCount>78584090</TickCount><SourceWorker>0x00000002E301C160</SourceWorker><TargetWorker>0x0000000000000000</TargetWorker><WorkerSignalTime>0</WorkerSignalTime><DiskIOCompleted>0</DiskIOCompleted><TimersExpired>0</TimersExpired><NextTimeout>6080</NextTimeout></Scheduler></Record>

 

이처럼 RESOURCE_GOVERNOR_IDLE 대기 유형 타입을 무시해서는 안된다. 사용자가 CPU CAP을 설정하는 경우 정확히 평가해야 한다. 너무 낮은 설정은 쿼리에 영향을 받을 수 있다.

 

다음 스크립트는 CPU CAP을 설정하고 실행 시간을 관찰한다.

--first measure how long this takes

select count_big (*) from sys.messages m1 cross join sys.messages m2 -- cross join sys.messages m3

go

 

--alter to 5 (make sure you revert it back later)

ALTER RESOURCE POOL [default]

WITH ( CAP_CPU_PERCENT = 5 );

go

ALTER RESOURCE GOVERNOR RECONFIGURE;

go

 

--see the configuration

select * from sys.dm_resource_governor_resource_pools

go

 

--now see how long it takes

select count_big (*) from sys.messages m1 cross join sys.messages m2 -- cross join sys.messages m3

go

 

--While the above query is running, open a different connection and run the following query

--you will see that it keeps going up. note that if you don't configure CAP_CPU_PERCENT, this value will be zero

select * from sys.dm_os_wait_stats where wait_type ='RESOURCE_GOVERNOR_IDLE'

 

 

--revert it back

ALTER RESOURCE POOL [default]

WITH ( CAP_CPU_PERCENT = 100 );

go

ALTER RESOURCE GOVERNOR RECONFIGURE;

go

 

 

[참고자료]

http://blogs.msdn.com/b/psssql/archive/2015/04/10/what-is-resource-governor-idle-and-why-you-should-not-ignore-it-completely.aspx

 

SQL Server, mssql, 쿼리성능, 쿼리튜닝, DB튜닝, sys.dm_os_wait_stats, sys.dm_os_ring_buffers, SQL 대기, SQL Wait

2015-04-20 / 강성욱 / http://sqlmvp.kr

 

 

'SQL Server > SQL Server Tip' 카테고리의 다른 글

트랜잭션로그 파일이 손상된 데이터베이스 복원 하기  (0) 2015.07.23
트랜잭션 로그 백업을 읽고 트랜잭션 발생 시간 및 사용자 찾기  (0) 2015.07.23
TDE 암호화된 데이터베이스 복원  (0) 2015.07.23
재해복구를 위한 SQL Server 역할 가져오기  (0) 2015.07.23
비관리자 계정에 Profiler 실행 권한 부여하기  (0) 2015.07.23

601 Error, Could not continue scan with NOLOCK due to SQL Server data Movement

  • 메시지 601, 수준 12, 상태 3, 프로시저 usp_SQLMVP, 줄 23
  • 데이터 이동으로 인해 NOLOCK으로 계속 검색할 수 없습니다.

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

데이터 조회 프로시저 호출에서 다음과 같이 601 에러가 발생 하였다. 601 에러는 어떨떄 발생 할까?

 

601에러는 READ UNCOMMITTED, NOLOCK 힌트를 사용하여 데이터를 조회 할 때 하나 이상의 데이터가 페이지가 없기 때문에 발생한다. 페이지 분할로 이동했거나 페이지가 삭제되었을 경우 발생 한다. 즉 SQL Server가 스캔 작업을 계속 할 수 없을 때 발생한다.

페이지 삭제의 경우 디스크 커럽션이 발생해서 데이터가 손실될 경우도 있으므로 페이지 손상이 있는지 DBCC CHECKDB 옵션을 사용하여 무결성 확인을 해 볼 필요가 있다.

 

이 외에도 인덱스 튜닝 등을 통하여 쿼리를 좀더 빠르게 개선할 수 있도록 한다. 또한 조회 데이터가 적을수록 발생할 가능성이 적어진다.

 

[참고자료]

 

 

2014-12-24 / 강성욱 / http://sqlmvp.kr

 

 

601 에러, MSSQL, sqlserver, 인덱스, index, DBCC CHECKDB, 디스크검사, NOLOCK, 격리수준, 쿼리튜닝, 디스크 커럽션, DISK CRUUPTION

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server 2008R2 Diagnostic Information Queries  (0) 2015.07.23
SQL Server 2005 Diagnostic Information Queries  (0) 2015.07.23
데이터베이스의 모든 인덱스 생성 삭제 스크립트 만들기  (0) 2015.07.23
SQL Server Spinlock 소개  (0) 2015.07.23
Ad-hoc 쿼리와 실행계획  (0) 2015.07.23

Ad-hoc 쿼리와 실행계획

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

Ad-hoc 쿼리를 실행 할 때 대소문자, 띄어쓰기, 스키마에 따라 기존의 실행 계획이 사용되지 않고 새로운 실행 계획이 생성되어 사용된다. Ad-hoc 쿼리는 컴파일 과정을 거치며 실행 계획을 생성하며 컴파일과 실행 계획 생성에 따른 성능 병목이 발생 할 수 있다.

 

[대소문자, 공백에 따른 실행계획 생성]

Ad-hoc으로 쿼리를 사용 할 경우 Case와 공백에 따라서도 다른 쿼리로 인식되어 새로운 실행계획이 생성된다고 하였다. 다음 예제를 통해 알아보자.

USE AdventureWorks2014;

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

GO

SELECT StoreID FROM Sales.Customer;

GO -- original query

GO

SELECT StoreID FROM Sales.Customer;

GO ----^---- extra space

GO

SELECT storeid FROM sales.customer;

GO ---- lower case names

GO

select StoreID from Sales.Customer;

GO ---- lower case keywords

GO

 

위 쿼리를 실행하면 동일한 계획을 생성하지만 캐시된 실행계획을 확인해 보면 쿼리별로 실행 계획이 생성된 것을 확인 할 수 있다.

SELECT t.[text], p.size_in_bytes, p.usecounts

FROM sys.dm_exec_cached_plans AS p

CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t

WHERE LOWER(t.[text]) LIKE N'%sales'+'.'+'customer%';

 

 

 

[스키마에 따른 실행 계획]

테이블을 호출하거나 프로시저를 호출 할 때 스키마 접두사를 사용하는 것은 중요하다. 동일한 쿼리라도 스키마에 따라 호출 되는 우선순위가 다르며 실행 계획 또한 재사용하지 못한다.

 

다음 스크립트는 Sales와 Person 스키마를 사용하여 동일한 쿼리를 호출 한다.

USE AdventureWorks2014;

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

GO

 

CREATE USER SQLPerf1 WITHOUT LOGIN WITH DEFAULT_SCHEMA = Sales;

CREATE USER SQLPerf2 WITHOUT LOGIN WITH DEFAULT_SCHEMA = Person;

GO

 

CREATE TABLE dbo.AnErrorLog(id INT);

GRANT SELECT ON dbo.AnErrorLog TO SQLPerf1, SQLPerf2;

GO

 

EXECUTE AS USER = N'SQLPerf1';

GO

SELECT id FROM AnErrorLog;

GO

REVERT;

GO

EXECUTE AS USER = N'SQLPerf2';

GO

SELECT id FROM AnErrorLog;

GO

REVERT;

GO

 

Sys.dm_exec_plan_attributes를 사용하여 캐시된 플랜을 확인해 보면 두 쿼리에 대해 서로 다른 계획을 생성한 것을 확인 할 수 있다.

SELECT t.[text], p.size_in_bytes, p.usecounts,

[schema_id] = pa.value,

[schema] = s.name

FROM sys.dm_exec_cached_plans AS p

CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t

CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS pa

INNER JOIN sys.schemas AS s ON s.[schema_id] = pa.value

WHERE t.[text] LIKE N'%AnError'+'Log%'

AND pa.attribute = N'user_id';

 

 

다음 스크립트는 복수의 결과를 가지고 있는 실행 계획을 확인 할 수 있다.

SELECT p.plan_handle, p.usecounts, p.size_in_bytes,

set_options = MAX(a.value)

FROM sys.dm_exec_cached_plans AS p

CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t

CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS a

WHERE t.objectid = OBJECT_ID(N'dbo.procedure_name')

AND a.attribute = N'set_options'

GROUP BY p.plan_handle, p.usecounts, p.size_in_bytes;

 

이처럼 같은 쿼리라 생각했던 것이 띄어쓰기나 대소문자로 기존의 실행 계획을 사용하지 못하는 것을 확인 할 수 있다.

 

저장된 실행계획에 대한 확인 및 활용은 다음 포스팅을 참고 한다.

 

 

[참고자료]

http://sqlperformance.com/2014/11/t-sql-queries/multiple-plans-identical-query

 

2014-11-25 / 강성욱 / http://sqlmvp.kr

 

 

Sqlserver, mssql, plan cache, ad-hoc, shcema, query plan, 쿼리 플랜, 플랜 재사용, 플랜 캐시, 저장된 플랜, 쿼리 실행 계획, 실행계획 재사용, DB튜닝, 쿼리튜닝

 

 

'SQL Server > SQL Server Tip' 카테고리의 다른 글

데이터베이스의 모든 인덱스 생성 삭제 스크립트 만들기  (0) 2015.07.23
SQL Server Spinlock 소개  (0) 2015.07.23
로그인 계정이 접근할 수 있는 데이터베이스 확인  (0) 2015.07.23
클러스터된 SQL 서버 인스턴스에 대한 호스트 이름 확인  (0) 2015.07.23
sys.dm_tran_locks 를 이용한 잠금 정보 확인  (0) 2015.07.23

테이블 변수와 TF 2453

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

테이블변수에 데이터를 삽입하면 카디널리티는 항상 1 이다. 행이 적을 경우에는 큰 문제가 되지 않지만 행이 많을 경우에는 쿼리 계획을 효율적으로 생성하지 못하여 쿼리 성능이 저하될 수 있다.

dbcc traceoff(2453,-1)

go

dbcc freeproccache

go

set statistics profile off

go

use tempdb

go

if OBJECT_ID ('t2') is not null

drop table t2

go

create table t2 (c2 int)

go

create index ix_t2 on t2(c2)

go

--insert 100,000 rows into the perm table

set nocount on

begin tran

declare @i int

set @i = 0

while @i < 100000

begin

insert into t2 values (@i)

set @i = @i + 1

end

commit tran

go

--update stats

update statistics t2

 

go

 

set nocount on

declare @t1 table (c1 int)

begin tran

declare @i int

set @i = 0

while @i < 100000

begin

insert into @t1 values (@i)

set @i = @i + 1

end

commit tran

set statistics profile on

select * from @t1 inner join t2 on c1=c2

go

 

set statistics profile off

 

 

SQL Server 2012 SP2 에서는 테이블변수에 대한 카디널리티를 개선했다. 이 개선은 테이블변수에 많은 행이 있을 경우에도 도움이 된다. SQL Server 2012 SP2에서 TF2453을 활성화 하면 테이블 변수를 사용하더라도 정확한 카디널리티를 예측하여 효율적인 쿼리 계획을 생성 할 수 있다.

dbcc freeproccache

go

dbcc traceon(2453,-1)

 

 

set nocount on

declare @t1 table (c1 int)

begin tran

declare @i int

set @i = 0

while @i < 100000

begin

insert into @t1 values (@i)

set @i = @i + 1

end

commit tran

set statistics profile on

select * from @t1 inner join t2 on c1=c2

go

 

set statistics profile off

 

 

  • SQL Server 2012 SP2 다운로드 :

http://www.microsoft.com/ko-kr/download/confirmation.aspx?id=43340

 

 

[참고자료]

http://blogs.msdn.com/b/psssql/archive/2014/08/11/if-you-have-queries-that-use-table-variables-sql-server-2012-sp2-can-help.aspx

 

 

 

2014-09-01 / 강성욱 / http://sqlmvp.kr

 

 

SQL Server 2012 SP2, SQL Server Service pack, 테이블변수, 임시테이블, 테이블변수 카디널리티, 실행계획, 쿼리튜닝, DB튜닝, SQL, MSSQL, SQL

'SQL Server > SQL Server Tip' 카테고리의 다른 글

컬럼스토어 인덱스 동시성  (0) 2015.07.23
컬럼스토어 인덱스 ROW와 ROWGROUP 영향  (0) 2015.07.23
Sp_trace_create MaxfileSize 오류  (0) 2015.07.23
RANDBETWEEN 함수 만들기  (0) 2015.07.23
Optimize for hint 쿼리 최적화  (0) 2015.07.23

블록킹 세션을 찾아 우선순위 낮은 세션 종료하기

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

데이터베이스 서버를 운영하다보면 다양한 쿼리 요청으로 인해 블록킹이 발생 할 수 있다. 차단 및 교착이 발생하였을 때 이를 감지하고 블록킹이 발생한 세션 중 우선순위가 가장 낮은 프로세스를 종료 할 수 있다면 원치 않는 비즈니스 중단을 예방 할 수 있다.

 

시나리오는 다음과 같다.

매일 밤 실행되는 일괄 처리 작업이 다른 프로세스를 차단하는 현상이 발생 하였을 때 일괄 처리 되는 세션을 종료시키고 싶다. 일괄 처리되는 스크립트에 라벨링을 하여 해당 세션을 강제 종료하는 방법에 대해서 알아 본다.

 

세션 식별 목적을 위해 스크립트의 시작 부분에 CONTEXT_INFO 함수를 사용하여 라벨링을 할 수 있다.

set context_info 0xdba911 -- arbitrary, and can be any value you like

 

세션 1에서 실습 테이블을 생성하고 트랜잭션을 발생시킨다. 스크립트 상단에 CONTEXT_INFO를 사용하여 세션을 식별 할 수 있도록 한다.

세션2에서는 세션1에서 사용중인 테이블에 잠금을 요청하여 블록을 발생 시킨다.

세션3에서는 현재 블록킹 된 세션을 감지하고 사용자 세션을 식별하여 종료시킬 SPID를 알려준다.

세션1

세션2

세션3

if object_id('dbo.t', 'U') is not null

drop table dbo.t;

create table dbo.t (a int, b varchar(30));

go

insert into dbo.t (a, b) values (1, 'hello')

go

set context_info 0xdba911; -- change 0xdba911 to your preferred value

begin tran

update dbo.t set b= 'world' from dbo.t where a=1

waitfor delay '00:05:00' -- wait for 5 min

commit tran;

    
 

delete from dbo.t where a=1;

 
   

set nocount on;

set deadlock_priority low;

declare @sqlcmd varchar(max);

declare @debug bit; -- 1=print out kill command, 0=execute kill command

 

set @debug = 1; -- 1=print, 0=exec

set @sqlcmd='';

 

; with cte (Session_id, Context_info) as

(

select r1.session_id, r1.context_info from sys.dm_exec_requests r1 with (nolock)

inner join sys.dm_exec_requests r2 with (nolock)

on r1.session_id = r2.blocking_session_id

where r1.session_id > 50

and r1.session_id <> @@spid

union

select s.session_id, s.context_info from sys.dm_exec_sessions s with (nolock)

inner join sys.dm_exec_requests r with (nolock)

on s.session_id = r.blocking_session_id

and r.session_id <> @@spid

)

select @sqlcmd = @sqlcmd + 'kill ' + cast(session_id as varchar) +';' + char(0x0d) + char(0x0a) from cte

where context_info = 0xdba911; -- 0xdba911 for labelling low priority sessions

if @debug = 1

print @sqlcmd;

else

exec (@sqlcmd);

 

 

세션3의 스크립트를 SQL Server Job Agent에 등록하여 주기적으로 검사하여 자동으로 블록킹된 SPID 세션을 강제 종료 할 수 있도록 설정하여 사용하면 된다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3285/detect-and-automatically-kill-low-priority-blocking-sessions-in-sql-server/

 

2014-07-17 / 강성욱 / http://sqlmvp.kr

 

 

Sqlserver, 쿼리 차단, 블록킹, 잠금, 데드락, DB, mssqlserver, 락킹, block session, 블록세션, 쿼리튜닝, dB튜닝, 데이터베이스

'SQL Server > SQL Server Tip' 카테고리의 다른 글

Optimize for hint 쿼리 최적화  (0) 2015.07.23
TempDB 파일 사이즈 증가 시 경고 받기  (0) 2015.07.23
다양한 포맷의 이름 파싱 하기  (0) 2015.07.23
비결정적 사용자 정의 함수 사용으로 인한 느린 쿼리  (0) 2015.07.23
스냅숏 격리 수준(SNAPSHOT ISOLATION LEVEL)  (0) 2015.07.23

스냅숏 격리 수준(SNAPSHOT ISOLATION LEVEL)

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

트랜잭션 격리 수준은 SQL Server에 연결하여 실행하는 T-SQL문의 잠금 및 행 버전 관리 기능을 제공한다. 격리 수준은 여러 종류가 있으며 격리 수준 기능은 다음과 같다.

 

SNAPSHOT 격리 수준은 트랜잭션에서 읽은 데이터가 다른 트랜잭션으로부터 일관성이 유지되도록 한다. 트랜잭션은 시작되기 전에 커밋된 데이터 내용만 인식할 수 있다. 현재 트랜잭션이 시작된 후 다른 트랜잭션에서 수정한 데이터는 현재 트랜잭션에서 실행되는 결과에 표시되지 않는다. 따라서 트랜잭션의 결과는 트랜잭션 시작 당시 커밋된 데이터의 스냅숏을 가져오는 것처럼 보인다.

 

데이터베이스가 복구 중인 경우를 제외하면 SNAPSHOT 트랜잭션은 데이터를 읽는 동안 잠금을 요청하지 않는다. 데이터를 읽는 SNAPSHOT 트랜잭션은 다른 트랜잭션의 데이터 쓰기를 차단하지 않으며 데이터를 쓰는 트랜잭션은 SNAPSHOT 트랜잭션의 데이터 읽기를 차단하지 않는다.

 

데이터베이스 복구를 롤백하는 동안 SNAPSOT 트랜잭션은 롤백 중인 다른 트랜잭션이 잠근 데이터를 읽으려는 시도가 있을 경우 잠금을 요청한다. SNAPSHOT 트랜잭션은 해당 트랜잭션이 롤백될 때까지 차단된다. 잠금은 부여된 후 바로 해제 된다.

 

[스큐에 쓰기]

스냅숏 격리는 스큐 쓰기에 대해 취약하다. 스큐는 두 개의 동시 트랜잭션이 서로 다른 트랜잭션에서 서로 다른 데이터를 수정하기 때문에 쓰기 충돌이 발생하지 않는다.

Session 1

Session 2

CREATE TABLE A (x integer NOT NULL);

CREATE TABLE B (x integer NOT NULL);

  

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;

INSERT A (x) SELECT COUNT_BIG(*) FROM B;

  
 

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;

INSERT B (x) SELECT COUNT_BIG(*) FROM A;

COMMIT TRANSACTION;

COMMIT TRANSACTION;

  

select * from a

select * from b

 

 

결과를 살펴보면 스냅숏 격리에서 해당 스크립트에 있는 테이블(A, B)의 값은 0 값을 포함하는 하나의 행을 반환한다. 이것은 정확한 결과이지만 serializable 결과는 아니다. serializable 트랜잭션에서는 하나의 트랜잭션은 다른 트랜잭션이 시작되기 전에 완료되어야 한다. 그래서 두 번 째 트랜잭션은 첫 번째로 삽입된 행을 계산해야 한다. 강력한 serializable를 보증하기 위해서는 트랜잭션 레벨을 serializable로 적용 해야한다.

 

 

[충돌 감지 요소]

스냅숏 트랜잭션이 시작된 후 다른 트랜잭션에서 수정하려고 할 때 스냅숏 쓰기 충돌이 발생 한다.

Session 1

Session 2

-- Test table

CREATE TABLE dbo.Conflict

(

ID1 integer UNIQUE,

Value1 integer NOT NULL,

ID2 integer UNIQUE,

Value2 integer NOT NULL

);

 

-- Insert one row

INSERT dbo.Conflict

(ID1, ID2, Value1, Value2)

VALUES

(1, 1, 1, 1);

  

BEGIN TRANSACTION;

 

UPDATE dbo.Conflict

SET Value1 = 1

WHERE ID1 = 1;

  
 

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;

 

UPDATE dbo.Conflict

SET Value2 = 1

WHERE ID2 = 1;

COMMIT TRANSACTION;

  

 

 

메시지 3960, 수준 16, 상태 2, 줄 5

업데이트 충돌로 인해 스냅숏 격리 트랜잭션이 중단되었습니다. 스냅숏 격리를 사용하여 데이터베이스

'SW_TEST'의 테이블 'dbo.Conflict'에 직접 또는 간접적으로 액세스하여 다른 트랜잭션에 의해 수정되거나 삭제된 행을 업데이트, 삭제 또는 삽입할 수 없습니다. 트랜잭션을 다시 시도하거나 UPDATE/DELETE 문에 대한 격리 수준을 변경하십시오.

 

 

[외래키 문제]

충돌 감지는 외래키 관계에 있는 부모행에 적용 된다. 스냅숏 격리에서 자식행을 수정하는 경우 다른 트랜잭션의 부모행에 대한 변경은 충돌을 일으킬 수 있다. 실행계획에서 자동으로 외래키 검사를 요구하는 자식 테이블에 대한 모든 작업은 예상치 못한 충돌이 발생 할 수 있다.

CREATE TABLE dbo.Dummy

(

x integer NULL

);

 

CREATE TABLE dbo.Parent

(

ParentID integer PRIMARY KEY,

ParentValue integer NOT NULL

);

 

CREATE TABLE dbo.Child

(

ChildID integer PRIMARY KEY,

ChildValue integer NOT NULL,

ParentID integer NULL FOREIGN KEY REFERENCES dbo.Parent

);

 

INSERT dbo.Parent

(ParentID, ParentValue)

VALUES (1, 1);

 

INSERT dbo.Child

(ChildID, ChildValue, ParentID)

VALUES (1, 1, 1);

 

Session 1

Session 2

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;

SELECT COUNT_BIG(*) FROM dbo.Dummy;

  
 

UPDATE dbo.Parent SET ParentValue = 1 WHERE ParentID = 1;

UPDATE dbo.Child SET ParentID = NULL WHERE ChildID = 1;

UPDATE dbo.Child SET ParentID = 1 WHERE ChildID = 1;

  

 

 

 

(1개 행이 영향을 받음)

메시지 3960, 수준 16, 상태 2, 줄 10

업데이트 충돌로 인해 스냅숏 격리 트랜잭션이 중단되었습니다. 스냅숏 격리를 사용하여 데이터베이스 'SW_TEST'의 테이블 'dbo.Parent'에 직접 또는 간접적으로 액세스하여 다른 트랜잭션에 의해 수정되거나 삭제된 행을 업데이트, 삭제 또는 삽입할 수 없습니다. 트랜잭션을 다시 시도하거나 UPDATE/DELETE 문에 대한 격리 수준을 변경하십시오.

 

 

[truncate table issue]

트랜잭션이 시작된 이후 액세스하는 테이블이 truncate 된 경우 스냅숏 트랜잭션은 오류와 함께 실패 한다.

Session 1

Session 2

CREATE TABLE dbo.AccessMe

(

x integer NULL

);

 

CREATE TABLE dbo.TruncateMe

(

x integer NULL

);

  

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;

SELECT COUNT_BIG(*) FROM dbo.AccessMe

  
 

TRUNCATE TABLE dbo.TruncateMe;

SELECT COUNT_BIG(*) FROM dbo.TruncateMe;

  

 

 

메시지 3961, 수준 16, 상태 1, 줄 8

문에서 액세스한 개체가 이 트랜잭션이 시작된 후 다른 동시 트랜잭션의 DDL 문에 의해 수정되어 데이터베이스 'SW_TEST'에서 스냅숏 격리 트랜잭션이 실패했습니다. 메타데이터에 버전이 지정되지 않았으므로 이 트랜잭션은 허용되지 않습니다. 스냅숏 격리를 함께 사용하여 메타데이터에 대해 동시 업데이트를 수행하면 일관되지 않은 결과가 발생할 수 있습니다.

 

 

 

[참고자료]

  • SET TRANSACTION ISOLATION LEVEL :

http://msdn.microsoft.com/ko-kr/library/ms173763.aspx

  • SNAPSHOT Isolation Level :

http://sqlperformance.com/2014/06/sql-performance/the-snapshot-isolation-level

 

 

2014-07-08 / 강성욱 / http://sqlmvp.kr

 

 

트랜잭션 격리 수준, 스냅숏 격리 수준, ISOLATION LEVEL, SQL Server, MSSQL, 잠금, 트랜잭션관리, 쿼리튜닝

'SQL Server > SQL Server Tip' 카테고리의 다른 글

다양한 포맷의 이름 파싱 하기  (0) 2015.07.23
비결정적 사용자 정의 함수 사용으로 인한 느린 쿼리  (0) 2015.07.23
SQL Server 파라메터 스니핑의 다양한 접근  (0) 2015.07.23
잘못된 개체를 참조하는 SQL Server 개체 찾기  (0) 2015.07.23
전체 백업에서 포함되는 트랜잭션 범위  (0) 2015.07.23

SQL Server에서 차단을 확인하는 다양한 방법

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server는 많은 수의 동시 사용자 요청을 처리한다. 수 많은 클라이언트가 요청을 하면 여러 프로세스가 동일한 자원에 대해 동시에 액세스하기 때문에 충돌이 발생 할 수 있다. 이때 차단이 발생하는데 SQL Server에서 발생하는 차단에 대해서 확인 하는 여러 가지 방법에 대해서 알아 본다.

 

[SSMS 작업 모니터]

SSMS에서 제공하는 작업 모니터는 SQL Server의 리소스 및 프로세스에 대한 잠금 정보를 확인 할 수 있다. SSMS의 개체 탐색기에서 [서버] –[마우스 오른쪽 클릭] –[작업모니터]를 선택한다.

 

 

프로세스 탭을 클릭하면 페이지가 확장되고 현재 실행되고 있는 프로세스에 대한 정보와 차단되는 프로세스ID를 확인 할 수 있다.

 

 

차단 프로세스를 종료 하고 싶은 경우에는 해당 목록에서 마우스 오른쪽 버튼을 클릭하여 프로스스 중지를 선택 한다.

 

 

[성능모니터]

SQLServer:Locks 항목에서 발생하는 잠금에 대해서 확인 할 수 있다.

 

  • Average Wait Time(ms) : 대기한 각 잠금 요청에 대한 평균 대기 시간(밀리초)
  • Lock Requests/sec : 잠금 관리자에서 요청한 새 잠금 및 잠금 반환 수
  • Lock Timeouts (timeout >0)/sec : 시간 초과된 잠금 요청 수. NOWAIT 잠금에 대한 요청이 포함되어 있지 않다.
  • Lock Timeouts/sec : 시간 초과된 잠금 요청 수
  • Lock Wait Time (ms) : 마지막 1초 동안의 잠금에 대한 총 대기 시간(밀리초)
  • Lock Waits/sec : 즉시 처리될 수 없어서 잠금 허가 전에 호출자가 대기해야 하는 잠금 요청 수
  • Number of Deadlocks/sec : 교착 상태를 일으킨 잠금 요청 수

 

 

[DMV]

Sys.dm_exec_requests 에서는 현재 요청에 대한 쿼리 계획, 요청이 실행된 시간, 블록킹 세션, 요청을 기다리는 자원의 이름 등 자세한 정보가 포함되어 있다.

USE [master]

GO

SELECT session_id

,blocking_session_id

,wait_time

,wait_type

,last_wait_type

,wait_resource

,transaction_isolation_level

,lock_timeout

FROM sys.dm_exec_requests

WHERE blocking_session_id <> 0

GO

 

 

  • Blocking_session_id : 블록킹 세션의 SPID
  • Wait_type : 대기의 종류
  • Wait_time : 대기 시간(밀리초)
  • Last_wait_type : 마지막 대기 타입
  • Wait_resource : 요청을 기다리는 자원
  • Transaction_isolation_level : 트랜잭션 격리 수준
  • Lock_timeout : 잠금 제한 시간

 

 

Sys.dm_tran_locks에서는 현재 잠금에 대한 차단 프로세스를 확인 할 수 있다.

USE [master]

GO

SELECT * from sys.dm_tran_locks

 

 

 

다음 스크립트는 sys.dm_tran_locks 와 sys.partitions를 사용하여 특정 데이터베이스의 잠금을 확인한다.

USE [master]

GO

SELECT tl.resource_type

,tl.resource_associated_entity_id

,OBJECT_NAME(p.object_id) AS object_name

,tl.request_status

,tl.request_mode

,tl.request_session_id

,tl.resource_description

FROM sys.dm_tran_locks tl

LEFT JOIN sys.partitions p

ON p.hobt_id = tl.resource_associated_entity_id

WHERE tl.resource_database_id = DB_ID()

GO

 

 

 

Sys.dm_os_waiting_taasks는 차단된 프로세스의 정보를 나타낸다. 차단된 프로세스는 session_id에서 확인 할 수 있다.

USE [master]

GO

SELECT w.session_id

,w.wait_duration_ms

,w.wait_type

,w.blocking_session_id

,w.resource_description

,s.program_name

,t.text

,t.dbid

,s.cpu_time

,s.memory_usage

FROM sys.dm_os_waiting_tasks w

INNER JOIN sys.dm_exec_sessions s

ON w.session_id = s.session_id

INNER JOIN sys.dm_exec_requests r

ON s.session_id = r.session_id

OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t

WHERE s.is_user_process = 1

GO

 

 

 

[SQL Profiler]

프로파일러에서 잠금 및 교착에 관한 이벤트를 추적할 수 있다. 프로파일러를 실행하여 모든 이벤트 표시를 선택한다. Event에서 Locks 항목을 체크한다.

 

 

잠금 및 교착이 발생 하였을 때 현재 상태를 확인하고 해결하기 위해 도구 사용법을 습득하고 잠금에 관한 정보를 추적하여 근본적인 원인을 해결 할 수 있도록 한다.

 

 

2014-05-12 / 강성욱 / http://sqlmvp.kr

 

SQLSERVER, mssql, SQL튜닝, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, 잠금, 교착, lock, wait, sys.dm_exec_requests, sys.dm_tran_locks, sys.dm_os_waiting_tasks

'SQL Server > SQL Server Tip' 카테고리의 다른 글

Windows Event Log에 SQL Server Agent Log 기록  (0) 2015.07.23
SQL Server Agent Error log 위치 변경  (0) 2015.07.23
SQL Server CPU 사용률이 높은 프로세서 및 쿼리 찾기  (0) 2015.07.23
Collation 변경  (0) 2015.07.23
인덱스 리빌드 동작 (Gather Streams from SORT)  (0) 2015.07.23

SQL Server CPU 사용률이 높은 프로세서 및 쿼리 찾기

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

SQL Server에서 CPU사용률이 높게 나타났다. 어떤 프로세스가 어떤 쿼리를 실행하여 CPU 사용률이 높은지 SPID와 KPID를 사용하여 알아보자.

 

[시작] – [실행] - [perfmon] 또는 성능 카운터를 실행한다. [Thread] 카운터 목록에서 [% Processor Time], [ID Thread]를 선택하고 인스턴스에 [sqlservr]를 선택 한다.

 

 

카운터를 추가하면 다음과 같이 스레드를 모니터링 할 수 있으며 이때 프로세서 값이 높은 스레드를 찾는다. 아래 그림에서는 스레드 1776이 CPU를 많이 사용하고 있는 것을 확인 할 수 있다.

 

 

스레드 ID 값을 사용하여 SPID 정보를 확인한다. SPID는 SQL Server 프로세스 ID 이며 KPID는 커널 프로세스ID 이다. KPID 1776의 SPID는 66이다.

SELECT spid, kpid, dbid, cpu, memusage FROM sysprocesses WHERE kpid=1776

 

 

SPID 66번의 상태를 확인하면 현재 실행중인 것을 알 수 있다.

SELECT spid, kpid, status, cpu, memusage, open_tran, dbid FROM sysprocesses WHERE spid=66

 

 

DBCC INPUTBUFFER 명령어를 사용하면 현재 입력된 쿼리의 정보를 확인할 수 있다. 현재 WHILE 구문의 쿼리로 인하여 CPU 사용률이 높은 것을 확인 할 수 있다.

dbcc inputbuffer(66)

 

 

 

CPU 사용률이 높은 쿼리를 찾았으면 해당 쿼리의 문제점을 수정 할 수 있도록 한다.

 

2014-05-09 / 강성욱 / http://sqlmvp.kr

 

 

CPU고부하쿼리, DB튜닝, 쿼리튜닝, DB강좌, mssql, SQL, DBA, KPID, SPID, DBCC INPUTBUFFER,

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server Agent Error log 위치 변경  (0) 2015.07.23
SQL Server에서 차단을 확인하는 다양한 방법  (0) 2015.07.23
Collation 변경  (0) 2015.07.23
인덱스 리빌드 동작 (Gather Streams from SORT)  (0) 2015.07.23
최소한의 다운타임으로 데이터베이스 이동하기  (0) 2015.07.23

인덱스 리빌드 동작 (Gather Streams from SORT)

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, SQL2014

 

SQL Server에서는 인덱스 리빌드 작업을 통하여 조각난 인덱스를 다시 작성한다. SQL Server 데이터베이스 엔진에서는 기본 데이터에 삽입, 업데이트, 삭제 작업을 수행 할 때마다 인덱스를 자동으로 유지 관리한다. 이러한 수정이 빈번이 발생하면 시간이 흐름에 따라 인덱스의 정보가 조각화 되어 데이터베이스 내에 흩어지게 될 수 있다.

조각화는 인덱스의 논리적 페이지 순서가 데이터파일의 물리적 순서와 일치하지 않을 때 나타난다. 조각화가 심할 경우에는 쿼리의 성능이 저하될 수 있다.

 

이번 포스트는 CSS SQL Engineers에 게시된 내용으로 인덱스 리빌드 동안 발생하는 CPU 자원의 사용량에 대해서 알아본다. 필자가 읽고 이해한 내용을 정리하였으며 번역의 오류 및 기술적 오류 가능성이 있으므로 원문은 참고 하길 바란다.

 

64 CPU, 128 GB RAM 시스템에서 1조 행이 있는 테이블에 인덱스 리빌드 작업을 테스트 하였다. 인덱스 리빌드를 시작하였을 때 64개의 CPU 사용량이 100%였다. 그러나 일정 시간이 지나서 1개의 CPU만이 100% 사용되는 것을 확인 할 수 있다.

 

리빌드가 시작되면 64개의 작업단위로 나누어 각각의 nested loop와 sort 작업이 수행되며 마지막에 Gather Streams을 거쳐 인덱스의 인서트 과정이 진행된다. 실행 계획은 아래 그림과 같으며 Sort 작업 다음에 Gather Streams 작업에서 CPU Node 사용이 1로 된 것을 확인 할 수 있다.

 

 

 

소비자는 각 작업자에서 행을 가져와 메모리 트리를 유지 한다. 64개의 병렬 작업이 있을 경우 64개의 트리 항목이 있으며 MAXOP 16일 경우 트리는 16 항목이 포함된다. 4개의 프로세스가 있는 경우 다음과 같은 순서로 진행 된다.

1.Get Row From Worker/Partition #1 – Insert into tree

2.Get Row From Worker/Partition #2 – Insert into tree

3.Get Row From Worker/Partition #3 – Insert into tree

4.Get Row From Worker/Partition #4 – Insert into tree

5.While (entries in tree)

{

Output lowest, sorted value from tree

Get Row from Worker/Partition you just removed from tree as lowest value

}

 

 

 

리빌드가 진행 되는 동안 sys.dm_exec_requests, sys.dm_os_waiting_tasks를 확인해 보면 CPU 노드의 활동을 확인 할 수 있다. Sys.dm_os_wait_stats를 확인해 보면 CXPACKET 대기가 증가한 것을 볼 수 있는데 병렬 처리에서 나머지 작업이 완료되기를 기다리는 부분이다.

 

병렬계획을 최적화 하기 위해 MAXDOP를 조절하며 테스트를 진행한 결과이다. 많은 CPU를 사용할 때 더 빠른 작업을 나타내었지만 계획의 마지막 CXPACKET 대기는 DOP 수준으로 크게 변경되지는 않았다.

  • 64 CPUs = 01:50:00
  • 32 CPUs = 02:17:00
  • 16 CPUs = 03:16:00

 

테스트를 통하여 인덱스 리빌드는 병렬자원을 활용하는 것을 알 수 있으며 각 작업에 대해서는 많은 CPU를 사용할 때 더 빠른 작업을 나타내었다. 하지만 CXPACKET 대기에서는 큰 차이가 나지 않아 여러 쿼리가 실행되는 서버의 경우 MAXDOP를 조절하여 다른 서비스에 영향을 주지 않도록 하면 최적화된 리빌드 작업을 할 수 있을 듯 하다.

 

[참고자료]

  • How It Works: Behavior of a 1 Trillion Row Index Build (Gather Streams from SORT)

http://blogs.msdn.com/b/psssql/archive/2014/04/29/how-it-works-behavior-of-a-1-trillion-row-index-build-gather-streams-from-sort.aspx

 

 

 

 

 

2014-05-02 / 강성욱 / http://sqlmvp.kr

 

SQLSERVER, mssql, SQL튜닝, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, 인덱스리빌드, 병렬처리, CXPACKET

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server CPU 사용률이 높은 프로세서 및 쿼리 찾기  (0) 2015.07.23
Collation 변경  (0) 2015.07.23
최소한의 다운타임으로 데이터베이스 이동하기  (0) 2015.07.23
백업 LSN 이해하기  (0) 2015.07.23
암호화 오버헤드 (작성자의 주관적인 자료임)  (0) 2015.07.23

최소한의 다운타임으로 데이터베이스 이동하기

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server를 운영하다보면 디스크의 공간 부족 또는 디스크의 성능 등으로 인하여 새로운 드라이브로 데이터베이스를 이동해야하는 상황이 발생 할 수 있다. 이때 대용량 데이터베이스를 최소한의 다운타임으로 이동하기 위한 여러가지 방법에 대해서 알아보자. 각 방법에는 장단점이 있다.

 

[데이터베이스 분리 후 이동하여 연결하기]

데이터베이스를 이동 할 때 많이 사용하는 방법이다. 데이터베이스를 분리하여 데이터 파일을 이동한 다음 데이터베이스를 연결 하는 방법이다.

exec sp_detach_db DBName

 

File Move

 

exec sp_attach_db DBName, Filepath

 

 

장점

  • 데이터베이스를 이동하는 가장 쉬운 방법이다.
  • 테이블과 인덱스에 대한 조각화를 생성하지 않는다.

단점

  • 파일이 이동하는 동안 데이터베이스를 사용할 수 없다.
  • 복제 된 데이터베이스가 분리 된 경우 배포를 할 수 없다.
  • 데이터베이스가 분리 되면 모든 메타데이터가 삭제 된다. 어떤 로그인의 계정의 기본 데이터베이스인 경우 mater 데이터베이스로 변경 된다. 또한 데이터베이스간 소유권이 끊어진다.
  • 데이터베이스를 분리 하기 전 스냅샷을 모두 삭제 해야 한다.
  • 데이터베이스가 미러링 되어 있는 경우 미러링 세션이 종료 될 때 까지 분리 할 수 없다.

 

 

[데이터베이스 백업 후 백업 파일을 이용하여 복원]

데이터베이스를 백업하고 WITH MOVE switch를 사용하여 새 파일의 위치를 지정하는 방법이다.

 

장점

  • 백업과 복원 방법만 알면 누구나 할 수 있다.
  • 테이블과 인덱스에 대한 조각화를 생성하지 않는다.

단점

  • 데이터베이스 복원이 완료 될 때까지 데이터베이스나 파일 그룹에 액세스 할 수 없다.
  • 백업 후 데이터가 변경된 경우 최근 백업을 다시 복원해야 한다.

 

 

[데이터베이스 OFFLINE 후 데이터베이스 이동]

데이터베이스를 오프라인으로 설정하고 수동으로 파일을 이동. 데이터베이스의 메타 데이터를 업데이트하는 명령을 실행하고 다시 온라인 시키는 방법이다.

 

장점

  • 데이터베이스 분리처럼 Detach – Attach 방법을 사용한다.
  • 테이블과 인덱스에 조각화를 생성하지 않는다.
  • 데이터베이스 분리와 달리 메타 데이터를 보존 한다.
  • 배포 및 미러된 데이터베이스도 작업 할 수 있다.

단점

  • 파일이 이동하는 동안 데이터베이스에 액세스 할 수 없다.

 

 

[새로운 파일 그룹을 추가하여 이동]

새로운 파일 그룹을 생성하고 테이블과 인덱스를 새로운 파일그룹에 다시 작성한다. 작업이 완료되면 기존 파일 그룹 삭제가 가능하다.

 

장점

  • 테이블과 인덱스에 대한 조각화를 생성하지 않는다.
  • 데이터베이스 메타 데이터가 보존 된다.

단점

주 파일 그룹에 대해서는 작동 하지 않는다.

  • 데이터베이스 설계가 변경되기 때문에 신중한 테스트가 필요하다.
  • 이전의 방법보다 많은 작업이 필요하다.

 

 

[로그 전달을 사용한 데이터베이스 이동]

로그 전달을 구성하여 데이터베이스를 이동한다. 복구모드가 심플일 경우 전체 복구 모두로 전환하여 구성 할 수 있다.

 

장점

  • 테이블과 인덱스에 대한 조각화를 생성하지 않는다.
  • 데이터베이스 메타 데이터가 보존된다.
  • 로그 전달 중에도 사용자는 기존 데이터베이스에 계속 액세스 할 수 있다.

단점

  • SQL Server Agent 실행이 필요하다.
  • 공유 폴더를 사용하기 때문에 폴더 사용 권한 검토가 필요하다.
  • 로그 전달에 대한 전문적인 지식이 필요하다.

 

 

[수동으로 로그 전달 및 복원]

필자가 주로 사용하는 방법으로 대용량 데이터베이스의 경우 전체 백업을 수행하여 먼저 복원해 놓고 주기적으로 트랜잭션 로그 백업을 복사하여 복원하는 방법이다. 마지막에 사용자 접근을 차단하고 마지막 로그 백업을 실행하여 새로운 데이터베이스에 복원한다.

 

장점

  • 테이블과 인덱스에 대한 조각화를 생성하지 않는다.
  • 데이터베이스 복원 시 이름 변경 및 파일 경로 변경 가능하다.
  • 데이터베이스 메타 데이터가 보존된다.
  • 기존 데이터베이스에는 사용자가 계속 액세스 할 수 있다.
  • 로그 전달보다 적은 구성이 사용된다.(백업, 복원만 하면 됨)

단점

  • 백업을 복원할 때 WITH MOVE 옵션을 사용해야 한다.

 

 

[DBCC SHRINK FILE 후 데이터베이스 이동]

DBCC SHRINKFILE 명령을 사용하여 빈 공간을 축소. 데이터베이스를 이동하는 방법이다. (굳이 추천하고 싶은 방법은 아니다.) 이 방법은 위의 모든 방법보다 가장 느린 방법이다. 이 방법은 성능에 영향을 미칠 수 있으므로 주의해야 한다.

 

장점

  • SHRINK FILE 작업 시 사용자가 데이터베이스에 계속 액세스 할 수 있다.

단점

  • 다른 방법보다 더 오래 걸릴 수 있다.
  • 테이블과 인덱스에 조각화를 많이 생산한다.
  • 로그가 기록되므로 전체 복구 모드의 경우 적합하지 않다.
  • SQL 로그 전달이 구성되어 있는 경우 권장되지 않는다.
  • 많은 시스템 리소스(특히 디스크IO)를 소비하므로 시스템 성능에 문제가 발생 할 수 있다.

 

 

대용량의 데이터베이스를 이동해야 하는 경우 각 운영 환경에 맞는 방법을 선택하여 최적의 시나리오를 구성하여 연습을 해야 한다. 특히 백업과 파일이동, 복원의 경우 해당 파일의 용량, 복사에 따른 네트워크 속도, 디스크의 성능 등을 고려하여 시간을 산정하면 최소한의 다운타임으로 이동이 가능하리라 생각한다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3212/options-to-move-a-big-sql-server-database-to-a-new-drive-with-minimal-downtime/

 

 

2014-04-24 / 강성욱 / http://sqlmvp.kr

 

 

SQLSERVER, mssql, SQL튜닝, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, 데이터베이스 백업, 트랜잭션 백업, 전체 백업, 데이터베이스 이동, 파일그룹, 데이터베이스 복원

'SQL Server > SQL Server Tip' 카테고리의 다른 글

Collation 변경  (0) 2015.07.23
인덱스 리빌드 동작 (Gather Streams from SORT)  (0) 2015.07.23
백업 LSN 이해하기  (0) 2015.07.23
암호화 오버헤드 (작성자의 주관적인 자료임)  (0) 2015.07.23
데이터베이스 연결 정보 수집  (0) 2015.07.23

백업 LSN 이해하기

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

 

SQL Server에서는 전체 백업, 로그 백업, 차등 백업 등 다양한 백업을 지원한다. 백업을 진행 하면 각 백업에 대해 고유한 LSN(Log Sequence Number)이 생성된다. 백업 파일을 복원 할 때 LSN을 이용하여 복원하기 때문에 LSN에 대한 이해는 중요하다고 할 수 있다.

 

데이터베이스를 복원 할 때 데이터베이스 복원 시퀀스는 데이터베이스의 전체 백업에서 시작해야 한다. 차등 및 트랜잭션 로그 백업 파일에서 복원을 진행 할 수 없다.

데이터베이스를 복원할 때 4가지(FirstLSN, LastLSN, CheckpointLSN, DatabaseBackupLSN)의 중요한 LSN이 있다. 이 값은 SQL Server 백업 파일에서 HEADERONLY 명령어로 확인 할 수 있으며 아래 스크립트를 사용하여 백업 파일에 대한 헤더 정보를 검색 할 수 있다.

USE [master]

RESTORE HEADERONLY FROM DISK = N'C:\Temp\F1.BAK'

RESTORE HEADERONLY FROM DISK = N'C:\Temp\T1.TRN'

RESTORE HEADERONLY FROM DISK = N'C:\Temp\T2.TRN'

RESTORE HEADERONLY FROM DISK = N'C:\Temp\D1.BAK'

 

헤더 정보를 엑셀로 정리해서 보면 다음과 같이 정리 할 수 있다. A컬럼의 정보 백업 유형과 순서를 나타낸다

  • F1 : 전체 백업
  • T1 : 첫 번째 로그 백업
  • D1 : 첫 번째 차등 백업

 

 

전체 백업의 LSN에 대한 특징은 다음과 같다.

  • 첫 번째 전체 백업의 경우 DatabaseBackupLSN 값은 0 이다.
  • FirstLSN값과 CheckpointLSN 값이 동일하다.

 

차등 백업의 LSN속성은 다음과 같다.

  • 차등 백업에 대한 DatabaseBackupLSN 값은 차등 데이터베이스 백업을 적용하기위해 전체 데이터베이스 백업을 식별한다.
  • 채등 백업에 대한 DatbaseBackupLSN 값은 전체 백업의 CheckpointLSN값과 동일한다.
  • CheckpointLSN 값은 차등 백업 이후 첫 번째 트랜잭션 로그 백업의 CheckpointLSN에 매핑 된다.

 

트랜잭션 로그 백업의 LSN 속성은 다음과 같다.

  • 모든 트랜잭션 로그 백업에 대해 고유한 LSN을 식별 한다.
  • 트랜잭션 로그의 LSN 체인은 전체 또는 차등 데이터베이스 백업에 영향을 받지 않는다.
  • LSN은 연속된 값으로 높은 LSN 값은 지정된 시간 이후를 나타낸다.

 

 

SQL Server의 전체 백업과 트랜잭션 로그 백업의 LSN 매핑에 대한 이해를 다음 그림을 통해 살펴보자. 첫 번째 트랜잭션로그 백업의 FirstLSN은 첫 번째 전체 백업을 식별한다. 두 번째 트랜잭션 로그 백업은 첫 번째 트랜잭션 로그 백업의 LastLSN을 참고하여 FirstLSN으로 기록한다.

 

 

SQL Server의 전체 백업과 차등 백업의 LSN 매핑에 대한 이해를 다음 그림을 통해 살펴보자. 차등 데이터베이스 백업은 전체 백업 DatabaseBackupLSN과 같다.

 

SQL Server의 트랜잭션 로그 백업과 차등 백업의 LSN 매핑에 대한 이해를 다음 그림을 통해 살펴보자. 차등백업의 LSN + 1은 트랜잭션 로그 백업의 FirstLSN과 LastLSN 사이에 있을 것이다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3209/understanding-sql-server-log-sequence-numbers-for-backups/

 

 

2014-04-23 / 강성욱 / http://sqlmvp.kr

 

SQLSERVER, mssql, SQL튜닝, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, 데이터베이스 백업, 트랜잭션 백업, 전체 백업, LSN, 로그시퀀스,

'SQL Server > SQL Server Tip' 카테고리의 다른 글

인덱스 리빌드 동작 (Gather Streams from SORT)  (0) 2015.07.23
최소한의 다운타임으로 데이터베이스 이동하기  (0) 2015.07.23
암호화 오버헤드 (작성자의 주관적인 자료임)  (0) 2015.07.23
데이터베이스 연결 정보 수집  (0) 2015.07.23
FileStream Garbage Collection  (0) 2015.07.23

암호화 오버헤드 (작성자의 주관적인 자료임)

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server에서 암호화된 컬럼을 복호화하는데 얼마나 많은 오버헤드가 증가할까? 16자리의 암호화된 카드번호를 복호화 과정을 통하여 발생하는 오버헤드를 측정해 보자.

 

실습을 위해 카드번호를 저장할 테이블을 생성한다.

CREATE TABLE dbo.tblCustomerData

(

CustomerID int identity(1,1) NOT NULL,

CreditCardNumberPlainText varchar(16) NOT NULL,

CreditCardNumberEncrypted varbinary(MAX) NULL

)

 

다음 포스트를 참고하여 열 수준 암호화를 진행 한다.

 

 

SSIS 패키지를 이용하여 암호화 화는 것과 일반문을 읽는 성능을 비교 하였다. 비교 방법은 100만건 처리 단위로 시간을 기록하여 1000만건 처리를 3회 실시하였다.

 

 

결과는 다음과 같이 나타났다.

 

 

 

이 결과를 보면 일반 컬럼을 처리할 때보다 암호화된 것을 복호화 할 때 2배 정도 오버헤드가 발생하는 것을 확인 할 수 있다. 물론 각 시스템의 성능이나 암호화 수준에 따라 발생하는 오버헤드는 다르지만 암호화에 따른 오버헤드가 발생한다는 것을 확인 할 수 있다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3196/how-much-overhead-does-encryption-add-to-a-sql-server-query/

 

 

2014-04-16 / 강성욱 / http://sqlmvp.kr

 

SQLSERVER, mssql, SQL튜닝, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, 암호화, 복호화, DB암호화, 컬럼 암호화, 암호화오버헤드, 대칭키암호화, 비대칭키,AES 암호화, RSA암호화, 테이블 암호화

 

 

 

 

'SQL Server > SQL Server Tip' 카테고리의 다른 글

최소한의 다운타임으로 데이터베이스 이동하기  (0) 2015.07.23
백업 LSN 이해하기  (0) 2015.07.23
데이터베이스 연결 정보 수집  (0) 2015.07.23
FileStream Garbage Collection  (0) 2015.07.23
프로시저 캐시에서 중복 쿼리 계획 확인  (0) 2015.07.23

데이터베이스 연결 정보 수집

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server 데이터베이스는 여러 응용프로그램에서 연결하여 사용한다. 문제는 이러한 응용프로그램이 제대로 연결을 종료하지 않을 때 발생 한다. 커넥션이 연결된 채로 종료 되지 않으면 다른 응용프로그램에서 커넥션을 할당하지 못하는 문제가 발생한다.

연결 개수를 모니터링 하여 어느 응용프로그램에서 연결을 많이 사용하는지 알아보자. 연결 개수를 모니터링 하기 위한 스크립트는 다음과 같다.

SELECT [host_name],

[program_name],

login_name,

count(c.session_id ) num_sessions,

getdate()

FROM sys.dm_exec_connections c JOIN

sys.dm_exec_sessions s on c.session_id = s.session_id

GROUP BY host_name, program_name, login_name ORDER BY 4 DESC

 

 

 

다음 스크립트를 실행하면 위의 연결 정보를 저장하는 테이블과 Job Agent를 생성하여 주기적으로 데이터를 수집할 수 있도록 생성한다.(수집 일정에 대한 부분은 사용자가 따로 설정 해주어야 함)

CREATE TABLE dbo._demo_sessions_alert(

[host_name] nvarchar(128) NULL,

[program_name] nvarchar(128) NULL,

login_name nvarchar(128) NULL,

num_sessions int NULL,

capture_time datetime NULL

) ON [PRIMARY]

GO

 

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories

WHERE name=N'DBA' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Sessions Monitoring',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=2,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'Inserts log records when number of connections is higher than 50',

@category_name=N'DBA',

@owner_login_name=N'sa',

@job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Log info',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'INSERT INTO dbo._demo_sessions_alert

SELECT host_name, program_name, login_name, count(c.session_id ) num_sessions, getdate()

FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s on c.session_id = s.session_id

GROUP BY host_name,program_name,login_name ORDER BY 4 DESC',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

 

 

 

수집된 정보를 활용하여 임계치 이상 연결이 생성되어 있을 때 어떤 응용프로그램에서 많은 연결을 사용하는지 파악 할 수 있다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3193/automated-collection-of-sql-server-database-connections-for-monitoring/

 

 

2014-04-15 / 강성욱 / http://sqlmvp.kr

 

 

SQLSERVER, mssql, SQL튜닝, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, SQL 연결, sql connection, sql session

'SQL Server > SQL Server Tip' 카테고리의 다른 글

백업 LSN 이해하기  (0) 2015.07.23
암호화 오버헤드 (작성자의 주관적인 자료임)  (0) 2015.07.23
FileStream Garbage Collection  (0) 2015.07.23
프로시저 캐시에서 중복 쿼리 계획 확인  (0) 2015.07.23
Sp_reset_connection  (0) 2015.07.23

FileStream Garbage Collection

 

  • Version : SQL Server 2012

 

SQL Server는 FILESTREAM 가비지 수집기를 실행하여 불필요한 FILESTREAM 파일을 삭제 한다. 가비지 수집기를 사용하여 FILESTREAM 컨테이너 안의 삭제된 파일을 모두 정리해야 해당 컨테이너를 제거할 수 있다. FILESTREAM 가비지 수집기는 자동으로 실행 된다. 그러나 가비지 수집기가 실행되기 전에 컨테이너를 제거할 경우 sp_filestream_force_garbage_collection을 사용하여 가비지 수집기를 수동으로 실행 할 수 있다.

 

컨테이너 지정 안 할 경우

USE FSDB;

GO

EXEC sp_filestream_force_garbage_collection @dbname = N'FSDB';

 

컨테이너 지정 할 경우

USE FSDB;

GO

EXEC sp_filestream_force_garbage_collection @dbname = N'FSDB' @filename = N'FSContainer';

 

요청한 데이터베이스 및 FILESTREAM 컨테이너에서 완료될 때까지 FILESTREAM 가비지 수집기 태스크를 명시적으로 실행 한다. 더 이상 필요하지 않을 경우 파일은 가비지 수집 프로세스에서 제거된다. 작업을 완료하는데 필요한 시간은 최근 FILESTREAM 데이터에 발생한 DML 작업의 양과 해당 데이터베이스 또는 컨테이너에 있는 FILESTREAM 데이터의 크기에 따라 결정 된다. 데이터베이스를 온라인 상태로 설정하여 이 작업을 실행 할 수 있지만 가바지 수집 프로세스가 수행하는 동안 I/O 저하로 인하여 성능이 저하 될 수 있다.

 

FILESTREAM을 사용하기 위해서는 SQL Server 구성관리자에서 활성화로 상태를 변경해야 한다. MSSQL Server 서 비스에서 [속성] – [FILESTREAM]탭에서 사용여부를 체크한다. Windows에서 FILESTREAM 데이터를 읽고 쓰려는 경우 파일 I/O 액세스에 FILESTREAM 사용을 체크한다. 원격 클라이언트가 이 공유에 저장된 FILESTREAM 데이터에 액세스해야 하는 경우 원격 클랑이언트 항목을 체크 한다.

 

SSMS를 실행하여 쿼리창에서 아래 스크립트를 실행하여 FILESTREAM 활성화 한다.

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

 

속성 변경을 완료 하였으면 SQL Server 서비스를 재시작 한다.

 

다음 예제를 통해서 FileStream Garbage Collection 동작을 살펴보자. 스크립트를 실행하면 데이터베이스를 생성하고 파일 세트를 축적 한다.

create database dbFS

on primary(name='FSData', filename = 'd:\SQL_Data\FSData.mdf'),

filegroup RSFX CONTAINS FILESTREAM DEFAULT(name='Rsfx', filename='d:\SQL_Data\RsFx')

log on(name='FSLog', filename='d:\SQL_Data\FSData.ldf')

go

 

set nocount on

go

 

ALTER DATABASE dbFS set recovery full

go

 

backup database dbFS to disk = 'd:\SQL_Backup\DelMe.bak' with init

go

 

use dbFS

go

 

CREATE TABLE RsFx

(

[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,

[Text] VARBINARY(MAX) FILESTREAM NULL

)

GO

 

insert into RsFx values(NEWID(), 0x10101010)

go

 

update RsFx set Text = 0x20202020

go 100

 

FILESTREAM 폴더를 확인해 보면 다음과 같이 파일이 생성된 것을 확인 할 수 있다. 로그 백업을 수행하지 않았기 때문에 파일이 남아 있으며 파일 이름은 트랜잭션 로그 레코드와 연관된 LSN 값이다.

 

 

FILESTREAM의 파일이름은 실제 LSN 이름과 일치한다. FILESTREAM 가비지 수집기가 정리 요구사항을 결정하는데 이 파일을 사용한다.

 

주의 할 점은 FILESTREAM 컨테이너에서 직접 파일 삭제는 데이터베이스 손상으로 간주되며 DBCC CHECKDB 손상을 보고 한다.

 

파일 스트림의 가비지 컬렉터를 가장 쉽게 하는 방법은 로그 백업과 체크포인트를 실행하는 것이다.

backup log dbFS to disk = 'c:\SQL_Backup\DelMe.bak' with init

go

checkpoint

go

 

 

FILESTREAM의 삭제 정보는 관리자 전용연결을 사용하여 삭제 테이블을 볼 수 있다. 반드시 관리자 전용연결(dac)을 사용해야 한다.

use dbFS

go

select * from sys.objects where name like '%tombstone%'

go

select * from sys.filestream_tombstone_2073058421 order by oplsn_fseqno asc, oplsn_bOffset asc, oplsn_slotid asc

go

 

 

GUID 열 및 FILESTREAM 값 이름을 참조 할 수 있도록 데이터의 버전 사본을 확인 할 수 있다. 가비지컬렉터는 이 테이블 정보를 사용하여 정렬된 LSN 및 클린업 파일에 접근 한다.

 

[참고자료]

  • sp_filestream_force_garbage_collection :

http://technet.microsoft.com/ko-kr/library/gg492195.aspx

  • FileStream (RsFx) Garbage Collection :

http://blogs.msdn.com/b/psssql/archive/2011/06/23/how-it-works-filestream-rsfx-garbage-collection.aspx

  • FileStream (RsFx) Garbage Collection–Part (2) :

http://blogs.msdn.com/b/psssql/archive/2014/04/04/how-it-works-filestream-rsfx-garbage-collection-part-2.aspx

 

 

2014-04-10 / 강성욱 / http://sqlmvp.kr

 

SQLSERVER, mssql, SQL튜닝, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, filestream, 파일스트림, 가비지컬렉터, dac, 파일 클린업

'SQL Server > SQL Server Tip' 카테고리의 다른 글

암호화 오버헤드 (작성자의 주관적인 자료임)  (0) 2015.07.23
데이터베이스 연결 정보 수집  (0) 2015.07.23
프로시저 캐시에서 중복 쿼리 계획 확인  (0) 2015.07.23
Sp_reset_connection  (0) 2015.07.23
성능분석 17탄 – SQLServer 블록킹 / 네트워크 관련 성능 카운터  (0) 2015.07.23

프로시저 캐시에서 중복 쿼리 계획 확인

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server에서 쿼리를 실행하면 실행 계획이 생성되며 이는 캐시에 저장 된다. 동일한 쿼리가 호출 될 경우 캐시에서 실행 계획이 재사용 된다. 실행계획이 중복되는 경우는 저장 프로시저에서 외부에서 만든 임시 테이블을 참조하는 경우이다. 외부 임시 테이블을 참조하는 프로시저를 호출하는 경우 실행 계획 캐시는 SPID(프로세스ID)가 키에 추가되어 생성되어 같은 세션에서 다시 실행 될 때 저장 프로시저에 대한 계획이 재사용 된다.

 

다음 스크립트는 중복 계획을 확인하는 방법이다.

-- Look and see if there is any hash bucket with a large number of entries (> 20)

-- which may cause slower lookup of entries

select p1.* from sys.dm_exec_cached_plans p1

join (select bucketid, count(*) as cache_entries, count(distinct plan_handle) as distinct_plans from sys.dm_exec_cached_plans p

group by bucketid

having count(*) > 20) as p2 on p1.bucketid = p2.bucketid

 

 

-- Save all of the "duplicate" plans for this specific query in a table in tempdb

select

    qs.sql_handle,

    qs.statement_start_offset,

    qs.statement_end_offset,

    qs.creation_time,

    qs.execution_count,

    qs.plan_generation_num,

    p.* into tempdb..DuplicateCachePlans

from sys.dm_exec_query_stats qs

    join sys.dm_exec_cached_plans p on qs.plan_handle = p.plan_handle

where qs.sql_handle = 0x0500FF7F98FFFFFF506126F90300000001000000000000000000000000000000000000000000000000000000

    and qs.query_hash = '0x00000003F9266060'

 

 

select p.plan_handle, pa.* from tempdb..DuplicateCachePlans p cross apply sys.dm_exec_plan_attributes (p.plan_handle) as pa

 

 

[참고자료]

http://blogs.msdn.com/b/psssql/archive/2014/04/03/i-think-i-am-getting-duplicate-query-plan-entries-in-sql-server-s-procedure-cache.aspx

 

 

2014-04-08 / 강성욱 / http://sqlmvp.kr

 

 

SQLSERVER, mssql, SQL튜닝, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, 프로시저캐시, 플랜캐시, 실행계획, sys.dm_exec_cached_plans, sys.dm_exec_query_stats

'SQL Server > SQL Server Tip' 카테고리의 다른 글

데이터베이스 연결 정보 수집  (0) 2015.07.23
FileStream Garbage Collection  (0) 2015.07.23
Sp_reset_connection  (0) 2015.07.23
성능분석 17탄 – SQLServer 블록킹 / 네트워크 관련 성능 카운터  (0) 2015.07.23
성능분석 16탄 – 메모리 / CPU 관련 성능 카운터  (0) 2015.07.23

Sp_reset_connection

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server 프로파일러를 사용하다보면 sp_reset_connection이라는 항목을 확인할 수 있다. sp_reset_connection은 SQL Server의 폴링된 커넥션 동작이다.

 

 

 

SQL Server는 데이터베이스에 연결 할 때 완전히 새로운 연결을 설정하는 것보다 빠른 풀링된 연결을 사용한다. 폴링의 사용 이유는 연결에 대한 CPU 오버헤드를 줄이고 빠른 연결을 위해서이다.

SQL Server는 풀링된 연결 상태를 리셋하기 위해 sp_reset_connection을 호출 한다. 즉 SQL Server 내부적으로 호출하는 명령이다.

 

SQL Server 내부적으로 호출한다고 해서 신경을 쓰지 않아야 하는 것은 아니다. 해당 비율이 높다면 어플리케이션이 커넥션 재연결을 빈번하게 하는 것으로 판단할 수 있다. 경량 작업으로 빈번하게 재연결을 한다면 어플리케이션의 디자인을 수정하여 연결을 끊지 않고 사용하는 것이 좋다. 하지만 재연결의 빈도를 낮추기 위해 모든 연결을 유지하는 것은 클라이언트 및 SQL Server에 전체적으로 더 많은 커넥션 개수를 증가시켜 더 많은 오버헤드를 가져올 수도 있다.

 

SQL Server 2012 SP1 누적 업데이트9 에서는 sp_reset_connection 오버헤드를 줄이기 위한 내용이 포함되었다. 간단히 내용을 요약하면 응용프로그램이 SQL Server 연결을 하면 먼저 데이터베이스 켄텍스트를 설정하는데 이때 기본적으로 연결 SH 모드로 데이터베이스 잠금을 얻으려고 시도한다. 데이터베이스의 수가 많을수록 이 잠금을 얻는데 시간이 오래 걸린다. 대부분의 응용프로그램은 디자인으로 인해 이 문제가 발생하지 않지만 추가 잠금이 필요한 경우 spinlock으로 인한 CPU 오버헤드를 증가 시킬 수 있다고 한다. 자세한 내용은 다음 링크를 참고 한다.

 

 

[참고자료]

 

2014-04-03 / 강성욱 / http://sqlmvp.kr

 

 

SQLSERVER, mssql, SQL튜닝, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, sp_reset_connection,커넥션풀링, SQL 서버 연결

'SQL Server > SQL Server Tip' 카테고리의 다른 글

FileStream Garbage Collection  (0) 2015.07.23
프로시저 캐시에서 중복 쿼리 계획 확인  (0) 2015.07.23
성능분석 17탄 – SQLServer 블록킹 / 네트워크 관련 성능 카운터  (0) 2015.07.23
성능분석 16탄 – 메모리 / CPU 관련 성능 카운터  (0) 2015.07.23
성능분석 15탄 – I/O 관련 성능 카운터  (0) 2015.07.23

성능분석 17탄 – SQLServer 블록킹 / 네트워크 관련 성능 카운터

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server 블록킹 및 네트워크 관련 성능 카운터에 대해서 알아 본다.

 

[SQL Server Blocking]

 

SQLServer:Locks

[SQLServer:Locks]에 위치하며 카운터의 정보는 각 서버의 잠금 요청에 대한 통계를 반환한다. 잠금 관련 성능 문제가 발생 할 경우 카운터 정보를 모니터링 하여 분석해야 한다. 카운터 정보만으로는 블록이 발생하지만 어떤 원인에 의해서 차단되는지는 알 수가 없다. 따라서 DMV등을 활용하여 차단되는 원인을 함께 분석해야 한다.

다음 링크는 Adam Machanic 블로그의 블록 모니터링 관련 아티클이다.

http://sqlblog.com/blogs/adam_machanic/archive/2011/04/23/leader-of-the-block-a-month-of-activity-monitoring-part-23-of-30.aspx

 

SQLServer:Wait Statistics

[SQLServer:Wait Statistics]에 위치하며 대기 통계를 나타낸다. DMV를 사용하여 대기 통계를 확인 할 수 있으나 성능 카운터의 장점은 여러 에코 시스템에서 수집, 저장, 분석을 쉽게 할 수 있다는 점이다. 모든 정보 유형을 성능 카운터를 통해 확인 할 수 있다.

 

SQLServer:Latches

[SQLServer:Latches]에 위치하며 내부 서버 래치와 관련된 통계를 나타낸다. 높은 래치의 값은 현재 경합이 발생 하고 있다는 것으로 원인을 찾아 분석해야 한다. 래치 경합에 대한 진단 빈 해결 방법은 다음 MS 백서를 참고 한다.

다운로드 : http://download.microsoft.com/download/B/9/E/B9EDF2CD-1DBF-4954-B81E-82522880A2DC/SQLServerLatchContention.pdf

 

 

 

[Network]

 

Network IO waits

[SQLServer:Wait Statistics] – [Network IO waits]에 위치하며 네트워크 IO 대기와 관련된 통계이다. 이 카운터의 정보는 클라이언트가 서버에서 반환하는 결과 세트를 처리하지 않기 때문에 서버가 차단되는 대기로 클라이언트가 큰 결과 집합을 요구하거나 클라이언트의 성능이 느린 경우 발생 한다. 이 숫자가 높다고 하여 무조건 네트워크 병목이라고 판단하기는 어렵다.

 

Network Interface

[Network Interface]에 위치하며 네트워크 연결을 통해 바이트와 패킷을 보내고 받는 속도를 측정하는 카운터로 구성 된다. 여기에는 연결 오류를 모니터링 하는 카운터도 포함된다.

 

IP object / TCP Object

[IP4], {IP6}, [TCP4], [TCP6]에 위치하며 프로토콜을 사용하여 보내고 받은 데이터그램 비율을 측정하여 나타낸다. IP, TCP 프로토콜 오류를 모니터링하는 카운터도 포함되어 있다. 네트워크의 헬스 체크에 유용하다.

 

[참고자료]

http://rusanu.com/2014/02/24/how-to-analyse-sql-server-performance/

 

2014-04-02 / 강성욱 / http://sqlmvp.kr

 

 

SQLSERVER, mssql, SQL튜닝, 강성욱, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, DMV, 성능모니터, Performance Counter, 윈도우 성능 모니터, 성능 카운터

 

 

'SQL Server > SQL Server Tip' 카테고리의 다른 글

프로시저 캐시에서 중복 쿼리 계획 확인  (0) 2015.07.23
Sp_reset_connection  (0) 2015.07.23
성능분석 16탄 – 메모리 / CPU 관련 성능 카운터  (0) 2015.07.23
성능분석 15탄 – I/O 관련 성능 카운터  (0) 2015.07.23
성능분석 14탄 – SQL Server 사용 성능 카운터  (0) 2015.07.23

성능분석 16탄 – 메모리 / CPU 관련 성능 카운터

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

메모리 및 CPU와 관련된 성능 카운터에 대해서 알아본다.

 

[메모리]

 

Page life expectancy

[SQLServer:Buffer Manager] – [Page life expectancy]에 위치하며 페이지가 버퍼풀에 머무르는 시간을 나타낸다. 페이지가 버퍼풀에 오래 머무를수록 디스크를 액세스하는 빈도가 낮아지며 메모리에서 데이터를 읽기 때문에 성능상 이점이 있다. Page life expectancy 계속해서 낮게 나온다면 메모리를 추가할 것을 고려해야 한다. MS백서에서는 임계값을 300(5분)으로 안내하고 있지만 이는 2006년에 권장한 값으로 각자의 시스템에 따라 유동적인 임계값을 설정할 필요가 있다. 중요한것은 임계값이 아니라 유동적인 변화를 파악하는데 있다.

 

SQLServer:Memory Manager

[SQLServer:Memory Manager]에 위치하며 이 카테고리의 값은 SQL Server 프로세스 내에서 메모리가 어떻게 사용되는지를 나타낸다. 카운터에 표시되는 값 자체를 다른것과 비교 할 수는 없지만 어떠한 변화를 가지는지 동향을 살펴보고 어느 부분에서 압력을 받고 있는지 파악하여야 한다.

 

Memory Grants Pending

[SQLServer:Memory Manager] – [Memory Grants Pending]에 위치하며 작업 영역 메모리 부여를 대기 중은 현재 프로세스 수를 나타낸다. 메모리 부여 대기는 블록킹을 이해하기 위해 특히 중요한 부분으로 주의해서 보아야 한다. 특정 고부하 쿼리 및 연산이 큰 쿼리는 많은 메모리를 요구한다. 이때 Memory Grants Pending 이 발생하고 대기가 발생한다.

 

Memory grant queue waits

[SQLServer:Wait Statistics]에 위치하며 메모리 부여를 기다리는 프로세스에 대한 통계이다.

 

Private Bytes

[Process] – [Private Bytes]에 위치하며 프로세스가 할당하여 다른 프로세스와는 공유할 수 없는 메모리의 현재 크기(byte)를 나타낸다. 이 카운터의 값을 확인하여 SQL Server 외부 메모리 부족으로 인한 성능 저하의 원인인 다른 프로세스를 추적 할 수 있다.

 

 

 

[CPU]

 

Processor Object

[Processor]에 위치하며 객체들은 OS의 CPU 사용량을 나타낸다.

 

Page lookups/sec

[SQLServer:Buffer Manager] – [Page lookups/sec]에 위치하며 CPU 사용량을 분석할 때 중요한 카운터 이다. 이 카운터는 버퍼 풀에서 페이지를 찾기 위한 요청 수를 나타낸다. 이 카운터의 값이 증가할 때는 CPU의 사용량이 증가한다. 더 많은 페이지를 스캔하고 더 많은 데이터를 사용하기 때문에 더 많은 CPU를 사용한다. 일반적으로 Page lookup/sec이 큰 경우에는 테이블 스캔(인덱스 누락, 잘못된 설계)이 발생한 경우이다.

 

Processor Time

[Process] – [% Processor Time]에 위치하며 모든 프로세스 스레드가 프로세서를 사용하여 컴퓨터 명령을 실행하는데 경과된 시간의 백분율을 나타낸다. 일부 하드웨어 인터럽트 또는 트랩 상태를 처리하기 위해 실행되는 코드도 이 계산에 포함된다. 프로세서 시간이 크다면 SQL Server 성능 저하 원인을 CPU를 소비하는 다른 프로세스를 추적해 보아야 한다.

 

 

[참고자료]

http://rusanu.com/2014/02/24/how-to-analyse-sql-server-performance/

 

2014-04-01 / 강성욱 / http://sqlmvp.kr

 

 

SQLSERVER, mssql, SQL튜닝, 강성욱, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, DMV, 성능모니터, Performance Counter, 윈도우 성능 모니터, 성능 카운터

'SQL Server > SQL Server Tip' 카테고리의 다른 글

Sp_reset_connection  (0) 2015.07.23
성능분석 17탄 – SQLServer 블록킹 / 네트워크 관련 성능 카운터  (0) 2015.07.23
성능분석 15탄 – I/O 관련 성능 카운터  (0) 2015.07.23
성능분석 14탄 – SQL Server 사용 성능 카운터  (0) 2015.07.23
성능분석 13탄 – 누락된 인덱스(missing index)  (0) 2015.07.23

성능분석 15탄 – I/O 관련 성능 카운터

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

I/O와 관련된 성능 카운터에 대해서 알아본다.

 

Page reads/sec

[SQLServer:Buffer Manager] – [Page reads/sec]에 위치하며 실제 데이터베이스에서 읽은 페이지의 수를 나타낸다. 이 카운터의 성능은 디스크 IO 읽기의 중요한 지표이다. 일반적으로 데이터는 버퍼풀에 캐싱되어 있어 디스크에서는 적은 데이터만 읽어 부하를 줄인다. 이 수가 높게 나타난다면 누락된 인덱스 또는 메모리 부족일 가능성 있다.

 

Page writes/sec

[SQLServer:Buffer Manager] – [Page reads/sec]에 위치하며 실제 데이터베이스에서 쓰기 페이지의 수를 나타낸다. 일부 큰 데이터쓰기는 높은 I/O를 유발하고 성능 하락으로 이어질 수 있다. 또한 I/O가 느린 경우 로그 플러시 대기 지연이 발생할 수 있다.

 

Log Flushes/sec

[SQLServer:Databases] – [LogFlushes/sec]에 위치하며 해당 위치의 Log Flush Wait Time, Log Flush Waits/sec, Log Flush Write Time (ms)와 함께 로그를 기록하는 횟수와 로그를 쓰기위해 기다리는 커밋 수 등을 확인 할 수 있다. OLTP 시스템에서는 이 부분이 주요 병목이 되기도 한다.

 

Checkpoint pages/sec

[SQLServer:Buffer Manager] – [Checkpoint pages/sec]에 위치하며 체크포인트는 Page writes/sec 스파이크를 유발하는 원인이기도 하다. 체크포인트는 자동으로 실행되지만 write 성능에 따라 체크포인트 수준을 조절하기도 한다.

 

Log Write waits

[SQL Server:Wait Statistics] – [Log write waits]에 위치하며 이 성능 카운터는 로그 쓰기의 대기 정보를 알려준다. 로그 버퍼 작성을 기다리는 프로세스에 대한 통계이다.

 

Page IO latch waits

[SQL Server:Wait Statistics] – [Page IO latch waits]에 위치하며 페이지 IO 래치와 관련된 대기 정보이다. 이 카운터는 IO 값을 측정하지 않고 IO 블록킹이 발생한 경우만 측정한다. 또한 읽기 및 쓰기를 구분하지 않는다.

 

Backup/Restore Throughput/sec

[SQLServer:Databases] – [Backup/Restore Throughput/sec]에 위치하며 데이터베이스 백업/복원에 대한 읽기/쓰기 처리량을 나타낸다. 성능 분석을 할 때 종종 백업에 대한 IO의 요구 사항은 간과 되는 경우가 있다. 하지만 백업은 높은 IO를 요구하는 작업으로 실제 백업 시 IO에 대한 문제가 발생하는 경우가 많다. 백업 처리량을 모니터링 하여 IO 스파이크 현상이 발생 할 때 성능 상관관계를 이해해야 한다.

 

PhysicalDisk Object

[PhysicalDisk]에 위치한 오브젝트들은 IO동작을 모니터링 하기 위한 운영체제 카운터이다. 디스크이 큐 길이, 큐 시간, 디스크 처리량 등을 나타낸다. 물리적인 디스크 외에도 논리적인 드라이브 모니터링이 가능하다. SQL Server 자체의 IO 통계와 DMV 정보를 함께 활용하여 디스크 사용량에 대한 다양한 정보를 확인 할 수 있다.

 

IO Data Operations/sec

[Process] – [IO Data Operations/sec]에 위치하며 프로세스가 읽기 및 쓰기 I/O 연산을 요청하는 속도이다. 이 카운터는 프로세스가 파일, 네트워크 및 장치 I/O에서 만들어 내는 모든 I/O 동작을 계산한다. SQL 서버에 의해 보고된 값과 시스템 전체 IO 값이 일치하지 않을 경우를 분석할 때 매우 중요하다. 다른 프로세스에 의해 디스크가 바쁠 때 SQL 서버의 응답시간이 증가하는 것을 발견 할 수 있다.

 

 

[참고자료]

http://rusanu.com/2014/02/24/how-to-analyse-sql-server-performance/

 

 

2014-03-31 / 강성욱 / http://sqlmvp.kr

 

 

SQLSERVER, mssql, SQL튜닝, 강성욱, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, DMV, 성능모니터, Performance Counter, 윈도우 성능 모니터, 성능 카운터, 디스크 성능 모니터, IO 모니터, perfmon, disk io

'SQL Server > SQL Server Tip' 카테고리의 다른 글

성능분석 17탄 – SQLServer 블록킹 / 네트워크 관련 성능 카운터  (0) 2015.07.23
성능분석 16탄 – 메모리 / CPU 관련 성능 카운터  (0) 2015.07.23
성능분석 14탄 – SQL Server 사용 성능 카운터  (0) 2015.07.23
성능분석 13탄 – 누락된 인덱스(missing index)  (0) 2015.07.23
성능분석 12탄 – 문제 쿼리 식별  (0) 2015.07.23

성능분석 14탄 – SQL Server 사용 성능 카운터

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

성능카운터는 SQL 서버 성능에 대한 또 다른 관점을 제공 한다. SQL Server에서 사용하는 성능카운터에 대해서 알아본다. 아래 소개한 카운터 외에도 다양한 카운터 정보를 활용 할 수 있다.

 

Batch Requests/sec

[SQLServer:SQL Statistics] – [Batch Requests/sec]에 위치하며 서버에 수신되는 SQL 일괄 처리 요청 수 이다.

 

Transactions

모든 활성 트랜잭션의 수를 나타낸다. 트랜잭션 카운터는 [SQLServer:General Statistics], [SQLServer:Transactions] 두 군데 위치하고 있다. 활성 트랜잭션의 수를 알면 작업 부하에 대한 설정을 할 때 정보로 활용 할 수 있다.

 

Process blocked

[SQLServer:General Statistics] – [Process blocked]에 위치하며 현재 차단되어 있는 프로세서의 수로 스파이크 또는 가변 성능 문제를 해결 할 때 유용하게 사용 할 수 있다.

 

Error/sec

[SQLServer:SQL Errors]에 위치하며 초당 오류 개수를 표시 한다. 오류가 사용자의 작업에서 발생하는 것은 성능 문제의 해결의 핵심포인트 이다. 오류를 제거하는 것은 성능 향상을 위한 가장 기초 단계이다. 교착상태 모니터링은 매우 중요하다.

 

Number of Deadlocks/sec

[SQLServer:Locks] – [Number of Deadlocks/sec]에 위치하며 교착상태를 일으킨 잠금 요청 수를 나타낸다.

 

Log Growths

로그 증가는 데이터베이스의 트랜잭션 로그 파일을 자동 증가로 설정 했을 때 로그 파일이 증가할 때 기록 된다. 로그 증가가 중요한 이유는 로그파일이 증가 할 때 순간적으로 파일을 확장하기 위해 높은 IO를 요구하며 파일이 증가 하는 동안 데이터베이스가 정지 된다. 따라서 로그 증가 모니터링을 통하여 유휴시간에 미리 로그를 증가 시켜 놓거나 증가 용량에 대한 시나리오를 시스템 환경에 맞게 수정해야 한다.

 

Data File(s) Size(KB)

[SQLServer:Databases] – [Data File(s) (KB)]에 위치하며 데이터베이스에 있는 데이터 파일의 총 크기를 나타낸다. 데이터 파일의 크기를 모니터링하는 이유는 데이터베이스 로그 파일의 경우에는 파일이 증가 하였을 경우 이벤트로 기록되지만 데이터 파일은 증가 이벤트를 기록하지 않는다. 따라서 데이터의 증가량을 확인하여 파일의 증가 속도와 필요한 공간을 미리 확인 할 수 있으며 파일이 증가할 때 발생하는 여러 가지 성능 문제를 미연에 방지 할 수 있다.

 

 

[참고자료]

http://rusanu.com/2014/02/24/how-to-analyse-sql-server-performance/

 

 

2014-03-28 / 강성욱 / http://sqlmvp.kr

 

SQLSERVER, mssql, SQL튜닝, 강성욱, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, DMV, 성능모니터, Performance Counter, 윈도우 성능 모니터, 성능 카운터

'SQL Server > SQL Server Tip' 카테고리의 다른 글

성능분석 16탄 – 메모리 / CPU 관련 성능 카운터  (0) 2015.07.23
성능분석 15탄 – I/O 관련 성능 카운터  (0) 2015.07.23
성능분석 13탄 – 누락된 인덱스(missing index)  (0) 2015.07.23
성능분석 12탄 – 문제 쿼리 식별  (0) 2015.07.23
성능분석 11탄 – 실행 계획 분석  (0) 2015.07.23

성능분석 13탄 – 누락된 인덱스(missing index)

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

 

SQL Server에서 누락된 인덱스는 성능 문제를 일으킬 수 있다. 누락된 인덱스(missing index)는 인덱스가 생성되어 있지만 사용되지 않는 인덱스이다.

 

SQL Server 2005 부터는 누락된 인덱스를 확인하기 위한 DMV가 제공되고 있다. 다음 DMV를 활용하여 누락된 인덱스의 정보를 확인 할 수 있다.

  • Sys.dm_db_missing_index_details : 누락된 인덱스에 대한 상세 정보 반환
  • Sys.dm_db_missing_index_group_stats : 누락 된 인덱스 그룹에 대한 요약 정보 반환
  • Sys.dm_db_missing_index_groups : 누락 된 인덱스의 특정 그룹에 대한 반품 정보 반환
  • Sys.dm_missing_index_columns(index_handle) : 인덱스에 대한 누락된 데이터베이스테이블 컬럼에 대한 정보를 반환.

 

 

아래 스크립트는 DMV를 활용하여 쿼리가 실행되고 있는 데이터베이스에 누락된 인덱스를 식별하고 비용이 높은 쿼리를 찾아 인덱스를 생성하는 가이드를 제공한다.

SELECT

    a.avg_user_impact * a.avg_total_user_cost * a.user_seeks,

db_name(c.database_id),

OBJECT_NAME(c.object_id, c.database_id),

c.equality_columns,

c.inequality_columns,

c.included_columns,

c.statement,

'USE [' + DB_NAME(c.database_id) + '];

CREATE INDEX mrdenny_' + replace(replace(replace(replace

(ISNULL(equality_columns, '')

+ ISNULL(c.inequality_columns, ''), ', ', '_'),

'[', ''), ']', ''), ' ', '') + '

ON [' + schema_name(d.schema_id) + ']

.[' + OBJECT_NAME(c.object_id, c.database_id) + ']

(' + ISNULL(equality_columns, '') +

CASE WHEN c.equality_columns IS NOT NULL

AND c.inequality_columns IS NOT NULL THEN ', '

ELSE '' END + ISNULL(c.inequality_columns, '') + ')

' + CASE WHEN included_columns IS NOT NULL THEN

'INCLUDE (' + included_columns + ')' ELSE '' END + '

WITH (FILLFACTOR=70, ONLINE=ON)'

FROM sys.dm_db_missing_index_group_stats a

JOIN sys.dm_db_missing_index_groups b

ON a.group_handle = b.index_group_handle

JOIN sys.dm_db_missing_index_details c

ON b.index_handle = c.index_handle

JOIN sys.objects d ON c.object_id = d.object_id

WHERE c.database_id = db_id()

ORDER BY DB_NAME(c.database_id),

ISNULL(equality_columns, '')

+ ISNULL(c.inequality_columns, ''), a.avg_user_impact

* a.avg_total_user_cost * a.user_seeks DESC

 

 

DMV로 확인한 정보를 이용하여 인덱스를 생성하거나 수정 할 때 중복 인덱스가 있는지 또는 다른 쿼리가 영향을 받는지 충분한 검토가 필요하다.

 

[참고자료]

 

 

2014-03-27 / 강성욱 / http://sqlmvp.kr

 

 

SQLSERVER, mssql, SQL튜닝, 강성욱, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, DMV, 누락된인덱스, missing index, Sys.dm_db_missing_index_details, Sys.dm_db_missing_index_group_stats, Sys.dm_db_missing_index_groups, Sys.dm_missing_index_columns(index_handle)

'SQL Server > SQL Server Tip' 카테고리의 다른 글

성능분석 15탄 – I/O 관련 성능 카운터  (0) 2015.07.23
성능분석 14탄 – SQL Server 사용 성능 카운터  (0) 2015.07.23
성능분석 12탄 – 문제 쿼리 식별  (0) 2015.07.23
성능분석 11탄 – 실행 계획 분석  (0) 2015.07.23
성능분석 10탄 – 쿼리 실행 대기 시간 분석(xevent)  (0) 2015.07.23

성능분석 12탄 – 문제 쿼리 식별

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server에서는 sys.dm_exec_query_stats DMV를 사용하여 실행된 쿼리에 대한 통계를 확인 할 수 있다. DMV에서는 몇 개의 쿼리가 실행 되었는지, 전체, 최대, 최소. 마지막 활성 CPU 시간 등을 확인 할 수 있으며 전체, 최대, 최소, 마지막 읽기 쓰기에 대한 정보를 확인 할 수 있어 문제를 식별하는데 매우 좋은 정보이다.

select* from sys.dm_exec_query_stats

 

 

Large execute count

많은 실행 횟수는 자주 실행되는 쿼리로 성능에 가장 민감하다. 빈도가 높은 만큼 작은 개선이 전반적으로 상당한 성능에 이점을 가져올 수 있다.

 

 

Large logical reads

많은 논리적 읽기는 많은 양의 데이터를 검색하기 때문에 느린 쿼리일 가능성이 높다. 데이터 스캔이나 미싱인덱스 문제일 수 있다. 논리적 읽기가 높은 것은 긴 작업타임 이슈이지 CPU 이슈는 아니다. 통계가 오래되거나 잘못된 실행 계획, 잘못된 모델 설계, 인덱스 누락 등으로도 발생 할 수 있다.

 

 

Large Physical reads

큰 물리적 읽기는 논리적 읽기와 같은 문제이지만 추가적으로 서버에 충분한 RAM이 없음을 나타낸다. 다행이 이런 문제는 RAM을 추가하면 해결되는 쉬운 케이스이다. 하지만 여전히 큰 논리적 읽기는 발생 할 수 있다.

 

 

High worker time with low logical reads

높은 작업 시간과 낮은 논리적 읽기 현상은 흔하지 않지만 발생하는 현상이다. 이는 적은 데이터를 처리하기 위해서도 높은 CPU를 사용하는 작업을 나타낸다. 문자열 처리 및 XML 처리 등이 일반적으로 높은 CPU를 요구한다.

 

 

High elapsed time with log worker time

쿼리 작업 시간이 오래 걸리며 CPU 사이클을 활용하는 쿼리 차단을 나타낸다. 이는 뭔가를 기다리는 것에 병렬 처리에서 다른 스레드 처리 결과를 기다리는데 대부분의 시간을 소비 한다.

 

 

High total rows count

큰 결과 행을 요청하는 것은 어플리케이션 설계 문제에서 해결 해야 한다. 꼭 필요한 데이터만 가져 올 수 있도록 요구하는 것이 좋다. 문제가 되는 쿼리를 찾기 위해서는 DMV sys.dm_exec_query_stats을 사용 할 수 있으며 쿼리 텍스트 및 실행 계획을 함께 확인 하기 위해 sys.dm_exec_sql_text, sys.dm_exec_query_plan 을 조인하여 정보를 확인 할 수 있다.

 

select st.text,

    pl.query_plan,

    qs.*

    from sys.dm_exec_query_stats qs

    cross apply sys.dm_exec_sql_text(qs.sql_handle) as st

    cross apply sys.dm_exec_query_plan(qs.plan_handle) as pl;

 

 

 

[참고자료]

http://rusanu.com/2014/02/24/how-to-analyse-sql-server-performance/

 

 

2014-03-25 / 강성욱 / http://sqlmvp.kr

 

 

SQLSERVER, mssql, SQL튜닝, 강성욱, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, 실행계획, SET SHOWPLAN_XML_ON, 쿼리분석, sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_query_plan

'SQL Server > SQL Server Tip' 카테고리의 다른 글

성능분석 14탄 – SQL Server 사용 성능 카운터  (0) 2015.07.23
성능분석 13탄 – 누락된 인덱스(missing index)  (0) 2015.07.23
성능분석 11탄 – 실행 계획 분석  (0) 2015.07.23
성능분석 10탄 – 쿼리 실행 대기 시간 분석(xevent)  (0) 2015.07.23
성능분석 9탄 – 쿼리 실행 분석  (0) 2015.07.23

성능분석 11탄 – 실행 계획 분석

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server에서는 쿼리에 대한 실제 실행 계획을 확인 할 수 있다. 실제 쿼리가 실행되는 과정을 XML 트리로 확인 할 수 있는 방법이 몇 가지 있다.

 

  • SET SHOWPLAN_XML_ON : 해당 세션에서 SET SHOWPLAN_XML_ON 명령을 수행하고 쿼리를 실행 한다.

set showplan_xml on

go

 

select * from [HumanResources].[EmployeeDepartmentHistory]

 

 

XML을 클릭하면 그래픽의 실행계획을 확인 할 수 있으며 마우스 오른쪽 클릭을 사용하여 XML 트리를 확인 할 수 있다.

 

  • Profiler 사용 : 프로파일러 Event를 사용하여 실행 계획을 캡처 할 수 있다. [Perfornamce] – [Showplan XML]을 선택 한다.

 

 

 

  • SSMS : SSMS에서 [실제 실행 계획 포함]을 클릭하여 쿼리를 실행하면 실제 실행 계획을 확인 할 수 있다.

 

  • DMV : sys.dm_exec_qeury_plan(plan_handle)을 사용하여 실행 계획을 확인 할 수 있다.

USE master;

GO

SELECT * FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);

GO

 

 

 

실행 계획에서는 대기 시간 분석과 달리 실제 실행한 시간인 CPU 런타임에 초점을 맞추어야 한다. SSMS에서 그래픽화 된 실행계획은 쉽게 병목현상을 확인 할 수 있다. 화살표가 두꺼운 것은 전송되는 데이터의 양이 많다는 것을 의미한다. 이는 시간이 더 많이 걸린다는 것을 의미하기도 한다.

 

그래픽 실행계획에서 마우스를 위치하면 팝업이 나타난다. 팝업창은 많은 정보를 표시하는데 SQL Server의 통계기반으로 예상 비용과 실제 처리되고 난 후 실제 비용을 확인 할 수 있다.

 

예상 행 수와 실제 행수의 차이가 큰 경우에는 통계가 오래되었을 가능성이 크다. 또한 정렬과 같은 오퍼레이터 부분이 많은 비용을 소모 하므로 실행 계획을 통하여 확인 할 수 있다.

 

 

[참고자료]

http://rusanu.com/2014/02/24/how-to-analyse-sql-server-performance/

 

 

2014-03-24 / 강성욱 / http://sqlmvp.kr

 

 

SQLSERVER, mssql, SQL튜닝, 강성욱, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, 실행계획, SET SHOWPLAN_XML_ON, 쿼리분석

'SQL Server > SQL Server Tip' 카테고리의 다른 글

성능분석 13탄 – 누락된 인덱스(missing index)  (0) 2015.07.23
성능분석 12탄 – 문제 쿼리 식별  (0) 2015.07.23
성능분석 10탄 – 쿼리 실행 대기 시간 분석(xevent)  (0) 2015.07.23
성능분석 9탄 – 쿼리 실행 분석  (0) 2015.07.23
성능분석 8탄 – IO 통계 (DISK 활동 분석)  (0) 2015.07.23

성능분석 10탄 – 쿼리 실행 대기 시간 분석(xevent)

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

쿼리 또는 저장프로시저에 대한 가장 좋은 대기 유형 정보는 실행 시간과 IO 정보이다. 이 정보는 확장이벤트(XEvent)를 사용하여 모니터링을 할 수 있다.

다음 스크립트는 Sqlos.wait_info 정보를 캡처하고 특정 실행 세션(SPID)에 대한 확장 이벤트 세션을 필터링하는 확장이벤트 세션 생성 작업이다.

 

create event session session_waits on server

add event sqlos.wait_info

(WHERE sqlserver.session_id= 54/*execution_spid*/ and duration>0)

, add event sqlos.wait_info_external

(WHERE sqlserver.session_id = 54/*execution_spid*/ and duration>0)

add target package0.asynchronous_file_target

(SET filename=N'c:\temp\wait_stats.xel', metadatafile=N'c:\temp\wait_stats.xem');

go

 

alter event session session_waits on server state= start;

go

 

 

 

이벤트 세션을 시작하고 분석할 쿼리나 프로시저를 실행 한다. 그 후 확장 이벤트 서비스를 중지하고 캡처된 데이터를 확인 한다.

alter event session session_waits on server state= stop;

go

 

with x as (

select cast(event_data as xml) as xevent

from sys.fn_xe_file_target_read_file

('c:\temp\wait_stats*.xel', 'c:\temp\wait_stats*.xem', null, null))

select * from x;

go

 

 

이벤트 세션을 중단하고 캡처된 이벤트를 클릭하면 XML 정보를 확인 할 수 있다.

 

4밀리세컨드의 IX 잠금이 발생하였다. 그리고 작업이 재개 될 때까지 또 다른 2밀리세컨드 보냈다. 다음 스크립트를 이용하ㅕㅁㄴ XML 데이터를 테이블 형식으로 확인할 수 있다.

with x as (

select cast(event_data as xml) as xevent

from sys.fn_xe_file_target_read_file

('c:\temp\wait_stats*.xel', 'c:\temp\wait_stats*.xem', null, null))

select xevent.value(N'(/event/data[@name="wait_type"]/text)[1]', 'sysname') as wait_type,

    xevent.value(N'(/event/data[@name="duration"]/value)[1]', 'int') as duration,

    xevent.value(N'(/event/data[@name="signal_duration"]/value)[1]', 'int') as signal_duration

from x;

 

다음 스크립트는 XML의 정보를 대기 유형에 따라 집계한 것이다.

with x as (

select cast(event_data as xml) as xevent

from sys.fn_xe_file_target_read_file

('c:\temp\wait_stats*.xel', 'c:\temp\wait_stats*.xem', null, null)),

s as (select xevent.value(N'(/event/data[@name="wait_type"]/text)[1]', 'sysname') as wait_type,

    xevent.value(N'(/event/data[@name="duration"]/value)[1]', 'int') as duration,

    xevent.value(N'(/event/data[@name="signal_duration"]/value)[1]', 'int') as signal_duration

from x)

select wait_type,

    count(*) as count_waits,

    sum(duration) as total__duration,

    sum(signal_duration) as total_signal_duration,

    max(duration) as max_duration,

    max(signal_duration) as max_signal_duration

from s

group by wait_type

order by sum(duration) desc

 

 

이 결과를 바탕으로 쿼리가 실행되는 동안 어떤 일이 있었는지를 확인 할 수 있다. 이 로그를 보면 742번의 WRITELOG가 발생하였으며, 12번의 잠금이 발생, 그리고 2개의 PAGELATCH가 발생하였다.

 

 

[참고자료]

http://rusanu.com/2014/02/24/how-to-analyse-sql-server-performance/

 

2014-03-21 / 강성욱 / http://sqlmvp.kr

 

 

SQLSERVER, mssql, SQL튜닝, 강성욱, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, xevent, wait_type, sql2012, sql2008, sql2008R2, Sqlos.wait_info

'SQL Server > SQL Server Tip' 카테고리의 다른 글

성능분석 12탄 – 문제 쿼리 식별  (0) 2015.07.23
성능분석 11탄 – 실행 계획 분석  (0) 2015.07.23
성능분석 9탄 – 쿼리 실행 분석  (0) 2015.07.23
성능분석 8탄 – IO 통계 (DISK 활동 분석)  (0) 2015.07.23
성능분석 7탄 – 프로파일러 대기 유형 및 PREEMPTIVE_OS_WRITEFILEGATHER  (0) 2015.07.23

+ Recent posts

티스토리툴바