SQL Server 2005 Diagnostic Information Queries

 

  • Version : SQL Server 2005

 

SQL Server 2005 진단 쿼리 – 이 쿼리 한방이면 SQL 상태 확인 가능.

첨부파일 SQL Server 2005 Diagnostic Information Queries.txt 참고.

-- SQL Server 2005 Diagnostic Information Queries

-- Glenn Berry

-- December 2014

-- Last Modified: December 18, 2014

-- http://sqlserverperformance.wordpress.com/

-- http://sqlskills.com/blogs/glenn/

-- Twitter: GlennAlanBerry

   

-- Please listen to my Pluralsight courses

-- http://www.pluralsight.com/author/glenn-berry

   

   

-- Many of these queries will not work if you have databases in 80 compatibility mode

-- Please make sure you are using the correct version of these diagnostic queries for your version of SQL Server

   

--******************************************************************************

--* Copyright (C) 2014 Glenn Berry, SQLskills.com

--* All rights reserved.

--*

--* For more scripts and sample code, check out

--* http://sqlskills.com/blogs/glenn

--*

--* You may alter this code for your own *non-commercial* purposes. You may

--* republish altered code as long as you include this copyright and give due credit.

--*

--*

--* THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF

--* ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED

--* TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

--* PARTICULAR PURPOSE.

--*

--******************************************************************************

 

 

[참고자료]

http://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-december-2014/?utm_source=rss&utm_medium=rss&utm_campaign=sql-server-diagnostic-information-queries-for-december-2014

 

 

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

 

Sqlserver, mssql, sql2005, sql 분석, DB 분석, DB 진단, DB튜닝, SQL Health Check, DBA

 

 

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

데이터베이스의 모든 인덱스 생성 삭제 스크립트 만들기

 

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

 

데이터베이스에 생성되어 있는 모든 인덱스를 생성하거나 삭제하는 스크립트를 만들어 본다. 이렇게 스크립트를 만들어 놓는 이유는 DBA로서 항상 복구할 준비를 할 수 있어야 하기 때문이다.

 

인덱스가 삭제되거나 손상되었을 때 또는 사본을 만들어야 할 때 유용하게 사용할 수 있다.

 

[인덱스 삭제 스크립트]

데이터베이스에 있는 모든 인덱스를 삭제하는 스크립트를 생성한다.

DECLARE @SchemaName VARCHAR(256)DECLARE @TableName VARCHAR(256)

DECLARE @IndexName VARCHAR(256)

DECLARE @TSQLDropIndex VARCHAR(MAX)

 

DECLARE CursorIndexes CURSOR FOR

SELECT schema_name(t.schema_id), t.name, i.name

FROM sys.indexes i

INNER JOIN sys.tables t ON t.object_id= i.object_id

WHERE i.type>0

    and t.is_ms_shipped=0

    and t.name<>'sysdiagrams'

    --and (is_primary_key=0 and is_unique_constraint=0)

 

OPEN CursorIndexes

FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName

 

WHILE @@fetch_status = 0

BEGIN

SET @TSQLDropIndex = 'DROP INDEX '+QUOTENAME(@SchemaName)+ '.' + QUOTENAME(@TableName) + '.' +QUOTENAME(@IndexName)

PRINT @TSQLDropIndex

FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName

END

 

CLOSE CursorIndexes

DEALLOCATE CursorIndexes

 

 

 

[인덱스 생성 스크립트]

데이터베이스의 모든 정규 인덱스를 생성한다. Sys.tables, sys.indexes, sys.index_column, sys.colimns 뷰를 사용하여 메타 데이터를 쿼리 한다. 생성된 스크립트는 다음과 같은 특징이 있다.

  • 인덱스가 비활성과 된 경우 비활성화된 코드를 생성한다.
  • Include column 경우 동일한 순서로 포함된다.
  • 인덱스의 모든 속성(ALLOW_PAGE_LOCKS, STATISTICS_NORECOMPUTE, FILLFACTOR, SORT_IN_TEMPDB 등)을 포함한다.
  • 생성된 스크립트는 기본키에 연결하지는 않는다.
  • 파티션된 인덱스는 스크립트에 포함되지 않는다.

 

declare @SchemaName varchar(100)declare @TableName varchar(256)

declare @IndexName varchar(256)

declare @ColumnName varchar(100)

declare @is_unique varchar(100)

declare @IndexTypeDesc varchar(100)

declare @FileGroupName varchar(100)

declare @is_disabled varchar(100)

declare @IndexOptions varchar(max)

declare @IndexColumnId int

declare @IsDescendingKey int

declare @IsIncludedColumn int

declare @TSQLScripCreationIndex varchar(max)

declare @TSQLScripDisableIndex varchar(max)

 

declare CursorIndex cursor for

select schema_name(t.schema_id) [schema_name], t.name, ix.name,

case when ix.is_unique = 1 then 'UNIQUE ' else '' END

, ix.type_desc,

case when ix.is_padded=1 then 'PAD_INDEX = ON, ' else 'PAD_INDEX = OFF, ' end

+ case when ix.allow_page_locks=1 then 'ALLOW_PAGE_LOCKS = ON, ' else 'ALLOW_PAGE_LOCKS = OFF, ' end

+ case when ix.allow_row_locks=1 then 'ALLOW_ROW_LOCKS = ON, ' else 'ALLOW_ROW_LOCKS = OFF, ' end

+ case when INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 then 'STATISTICS_NORECOMPUTE = ON, ' else 'STATISTICS_NORECOMPUTE = OFF, ' end

+ case when ix.ignore_dup_key=1 then 'IGNORE_DUP_KEY = ON, ' else 'IGNORE_DUP_KEY = OFF, ' end

+ 'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(3)) AS IndexOptions

, ix.is_disabled , FILEGROUP_NAME(ix.data_space_id) FileGroupName

from sys.tables t

inner join sys.indexes ix on t.object_id=ix.object_id

where ix.type>0

--and ix.is_primary_key=0

--and ix.is_unique_constraint=0

--and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName

and t.is_ms_shipped=0 and t.name<>'sysdiagrams'

order by schema_name(t.schema_id), t.name, ix.name

 

open CursorIndex

fetch next from CursorIndex into @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName

 

while (@@fetch_status=0)

begin

declare @IndexColumns varchar(max)

declare @IncludedColumns varchar(max)

 

set @IndexColumns=''

set @IncludedColumns=''

 

declare CursorIndexColumn cursor for

select col.name, ixc.is_descending_key, ixc.is_included_column

from sys.tables tb

inner join sys.indexes ix on tb.object_id=ix.object_id

inner join sys.index_columns ixc on ix.object_id=ixc.object_id and ix.index_id= ixc.index_id

inner join sys.columns col on ixc.object_id =col.object_id and ixc.column_id=col.column_id

where ix.type>0 and (ix.is_primary_key=0 or ix.is_unique_constraint=0)

and schema_name(tb.schema_id)=@SchemaName and tb.name=@TableName and ix.name=@IndexName

order by ixc.index_column_id

 

open CursorIndexColumn

fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn

 

while (@@fetch_status=0)

begin

if @IsIncludedColumn=0

set @IndexColumns=@IndexColumns + @ColumnName + case when @IsDescendingKey=1 then ' DESC, ' else ' ASC, ' end

else

set @IncludedColumns=@IncludedColumns + @ColumnName +', '

 

fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn

end

 

close CursorIndexColumn

deallocate CursorIndexColumn

 

set @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1)

set @IncludedColumns = case when len(@IncludedColumns) >0 then substring(@IncludedColumns, 1, len(@IncludedColumns)-1) else '' end

-- print @IndexColumns

-- print @IncludedColumns

 

set @TSQLScripCreationIndex =''

set @TSQLScripDisableIndex =''

set @TSQLScripCreationIndex='CREATE '+ @is_unique +@IndexTypeDesc + ' INDEX ' +QUOTENAME(@IndexName)+' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ '('+@IndexColumns+') '+

case when len(@IncludedColumns)>0 then CHAR(13) +'INCLUDE (' + @IncludedColumns+ ')' else '' end + CHAR(13)+'WITH (' + @IndexOptions+ ') ON ' + QUOTENAME(@FileGroupName) + ';'

 

if @is_disabled=1

set @TSQLScripDisableIndex= CHAR(13) +'ALTER INDEX ' +QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName) + ' DISABLE;' + CHAR(13)

 

print @TSQLScripCreationIndex

print @TSQLScripDisableIndex

 

fetch next from CursorIndex into @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName

 

end

close CursorIndex

deallocate CursorIndex

 

 

위 스크립트를 사용하기 전에 항상 테스트 환경에서 영향 평가를 할 수 있도록 한다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3441/script-out-all-sql-server-indexes-in-a-database-using-tsql/

 

 

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

 

 

Mssql, sqlserver, create index, drop index, DBA, 인덱스 생성, 인덱스 삭제, 인덱스 관리, 데이터베이스관리, 스크립트 생성

SQL Server Spinlock 소개

 

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

 

Spinlock은 Latch처럼 공유 데이터 구조에 대한 접근 스레드가 동기화 스토리지 엔진에 의해 사용되는 경량의 동기화 객체이다. Latch가 발생할 경우 연관된 오버헤드가 발생하는데 바쁠 때는 래치의 데이터 구조를 보호하는 것은 의미가 없다. 이 때문에 SQL Server는 Spinlock를 구현한다.

 

 

 

Latch의 경우는 쿼리가 Latch를 획득하지 못하였을 경우에는 대기 상태로 대기하고 이후에 실행 가능한 상태로 이동한다. 쿼리는 CPU에서 실행된 상태가 아니기 때문에 실행 가능 상태로 남게된다. 그리고 CPU로 다시 이동해서 마지막으로 성공적으로 획득한 Latch로 보호되는 공유 데이터에 접근하여 쿼리를 실행하게 된다. 다음 그림은 SQLOS의 스케줄링을 나타낸다.

 

 

Spinlock은 Latch처럼 공유 데이터 구조에 접근하는 스레드 동기화 스토리지 엔진에 의해 사용되는 경량의 동기화 객체이다. Latch와 다른점은 CPU를 떠나지 않고 spinlock를 획득 할 때까지 루프에서 회전하는 것이다. 즉 CPU에서 항상 실행상태에 있다. Spinlock의 가장 큰 장점은 쿼리가 스핀에서 대기해야하는 경우 컨텍스트 스위치가 관여하지 않는것이다. 이는 바쁜 CPU 사이클에서 다른 쿼리가 효과적으로 사용 할 수 있다.

 

SQL Server 2008 R2에서는 너무 많은 CPU 사이클을 방지하기 위해 스레드가 회전을 잠시 중지하는 exponential backoff mechanism을 시행한다. 간격 후 스레드는 spinlock을 획득 할 수 있는 시간 사이에 슬립모드로 전환한다. 이러한 행동은 CPU 성능 부담을 줄여준다.

 

Spinlock 문제를 해결하기 위한 주요 DMV는 sys.dm_os_spinlock_stats 이다. 이 DMV에서 반한되는 모든 행은 SQL 서버에서 하나의 특정 스핀을 나타낸다. SQL Server 2014는 262개의 다른 Spinlock을 구현한다.

select * from sys.dm_os_spinlock_stats

 

 

  • Name : Spinlock 이름
  • Collisions : 스핀락이 보호된 데이터 구조에 액세스하려고 할 때 스레드가 스핀락에 의해 차단된 횟수
  • Spins : 스핀락을 구하려고 루프를 시도한 횟수
  • Spins_per_collision : 스핀과 충돌 사이의 비율
  • Sleep_time : 백오프 때문에 스레드가 슬립한 시간
  • Backoffs : 스레드가 백오프된 동안 다른 스레드가 CPU에 계속 할 수 있도록 한 횟수

 

가장 중요한 열은 backoffs이다. 이 이벤트는 특정 Spinlock 유형에 대한 발생 빈도를 알 수 있다. 매우 높은 백오프는 높은 CPU소비와 Spinlock 경합을 발생시킨다.

 

스핀락 경합 문제를 해결하려면 XEvent의 splos.spinlock_backoff를 사용할 수 있다. 백오프가 발생하면 이 이벤트는 발생되며 백오프는 항상 SQL서버에서 발생하기 때문에 좋은 조건절을 사용했는지 확인해야 한다.

-- Retrieve the type value for the LOCK_HASH spinlock.

-- That value is used by the next XEvent session

SELECT * FROM sys.dm_xe_map_values

WHERE name = 'spinlock_types'

AND map_value = 'LOCK_HASH'

GO

 

-- Tracks the spinlock_backoff event

CREATE EVENT SESSION SpinlockContention ON SERVER

ADD EVENT sqlos.spinlock_backoff

(

ACTION

(

package0.callstack

)

WHERE

(

[type] = 129 -- <<< Value from the previous query

)

)

ADD TARGET package0.histogram

(

SET source = 'package0.callstack', source_type = 1

)

GO

 

SQL Server 내에서 특정 스핀록 유형에 대한 가장 높은 백오프를 생성한 코드 경로를 볼 수 있다. 이때 공용 심볼을 설치하기 위한 trace flag 3656을 활성화 해야한다. 자세한 내용은 Paul Randal 블로그를 참고 한다.

  • How to download a sqlservr.pdb symbol file

http://www.sqlskills.com/blogs/paul/how-to-download-a-sqlservr-pdb-symbol-file/

 

 

[참고자료]

http://www.sqlpassion.at/archive/2014/06/30/introduction-to-spinlocks-in-sql-server-2/

 

 

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

 

 

Spinlock, 스핀락, 래치, Latch, sys.dm_os_spinlock_stats, 동시성 경합, 공유 리소스 잠금, SQL latch, sqlserver, mssql, lock

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 서버 인스턴스에 대한 호스트 이름 확인

 

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

 

SQL Server 클러스터 페일오버를 트러블슈팅할때 페일오버된 시간과 페일오버 이전 호스트의 이름을 확인하는 것은 필수 사항이다. 이번 시간에 장애조치가 발생한 시간과 이전에 실행된 SQL Server 이름을 확인하는 방법에 대해서 알아본다.

 

SQL Server 클러스터링 환경에서 페일오버 이후에는 이전의 SQL Server 노드를 식별하는 것이 곤란하다. 로깅의 대부분이 네트워크 또는 가상 서버의 인스턴스명을 사용하기 때문이다. 이때 SQL Server 에러로그와 이벤트 뷰어 로그를 사용하여 확인 할 수 있다.

 

SQL Server가 현재 실행중인 노드/호스트 이름을 확인하는 것은 매우 간단하다. 다음 스크립트를 실행하면 확인 할 수 있다.

select serverproperty('ComputerNamePhysicalNetBIOS')

 

 

 

SQL Serer가 다시 시작되었을 때 장애 시점은 에러로그를 통해서 확인 할 수 있다. SSMS에서 로그파일 뷰어를 사용하거나 xp_readerrorlog 확장 저장 프로시저를 사용할 수 있다.

 

아래 그림은 SSMS의 화면이다. 첫 번째 메시지는 현재 호스트의 로그가 기록되어 있기 때문에 이전의 기록인 2 번째 로그를 확인 한다.

 

예를 들어 아래 그림을 보면 동일한 호스트 / 노드(DBP2)에서 다시 시작되었음을 나타낸다. 마지막 메시지는 SQL Server가 장애조치 이전의 호스트 DBP1에서 실행되었음을 나타낸다.

 

 

확장 프로시저를 이용할 때에는 NETBIOS라는 구문이 포함된 문장을 확인해야 한다. 다음 스크립트를 실행하면 NETBIOS를 포함한 오류로그를 확인할 수 있다.

SET NOCOUNT ON

-- 1 - Declare variables

DECLARE @numerrorlogfile int

-- 2 - Create temporary table

CREATE TABLE #errorLog

([LogDate] datetime,

[ProcessInfo] nvarchar(20),

[Text] nvarchar(max)

)

-- 3 - Initialize parameters

SET @numerrorlogfile = 0

-- 4 - WHILE loop to process error logs

WHILE @numerrorlogfile < 5

BEGIN TRY

INSERT #errorLog ([LogDate], [ProcessInfo], [Text])

EXEC master.dbo.xp_readerrorlog @numerrorlogfile, 1, N'NETBIOS', NULL, NULL, NULL, N'desc'

 

SET @numerrorlogfile = @numerrorlogfile + 1;

END TRY

BEGIN CATCH

SET @numerrorlogfile = @numerrorlogfile + 1;

END CATCH

-- 5 - Final result set

SELECT LogDate,[Text] FROM #errorLog

-- 6 - Clean-up temp table

DROP TABLE #errorlog

GO

 

 

 

Windows Event 뷰어에서도 호스트의 이름을 확인 할 수 있다. 이벤트ID 17664 로그에서 Node/Host를 확인 할 수 있다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/2744/steps-to-check-the-host-name-for-a-clustered-sql-server-instance

 

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

 

 

SQL Server, mssql, sql cluster, 클러스터링, 데이터베이스, SQL Failover, SQL 클러스터, DB 클러스터, High Available, 고가용성, SQL 2012, DBA

sys.dm_tran_locks 를 이용한 잠금 정보 확인

 

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

 

 

Sys.dm_tran_locks는 현재 활성 상태인 잠금 관리자 리소스에 대한 정보를 반환 한다. 각 행은 이미 허용된 잠금 또는 허용 대기 중인 잠금에 대해 현재 활성 상태인 잠금 관리자 요청을 나타낸다.

 

select * from sys.dm_tran_locks;

 

 

  • Resource_type : 리소스 유형을 나타낸다. 리소스는 DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT 또는 ALLOCATION_UNIT 중 하나이다.
  • Request_mode : 요청 모드이다. 허용 된 요청의 경우 허용 모드이고 대기 중인 요청의 경우에는 요청 중인 모드가 된다.
  • Request_status : 요청의 현재 상태이다. 가능한 값은 GRANTED, CONVERT, WAIT, LOW_PRIORITY_CONVERT, LOW_PRIORITY_WAIT 또는 ABORT_BLOCKERS이다.
  • resource_associated_entity_id : 리소스가 연결된 데이터베이스 내의 엔터티ID이다. 리소스 유형에 따라 개체 ID, Hobt ID 또는 할당 단위 ID가 될 수 있다.

 

 

Sys.dm_tran_locks, sys.sysprocesses, sys.dm_exec_sql_text 정보를 활용하여 잠금 정보를 좀더 상세히 알아본다. 다음 스크립트를 실행하면 잠금에 대한 정보, 차단된SID, 실행중인 쿼리 등을 보여준다.

SELECT DTL.resource_type,

CASE

WHEN DTL.resource_type IN ('DATABASE', 'FILE', 'METADATA') THEN DTL.resource_type

WHEN DTL.resource_type = 'OBJECT' THEN OBJECT_NAME(DTL.resource_associated_entity_id, SP.[dbid])

WHEN DTL.resource_type IN ('KEY', 'PAGE', 'RID') THEN

(

SELECT OBJECT_NAME([object_id])

FROM sys.partitions

WHERE sys.partitions.hobt_id =

DTL.resource_associated_entity_id

)

ELSE 'Unidentified'

END AS requested_object_name, DTL.request_mode, DTL.request_status,

DEST.TEXT, SP.spid, SP.blocked, SP.status, SP.loginame

FROM sys.dm_tran_locks DTL

INNER JOIN sys.sysprocesses SP

ON DTL.request_session_id = SP.spid

--INNER JOIN sys.[dm_exec_requests] AS SDER ON SP.[spid] = [SDER].[session_id]

CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS DEST

WHERE SP.dbid = DB_ID()

AND DTL.[resource_type] <> 'DATABASE'

ORDER BY DTL.[request_session_id];

 

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/1968/understanding-sql-server-locking/

 

 

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

 

데이터베이스, 잠금, DB 락, DB 블록킹, sys.dm_tran)locks, sys.dm_exec_sql_text, sys.sysprocesses, SQL Server, MSSQL, DBA, DMV, db lock

외래키 제약 조건 삭제 후 재작성 스크립트 생성하기

 

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

 

외래키 삭제 후 재작성 하는 스크립트를 생성하는 스크립트이다. SSMS 기능에도 스크립트 생성 기능은 있지만 스키마 또는 데이터 및 스키마, 데이터만 가능하다. 아래 스크립트는 외래키만 삭제, 생성하는 쿼리문을 만들어 제공한다.

 

CREATE TABLE #x -- feel free to use a permanent table

(

drop_script NVARCHAR(MAX),

create_script NVARCHAR(MAX)

);

 

DECLARE @drop NVARCHAR(MAX) = N'',

@create NVARCHAR(MAX) = N'';

 

-- drop is easy, just build a simple concatenated list from sys.foreign_keys:

SELECT @drop += N'

ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)

+ ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'

FROM sys.foreign_keys AS fk

INNER JOIN sys.tables AS ct

ON fk.parent_object_id = ct.[object_id]

INNER JOIN sys.schemas AS cs

ON ct.[schema_id] = cs.[schema_id];

 

INSERT #x(drop_script) SELECT @drop;

 

-- create is a little more complex. We need to generate the list of

-- columns on both sides of the constraint, even though in most cases

-- there is only one column.

SELECT @create += N'

ALTER TABLE '

+ QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)

+ ' ADD CONSTRAINT ' + QUOTENAME(fk.name)

+ ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)

-- get all the columns in the constraint table

FROM sys.columns AS c

INNER JOIN sys.foreign_key_columns AS fkc

ON fkc.parent_column_id = c.column_id

AND fkc.parent_object_id = c.[object_id]

WHERE fkc.constraint_object_id = fk.[object_id]

ORDER BY fkc.constraint_column_id

FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')

+ ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)

+ '(' + STUFF((SELECT ',' + QUOTENAME(c.name)

-- get all the referenced columns

FROM sys.columns AS c

INNER JOIN sys.foreign_key_columns AS fkc

ON fkc.referenced_column_id = c.column_id

AND fkc.referenced_object_id = c.[object_id]

WHERE fkc.constraint_object_id = fk.[object_id]

ORDER BY fkc.constraint_column_id

FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'

FROM sys.foreign_keys AS fk

INNER JOIN sys.tables AS rt -- referenced table

ON fk.referenced_object_id = rt.[object_id]

INNER JOIN sys.schemas AS rs

ON rt.[schema_id] = rs.[schema_id]

INNER JOIN sys.tables AS ct -- constraint table

ON fk.parent_object_id = ct.[object_id]

INNER JOIN sys.schemas AS cs

ON ct.[schema_id] = cs.[schema_id]

WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;

 

UPDATE #x SET create_script = @create;

 

PRINT @drop;

PRINT @create;

 

/*

EXEC sp_executesql @drop

-- clear out data etc. here

EXEC sp_executesql @create;

*/

 

 

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3347/drop-and-recreate-all-foreign-key-constraints-in-sql-server/

 

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

 

Sqlserver, 외래키 작성, foreign key, DBA, DB관리, 데이터베이스 관리, mssql, sql2012, 제약조건,

페이지 ID로 테이블 이름 찾기

 

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

 

손상된 페이지가 있을 때 어느 테이블의 손상인지 확인 하는 방법에 대해서 알아본다. 손상된 페이지가 있을 경우 일반적으로 DBCC CHECKDB를 실행하지만 데이터베이스가 TB 단위인 경우에는 이 문제를 파악하기 위해 몇 시간이 걸릴지도 모르는 일이다. 또 다른 확인 방법으로는 PAGELATCH_EX 대기를 보고 sys.dm_os_waiting_tasks 의 resource_description 정보를 이용하여 테이블 이름을 알아 낼 수도 있다.

 

suspect_pages 테이블의 데이터를 이용하여 테이블 이름을 확인해 보자. Suspect_page 테이블은 SQL Server 2005부터 도입 되었으며 주의 대상이 발생하였을 때 대상 페이지에 대한 정보를 유지 관리하는데 사용되며 복원이 필요한지 여부를 결정하는데 사용 된다.

 

Suspect_pages 테이블에서 손상된 페이지가 있는지 확이 한다.

 

Page_id 를 이용하여 페이지 정보를 확인 한다.

 

 

Metadata: Objectid 정보를 이용하여 오브젝트 네임을 조회 한다.

 

 

[참고자료]

http://www.sqlskills.com/blogs/paul/finding-table-name-page-id/?utm_source=rss&utm_medium=rss&utm_campaign=finding-table-name-page-id

 

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

 

 

MSSQL, suspect, 주의 대상, 서스펙트, DB복구, DB관리, DBA,SQL Server, dbcc checkdb, pageID, 데이터베이스

DBCC CHECKPRIMARYFILE 사용법

 

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

 

현재 생성되어 있는 데이터베이스의 파일정보를 확인하는 방법에는 SSMS를 활용한 방법과 쿼리로 확인하는 방법이 있다. SSMS의 UI를 사용하는 경우 데이터베이스 수 만큼 클릭하여 확인해야 하는 반복적인 노동 작업이다.

문서화 되지 않은 DBCC CHECKPRIMARYFILE 명령을 이용하면 파일의 메타 정보(디스크 경로, 데이터베이스 이름)를 쉽게 확인 할 수 있다. 비록 문서화 되어 있지 않은 명령어 이지만 위험성이 없는 작업이므로 걱정하지 않아도 된다.

 

[기본 구문]

Syntax:DBCC CHECKPRIMARYFILE ({'PhysicalFileName'} [,opt={0|1|2|3}])

 

PhysicalFileName is the full path for the primary database file.

 

opt=0 - checks if the file a primary database file.

opt=1 - returns name, size, maxsize, status and path of all files associated to the database.

opt=2 - returns the database name, version and collation.

opt=3 - returns name, status and path of all files associated with the database.

 

[SQL Server DBCC CHECKPRIMARYFILE with Option = 0]

기본 파일인지 확인하여 1이면 기본 파일, 아니면 0이 반환된다.

DBCC CHECKPRIMARYFILE('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SW_TEST.MDF',0) WITH NO_INFOMSGS

 

 

[DBCC CHECKPRIMARYFILE with Option = 1]

논이적 이름, 크기, MAXSIZE, 상태, 실제 물리적 이름 등을 확인 한다.

DBCC CHECKPRIMARYFILE('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SW_TEST.MDF',1) WITH NO_INFOMSGS

 

 

 

[DBCC CHECKPRIMARYFILE with Option = 2]

데이터베이스 이름 및 버전, 정렬을 확인 한다.

DBCC CHECKPRIMARYFILE('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SW_TEST.MDF',2) WITH NO_INFOMSGS

 

 

[DBCC CHECKPRIMARYFILE with Option = 3]

모든 파일의 논리적 이름, 상태, 물리적 경로를 확인 한다.

DBCC CHECKPRIMARYFILE('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SW_TEST.MDF',3) WITH NO_INFOMSGS

 

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3342/how-to-use-dbcc-checkprimaryfile-to-attach-several-sql-server-databases-in-seconds/

 

 

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

 

 

CHECKPRIMARYFILE, 데이터베이스 정보, MSSQL, SQL Server, DBA, DBMS, DB관리, DB복원, DB경로, 데이터베이스 관리

테이블 변수와 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

비결정적 사용자 정의 함수 사용으로 인한 느린 쿼리

 

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

 

SQL Server에서는 사용자 정의 함수를 생성하고 사용할 수 있다. 사용자 정의 함수는 매개변수를 허용하고 복잡한 계산 등의 동작을 수행하며 해당 동작의 결과를 값으로 반환한다. 반환 값은 단일 스칼라 값이나 테이블일 수 있다.

 

 

이번 포스트는 비결정적인 사용자 정의 함수로 인하여 쿼리가 느린 상황을 살펴보고 해결하는 방법에 대해서 살펴본다. 포스트의 내용은 CSS SQL Engineers를 참고 하였으며 읽고 이해한 내용을 정리하였다. 번역의 오류나 기술적 오류가 있을 수 있으며 자세한 내용은 원문을 참고한다.

 

느린 쿼리의 상황은 "NO JOIN PREDICATE" 경고가 발생한 것으로 카티션 프로덕트가 발생하였다. 이해를 돕기 위해 실습을 통해 알아보자. 스크립트를 실행하여 테스트 테이블 및 데이터를 생성한다.

create table t1 (c1 int not null, c2 varchar(100))

go

 

create table t2 (c1 int not null, c2 varchar(100))

go

 

set nocount on

go

 

declare @i int

begin tran

select @i = 0

while (@i < 1000)

begin

insert into t1 (c1, c2) values (@i, 'a')

insert into t2 (c1, c2) values (@i, 'b')

select @i = @i + 1

end

 

commit tran

go

 

create function dbo.myfunc (@c1 int)

returns int

--with schemabinding

as

begin

return (@c1 * 100 )

end

go

 

create view v1 as select c1, c2, dbo.myfunc(c1) as c3 from t1

go

create view v2 as select c1, c2, dbo.myfunc(c1) as c3 from t2

go

 

쿼리를 실행한다. 쿼리 계획을 보면 Warning 컬럼에 "NO JOIN PREDICATE" 경고를 확인 할 수 있다. 이는 조인 결과가 1000000(1000 * 1000 rows 테이블 데이터) 이다. 5번 줄에서 myfunc 호출은 2000000을 호출 한다. (t1.c1 : 1000000, t2.c1 : 1000000)

dbcc freeproccache

go

set statistics profile on

go

 

-- But by pulling UDF above join in this query we actually introduce a cartesian product (NO JOIN PREDICATE)

-- UDF is called 1 million times instead of 1000 times each for the two views!

select count(*) from v1 as t1 join v2 as t2 on t1.c3 = t2.c3

go

set statistics profile off

go

 

 

 

해결방법으로 사용자 정의 함수에 스카미바인딩을 추가 하였다.

drop function dbo.myFunc

go

 

create function dbo.myfunc (@c1 int)

returns int

with schemabinding

as

begin

return (@c1 * 100 )

end

 

 

쿼리를 실행 하여 계획을 보면 "NO JOIN PREDICATE" 경고가 발생하지 않는 것을 확인 할 수 있으며 scalare UDF는 오른쪽 테이블 스캔 후 아래로 푸쉬하여 각 테이블당 100회 적용 된다.

dbcc freeproccache

go

set statistics profile on

go

 

-- But by pulling UDF above join in this query we actually introduce a cartesian product (NO JOIN PREDICATE)

-- UDF is called 1 million times instead of 1000 times each for the two views!

select count(*) from v1 as t1 join v2 as t2 on t1.c3 = t2.c3

go

set statistics profile off

go

 

 

 

다음의 경우는 스키마바인딩을 사용하지만 GETDATE()로 인하여 "NO JOIN PREDICATE" 경고와 함께 카티션 프로덕트가 발생한 것을 확인 할 수 있다.

drop function dbo.myFunc

go

create function dbo.myfunc (@c1 int)

returns int

with schemabinding

as

begin

return (@c1 * 100 * datepart (mm,getdate()))

end

 

 

 

 

[참고자료]

http://blogs.msdn.com/b/psssql/archive/2014/07/08/slow-query-using-non-deterministic-user-defined-function.aspx

 

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

 

 

SQL 튜닝, 쿼리 튜닝, 사용자 정의 함수, 스키마바인딩, 실행계획, 카티션프로덕트, MSSQL, SQL Server, DB튜닝, 데이터베이스

스냅숏 격리 수준(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, 잠금, 트랜잭션관리, 쿼리튜닝

전체 백업에서 포함되는 트랜잭션 범위

 

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

 

SQL Server 전체 백업을 진행 할 때 마지막 포함된 트랜잭션 범위에 대해서 알아보자.

 

 

  1. 백업작업 시 체크포인트를 실행하여 버퍼풀에 있는 모든 더티 페이지를 강제로 디스크에 기록한다. 백업 작업은 데이터베이스에 할당된 페이지를 읽기 시작한다.
  2. 백업 작업이 페이지 X를 읽는다.
  3. 트랜잭션 A가 발생 한다.
  4. 트랜잭션 A가 페이지 X를 변경한다.
  5. 트랜잭션 B가 발생 한다.
  6. 트랜잭션 A가 끝나고 페이지 X에 대한 변경사항을 커밋한다.
  7. 백업 데이터 읽기 작업이 완료되고 트랜잭션 로그를 읽기 시작한다.

 

백업 데이터 읽기 작업이 완료된 시점에 트랜잭션 A에 대한 내용은 트랜잭션로그에 커밋이 완료 되었기 때문에 현재 트랜잭션로그와 일치한다. 가장 오래된 활성 트랜잭션의 LSN(포인트 5번)의 경우 트랜잭션 로그에는 포함되어 있지만 마지막 백업 데이터를 읽은 시점에서(포인트 7)에서의 트랜잭션과는 일치 하지 않는다.

 

트랜잭션 로그에 포함된 최소 LSN은 전체 백업에 포함된 트랜잭션 로그(마지막 체크포인트LSN, 활성 LSN)을 포함한다. 일치하지 않은 트랜잭션에 대한 REDO log 레코드는 최신의 페이지를 가지고 로그 레코드를 다시 실행한다.

 

[참고자료]

http://www.sqlskills.com/blogs/paul/more-on-how-much-transaction-log-a-full-backup-includes/

 

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

 

 

SQLSERVER, mssql, SQL튜닝, SQL강좌, SQL백업, 트랜잭션 범위, 로그레코드, 트랜잭션 로그, 데이터베이스 백업, Database backup, transaction log

REPL_SCHEMA_ACCESS 대기 유형

 

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

 

SQL Server의 고가용성 솔루션인 복제 구성에서 트랜잭션 복제 지연 문제가 발생 할 때 나타나는 대기와 이를 해결 할 수 있는 방법에 대해서 알아본다.

 

이 포스트는 CSS SQL Server Engineers 팀 블로그의 내용을 읽고 이해한 것을 정리하였으며 번역의 오류나 기술적 오류 가능성이 있으므로 자세한 내용은 원문을 참고한다.

 

DMV를 사용하여 현재 실행 중인 각 요청에 대한 정보를 확인 한다. REPL_SCHEMA_ACCESS 대기를 확인 할 수 있다.

select * from sys.dm_exec_requests

 

 

REPL_SCHEMA_ACCESS 대기는 복제 스키마 버전 정보 동기화중에 발생하는 것으로 복제된 개체에 대해 DDL문을 실행하고 로그판독기가 DDL 발생을 기반으로 버전이 지정된 스키마를 작성하거나 사용할 때 이 상태가 나타난다.

 

트랜잭션 복제와 하나의 게시자에 많은 데이터베이스가 활성화 되어 있는 경우 이 충돌이 발생할 수 있다.

 

이를 방지 하기 위해서는 다음과 같은 방법이 있다.

  • 큰 트랜잭션 보다는 작은 트랜잭션을 사용한다. 큰 트랜잭션 명령은 높은 메모리 버퍼를 요구한다.
  • 서로 다른 시간에 배포와 게시 작업을 실행하여 동일 시간에 집중적인 트랜잭션을 분산한다.
  • 로그 리더의 수를 줄인다. 대기는 서버 단위에서 발생하기 때문에 같은 하드웨어의 경우에라도 서로 다른 인스턴스에서 게시한다면 경합이 감소 될 수도 있다.
  • 로그 리더 에이전트의 기본 5초인 PollingInterval 시간을 줄인다.

 

 

[참고자료]

 

 

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

 

 

SQLSERVER, mssql, SQL튜닝, SQL강좌, SQL 복제, 대기 유형, 복제 경합, 트랜잭션복제, REPL_SCHEMA_ACCESS, 게시서버, 구독서버

 

Database 손상시 Emergency 모드로 복구하기

 

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

 

SQL Server에서 데이터베이스가 손상되었을 때 조치할 수 있는 방법에 대해서 알아본다.

 

데이터베이스가 손상되었을 때 유일하게 접속할 수 있는 방법은 Emergency 모드이다. 시나리오는 다음과 같다.

  1. Emergency 모드로 데이터베이스에 접근 한다.
  2. 새로운 트랜잭션로그를 구축하기 위해 문서화 되지 않은 DBCC REBUILD_LOG 명령을 사용한다.
  3. 데이터 파일에 대한 손상을 해결하기 위해 REPAIR_ALLOW_DATA_LOSS 옵션으로 DBCC CHECKDB를 실행 한다.
  4. 데이터복구가 완료되면 백업을 진행하고 Emergency 모드를 해제 한다.

 

 

시나리오에 따라 실습을 진행하여 보자. 데이터베이스가 손상되었을 경우를 가정하고 진행 하였다.

 

문제가 발생한 데이터베이스를 Emergency 모드로 변경하고 다른 유저가 접속하지 못하도록 단일 사용자 모드로 변경한다.

ALTER DATABASE SW_TEST SET EMERGENCY;

GO

 

ALTER DATABASE SW_TEST SET SINGLE_USER

GO

 

 

 

DBCC CHECKDB를 실행한다. 이때 REPAIR_ALLOW_DATA_LOSS 옵션을 사용한다. 이 옵션은 손상된 데이터를 삭제하는 옵션이다. 중요 데이터의 경우 삭제 될 수 있으므로 신중하게 사용해야 한다.

DBCC CHECKDB (N'SW_TEST', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS

GO

 

 

 

데이터복구가 완료 되었으면 백업을 진행 한다.

BACKUP DATABASE SW_TEST TO DISK = 'C:\BACKUP\SW_TEST.BAK'

GO

 

 

백업이 완료 되었으면 Emergency 모드에서 Online 모드로 변경한다.

ALTER DATABASE SW_TEST SET ONLINE

GO

 

 

 

다른 사용자가 접속하여 사용할 수 있도록 하기 위해 데이터베이스 상태를 멀티유저모드로 변경한다.

ALTER DATABASE SW_TEST SET MULTI_USER

GO

 

 

손상된 데이터베이스를 복구하는 가장 좋은 방법은 백업파일을 이용하여 복구하는 것이다. 하지만 이미 손상된 상태에서 백업을 진행하였다면 백업 파일 또한 손상되어 있으므로 주기적인 백업과 백업파일에 대한 무결성 검사등을 진행하여 데이터의 안정성을 보장해야 한다.

 

데이터베이스 복구 방법은 이 외에도 비상로그 백업을 이용한 복구 등 매우 다양한 방법이 있다. 비상시 대응 할 수 있도록 매뉴얼을 만들고 숙지할 수 있도록 하자.

 

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

 

SQLSERVER, mssql, SQL튜닝, SQL강좌, 데이터베이스 복구, DBCC CHECKDB, emergency 모드, 응급 복구모드, 데이터복구, 데이터베이스 손상, suspect

 

서버 그룹을 이용한 다중서버 쿼리하기

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

 

여러 SQL Server에 동일한 쿼리를 실행하는 경우가 있다. 관리하는 서버 수가 적다면 직접 서버에 접속하여 하나씩 실행해도 되지만 관리하는 서버가 많다면 하나씩 실행하는 것은 많은 리소스가 소모되는 일이다.

 

SQL Server 2008부터는 중앙관리 서버 기능을 사용하여 서버그룹에 등록되어있는 서버에 대해서는 다중 서버 쿼리가 가능하다. 다중 서버 쿼리를 사용하기 위해서는 우선 서버 그룹에 서버를 등록해야 한다.

 

등록된 서버를 보기 위해서는 메뉴에서 [보기] [등록된 서버]를 실행 한다.

 

[등록된 서버]에서 로컬 서버 그룹에서 마우스 오른쪽 버튼을 클릭하여 [새 서버 그룹]을 생성 한다.

 

 

생성한 그룹에서 마우스 오른쪽을 클릭하여 [새 서버 등록]을 실행 한다.

 

 

서버를 등록하고 그룹에서 마우스 오른쪽을 클릭하여 [새 쿼리]를 실행하면 현재 그룹에 등록된 서버에 동일한 쿼리를 적용할 수 있다. 다중 서버 쿼리의 경우 SSMS의 쿼리창 하단의 색상이 분홍색으로 나타남을 확인 할 수 있다.

 

 

간단히 SQL Server 버전을 확인하는 쿼리를 실행해 보았다. 그룹에 등록된 모드 서버에 쿼리가 실행되어 하나의 결과 셋으로 반환되는 것을 확인 할 수 있다.

 

 

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

 

 

SQLSERVER, mssql, 데이터베이스 강좌, SQL Server Tip, 다중서버 쿼리하기, 서버 그룹 등록, 다중 서버 관리, 중앙서버 관리, SQL 2012, SQL 2014

SQL Server Agent에서 CmdExec 오류

 

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

 

SQL Server 마이그레이션 후 CmdExec 실행 시 다음과 같은 오류가 발생한다면 각 하위 시스템에해당하는 파일이 지정된 경로에 있는지 확인해 볼 필요가 있다.

Unable to start execution of step 1 (reason: The CmdExec subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed.

 

하위 시스템 경로에 dll 파일이 존재하지 않으면 다음과 같은 에러를 확인 할 수 있다.

  • 하위 시스템 'ActiveScripting' 로드할 수 없습니다 (원인: 지정한 모듈을 찾을 수 없습니다)
  • 하위 시스템 'CmdExec' 로드할 수 없습니다 (원인: 있는 지정 된 모듈을 찾을 수 없습니다)
  • 하위 시스템 '스냅숏' 로드할 수 없습니다 (원인: 있는 지정 된 모듈을 찾을 수 없습니다)
  • 로그 판독기' 하위 시스템을 로드할 수 없습니다 (원인: 지정한 모듈을 찾을 수 없습니다)
  • 하위 시스템 '배포' 로드할 수 없습니다 (원인: 있는 지정 된 모듈을 찾을 수 없습니다)
  • 하위 시스템 '병합'을 로드할 수 없습니다 (원인: 있는 지정 된 모듈을 찾을 수 없습니다)
  • 대기열 판독기' 하위 시스템을 로드할 수 없습니다 (원인: 지정한 모듈을 찾을 수 없습니다)
  • 하위 시스템 'ANALYSISQUERY' 로드할 수 없습니다 (원인: 지정한 모듈을 찾을 수 없습니다)
  • 하위 시스템 'ANALYSISCOMMAND' 로드할 수 없습니다 (원인: 지정한 모듈을 찾을 수 없습니다)
  • 하위 시스템 'Dts' 로드할 수 없습니다 (원인: 지정한 모듈을 찾을 수 없습니다)

 

이 문제를 해결하려면 각 하위 시스템이 올바른 위치를 가리키도록 msdb.dbo.syssubsustems 시스템 테이블에 포함된 하위 시스템 메타 데이터를 업데이트 해야 한다.

하위 시스템

Subsystem.dbll

Agent_exe

ActiveScripting

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqlatxss90.dll

NULL

CmdExec

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqlcmdss90.dll

NULL

Snapshot

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqlrepss90.dll

C:\Program Files\Microsoft SQL Server\90\COM\Snapshot.exe

LogReader

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqlrepss90.dll

C:\Program Files\Microsoft SQL Server\90\COM\Logread.exe

Distribution

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqlrepss90.dll

C:\Program Files\Microsoft SQL Server\90\COM\Distrib.exe

Merge

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqlrepss90.dll

C:\Program Files\Microsoft SQL Server\90\COM\Replmerg.exe

QueueReader

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqlrepss90.dll

C:\Program Files\Microsoft SQL Server\90\COM\Qrdrsvc.exe

ANALYSISQUERY

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqlolapss90.dll

NULL

ANALYSISCOMMAND

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqlolapss90.dll

NULL

Dts

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqldtsss90.dll

C:\Program Files\Microsoft SQL Server\90\DTS\binn\Dtexec.exe

 

다음 스크립트는 현재 msdb에 저장되어 있는 하위시스템 경로를 확인한다.

select * from msdb.dbo.syssubsystems

 

 

 

다음 스크립트는 하위 시스템 경로를 변경하는 것으로 e:에서 c:로 변경하는 예제를 포함하고 있다.

sp_configure "allow updates", 1

reconfigure with override

 

update syssubsystems

set subsystem_dll = replace(subsystem_dll,'E:\Program Files','C:\Program Files')

from syssubsystems

where subsystem_dll like 'E:\Program Files%'

 

sp_configure "allow updates", 0

reconfigure with override

 

--Restart SQL Server Agent

 

하위 시스템의 경로를 변경하였으면 SQL Server Agent 서비스를 반드시 재시작해야 한다.

 

 

[참고자료]

 

 

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

 

 

SQLSERVER, mssql, 데이터베이스 강좌, SQL Server Agent, Agent CmdExec, msdb, 에이전트 실행 에러, 하위시스템로드 에러

Verbose SQL Server Agent Logging

 

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

 

SQL Server Agent 실행 결과를 출력파일로 저장하여 상세한 로그를 확인하는 방법에 대해서 알아본다.

 

SSMS에서 [SQL Server 에이전트] – [작업] 에서 Verbose 로그를 남길 작업을 선택하여 속성을 실행한다. 작업 속성의 [단계] 탭에서 [고급]을 선택 한다.

 

[출력 파일] 항목에 로그 파일을 기록할 경로를 입력 한다. 로그 기록을 동일한 파일에 덧붙여 기록하려면 [기존 파일에 출력 추가]를 선택 한다. (선택 해제 시 최근 기록으로 덮어씀)

 

Verbose 로그를 확인 하기 위해 예제로 백업을 실행해 보았다. 지정한 경로에 로그 파일이 생성된 것을 확인 할 수 있다.

BACKUP DATABASE SW_TEST TO DISK = 'C:\ERRLOG\SW_TEST.BAK' WITH STATS = 10

 

 

 

로그를 살펴 보면 상세한 로그가 기록된 것을 확인 할 수 있다.

 

 

한 개의 작업 목록에 여러 단계의 작업이 있을 때 각 작업에 대해 동일한 출력파일을 사용하고 [기존 파일에 출력 추가] 설정을 하면 각 단계의 로그를 한번에 확인 할 수 있다.

 

 

Verbose 로그를 사용하면 작업 실패 시 자세한 오류를 확인 할 수 있으며 오류 기록을 오랫동안 보관할 수 있는 장점이 있다.

 

 

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

 

SQLSERVER, mssql, 데이터베이스 강좌, SQL Server Agent, Agent errorlog, Agent Log, Verbose로그, Agent 상세 로그

Windows Event Log에 SQL Server Agent Log 기록

 

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

 

SQL Server Agent는 기본적으로 알림이 활성화 되어 있지 않다. SSMS에서 작업을 생성할 때 알림 탭으로 이동하면 알림을 활성화 할 수 있는 옵션을 확인 할 수 있다. 알림 옵션에는 이메일을. 호출, 메시지 호출, 윈도우 이벤트 로그, 작업삭제가 있다.

 

작업 관리를 위해 효율적인 방법은 작업의 실패의 경우 윈도우이벤트로그의 응용프로그램 이벤트 로그에 정보를 기록 하는 것이다. 다음 예제는 작업이 실패 할 경우 윈도우 이벤트의 응용프로그램 이벤트 로그에 로그를 기록하는 구성방법이다.

 

스크립트를 실행하면 현재 인스턴스에 정의된 작업에 대한 이벤트 알림에 대한 세부정보를 확인 할 수 있다.

SELECT

    name, notify_level_eventlog

FROM msdb..sysjobs

 

 

Notify_level_eventlog의 레벨 옵션은 다음과 같다.

Notify_level_eventlog

0 = 로깅 없음

1 = 성공시 기록

2 = 실패시 기록

3 = 모두 기록

 

 

로깅을 수행하지 않은 작업을 확인 하는 스크립트이다. 위의 표를 참고하여 notify_level_eventlog 값을 조건절에 입력 한다.

SELECT

    name

FROM msdb..sysjobs

WHERE notify_level_eventlog = 0

 

 

SQL Agent의 작업 실패 시 윈도우 이벤트로그의 응용프로그램 이벤트 로그에 기록할 수 있도록 설정을 변경 하는 스크립트이다.

EXEC msdb.dbo.sp_update_job @job_name=N'JOB_A', @notify_level_eventlog=2

GO

 

JOB_A라는 작업에서 강제로 0으로 나누어서 오류를 발생시켜 작업이 실패하도록 설정하였다.

 

이벤트로그에 기록되는 것을 확인 할 수 있다.

 

 

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

 

SQLSERVER, mssql, 데이터베이스 강좌, SQL Server Agent, Agent errorlog, Agent Log. Windows Event log, sp_update_job, Agent 알림 활성화

SQL Server Agent Error log 위치 변경

 

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

 

SQL Server 에이전트는 특정 이벤트를 캡처하여 로그파일로 기록한다. 기본적으로 이 파일의 위치는 SQL Server가 설치된 로그 폴더에 있다. 이번 포스트는 SQL Server Agent의 로그 파일의 폴더를 변경해 본다.

 

첫 번째로 기존의 에이전트 로그 파일 위치를 확인하다. 아래 스크립트를 실행하면 로그 파일의 경로를 확인 할 수 있으며 저장프로시저의 위치는 MSDB에 있다.

EXEC msdb..sp_get_sqlagent_properties

GO

 

 

 

다음 스크립트는 로그 파일의 경로를 변경 한다. 이때 해당 경로에 폴더가 미리 생성되어 있어야 한다. 만약 폴더가 생성되어 있지 않은 상태에서 해당 스크립트를 실행 한다면 SQL Server Agent 서비스를 재시작 할 때 오류가 발생한다.

EXEC msdb.dbo.sp_set_sqlagent_properties

@errorlog_file=N'c:\SQLAgentLog\LOG\SQLAGENT.OUT'

GO

 

 

다음 스크립트를 실행하여 수정 후 경로가 정상적으로 변경 되었는지 확인 한다.

EXEC msdb..sp_get_sqlagent_properties

GO

 

 

 

로그 파일의 경로가 변경된 것을 확인 할 수 있다. 하지만 Agent Services 서비스를 다시 시작 할 때까지 변경될 폴더로 로그가 기록되지 않는다. Agent Services를 재시작하여 변경 사항을 적용한다. 변경된 폴더로 에러로그가 기록되는 것을 확인 할 수 있다.

 

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3093/how-to-change-the-sql-server-agent-log-file-path/

 

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

 

SQLSERVER, mssql, 데이터베이스 강좌, SQL Server Agent, Agent errorlog, 에러로그 위치 변경, SQL 에러로그, SQL Agent 에러로그

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

Collation 변경

 

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

 

 

SQL Server를 설치할 기본 데이터 정렬 구성은 Windows Server의 데이터 정렬을 참고 한다. 사용자 설정에 따라 기본 데이터 정렬을 변경 할 수도 있다.

 

데이터베이스의 기본 데이터 정렬 설정에 따라 테이블을 생성할 때 문자열 타입의 경우 기본적으로 데이터베이스의 기본 데이터 정렬을 참고하여 생성된다. 이번 포스트는 테이블 컬럼에 Korean_Wansung_CI_AS 정렬에서 SQL_Latin1_General_CP1_CI_AS 정렬로 변경하는 방법에 대해서 알아 본다.

 

실습을 위해 문자 열이 있는 테이블을 생성한다. 그리고 PK, FK, 계산된 열, index등 다양한 옵션을 추가 한다.

CREATE TABLE dbo.MainTable

(PKColumn int NOT NULL IDENTITY (1, 1),

CharColumn char(10) NULL,

NCharColumn nchar(10) NULL,

VarcharColumn varchar(50) NULL,

NVarcharColumn nvarchar(50) NULL,

VarcharMaxColumn varchar(MAX) NULL,

NVarcharMaxColumn nvarchar(MAX) NULL,

ComputedColumn AS CharColumn + VarcharColumn)

GO

 

ALTER TABLE dbo.MainTable ADD CONSTRAINT CK_Table_NVarCharMaxColumn

CHECK (NVarCharMaxColumn IN ('Apple','Pear','Orange','Banana'))

GO

 

ALTER TABLE dbo.MainTable ADD CONSTRAINT PK_MainTable PRIMARY KEY CLUSTERED (PKColumn)

GO

 

CREATE NONCLUSTERED INDEX IX_Table_NVarcharColumn ON dbo.MainTable (NVarcharColumn)

GO

 

CREATE STATISTICS Stats_MainTable_VarcharMaxColumn

ON dbo.MainTable (VarcharMaxColumn) WITH FULLSCAN

GO

 

CREATE TABLE dbo.FKTable

(CharColumn char(10) NOT NULL,

DataColumn int NULL)

GO

 

ALTER TABLE dbo.FKTable ADD CONSTRAINT PK_FKTable PRIMARY KEY CLUSTERED (CharColumn)

GO

 

ALTER TABLE dbo.MainTable ADD CONSTRAINT

FK_MainTable_FKTable FOREIGN KEY

(CharColumn) REFERENCES dbo.FKTable

(CharColumn)

ON UPDATE NO ACTION

ON DELETE NO ACTION

GO

 

데이터베이스에서 Korean_Wansung_CI_AS 열을 사용하는 테이블 및 컬럼을 식별하기 위해 다음스크립트를 실행 한다. WHERE절에 Korean_Wansung_CI_AS를 입력 하였다.

SELECT t.name "Table Name",

c.name "Column Name",

c.collation_name "Collation"

FROM sys.tables t INNER JOIN

sys.columns c ON c.object_id=t.object_id INNER JOIN

sys.types s ON s.user_type_id=c.user_type_id

WHERE c.collation_name LIKE 'Korean_Wansung_CI_AS'

AND t.type like 'U'

AND t.name not like 'spt%'

AND t.name not like 'MSrep%'

 

 

계산된 열에 대한 목록을 식별하기 위해서는 다음 스크립트를 실행 한다.

-- computed columns

SELECT OBJECT_NAME(c.object_id) "Table Name",

COL_NAME(sd.referenced_major_id, sd.referenced_minor_id) "Column Name",

c.collation_name "Collation",

definition "Definition"

FROM sys.computed_columns cc INNER JOIN

sys.sql_dependencies sd ON cc.object_id=sd.object_id AND

cc.column_id=sd.column_id AND

sd.object_id=sd.referenced_major_id INNER JOIN

sys.columns c ON c.object_id=sd.referenced_major_id AND

c.column_id = sd.referenced_minor_id

WHERE c.collation_name like 'Korean_Wansung_CI_AS'

AND sd.class=1

 

 

 

FK 및 PK 목록을 식별하기 위해서는 다음 스크립트를 실행 한다.

--foreign keys

SELECT f.name "Foreign Key Name",

OBJECT_NAME(f.parent_object_id) "Table Name",

COL_NAME(fc.parent_object_id,fc.parent_column_id) "Column Name",

c1.collation_name "Collation",

OBJECT_NAME (f.referenced_object_id) "Reference Table Name",

COL_NAME(fc.referenced_object_id,fc.referenced_column_id) "Reference Column Name",

c2.collation_name "Collation"

FROM sys.foreign_keys AS f INNER JOIN

sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id INNER JOIN

sys.columns c1 ON c1.object_id=fc.parent_object_id AND

c1.column_id=fc.parent_column_id INNER JOIN

sys.columns c2 ON c2.object_id=fc.parent_object_id AND

c2.column_id=fc.parent_column_id

WHERE c1.collation_name like 'Korean_Wansung_CI_AS'

OR c2.collation_name like 'Korean_Wansung_CI_AS'

 

-- primary keys

SELECT i.name AS "Primary Key Name",

OBJECT_NAME(ic.object_id) "Table Name",

COL_NAME(ic.object_id,ic.column_id) "Column Name",

c.collation_name "Collation"

FROM sys.indexes AS i INNER JOIN

sys.index_columns AS ic ON i.object_id = ic.object_id AND

i.index_id = ic.index_id INNER JOIN

sys.columns c ON ic.object_id=c.object_id AND

c.column_id=ic.column_id

WHERE i.is_primary_key=1

AND c.collation_name like 'Korean_Wansung_CI_AS'

 

 

 

인덱스에 사용된 목록을 식별하기 위해 다음 스크립트를 실행 한다.

--indexes

SELECT i.name AS "Index Name",

OBJECT_NAME(ic.object_id) "Table Name",

COL_NAME(ic.object_id,ic.column_id) "Column Name",

c.collation_name "Collation"

FROM sys.indexes AS i INNER JOIN

sys.index_columns AS ic ON i.object_id = ic.object_id AND

i.index_id = ic.index_id INNER JOIN

sys.columns c ON ic.object_id=c.object_id AND

c.column_id=ic.column_id

WHERE c.collation_name like 'Korean_Wansung_CI_AS'

AND i.is_primary_key <> 1

AND OBJECT_NAME(ic.object_id) NOT LIKE 'sys%'

 

 

 

Check constraint에 사용된 목록을 식별하기 위해 다음 스크립트를 실행 한다.

-- check constraints

SELECT OBJECT_NAME(cc.object_id) "Constraint Name",

OBJECT_Name(c.object_id) "Table Name",

COL_NAME(sd.referenced_major_id, sd.referenced_minor_id) "Column Name",

c.collation_name "Collation",

definition "Definition"

FROM sys.check_constraints cc INNER JOIN

sys.sql_dependencies sd ON cc.object_id=sd.object_id INNER JOIN

sys.columns c ON c.object_id=sd.referenced_major_id AND

c.column_id = sd.referenced_minor_id

WHERE c.collation_name like 'Korean_Wansung_CI_AS'

AND cc.type = 'C'

AND sd.class=1

 

 

 

데이터 정렬을 변경하기 위해서는 먼저 계산된 열, FK, PK, index, CONSTRAINT 등을 삭제 한다.

ALTER TABLE dbo.MainTable DROP COLUMN ComputedColumn

ALTER TABLE dbo.MainTable DROP CONSTRAINT FK_MainTable_FKTable

ALTER TABLE dbo.FKTable DROP CONSTRAINT PK_FKTable

DROP INDEX dbo.MainTable.IX_Table_NVarcharColumn

DROP STATISTICS dbo.MainTable.Stats_MainTable_VarcharMaxColumn

ALTER TABLE dbo.MainTable DROP CONSTRAINT CK_Table_NVarCharMaxColumn

 

컬럼에 대한 데이터 정렬을 변경 한다. 각 컬럼에 대해 속성을 업데이트 해야 한다. 실습에서는 Korean_Wansung_CI_AS 정렬에서 SQL_Latin1_General_CP1_CI_AS 정렬로 변경한다.

ALTER TABLE dbo.MainTable

ALTER COLUMN NCharColumn nchar(10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL

ALTER TABLE dbo.MainTable

ALTER COLUMN CharColumn char(10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL

ALTER TABLE dbo.FKTable

ALTER COLUMN CharColumn char(10) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL

ALTER TABLE dbo.MainTable

ALTER COLUMN VarcharColumn varchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL

ALTER TABLE dbo.MainTable

ALTER COLUMN NVarcharColumn nvarchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL

ALTER TABLE dbo.MainTable

ALTER COLUMN VarcharMaxColumn varchar(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS NULL

ALTER TABLE dbo.MainTable

ALTER COLUMN NVarcharMaxColumn nvarchar(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS NULL

 

데이터 정렬 변경이 완료 되었으면 각 기존에 사용하던 계산된 열 및 인덱스, FK, PK, index등을 설정 한다.

ALTER TABLE dbo.MainTable ADD ComputedColumn AS CharColumn + VarcharColumn

GO

ALTER TABLE dbo.MainTable ADD CONSTRAINT CK_Table_NVarCharMaxColumn

CHECK (NVarCharMaxColumn IN ('Apple','Pear','Orange','Banana'))

GO

CREATE NONCLUSTERED INDEX IX_Table_NVarcharColumn ON dbo.MainTable (NVarcharColumn)

GO

CREATE STATISTICS Stats_MainTable_VarcharMaxColumn

ON dbo.MainTable (VarcharMaxColumn) WITH FULLSCAN

GO

ALTER TABLE dbo.FKTable ADD CONSTRAINT PK_FKTable PRIMARY KEY CLUSTERED (CharColumn)

GO

ALTER TABLE dbo.MainTable ADD CONSTRAINT

FK_MainTable_FKTable FOREIGN KEY

(CharColumn) REFERENCES dbo.FKTable

(CharColumn)

ON UPDATE NO ACTION

ON DELETE NO ACTION

GO

 

 

다음 스크립트를 실행하면 열의 데이터 정렬이 변경된 것을 확인 할 수 있다.

SELECT t.name "Table Name",

c.name "Column Name",

c.collation_name "Collation"

FROM sys.tables t INNER JOIN

sys.columns c ON c.object_id=t.object_id INNER JOIN

sys.types s ON s.user_type_id=c.user_type_id

 

 

 

데이터 정렬이 다른 경우 조인 등을 실행 할 때 문제가 발생 한다. 처음 테이블을 설계 할 때 사용자 환경에 맞는 데이터 정렬을 반드시 확인해야 하며 만약 서로 다른 정렬을 사용 할 때에는 굳이 테이블을 변경하지 않아도 COLLATE 명령어를 사용하여 조인이 가능 하다.

 

이 외에도 COLLATE 불일치로 인한 다양한 이슈가 있다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3221/how-to-change-the-collation-of-a-sql-server-column/

 

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

 

mssql, SQL튜닝, 강성욱, SQL강좌, Collation, 데이터정렬셋, 실행계획, 쿼리플랜, 옵티마이저, DB튜닝, COLLATE, Korean_Wansung_CI_AS

인덱스 리빌드 동작 (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

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

 

  • 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튜닝, 쿼리튜닝, 데이터베이스튜닝, 데이터베이스 백업, 트랜잭션 백업, 전체 백업, 데이터베이스 이동, 파일그룹, 데이터베이스 복원

백업 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, 로그시퀀스,

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

 

  • 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암호화, 테이블 암호화

 

 

 

 

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

 

  • 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

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, 파일 클린업

+ Recent posts