SA 계정 이름 변경 및 비활성화

 

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

 

SQL Server를 설치하면 기본적으로 SA 계정이 생성되어 사용된다. 보안 규칙이 잘 정의된 조직이라면 SA에 대한 보안에 많은 신경을 쓰지만 일부 조직에서는 SA를 기본적으로 사용하는 곳도 많다. SA 계정은 외부 위협으로부터 많이 사용되는 계정이므로 보안을 강화하거나 비활성화 하여 노출을 최소화하는 것이 좋다.

 

SA 계정에 대한 보안을 강화하기 위한 방법은 크게 4가지가 있다.

  1. 암호를 추측할 수 없도록 복잡하게 구성한다.
  2. SA의 이름을 변경한다.
  3. SA를 사용하지 않도록 비활성화 한다.
  4. 다른 계정 이름이 SA가 없는지 확인한다.

 

[SA 로그인 이름 바꾸기]

SSMS에서 [보안] – [로그인]에서 현재 생성되어 있는 계정을 확인할 수 있다.

또는 아래 스크립트를 사용하여 조회가 가능하다. SA 계정은 항상 SID가 0x01로 되어 있다.

SELECT name FROM sys.sql_logins WHERE sid = 0x01;

 

 

이름을 변경하기 위해서 SA의 속성을 실행하면 회식으로 표시되어 GUI에서 변경할 수 없다.

 

이름을 변경하기 위해서 SA에서 마우스 오른쪽을 클릭하여 [이름 바꾸기]를 선택하여 변경 할 수 있다.

 

아래 스크립트는 sa 계정의 이름을 old_sa로 변경한다.

ALTER LOGIN [sa] WITH NAME = [old_sa];

 

 

 

[SA 계정 비활성화]

SA 계정을 비활성화 하는 방법이다. SSMS에서 sa의 속성에서 [상태] - [로그인]에서 [사용안함]을 선택한다.

 

스크립트로 비활성화도 가능하다. 아래 스크립트는 SA 계정을 비활성화 한다.

ALTER LOGIN [sa] DISABLE;

 

SA 계정이 비활성화 되어 있는데 SA 계정으로 로그인을 시도한 경우 에러로그에 다음과 같이 기록됨을 확인할 수 있다.

 

 

[SA 이름의 계정이 있는지 확인]

일부 어플리케이션 등에서 SA계정을 사용하는 경우 SA 계정을 생성하여 사용할 수도 있다. 아래스크립트를 주기적으로 실행하여 SA 계정 여부를 확인할 수 있다.

SELECT sid, name FROM sys.sql_logins WHERE name = 'sa';

 

 

 

 

2015-08-25 / 강성욱 / http://sqlmvp.kr

 

 

MSSQL, SQL Server, SA 계정 비활성화, SQL 로그인 보안, SA, DBA

SQL Server 데이터베이스 속성을 확인할 때 사용되는 master.dbo.spt_values 복구

 

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

 

SSMS에서 SQL Server의 속성을 클릭하였을 때 master.dbo.spt_values 오류가 발생하였을 때 해결하는 방법에 대해서 알아본다.

 

SSMS에서 서버 속성을 클릭하였을 때 master.dbo.spt_values 오류가 발생하면 다음과 같은 경고문이 나타난다.

 

master.dbo.spt_values 프로시저는 다양한 데이터 항목을 포함하는 문서화 되지 않은 항목이다. 이 프로시저는 C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Install에서 u.table.sql에서 생성하는 것을 확인할 수 있다.

 

master.dbo.spt_values 오류 문제는 동일한 버전 및 에디션을 실행중인 다른 서버에서 복사하여 해결할 수 있다.

 

개체를 복사하는 방법으로는 스크립트를 생성하거나 가져오기 및 내보내기 마법사를 사용한다.

 

 

가져오기 및 내보내기에서 객체를 가져올 서버를 선택하고 다음을 클릭한다.

 

 

마찬가지로 master.dbo.stp_values를 생성할 대상 정보를 입력하고 다음을 클릭한다.

 

 

다음과 같이 화면이 나타나면 두 번째 옵션을 선택하고 다음을 클릭한다.

 

Master.dbo.stp_values는 시스템 오브젝트이기 때문에 T-SQL 쿼리를 사용하여 직접 가져와야 한다. 아래와 같은 스크립트를 입력하고 다음을 클릭한다.

SELECT * FROM master.dbo.spt_values

 

 

 

대상 객체의 이름을 설정한다. 이때 이름은 master.dbo.stp_values로 설정한다.

 

 

미리보기 버튼을 클릭하면 소스 오브젝트의 데이터를 표시한다. 실제 가져오기를 수행하려면 다음을 클릭한다.

 

 

가져오기 작업이 진행 되며 경고가 발생하면 해당 경고를 해결 후 다시 실행 한다.

 

 

정상적으로 가져오기 작업이 완료 되면 SSMS에서 서버 속성을 클릭하여 정상적으로 정보가 표시되는 것을 확인할 수 있다.

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/3694/fix-invalid-object-name-masterdbosptvalues-when-viewing-sql-server-database-properties/

 

 

2015-08-19 / 강성욱 / http://sqlmvp.kr

 

SQL Server, MSSQL, SSMS, master.dbo.stp_values, 데이터베이스 속성 오류, SQL 서버 속성 오류

XML로 생성된 정보를 테이블로 저장하기(sp_xml_preparedocument)

 

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

 

XML로 작성된 정보를 파싱하여 테이블로 저장하는 방법에 대해서 살펴본다. SQL Server에서제공되는 sp_xml_preparedocument 프로시저와 OPENXML 함수 기능을 함께 사용하여 편리하게 XML데이터를 사용할 수 있다.

 

sp_xml_preparedocument는 입력으로 제공되는 XML 텍스트를 읽고 MSXML 파서(Msxmlsql.dll)을 사용하여 텍스트의 구문을 분석하여 사용할 수 있는 상태로 제공한다. sp_xml_preparedocument는 XML문서의 새로 생성된 내부 표현에 액세스하는데 사용할 수 있는 핸들을 반환하고 이 핸들은 sp_xml_removedocument를 실행하여 무효화한 경우를 제외하고는 세션이 유지 되는동안 유효하다.

 

구문 분석된 문서는 SQL Server 내부캐시에 저장되며 MSXML 파서는 SQL Server에서 사용할 수 있는 총 메모리의 1/8을 사요한다. 메모리 부족을 방지하려면 sp_xml_removedocument를 실행하여 메모리를 확보해야한다.

 

참고로 sp_xml_preparedocument는 한번에 열수 있는 요소 수를 최대 256개로 제한되어 있다.

 

다음과 같이 XML로 생성된 파일이 있을 때 OPENROWSET 명령을 사용하여 가져온다.

 

CREATE TABLE XMLwithOpenXML

(

Id INT IDENTITY PRIMARY KEY,

XMLData XML,

LoadedDateTime DATETIME

)

 

INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)

SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()

FROM OPENROWSET(BULK 'C:\Test.xml', SINGLE_BLOB) AS x;

 

SELECT * FROM XMLwithOpenXML

 

 

XMLData 컬럼의 링크를 클릭하면 테이블로 로드된 XML정보를 확인할 수 있다.

 

XML 데이터가 로드되었으면 OPENXML 함수와 sp_xml_preparedocument를 사용하여 데이터를 파싱한다. 정상적으로 파싱된 데이터가 반환되면 INSERT 구문을 추가하여 데이터를 테이블로 저장하여 사용할 수 있다.

USE sw_test

GO

 

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

 

SELECT @XML = XMLData FROM XMLwithOpenXML

 

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

 

SELECT ponumber, podate

FROM OPENXML(@hDoc, 'root/polist/po')

WITH

(

ponumber int 'ponumber',

podate datetime 'podate'

)

 

EXEC sp_xml_removedocument @hDoc

GO

 

 

 

[참고자료]

 

 

2015-08-12 / 강성욱 / http://sqlmvp.kr

 

 

MSSQL, SQL Server, SQL XML, XML 저장, XML 파싱, DB XML

SQL Server 2016 SSMS 새로운 기능

 

  • Version : SQL Server 2016

 

SQL Server 2016에서 새롭게 소개된 SSMS의 새로운 기능에 대해서 살펴본다.

 

[인터넷에서 SSMS 설치]

클라이언트 도구인 SSMS를 설치하기 위해 더 이상 SQL Server DVD가 필요하지 않게 되었다. SQL Server 데이터 도구처럼 인터넷에서 다운로드 하는 웹 설치 프로그램을 사용한다.

 

 

[SSMS 업데이트 확인]

SSMS에서 온라인으로 업데이트를 확인하여 사용자가 클릭으로 업데이트를 진행 할 수 있다. 아래 이미지는 SQL Server 2014와 2016 버전의 도구메뉴 비교이다.

 

 

 

[라이브 쿼리 통계]

SSMS 2016 버전에서는 실행중인 쿼리의 실행계획을 그래픽으로 볼 수 있는 기능이 제공된다. SSMS에서 실제 실행 계획 및 표시 와 클라이언트 통계 사이에 활성화 버튼이 위치한다.

 

라이브 쿼리 통계를 활성화 하고 다음 스크립트를 실행 한다.

SELECT 0 FROM sys.objects A

CROSS APPLY sys.objects B

CROSS APPLY sys.objects C

 

 

 

[SSMS Azure 통합]

SSM가 Azure 데이터베이스에 연결하여 사용할 수 있도록 통합되었다.

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/3683/new-features-in-sql-server-management-studio-for-sql-server-2016/

 

 

2015-08-10 / 강성욱 / http://sqlmvp.kr

 

SQL Server 2016, SSMS 2016, 라이브 쿼리 통계, MSSQL, SQL Server Management Studio

DBCC DROPCLEANBUFFERS가 작동하지 않을 때

 

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

 

DBCC DROPCLEANBUFFERS는 버퍼 풀에서 빈 버퍼를 모두 제거하는 명령이다. 이 명령을 사용하면 서버를 종료하고 다시 시작하지 않아도 완전히 빈 버퍼 캐시를 사용하여 쿼리를 테스트할 수 있다.

 

버퍼풀에서 빈 버퍼를 삭제하려면 CHECKPOINT를 사용하여 빈 버퍼 캐시를 만든다. CHECKPOINT는 현재 데이터베이스에 대한 모든 커밋되지 않은 페이지를 디스크로 기록하고 버퍼를 비운다.

 

다음 내용은 DBCC DROPCLEANBUFFERS 사용하어 빈 버퍼를 제거한 상태에서 SELECT를 이용하여 데이터를 조회하였는데 물리적 읽기가 아닌 논리적 읽기가 발생되었다 왜 이런 현상이 발생 할까?

 

DBCC DROPCLEANBUFFERS는 단지 버퍼풀에서의 클린 페이지만 삭제한다. 클린 페이지는 메모리에서 읽거나 마지막으로 디스크에 기록된 이후 변경되지 않은 것이다. 더티페이지는 마지막으로 변경한 이후 디스크에 기록되지 않은 것이다. 더티페이지를 디스크에 기록하여 클린 페이지가 되지 않으면 DBCC DROPCLEANBUFFERS는 더티페이지를 삭제하지 않는다. 따라서 해당 테이블이 업데이트 되고 DBCC DROPCLEANBUFFERS를 실행하면 테이블의 페이지는 메모리에 남아 있어 물리적 읽기를 요구하지 않는다.

 

데이터베이스에서 모든 페이지가 메모리에서 플러시되려면 DBCC DRPOCLEANBUFFERS를 실행하기전 수동으로 CHECKPOINT를 실행해야한다. 아래 스크립트는 데이터베이스에 대한 페이지가 메모리에 존재하는지 확인한다.

 

SELECT *,

[DirtyPageCount] * 8 / 1024 AS [DirtyPageMB],

[CleanPageCount] * 8 / 1024 AS [CleanPageMB]

FROM

(SELECT

(CASE WHEN ([database_id] = 32767)

THEN N'Resource Database'

ELSE DB_NAME ([database_id]) END) AS [DatabaseName],

SUM (CASE WHEN ([is_modified] = 1)

THEN 1 ELSE 0 END) AS [DirtyPageCount],

SUM (CASE WHEN ([is_modified] = 1)

THEN 0 ELSE 1 END) AS [CleanPageCount]

FROM sys.dm_os_buffer_descriptors

GROUP BY [database_id]) AS [buffers]

ORDER BY [DatabaseName]

GO

 

 

 

 

[참고자료]

  • DBCC DROPCLEANBUFFERS

https://msdn.microsoft.com/ko-kr/library/ms187762(v=sql.120).aspx

  • When DBCC DROPCLEANBUFFERS doesn't work

http://www.sqlskills.com/blogs/paul/when-dbcc-dropcleanbuffers-doesnt-work/

 

 

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

 

MSSQL, SQLSERVER, CLEANBUFFERS, DBA, CHECKPOINT, SQL MEMORY

손상된 부트페이지 복구하기

 

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

 

데이터베이스 장애 중 큰 이슈는 부트페이지 손상이다. 부트 페이지가 손상된 경우에는 데이터베이스를 온라인 또는 긴급 모드로 전환될 수 없다. 이번 포스트는 손상된 부트페이지를 복구하는 방법에 대해서 알아본다.

 

부트 페이지를 손상하고 복원하기 위한 테스트 데이터베이스를 생성한다. 생성된 데이터베이스를 분리하여 데이터파일에 사용자가 접근할 수 있도록 한다.

-- Drop old database

USE [master];

GO

 

IF DATABASEPROPERTYEX (N'Company', N'Version') > 0

BEGIN

ALTER DATABASE [Company] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE [Company];

END

 

-- Create database and table

CREATE DATABASE [Company] ON PRIMARY (

NAME = N'Company',

FILENAME = N'D:\SQLskills\Company.mdf')

LOG ON (

NAME = N'Company_log',

FILENAME = N'D:\SQLskills\Company_log.ldf');

GO

 

USE [Company];

GO

 

CREATE TABLE [Sales] (

[SalesID] INT IDENTITY,

[CustomerID] INT DEFAULT CONVERT (INT, 100000 * RAND ()),

[SalesDate] DATETIME DEFAULT GETDATE (),

[SalesAmount] MONEY DEFAULT CONVERT (MONEY, 100 * RAND ()));

 

CREATE CLUSTERED INDEX [SalesCI] ON [Sales] ([SalesID]);

GO

 

-- Populate the table

SET NOCOUNT ON;

GO

 

INSERT INTO [Sales] DEFAULT VALUES;

GO 5000

 

-- Create some nonclustered indexes

CREATE NONCLUSTERED INDEX [SalesNCI_CustomerID] ON [Sales] ([CustomerID]);

 

CREATE NONCLUSTERED INDEX [SalesNCI_SalesDate_SalesAmount] ON [Sales] ([SalesDate]) INCLUDE ([SalesAmount]);

GO

 

-- Create a good backup

BACKUP DATABASE [Company] TO DISK = N'C:\SQLskills\OldCompany.bck'

WITH INIT;

 

-- And detach it

USE [master]

GO

 

EXEC sp_detach_db N'Company';

GO

 

데이터파일의 분리가 완료 되었으면 16진수 편집기(헥사에디터)를 사용하여 데이터파일을 오픈한다.

 

 

오픈한 데이터파일에서 부트페이지의 오프셋(offset)으로 이동한다. 오프셋은 항상 9페이지에 있기 때문에 8192*9 = 73728 오프셋에 있다.

 

선택 창이 나타나면 10진수(dec)와 선두부터 시작하는 옵션을 선택한다.

 

데이터베이스 이름을 포함하여 부팅 페이지의 내용을 볼 수 있다.

 

데이터베이스 이름에 이르기까지 모든 라인을 선택한 후 마우스 오른쪽 버튼을 클릭하고 채우기를 선택한다.

 

선택한 영역을 00으로 채운다.

 

00으로 채우고 나면 아래 그림과 같다.

 

데이터 변경이 완료 되었으면 저장한 다음 에디터를 종료한다. 이제 데이터파일은 손상된 상태로 설정이 완료 되었다.

 

데이터베이스를 연결을 시도해본다.

USE [master];

GO

 

-- Try attaching it again

EXEC sp_attach_db @dbname = N'Company',

@filename1 = N'D:\SQLskills\Company.mdf',

@filename2 = N'D:\SQLskills\Company_log.ldf';

GO

 

연결이 실패하면서 오류가 반환된다.

Msg 1813, Level 16, State 2, Line 5

Could not open new database 'Company'. CREATE DATABASE is aborted.

Msg 824, Level 24, State 2, Line 5

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:9; actual 0:0). It occurred during a read of page (1:9) in database ID 6 at offset 0x00000000012000 in file 'D:\SQLskills\Company.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

 

 

손상된 데이터베이스를 복구하기 위해 손상된 데이터베이스 파일을 복사하고 더미 데이터베이스를 생성한 다음 오프라인으로 설정한다.

CREATE DATABASE [Company] ON PRIMARY (

NAME = N'Company',

FILENAME = N'D:\SQLskills\Company.mdf')

LOG ON (

NAME = N'Company_log',

FILENAME = N'D:\SQLskills\Company_log.ldf');

GO

 

ALTER DATABASE [Company] SET OFFLINE;

GO

 

생성된 더미 데이터파일을 삭제하고 원래 손상된 데이터베이스 파일을 복사한다. 그리고 온라인으로 실행 한다.

ALTER DATABASE [Company] SET ONLINE;

GO

 

하지만 연결이 실패되고 작동하지 않는다.

Msg 5181, Level 16, State 5, Line 33

Could not restart database "Company". Reverting to the previous status.

Msg 5069, Level 16, State 1, Line 33

ALTER DATABASE statement failed.

Msg 824, Level 24, State 2, Line 33

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:9; actual 0:0). It occurred during a read of page (1:9) in database ID 6 at offset 0x00000000012000 in file 'D:\SQLskills\Company.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

 

현재 데이터베이스 상태는 서스펙트 상태로 나타난다.

SELECT DATABASEPROPERTYEX (N'Company', N'STATUS');

GO

 

데이터베이스 복구를 위해 응급모드로 변경을 시도하지만 실패 한다.

ALTER DATABASE [Company] SET EMERGENCY;

GO

ALTER DATABASE [Company] SET SINGLE_USER;

GO

 

Msg 824, Level 24, State 2, Line 43

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:9; actual 0:0). It occurred during a read of page (1:9) in database ID 6 at offset 0x00000000012000 in file 'D:\SQLskills\Company.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

 

손상된 부트페이지는 데이터베이스에 액세스 할 수 없기 때문에 작동하지 않는다. 손상된 부트페이지를 복원하기 위해 16진수 편집기를 사용해서 문제를 해결할 수 있다. 현재 생성되어 있는 데이터베이스를 제거하고 다시 손상된 데이터파일을 복사한다.

ALTER DATABASE [Company] SET OFFLINE;

GO

 

-- ***** Copy off the corrupt files

 

DROP DATABASE [Company];

GO

 

이전의 정상적인 백업 파일을 복원한 다음 헥사에디터에서 파일을 오픈할 수 있도록 오프라인으로 설정한다.

RESTORE DATABASE [Company] FROM

DISK = N'C:\SQLskills\OldCompany.bck'

WITH REPLACE;

GO

 

ALTER DATABASE [Company] SET OFFLINE;

GO

 

백업 파일에서 복원된 데이터베이스의 부트페이지로 이동한다.

 

복원된 사본에는 부팅페이지가 손상되지 않은 것을 볼 수 있다. 부트페이지 오프셋을 복사한다. 이 오프셋은 모든 데이터베이스에 동일하다.(12000 ~ 14000 까지 복사)

 

손상된 파일의 부팅 오프셋으로 이동하여 복사한 부팅 페이지를 덮어쓴다.

 

빨간색으로 복사된 것을 덮어쓴 것을 확인 할 수 있다. 저장하고 편집기를 종료한다

 

현재 복원된 파일을 삭제하고 손상된 파일의 이름을 바꾼다. 그리고 데이터베이스를 온라인 한다.

ALTER DATABASE [Company] SET ONLINE;

GO

 

복구된 부트페이지와 트랜잭션로그가 일치하지 않기 때문에 오류가 발생한다. 긴급모드로 변경하여 CHECKDB를 실행하여 복구 한다.

ALTER DATABASE [Company] SET EMERGENCY;

GO

ALTER DATABASE [Company] SET SINGLE_USER;

GO

DBCC CHECKDB (N'Company', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

GO

 

 

DBCC CHECKDB 작업이 완료되면 데이터베이스에 액세스가 가능하다.

 

[참고자료]

http://www.sqlskills.com/blogs/paul/disaster-recovery-101-fixing-a-broken-boot-page/

 

 

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

 

 

SQLServer, MSSQLSERVER, MSSQL, 데이터베이스 복구, DB복구,DB손상, DBA

Temp table 객체 생성시 세션간 충돌하지 않는 이유

 

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

 

데이터베이스를 사용할 때 temp table(임시 테이블)을 많이 사용한다. 단일 tempdb가 여러 세션에서 동일한 개체를 만드는 경우에도 어떻게 충돌을 방지할 수 있을까? 다음 간단한 테스트를 통해서 임시테이블 생성과 충돌 방지에 대해서 알아본다.

 

아래 스크립트는 임시 테이블을 생성한다.

-- Session 1: Table creation

CREATE TABLE #temptable_test (

id INT NOT NULL IDENTITY (1,1)

,Name CHAR(100) NOT NULL

,DOJ DATETIME NOT NULL

);

 

테이블을 만든 후에 메타데이터에서 tempdb 데이터베이스 내부에 생성된 객체를 확인한다.

-- SHOW USER TABLE

SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U'

 

 

생성된 임시 테이블은 부정적인 개체ID와 이름에 긴 밑줄과 함께 번호가 있는것으로 확인할 수 있다. SQL Server에서는 임시테이블에 번호를 부여하여 전체 인스턴스에 대한 단일 tempdb가 여러 세션에서 동일한 개체를 만드는 경우에도 충돌을 방지할 수 있다. 동일한 이름으로 두 번째 임시 테이블을 생성한다.

-- Session 2: Table creation

CREATE TABLE #temptable_test (

id INT NOT NULL IDENTITY (1,1)

,Name CHAR(100) NOT NULL

,DOJ DATETIME NOT NULL

);

 

 

메타데이터를 확인해 보면 다른 번호를 가지고 있는 객체가 생성된 것을 확인할 수 있다.

 

이렇게 생성된 임시테이블은 다른 세션에서 동일한 이름을 사용해도 충돌을 방지 할 수 있으며 세션이 종료될 때 자동으로 제거된다. 임시 테이블인 세션이 종료될 때 삭제되는 것을 제외하면 일반 테이블과 유사하다.

 

 

[참고자료]

http://blog.sqlauthority.com/2014/11/27/sql-server-inside-temp-table-object-creation/

 

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

 

SQL Server, TempDB, Temp table, 임시테이블, mssql, 데이터베이스, sys.sysobjects

SQL Server 데이터베이스 메일 계정 수정

 

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

 

SQL Server에서 제공하는 데이터베이스 메일 기능을 사용하면 SQL Server에서 발생하는 다양한 이벤트를 관리자에게 이메일로 발송할 수 있다.

 

SQL Server 데이터베이스 메일은 SMTP 서버 이름, 포트번호, 이메일 주소 등을 이용하여 메일을 발송한다. 이번 포스트는 생성된 데이터베이스 메일의 계정 및 기타 정보 수정방법에 대해서 알아본다. 데이터베이스 메일 정보 수정은 SSMS 및 T-SQL로 가능하다.

 

[SSMS로 데이터베이스 메일 정보 변경]

SSMS를 실행하여 개체 탐색기에서 [관리] - [데이터베이스 메일]에서 마우스 오른쪽을 클릭하여 [데이터베이스 메일 구성]을 클릭한다.

 

데이터베이스 메일 구성 마법사다 시작되면 다음을 클릭하여 진행 한다.

 

구성 태스크 선택에서 [데이터베이스 메일 계정 및 프로필 관리]를 선택한다.

 

프로필 및 계정관리에서 [기존 계정 확인, 변경 도는 삭제]를 선택한다.

 

기존 계정 관리 창이 나타나면 정보를 수정한다.

 

수정 요약 알림이 나타나면 [마침]을 클릭하면 즉시 반영된다.

 

정상적으로 반영되면 성공으로 표시된다. 오류가 발생하면 해당 오류를 수정하고 다시 실행한다.

 

 

[T-SQL]

데이터베이스 메일 정보는 MSDB에 저장되어 있다. 다음 스크립트는 현재 생성되어 있는 계정 목록 및 SMTP 정보를 확인한다.

SELECT

    [sysmail_server].[account_id]

    ,[sysmail_account].[name] AS [Account Name]

,[servertype]

,[servername] AS [SMTP Server Address]

,[Port]

FROM [msdb].[dbo].[sysmail_server]

    INNER JOIN [msdb].[dbo].[sysmail_account]

        ON [sysmail_server].[account_id]=[sysmail_account].[account_id]

 

 

현재 생성되어 있는 계정 중 Jevida@naver.com 계정의 정보를 수정한다.

EXECUTE msdb.dbo.sysmail_update_account_sp

@account_name = 'jevida@naver.com' -- 'MSSQL_Name_mail_account'

,@description = 'Mail account for administrative e-mail.'

,@mailserver_name = 'smtp.XXXX.com'

,@mailserver_type = 'SMTP'

,@port = 25

 

수정이 완료되고 계정 정보를 조회하면 수정된 내역이 반영되었음을 확인할 수 있다.

SELECT

    [sysmail_server].[account_id]

    ,[sysmail_account].[name] AS [Account Name]

,[servertype]

,[servername] AS [SMTP Server Address]

,[Port]

FROM [msdb].[dbo].[sysmail_server]

    INNER JOIN [msdb].[dbo].[sysmail_account]

        ON [sysmail_server].[account_id]=[sysmail_account].[account_id]

 

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3654/how-to-modify-sql-server-database-mail-accounts/

 

2015-06-18 / 강성욱 / http://sqlmvp.kr

 

 

MSSQL, SQL Server, DBA, SQL Mail, 데이터베이스 메일, sysmail_update_account_sp

XEvent(확장이벤트)를 활용한 활성 로그 모니터링 하기

 

  • Version : SQL Server 2012, 2014

 

XEvent의 file_wirte_complete, transaction_log 이벤트를 사용하여 트랜잭션이 커밋될 때 트랜잭션 로그 파일에 기록되는 것을 모니터링할 수 있다.

 

실습을 위해 테스트 테이블을 생성한다.

CREATE TABLE TestTable (

c1 INT IDENTITY,

c2 CHAR (1000) DEFAULT 'a');

GO

 

INSERT INTO [TestTable] DEFAULT VALUES;

GO

 

XEvent를 설정한다.

-- Drop the session if it exists.

IF EXISTS (

SELECT * FROM sys.server_event_sessions

WHERE [name] = N'MonitorLog')

DROP EVENT SESSION [MonitorLog] ON SERVER

GO

 

-- Create the event session

CREATE EVENT SESSION [MonitorLog] ON SERVER

ADD EVENT [sqlserver].[file_write_completed],

ADD EVENT [sqlserver].[transaction_log]

ADD TARGET [package0].[ring_buffer]

WITH (MAX_MEMORY = 50MB, max_dispatch_latency = 1 seconds)

GO

 

-- Start the session

ALTER EVENT SESSION [MonitorLog] ON SERVER

STATE = START;

GO

 

 

SSMS에서 XEvent의 라이브데이터 감시를 UI를 통해서 확인할 수 있다.

 

 

라이브데이터 감시가 시작되면 상단의 컬럼탭에서 마우스 오른쪽을 클릭하여 [열 선택]을 클릭한다.

 

 

[열 선택] 창이 나타나면 필요한 열을 추가하여 모니터링에 표시할 수 있도록 한다.

 

데이터를 입력하면 트랜잭션 로그가 커밋 될 때 트랜잭션로그파일에 기록되는 것을 확인할 수 있다.

 

 

[참고자료]

http://www.sqlskills.com/blogs/paul/t-sql-tuesday-67-monitoring-log-activity-with-extended-events/

 

 

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

 

SQL Server, MSSQL, DB, 확장이벤트, XEvent, Extended Event, 트랜잭션로그, 로그 모니터링, DB모니터링, 트랜잭션 커밋, 활성 로그

특정 사용자에 대한 트랜잭션 로그 찾기

 

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

 

SQL Server에서 일어나는 모든 일은 트랜잭션 로그에 기록 된다. 이전에도 트랜잭션로그를 이용하여 삭제된 데이터를 복구하거나 트랜잭션 발생 시간 및 사용자 찾는 방법에 대해서 다룬적이 있다.

 

 

이번 포스트는 특정 사용자에 대한 트랜잭션 로그를 찾는 법에 대해서 알아본다. 사용자 정보는 직접 로그를 검색하는데 사용할 수 없지만 모든 LOP_BEGI_XACT 로그 레코드는 트랜잭션을 실행하는 사람들의 SID를 포함한다. SID는 SUSER_SID 함수를 통해서 확인할 수 있다.

SELECT SUSER_SID ('KSW_2012_2\Administrator') AS [SID];

GO

 

 

SID를 이용하여 fn_dblog() 또는 fn_dump_dblog에서 사용자를 필터할 수 있다.

SELECT

[Current LSN],

[Operation],

[Transaction ID],

[Begin Time],

LEFT ([Description], 40) AS [Description]

FROM

fn_dblog (NULL, NULL)

WHERE

[Transaction SID] = SUSER_SID ('KSW_2012_2\Administrator');

GO

 

 

하루에도 수 많은 트랜잭션로그가 발생하기 때문에 시간 범위를 좁혀서 특정 사용자의 트랜잭션 로그를 확인한다.

SELECT

[Current LSN],

[Operation],

[Transaction ID],

[Begin Time],

LEFT ([Description], 40) AS [Description]

FROM

fn_dblog (NULL, NULL)

WHERE

[Transaction SID] = SUSER_SID ('KSW_2012_2\Administrator')

AND ([Begin Time] > '2015/06/09 08:55:00:877' AND [Begin Time] < '2015/06/09 08:55:01:003');

GO

 

 

 

범위가 좁혀진 트랜잭션 로그에서 Trasnaction ID 정보를 이용하여 트랜잭션 로그에서 삭제된 행에 대한 정보를 얻고 데이터를 복원할 수 있다.

 

 

[참고자료]

http://www.sqlskills.com/blogs/paul/finding-a-transaction-in-the-log-for-a-particular-user/

 

 

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

 

 

SQL Server, mssql, 트랜잭션로그, 특정 사용자 트랜잭션로그 찾기, 특정 사용자 로그 보기, 사용자 로그 분석, SQL 로그분석, fn_dblog, fn_dump_dblog, DBA

SQL Server I/O 서브시스템 레이턴시 확인

 

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

 

데이터베이스 성능에 큰 영향을 주는 부분이 디스크이다. 보통 데이터베이스 시스템의 부하가 증가하면 I/O 서브시스템의 작업량이 증가한다. 만약 I/O 대기 시간이 증가되었음을 발견되었다면 I/O 서브시스템은 이전의 SQL 서버 동작에서 변화가 발생한 것이다. 예를 들면 인덱스가 테이블 스캔을 실행할 정도로 통계가 오래된 경우, 코드 변경, 암시적 변환, 쿼리 계획 변경, 인덱스 추가, 인덱스 유지 관리 작업, 액세스 패턴, 페이지 분할, 데이터 캡처, 변경 내용 추적, 임시테이블 사용, 버퍼풀 부족으로 인한 지연 기록기 증가 또는 호스트의 다른 작업에 의한 증가 등 변화의 이유는 다양하다.

 

성능 카운터를 사용하여 I/O 시스템에 대해 모니터링 할 경우 디스크 수준에서 정보를 확인할 수 있다. 하지만 성능카운터에서는 SQL Server와 관련된 파일이 어느 LUN에 있으며 어디에서 지연이 발생하는지 확인할 수 없다. 이 경우 sys.dm_io_virtual_file_stats을 이용하여 SQL Server에서 사용하는 I/O 정보를 확인 할 수 있다.

 

Sys.dm_io_virtual_file_stats를 사용한 디스크 병목에 관한 내용은 이전에도 다루었다.

  • 성능분석 8탄 – IO 통계 (DISK 활동 분석)

http://sqlmvp.kr/140208808812

  • DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기

http://sqlmvp.kr/140178245107

  • DISK I/O 병목 확인

http://sqlmvp.kr/140192055664

 

아래 스크립트는 sys.dm_io_virtual_file_stats와 sys.master_files를 조인하여 현재 파일이 위치한 경로와 사용중인 데이터베이스 목록을 레이턴시 정보화 함께 나타낸다. 주의할 점은 결과로 나타나는 정보는 데이터베이스가 온라인 상태 이후의 평균 시간이다. 정확한 분석을 위해서는 짧은 시간에 여러 번 수행하여 평균을 조하사는 것이다.

SELECT

[ReadLatency] =

CASE WHEN [num_of_reads] = 0

THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,

[WriteLatency] =

CASE WHEN [num_of_writes] = 0

THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,

[Latency] =

CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)

THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,

[AvgBPerRead] =

CASE WHEN [num_of_reads] = 0

THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,

[AvgBPerWrite] =

CASE WHEN [num_of_writes] = 0

THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,

[AvgBPerTransfer] =

CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)

THEN 0 ELSE

(([num_of_bytes_read] + [num_of_bytes_written]) /

([num_of_reads] + [num_of_writes])) END,

LEFT ([mf].[physical_name], 2) AS [Drive],

DB_NAME ([vfs].[database_id]) AS [DB],

[mf].[physical_name]

FROM

sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]

JOIN sys.master_files AS [mf]

ON [vfs].[database_id] = [mf].[database_id]

AND [vfs].[file_id] = [mf].[file_id]

-- WHERE [vfs].[file_id] = 2 -- log files

-- ORDER BY [Latency] DESC

-- ORDER BY [ReadLatency] DESC

ORDER BY [WriteLatency] DESC;

GO

 

 

오랜 시간동안 수집되어 나타낸 통계임을 고려할 때 일부 쿼리(배치 작업 등)에 의해 평균값이 부풀려 질 수 있다는 점에 유의한다. 그래서 주기적으로 스냅샷으로 저장하여 변화를 확인 할 수 있도록 한다.

 

[참고자료]

  • How to examine IO subsystem latencies from within SQL Server

http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/

  • sys.dm_io_virtual_file_stats

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

 

 

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

 

 

SQL Server, MSSQL, DISK IO, sys.dm_io_virtual_file_stats, DB튜닝, 디스크 병목, DB 성능 정보, DB 모니터링

실행계획의 물리 및 논리연산자 설명

 

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

 

쿼리 최적화 프로그램은 쿼리 계획을 논리 연산자로 이루어진 트리로 만든다. 쿼리 최적화 프로그램은 쿼리 계획을 만든 다음 각 논리 연산자에 대해 가장 효율적인 물리연산자를 선택한다. 쿼리 최적화 프로그램은 비용 기반을 둔 방법을 사용하여 논리 연산자를 구현할 물리 연산자를 결정한다. 일반적으로 여러 물리 연산자가 하나의 논리 연산자를 구현할 수 있다. 그러나 간혹 하나의 물리 연산자가 여러 논리 연산자를 구현하는 경우도 있다.

 

아래 표는 논리 및 물리 연산자에 대한 실행계획 아이콘과 설명이다. 쿼리 실행계획을 분석하는데 참고 할 수 있도록 한다.

 

 

[참고자료]

https://technet.microsoft.com/ko-kr/library/ms191158.aspx

 

 

2015-05-26 / 강성욱 / http://sqlmvp.kr

 

 

SQL Server, MSSQL, SQL 실행계획, SQL 물리연산자, SQL 논리 연산자, 실행계획 아이콘, 그래픽 실행계획, sql optimizer,

백업 압축과 추적플래그 3042

 

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

 

SQL Server의 백업 압축은 SQL Server 2008부터 지원되며 압축된 백업은 동일한 데이터의 압축되지 않는 백업보다 작으므로 일반적으로 I/O에 대한 비용이 절약되며 백업속도가 크게 향상된다. 하지만 압축을 하기 위한 추가 연산으로 약간의 CPU 오버헤드가 발생한다. 하지만 시스템 중에 가장 느린 부분은 디스크 이므로 CPU의 오버헤드를 감안하여도 충분히 이점이 있는 작업이다.

 

압축된 백업에 대한 최종 백업 파일의 크기는 데이터의 압축 가능한 정도에 따라 달라진다. 백업작업이 완료되기 전까지는 크기를 알 수 없다. 따라서 기본적으로 압축을 사용하여 데이터베이스를 백업 할 때 데이터베이스 엔진은 백업 파일에 대한 사전 할당 알고리즘을 사용한다.

 

사전 할당 알고리즘은 백업 파일의 데이터베이스 크기에 대해 미리 정의된 백분율이 사전 할당 된다. 백업하는 동안 더 많은 공간이 필요한 경우 데이터베이스 엔진은 파일을 계속해서 늘려간다. 백업 작업의 마지막에 최종 크기가 할당된 공간보다 작으면 데이터베이스 엔진이 파일을 백업의 실제 최종 크기로 축소한다.

 

추적플래그 3042는 백업 파일을 최종 크기에 도달하는데 필요한 만큼만 늘리도록 허용한다. 이 추적 플래그를 사용하면 백업 작업에 기본 백업 압축 사전 할당 알고리즘을 무시하여 압축된 백업에 실제로 필요한 크기만 할당하여 공간에 저장해야 하는 경우 유용하다. 이 추적 플래그를 활성화 하면 약간의 성능저하가 발생 하여 백업 작업 시간이 늘어날 수 있다.

 

DBCC TRACEON (3042,-1);

GO

BACKUP DATABASE SW_TEST to DISK = N'D:\SQL_Backup\SW_TEST.bak' WITH COMPRESSION

GO

DBCC TRACEOFF (3042,-1);

GO

 

 

[참고자료]

  • Testing SQL Server Backup Performance with Trace Flag 3042 :

http://www.mssqltips.com/sqlservertip/3626/testing-sql-server-backup-performance-with-trace-flag-3042/

 

 

 

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

 

 

 

SQL Server, MSSQL, 백업압축, SQL 백업, Trace Flag 3042, 추적플래그3042, 백업 사전 할당, 백업 파일, 백업 크기, DB백업

SOS_SCHEDURLER_YIELD 대기와 쿼리 식별

 

  • Version : SQL Server 2008, 2008R2, 2012

 

SOS_SCHEDULER_YIELD 대기 유형의 문제점 중 하나는 실제로 대기 유형이 아니다는 것이다. 이 대기 유형의 발생은 4ms 스케줄링 퀀텀에 따른 자발적인 CPU 양보로 인해 실행중인 스레드를 반환하고 실행 가능한 큐의 맨 아래로 이동하기 때문이다. 그리고 SOS_SCHEDULER_YIELD를 사용하여 대기 프로세스에 등록된다.

 

대기 유형에 대한 자세한 내용은 다음 포스트를 참고 한다.

  • SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock :

http://www.sqlskills.com/blogs/paul/sos_scheduler_yield-waits-and-the-lock_hash-spinlock/

  • Knee-Jerk Wait Statistics : SOS_SCHEDULER_YIELD :

http://sqlperformance.com/2014/02/sql-performance/knee-jerk-waits-sos-scheduler-yield

 

SOS_SCHEDULER_YIELD 대기는 실제 대기 유형이 아니기 때문에 sys.dm_owS_waiting_tasks DMV에서 확인 할 수 없다. 따라서 sys.dm_exec_requests를 사용하여 실행중인 쿼리에 대해 last_wait_type가 SOS_SCHEDULER_YIELD인 상태의 쿼리와 실행계획을 확인할 수 있다.

SELECT

[er].[session_id],

[es].[program_name],

[est].text,

[er].[database_id],

[eqp].[query_plan],

[er].[cpu_time]

FROM sys.dm_exec_requests [er]

INNER JOIN sys.dm_exec_sessions [es] ON

[es].[session_id] = [er].[session_id]

OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]

OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]

WHERE

[es].[is_user_process] = 1

AND [er].[last_Wait_type] = N'SOS_SCHEDULER_YIELD'

ORDER BY

[er].[session_id];

GO

 

 

 

[참고자료]

http://www.sqlskills.com/blogs/paul/identifying-queries-with-sos_scheduler_yield-waits/?utm_source=rss&utm_medium=rss&utm_campaign=identifying-queries-with-sos_scheduler_yield-waits

 

 

2015-05-08 / 강성욱 / http://sqlmvp.kr

 

 

SQL Server, MSSQL, DMV, last_wait_type, SOS_SCHEDULER_YIELD, 쿼리 튜닝, 대기유형, DB튜닝, SQL튜닝, SQL분석

랜덤 캐릭터 생성하기

 

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

 

무작위로 패스워드를 생성해야 하거나 쿠폰번호 등을 생성해야 할 때 일정한 범위내에서 랜덤한 문자열을 생성하는 코드를 만들어 본다.

 

랜덤한 문자열을 만들기 위해서 이전에 다루었던 RAND BETWEEN 함수를 사용하여 일정한 범위 내에서 난수를 발생 시킬 수 있도록 한다.

 

RAND BETWEEN 생성

create view vRandomNumber

as

    select rand() as RandomNumber

go

 

create function randbetween(@bottom int, @top int)

returns int

as

begin

return (select cast(round((@top-@bottom)* RandomNumber + @bottom,0) as integer) from vRandomNumber)

end

go

 

RANDBWTWEEN을 활용한 랜덤 캐릭터 생성

CREATE FUNCTION dbo.GeneratePassword ()

RETURNS varchar(10)

AS

BEGIN

DECLARE @randInt int;

DECLARE @NewCharacter varchar(1);

DECLARE @NewPassword varchar(10);

SET @NewPassword='';

 

--6 random characters

WHILE (LEN(@NewPassword) <6)

BEGIN

select @randInt=dbo.randbetween(48,122)

    -- 0-9 < = > ? @ A-Z [ \ ] a-z

IF @randInt<=57 OR (@randInt>=60 AND @randInt<=93) OR (@randInt>=97 AND @randInt<=122)

Begin

select @NewCharacter=CHAR(@randInt)

select @NewPassword=CONCAT(@NewPassword, @NewCharacter)

END

END

 

--Ensure a lowercase

select @NewCharacter=CHAR(dbo.randbetween(97,122))

select @NewPassword=CONCAT(@NewPassword, @NewCharacter)

 

--Ensure an upper case

select @NewCharacter=CHAR(dbo.randbetween(65,90))

select @NewPassword=CONCAT(@NewPassword, @NewCharacter)

 

--Ensure a number

select @NewCharacter=CHAR(dbo.randbetween(48,57))

select @NewPassword=CONCAT(@NewPassword, @NewCharacter)

 

--Ensure a symbol

WHILE (LEN(@NewPassword) <10)

BEGIN

select @randInt=dbo.randbetween(33,64)

    -- ! # $ % & < = > ? @

IF @randInt=33 OR (@randInt>=35 AND @randInt<=38) OR (@randInt>=60 AND @randInt<=64)

Begin

select @NewCharacter=CHAR(@randInt)

select @NewPassword=CONCAT(@NewPassword, @NewCharacter)

END

END

 

RETURN(@NewPassword);

END;

GO

 

랜덤하게 생성되는 캐릭터를 확인한다.

SELECT dbo.GeneratePassword() AS 'NewPassword';

SELECT dbo.GeneratePassword() AS 'NewPassword';

SELECT dbo.GeneratePassword() AS 'NewPassword';

 

 

 

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

 

 

SQL Server, MSSQL, RANDBETWEEN,랜덤함수, 난수 발생, 무작위 문자열 생성, 랜덤캐릭터생성, 암호생성

트랜잭션 로그 백업을 읽고 트랜잭션 발생 시간 및 사용자 찾기

 

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

 

데이터베이스에서 발생하는 행위는 모두 트랜잭션 로그에 기록된다. 트랜잭션 로그를 읽는 방법과 트랜잭션 로그에서 삭제된 데이터 복구, 삭제한 사용자 찾기에 대한 내용은 이전 아티클을 참고 한다.

 

누군가 일부 데이터를 삭제 하였을 경우 fn_dblog()를 실행하여 활성 트랜잭션로그파일에서 정보를 확인 할 수 있었다. 하지만 로그가 플러쉬 된 경우에는 아무런 정보를 확인 할 수 없다. 이때 세부 정보를 확인 하기 위해 문서화되지 않은 fn_dump_dblog()를 사용하여 트랜잭션백업파일에서 삭제된 시간 및 사용자를 확인할 수 있다.

 

이 시나리오는 일반적인 데이터베이스 운영환경 (전체 백업 및 트랜잭션 로그 백이 진행되고 있는상황)을 가장하고 진행 하였다.

 

테스트를 위해 샘플 데이터를 생성 한다. 20건의 데이터를 생성한다.

create table ReadingDBLog(

Num int,

RegDate datetime,

Name nvarchar(50)

)

go

 

insert into ReadingDBLog values (1, getdate(), 'KSW')

insert into ReadingDBLog values (2, getdate(), 'KSW')

insert into ReadingDBLog values (3, getdate(), 'KSW')

insert into ReadingDBLog values (4, getdate(), 'KSW')

insert into ReadingDBLog values (5, getdate(), 'KSW')

insert into ReadingDBLog values (6, getdate(), 'KSW')

insert into ReadingDBLog values (7, getdate(), 'KSW')

insert into ReadingDBLog values (8, getdate(), 'KSW')

insert into ReadingDBLog values (9, getdate(), 'KSW')

insert into ReadingDBLog values (10, getdate(), 'KSW')

insert into ReadingDBLog values (11, getdate(), 'KSW')

insert into ReadingDBLog values (12, getdate(), 'KSW')

insert into ReadingDBLog values (13, getdate(), 'KSW')

insert into ReadingDBLog values (14, getdate(), 'KSW')

insert into ReadingDBLog values (15, getdate(), 'KSW')

insert into ReadingDBLog values (16, getdate(), 'KSW')

insert into ReadingDBLog values (17, getdate(), 'KSW')

insert into ReadingDBLog values (18, getdate(), 'KSW')

insert into ReadingDBLog values (19, getdate(), 'KSW')

insert into ReadingDBLog values (20, getdate(), 'KSW')

go

 

아래 스크립트를 사용하여 < 10 행을 삭제 한다.

DELETE ReadingDBLog WHERE Num < 10

go

 

select * from ReadingDBLog

 

 

현재 활성 로그를 플러시 하기 위해 트랜잭션 로그 백업 또는 전체 백업을 진행 한다.

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

BACKUP LOG SW_TEST TO DISK ='C:\SQL_Backup\SW_TEST_201504230945.trn'

 

백업이 완료되고 기존 fn_dblog()를 사용하여 활성 트랜잭션 로그 정보를 보면 아무것도 나타나지 않는다.

SELECT

    [Current LSN],

    [Transaction ID],

    [Transaction Name],

    Operation,

    [Begin Time]

FROM fn_dblog(NULL, NULL)

WHERE [Operation] = 'LOP_DELETE_ROWS'

 

 

이 시점에서 문서화 되지 않은 fn_dbump_dblog를 사용해서 트랜잭션로그백업 정보를 읽을 수 있다. 이 함수는 많은 파라메터를 요구하지만 백업 파일 이름과 위치만 입력하고 나머지는 DEFAULT 값을 입력 한다. 이 코드를 실행 하면 해당 트랜잭션백업 파일의 모든 행의 목록을 얻을 수 있다. 다른 트랜잭션로그 백업의 정보를 확인 할 때에는 참조하는 파일의 이름을 변경하면 된다.

SELECT

    [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time], [PartitionID], [TRANSACTION SID]

FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'c:\SQL_Backup\SW_TEST_201504230945.trn',

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

WHERE Operation = 'LOP_DELETE_ROWS'

GO

 

 

아래 스크립트는 위 단계에서 찾은 PartitionID와 Transaction ID를 활용하여 LOP_BEGIN_XACT을 찾는다. 트랜잭션이 시작되었을 때의 정보를 확인 할 수 있지만 어느 테이블에 작업이 진행되었는지는 나타나지 않는다.

SELECT

    [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time], SUSER_SNAME([TRANSACTION SID]) as [LoginName]

FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'c:\SQL_Backup\SW_TEST_201504230945.trn',

    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

WHERE [Transaction ID] = '0000:0000031e'

    AND Operation = 'LOP_BEGIN_XACT'

GO

 

 

다음 스크립트는 fn_dump_dblog()에서 찾은 파티션 ID값을 사용하여 실제 어느 테이블에서 삭제 작업이 진행 되었는지, 누가 삭제 하였는지에 대한 정보를 확인 할 수 있다.

SELECT so.*

FROM sys.objects so

INNER JOIN sys.partitions sp on so.object_id = sp.object_id

WHERE partition_id = 72057594039042048

 

 

 

다음 스크립트는 위에서 찾은 name 정보를 활용하여 총 몇건의 데이터가 삭제되었는지 한번에 확인 하는 스크립트이다. 사용자에 따라 where name = '??' 구문을 수정하여 사용한다.

WITH CTE

as

(SELECT [Transaction ID], count(*) as DeletedRows

FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'c:\SQL_Backup\SW_TEST_201504230945.trn',

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

WHERE Operation = ('LOP_DELETE_ROWS')

AND [PartitionId] in (SELECT sp.partition_id

FROM sys.objects so

INNER JOIN sys.partitions sp on so.object_id = sp.object_id

WHERE name = 'queue_messages_1067150847')

GROUP BY [Transaction ID]

)

SELECT [Current LSN], a.[Transaction ID], [Transaction Name], [Operation], [Begin Time], SUSER_SNAME([TRANSACTION SID]) as LoginName, DeletedRows

FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'c:\SQL_Backup\SW_TEST_201504230945.trn',

    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) as a

INNER JOIN cte on a.[Transaction ID] = cte.[Transaction ID]

WHERE Operation = ('LOP_BEGIN_XACT')

 

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3555/read-sql-server-transaction-log-backups-to-find-when-transactions-occurred/

 

 

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

 

 

SQLSERVER, mssql, SQL튜닝, 강성욱, SQL강좌, DB복원, 데이터복구, 트랜잭션로그, LSN, STOPBEFORREMARK, fn_dump_dblog(), fn_dblog()

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

 

 

TDE 암호화된 데이터베이스 복원

 

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

 

SQL Server 2008부터 도입된 암호화 솔루션인 TDE는 전체 데이터베이스를 암호화 하고 암호화된 데이터베이스에 액세스하는 응용프로그램에 완전히 투명하게 하도록 하는 기능을 제공한다.

 

TDE는 고급 암호화 표준(AES) 또는 트리플 DES(3DES) 암호화를 사용하여 데이터베이스 파일(MDF)과 로그파일(LDF) 모두에 저장된 데이터를 암호화 한다. 이 암호화 에서는 DEK(데이터베이스 암호화 키)를 사용하며 이 키는 복구하는 동안 사용할 수 있도록 데이터베이스 부트 레코드에 저장된다. DEK는 서버의 master 데이터베이스에 저장된 인증서 또는 EKM 모듈로 보호되는 비대칭 키를 사용하여 보호되는 대칭 키 이다.

 

데이터베이스 파일 암호화는 페이지 수준에서 수행된다. 암호화된 데이터베이스 페이지는 암호화 된 후 디스크에 작성되고 메모리로 읽어 들일 때 암호화 해독 된다. TDE로 암호화된 데이터베이스의 크기가 증가되지 않는다. 암호화 및 암호화 해독 작업은 SQL Server에 의해 백그라운드 스레드로 예약된다.

 

TDE는 데이터베이스 백업도 암호화 된다. 이것은 백업 미디어를 도난 당한 경우 중요한 정보 손실에 대한 보호기능을 제공한다.

 

 

이번 포스트에서는 TDE로 암호화된 데이터베이스를 다양한 시나리오를 통해서 복구방법에 대해서 알아본다.

 

아래 스크립트는 실습에서 사용할 TDE를 사용한 데이터베이스를 생성한다. 인증서를 생성하고 인증서 백업을 진행 한다.

USE [master];

GO

 

-- Create the database master key

-- to encrypt the certificate

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'FirstServerPassw0rd!';

GO

 

-- Create the certificate we're going to use for TDE

CREATE CERTIFICATE TDECert

WITH SUBJECT = 'TDE Cert for Test';

GO

 

-- Back up the certificate and its private key

-- Remember the password!

BACKUP CERTIFICATE TDECert

TO FILE = N'C:\SQLBackups\TDECert.cer'

WITH PRIVATE KEY (

FILE = N'C:\SQLBackups\TDECert_key.pvk',

ENCRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'

);

GO

 

-- Create our test database

CREATE DATABASE [RecoveryWithTDE];

GO

 

-- Create the DEK so we can turn on encryption

USE [RecoveryWithTDE];

GO

 

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDECert;

GO

 

-- Exit out of the database. If we have an active

-- connection, encryption won't complete.

USE [master];

GO

 

-- Turn on TDE

ALTER DATABASE [RecoveryWithTDE]

SET ENCRYPTION ON;

GO

 

 

 

아래 스크립트는 암호화 되어있는 데이터베이스를 확인 한다. Encryption_state 값이 3인 경우는 암호화된 상태를 의미한다.

SELECT DB_Name(database_id) AS 'Database', encryption_state

FROM sys.dm_database_encryption_keys;

 

 

 

TED로 암호화된 데이터베이스를 백업 한다.

BACKUP DATABASE [RecoveryWithTDE]

TO DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak';

GO

 

 

 

  1. 인증서가 없는 새로운 인스턴스에서 TED로 암호회된 데이터베이스 복원

인증서가 없는 곳에서 TDE가 적용된 데이터베이스 백업을 복원한 내용이다. 인증서를 찾을 수 없다는 메시지와 함께 오류가 발생한다.

-- Attempt the restore without the certificate installed

RESTORE DATABASE [RecoveryWithTDE]

FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak'

WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf',

MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf';

GO

 

 

 

  1. 인증서가 없는 새로운 인스턴스에서 인증서를 생성하여 데이터베이스 복원

새로운 인스턴스에서 동일한 이름의 인증서를 생성한다. 마스터키를 생성할 때 기존의 마스터키의 비밀번호와 다르게 생성 하였다. 복원 시도를 하였을 때 인증서를 찾을 수 없다는 메시지와 함께 오류가 발생한다.

-- Let's create the database master key and a certificate with the same name

-- But not from the files. Note the difference in passwords

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'SecondServerPassw0rd!';

GO

 

-- Though this certificate has the same name, the restore won't work

CREATE CERTIFICATE TDECert

WITH SUBJECT = 'TDE Cert for Test';

GO

 

-- Since we don't have the corrected certificate, this will fail, too.

RESTORE DATABASE [RecoveryWithTDE]

FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak'

WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf',

MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf';

GO

 

 

 

  1. 복사한 인증서에 권한상속을 부여하여 인증서 복원 후 데이터베이스 복원

인증서를 새로운 인스턴스로 복사하여 인증서에 대한 권한 상속을 수정하여 인증서 복원 작업을 진행하였다. 인증서 파일이 손상되었다는 메시지와 함께 오류가 발생한다.

 

 

 

 

-- Let's drop the certificate and do the restore of it...

-- But without the private key

DROP CERTIFICATE TDECert;

GO

 

-- Restoring the certificate, but without the private key.

CREATE CERTIFICATE TDECert

FROM FILE = 'C:\SQLBackups\TDECert.cer'

GO

 

-- We have the correct certificate, but not the private key.

-- This should fail as well.

RESTORE DATABASE [RecoveryWithTDE]

FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak'

WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf',

MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf';

GO

 

 

  1. 마스터키를 사용하여 인증서 복원 후 데이터베이스 복원

마스터키를 사용하여 데이터베이스 암호화에 사용된 인증서를 복원하여 데이터베이스를 정상적으로 복원 하였다.

-- Let's do this one more time. This time, with everything,

-- Including the private key.

DROP CERTIFICATE TDECert;

GO

 

-- Restoring the certificate, but without the private key.

CREATE CERTIFICATE TDECert

FROM FILE = 'C:\SQLBackups\TDECert.cer'

WITH PRIVATE KEY (

FILE = N'C:\SQLBackups\TDECert_key.pvk',

DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'

);

GO

 

-- We have the correct certificate and we've also restored the

-- private key. Now everything should work. Finally!

RESTORE DATABASE [RecoveryWithTDE]

FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak'

WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf',

MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf';

GO

 

 

 

TDE로 암호화된 데이터베이스를 복원하기 위해서는 인증서가 반드시 필요하며 인증서는 항상 백업해서 잘 관리할 수 있도록 해야 한다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3572/recovering-a-sql-server-tde-encrypted-database-successfully/

 

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

 

Sqlserver, mssql, DB보안, DB암호화, SQL 암호화, TDE, DB Encryption, SQL암호화, DB 복원, 인증서 복원, 인증서 백업, MASTER KEY 백업

 

비관리자 계정에 Profiler 실행 권한 부여하기

 

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

 

SQL Server Profiler는 추적을 작성 및 관리하고 추적 결과를 분석하거나 특정 단계를 재생 할 수 있다.

 

 

SQL Server Profiler를 사용하기 위해서는 ALTER TRACE 권한이 필요하다. 권한이 없을 때에는 다음과 같은 오류가 발생 한다.

 

실습을 통해서 ALTER TRACE 권한을 부여하는 방법에 대해서 알아본다. 우선 권한이 없는 일반 사용자 계정을 생성한다.

USE Master;

GO

CREATE LOGIN sqlmvp

WITH PASSWORD = 'sql!mvp@1';

GO

 

USE sw_test

GO

CREATE USER sqlmvp FOR LOGIN sqlmvp

GO

 

exec sp_addrolemember 'db_owner', 'sqlmvp';

GO

 

권한을 확인 하기 위해 sp_helplogins 프로시저를 실행하여 로그인 정보를 확인 할 수 있다. sqlmvp라는 계정이 sw_test 데이터베이스에 대해서만 db_owner 권한이 있으며 프로파일러를 실행하려면 ALTER TRACE에 사용자 GRANT권한을 부여해야 한다.

sp_helplogins sqlmvp

 

 

 

SSMS에서 ALTER TRACE 권한을 부여하는 방법에 대해서 알아본다. 개체탐색기에서 [로그인]에서 권한을 부여할 로그인 계정을 선택한 후 마우스 오른쪽을 클릭하여 [속성]을 실행 한다.

 

 

로그인 속성창이 나타타면 [보안개체]에서 검색을 클릭하여 개체 추가를 한다.

 

 

개체 추가가 완료되면 사용권한을 부여하는데 SQL Server Profiler를 실행 할 수 있도록 [추적 변경](ALTER TRACE)을 선택하고 확인을 클릭한다

 

 

권한 부여가 완료되었으면 Profiler를 실행하여 정상적으로 프로파일러가 실행되는지 확인 한다.

 

 

스크립트로 ALTER TRACE 권한을 추가하는 방법에 대해서 알아 본다. 아래 스크립트는 권한을 부여하려는 계정의 로그인 인증 방법에 따라 다르게 실행해야 한다.

-- To Grant access to a Windows Login

USE Master;

GO

GRANT ALTER TRACE TO [KSW_2012_2\WindowsLogin]

GO

 

-- To Grant access to a SQL Login

USE master;

GO

GRANT ALTER TRACE TO sqlmvp

GO

 

부여된 ALTER TRACE 권한을 삭제 하는 방법에 대해서 알아본다. 로그인 인증 방식에 따라 다르게 실행 해야 한다.

--REVOKE access FROM a SQL Login

USE Master;

GO

REVOKE ALTER TRACE FROM sqlmvp;

GO

 

-- REVOKE access FROM a Windows Login

USE master;

GO

REVOKE ALTER TRACE FROM [KSW_2012_2\WindowsLogin]

GO

 

 

이처럼 비관리자 계정에 대해서만 Profiler 실행 권한을 부여 할 수 있어 접근제어에 따라 적절하게 사용할 수 있다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3559/how-to-grant-permissions-to-run-sql-server-profiler-for-a-non-system-admin-user/

 

 

SQL Server, MSSQL, SQL Profiler, 프로파일러, 데이터베이스, ALTER TRACE, 추적파일, SQL추적, DB튜닝, Permission, create login, sql2012, sql2014, sql2008, sql

SQL Server Agent 공유 일정 생성하기

 

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

 

SQL Server Agent에서 Job을 등록하고 Job이 실행될 일정을 등록한다. 이때 여러 job에서 같은 시간에 반복되는 일정이 있더라도 매번 일정을 등록해 주어야 한다. 공유된 일정을 만들어서 해당 일정을 여러 Job에서 가져다 쓸 수 있으면 얼마나 편리할까? 이번 포스트는 SQL Server Agent의 공유 일정 사용법에 대해서 알아 본다.

 

SSMS에서 [SQL Server Agent] – [Job]에서 마우스 오른쪽을 클릭하여 [일정 관리]를 선택 한다.

 

일정 관리를 클릭하면 현재 등록된 모든 일정에 대해서 확인 할 수 있다. 사용자가 등록한 일정 외에 SQL Server에서 사용하는 일정도 확인이 가능하다.

 

이 일정내의 작업의 숫자는 해당 일정을 사용하고 있는 Job의 개수를 의미한다. 해당 숫자를 클릭하면 일정을 사용하는 Job의 목록을 확인 할 수 있다.

 

 

공유된 일정을 만드는 방법은 [일정 관리] – [새로 만들기]를 클릭해서 만들 수 있다.

 

공유된 일정을 사용하는 방법은 Job을 생성할 때 일정 등록에서 [선택] 버튼을 이용해서 이미 생성된 일정을 가져다 쓸 수 있다.

 

 

이과 같이 공유된 일정을 사용하면 여러 Job에서 동일한 일정을 사용할 수 있으며 일정 변경이 발생할 경우 해당 공유 일정을 사용하는 Job의 일정을 일괄적으로 변경 할 수 있다.

 

주의할 점은 공유된 일정을 사용하는 Job에서 일정을 변경 할 경우 공유된 일정 또한 변경된다는 점이다. 만약 다른 사용자가 공유된 일정을 사용하는지 인지하지 못하는 상태에서 일정을 변경하게 되면 동일한 공유 일정을 사용하는 모든 Job의 일정이 변경됨으로 주의해야 한다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3554/understanding-how-sql-server-agent-jobs-can-share-schedules/

 

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

 

 

SQL Server, mssql, SQL Agent, 일정등록, SQL Job Agent, 공유일정 등록, SQL Job, DBA, share schedules, SSMS

 

 

인덱스 유지관리 작업과 SQL Server 쿼리 성능

 

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

 

SQL Server 데이터베이스 엔진에서는 기본 데이터에 삽입, 업데이트 또는 삭제 작업을 수행할 때마다 인덱스를 자동으로 유지 관리한다. 이러한 수정이 거듭되면 시간이 흐름에 따라 인덱스의 정보가 조각화되어 데이터베이스 내에 흩어지게 될 수 있다. 조각화는 키 값을 기준으로 하는 인덱스의 논리적 페이지 순서가 데이터 파일 내의 물리적 순서와 일치하지 않을 때 나타난다. 심하게 조각화된 인덱스는 쿼리 성능을 저하시키고 응용 프로그램의 응답이 느릴 수 있다.

 

인덱스 조각화가 심할 경우에는 Reorganization 또는 Rebuild에 대한 고민을 하게 되는데 이러한 작업 이후 쿼리 성능이 어떻게 되는지 살펴보자.

 

  • 인덱스 다시 구성 및 다시 작성 :

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

 

 

테스트를 하기 위해 샘플 테이블 및 인덱스를 생성 한다.(데이터 생성에 많은 시간이 걸린다. 필자는 30분동안 데이터를 생성하였다.)

-- Create sample table and indexes

CREATE TABLE testtable ([col1] [int] NOT NULL primary key clustered,

[col2] [int] NULL,

[col3] [int] NULL,

[col4] [varchar](50) NULL);

 

CREATE INDEX idx_testtable_col3 on testtable (col3 asc);

 

-- Load sample data into table

DECLARE @val INT

SELECT @val=1

WHILE @val < 5000000

BEGIN

INSERT INTO testtable (col1, col2, col3, col4)

VALUES (@val,round(rand()*100000,0),round(rand()*100000,0),'TEST' + CAST(@val AS VARCHAR))

SELECT @val=@val+1

END

GO

 

 

샘플 데이터가 완성되었으면 SQL Server 인덱스 성능을 테스트 한다. 생성된 인덱스의 조각화 정보 확인 및 실제 조회 시 사용된 CPU, Reads, Write, Duration을 체크 한다. 이때 쿼리는 단일 값 및 범위, 인덱스 스캔을 할 수 있는 3가지 유형을 테스트하였다.

 

인덱스 조각화 정보 확인

SELECT object_name(object_id) as tablename,

index_id,index_type_desc,

avg_fragmentation_in_percent,

     page_count,page_count*8/1024 as [size(mb)]

FROM [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, DEFAULT)

WHERE object_name(object_id) = 'testtable' and index_id=2

 

 

 

각 쿼리의 성능을 비교 한다. 아래 성능표는 각 쿼리를 실행 했을 때 프로파일러를 통해 확인한 값이다.

SELECT col2 FROM testtable WHERE col3=55627;

SELECT col2 FROM testtable WHERE col3 BETWEEN 72000 AND 75000;

SELECT count(col3) FROM testtable;

 

Test Scenario

CPU

Read

Write

Duration

1회

Single Value

0

189

0

0

By Range

329

22583

0

716

Index scan

1548

13662

0

423

2회

Single Value

0

189

0

1

By Range

483

22583

0

687

Index scan

1174

13662

0

331

3회

Single Value

0

189

0

0

By Range

452

22583

0

668

Index scan

1283

13662

0

356

 

 

인덱스 Reorganization 및 Rebuild를 통해서 성능을 확인하기 위해 인덱스 유지관리 작업을 한다. 이때 동일한 환경에서 작업 후 성능을 평가하기 위해 데이터베이스 백업 작업을 진행 한다. 이후 각 테스트마다 백업 된 데이터베이스를 복원하여 유지관리 작업을 진행하여 성능을 평가 한다.

 

인덱스 Rebuild

-- REBUILD test

-- After this completes run query against [dm_db_index_physical_stats] to

-- get fragmentation stats

ALTER INDEX [idx_testtable_col3] ON [dbo].[testtable] REBUILD PARTITION = ALL

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,

ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

 

Test Scenario

CPU

Read

Write

Duration

1회

Single Value

0

193

0

0

By Range

593

22587

0

699

Index scan

1171

8774

0

324

2회

Single Value

0

189

0

0

By Range

517

22583

0

703

Index scan

1219

8772

0

357

3회

Single Value

0

189

0

0

By Range

671

22583

0

824

Index scan

1094

8772

0

315

 

인덱스 Reorganization

-- REORG test

-- After this completes run query against [dm_db_index_physical_stats] to

-- get fragmentation stats

ALTER INDEX [idx_testtable_col3] ON [dbo].[testtable] REORGANIZE

WITH ( LOB_COMPACTION = ON );

 

Test Scenario

CPU

Read

Write

Duration

1회

Single Value

0

484

0

21

By Range

640

22587

0

814

Index scan

1156

8936

0

299

2회

Single Value

0

189

0

1

By Range

453

22583

0

747

Index scan

1485

8934

0

402

3회

Single Value

0

189

0

0

By Range

531

22583

0

746

Index scan

1110

8934

0

333

 

 

이 테스트 결과를 보면 인덱스 유지관리를 하였을 때 전체적으로 쿼리 성능이 크게 변하지 않는 것을 확인 할 수 있었다. 하지만 인덱스 스캔의 Read의 경우 매우 많은 성능 이점을 확인 할 수 있었다. 인덱스 재구성 후 인덱스 페이지가 감소되어 인덱스 스캔의 성능이 빨라진 것으로 유추할 수 있다.

 

이번 테스트 이후 인덱스 리빌드 작업은 과연 모든 운영 시스템에 필요한지 다시 한번 생각하게된다. 인덱스 스캔이 많다면 고려해볼만 하지만 대부분의 쿼리는 단일 값 또는 범위 검색을 하기 때문이다. 비즈니스를 확인하고 선택은 스스로 판단할 수 있도록 한다.

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3533/sql-server-query-performance-after-index-maintenance-for-reorganization-vs-rebuild-operations/

 

2015-03-05 / 강성욱 / http://sqlmvp.kr

 

 

인덱스 리빌드, 인덱스 재구성, sqlserver, mssql, index rebuild, index reorg, sql 인덱스, DB 튜닝, sql튜닝

 

 

 

네트워크 드라이브에 데이터베이스 복원하기

 

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

 

SQL Server에서 데이터베이스 복원작업을 진행 할 때 SSMS를 사용할 경우 로컬 드라이브만 표시 된다. 원격지의 네트워크 드라이브에 데이터베이스를 복원할 때 드라이브 목록에 네트워크 드라이브를 추가할 수 있는 방법에 대해서 알아 본다.

 

데이터베이스를 복원할 때 로컬의 드라이브만 표시 된다.

 

네트워크 드라이브를 추가 하기 위해 Windows에서 네트워크 드라이브를 매핑해야 한다.

 

네트워크 드라이브 매핑이 완료 되었으면 SQL Server에서 해당 네트워크 드라이브를 식별하기 위해 xp_cmdshell 명령을 사용해야 한다. Xp_cmdshell은 기본적으로 비활성화 되어 있으므로 sp_configure adufud을 사용하여 활성화 한다.

EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

 

EXEC sp_configure 'xp_cmdshell',1

GO

RECONFIGURE

GO

 

Xp_cmdshell 명령으로 SQL에 대한 공유 드라이브를 정의 한다.

EXEC XP_CMDSHELL 'net use H: \\RemoteServerName\ShareName'

 

매핑된 새 드라이브를 확인하기 위해 다음 스크립트를 실행하면 매핑 된 드라이브에 있는 모든 파일의 목록을 보여준다.

EXEC XP_CMDSHELL 'Dir H:'

 

네트워크 드라이브의 파일의 목록이 조회가 된다면 정상적으로 연결되었다. SSMS에서 데이터베이스 복원 할 때 로컬 드라이브 외에 네트워크 드라이브 경로가 추가 된 것을 확인 할 수 있다.

 

매핑된 드라이브를 삭제는 다음 스크립트를 사용 한다.

EXEC XP_CMDSHELL 'net use H: /delete' /pre>

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3499/make-network-path-visible-for-sql-server-backup-and-restore-from-within-ssms/

 

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

 

 

데이터베이스 복원, sqlserver, mssql, 네트워크 복원, 네트워크 드라이브 매핑, Restore to Networkdrive, 원격지 DB복원

확장 저장 프로시저를 활용한 논리디스크 용량 확인

 

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

 

DBA의 업무에서 중요한 작업 중 하나인 디스크 공간을 확인 하는 부분이다. 디스크의 총 용량, 사용 가능한 공간, 논리 드라이브의 정보 등을 확인하여 예기치 못한 장애에 대응할 수 있도록 해야 한다.

 

각 디스크 드라이브의 여유 공간을 확인하는 방법은 다양 하다. 윈도우 탐색기를 이용하여 파일 시스템 유형, 총 용량, 여유 가능한 공간 등을 확인 할 수 있다. 또 다른 방법으로는 확장 저장 프로시저 xp_fixeddrives을 사용하여 SQL Serve에서 직접 드라이브의 여유 공간을 확인 할 수 있다.

 

확장 저장 프로시저를 활용하여 논리 디스크의 정보를 확인하는 스크립트를 만들어 편하게 확인 할 수 있는 방법에 대해서 알아본다. 스크립트로 확인 할 수 있는 정보는 다음과 같다.

  • 드라이브 문자
  • 볼륨이름
  • 파일 시스템 유형
  • 전체 용량(GB)
  • 남은 용량(GB)
  • 남은 용량(%)
  • 전체 데이터 파일 사이즈
  • 전체 로그 파일 사이즈

 

드라이브의 속성을 확인하기 위해 sp_OACreate 및 sp_OAGetProperty를 사용하여 드라이브의 속성을 가져오는 함수를 생성한다.

USE [master]

GO

CREATE FUNCTION [dbo].[ufn_LogicalDiskDrives]()

RETURNS @DriveList Table

(

[DriveLetter] CHAR(1)

,[VolumeName] VARCHAR(255)

,[FileSystem] VARCHAR(50)

,[TotalSize] BIGINT

,[AvailableSpace] BIGINT

,[FreeSpace] BIGINT

)

AS

BEGIN

--Written by Percy Reyes

DECLARE @DriveLetter_ASCII_Code INT

DECLARE @FileSystemInstance INT

DECLARE @DriveCount INT

DECLARE @DriveCollection INT

DECLARE @Drive INT

DECLARE @Property NVARCHAR(100)

DECLARE @DriveLetter VARCHAR(1)

DECLARE @TotalSize BIGINT

DECLARE @AvailableSpace BIGINT

DECLARE @FreeSpace BIGINT

DECLARE @FileSystem VARCHAR(128)

DECLARE @VolumeName VARCHAR(128)

DECLARE @IsReady VARCHAR(5)

 

--Creating a File System Object for getting files or disk info.

exec sp_OACreate 'Scripting.FileSystemObject', @FileSystemInstance OUT

--Getting the collection of drives

exec sp_OAGetProperty @FileSystemInstance,'Drives', @DriveCollection OUT

--Getting the count of drives from collection

exec sp_OAGetProperty @DriveCollection,'Count', @DriveCount OUT

 

--starting from Drive "A" (ASCII 65)

SET @DriveLetter_ASCII_Code = 65

--to "Z" (ASCII 90)

WHILE @DriveLetter_ASCII_Code <= 90

BEGIN

---Creating the instance drive from Drive Collection

SET @Property = 'item("'+CHAR(@DriveLetter_ASCII_Code)+'")'

exec sp_OAGetProperty @DriveCollection,@Property, @Drive OUT

-- Getting the drive letter property

exec sp_OAGetProperty @Drive,'DriveLetter', @DriveLetter OUT

 

IF @DriveLetter = CHAR(@DriveLetter_ASCII_Code)

BEGIN

-- Getting more properties from each drive

exec sp_OAGetProperty @Drive,'VolumeName', @VolumeName OUT

exec sp_OAGetProperty @Drive,'FileSystem', @FileSystem OUT

exec sp_OAGetProperty @Drive,'TotalSize', @TotalSize OUT

exec sp_OAGetProperty @Drive,'AvailableSpace', @AvailableSpace OUT

exec sp_OAGetProperty @Drive,'FreeSpace', @FreeSpace OUT

exec sp_OAGetProperty @Drive,'IsReady' , @IsReady OUT;

 

IF @IsReady='True'

INSERT INTO @DriveList ( [DriveLetter],[TotalSize], [AvailableSpace],[FreeSpace],[FileSystem] ,[VolumeName] )

VALUES( @DriveLetter,@TotalSize,@AvailableSpace,@FreeSpace,@FileSystem,@VolumeName)

 

END

-- forward next drive

SET @DriveLetter_ASCII_Code = @DriveLetter_ASCII_Code +1

END

 

EXEC sp_OADestroy @Drive

EXEC sp_OADestroy @DriveCollection

 

RETURN

END

 

위에서 생성한 함수가 정보를 가져 올 수 있도록 SQL Server 설정을 변경한다.

EXECUTE sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

GO

EXECUTE sp_configure 'Ole Automation Procedures', 1

RECONFIGURE WITH OVERRIDE

GO

 

생성한 함수를 호출하여 정보를 확인한다. 논리 디스크의 볼륨명과 파일 시스템, 전체 용량과 남은 공간 등을 확인 할 수 있다.

SELECT * FROM [dbo].[ufn_LogicalDiskDrives]()

 

 

아래 스크립트는 각 논리 드라이브에 위치한 데이터 파일 사이즈 및 로그파일 사이즈를 확인한다.

select ISNULL(DF.DriveLetter, LF.DriveLetter) DriveLetter, DF.DataSize_GB, LF.LogSize_GB from (

select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024 AS DECIMAL(10,2)) DataSize_GB

from sys.master_files

where type_desc='ROWS'

group by left(physical_name, 1), type_desc

) DF

full outer join

(

select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024 AS DECIMAL(10,2)) LogSize_GB

from sys.master_files

where type_desc='LOG'

group by left(physical_name, 1), type_desc

) LF

on DF.DriveLetter=LF.DriveLetter

 

 

아래 스크립트는 위에서 사용한 쿼리를 프로시저로 만들어 디스크의 정보를 확인 할 수 있도록 한다.

USE [master]

GO

CREATE PROC dbo.sp_fixeddrives

WITH ENCRYPTION

AS

BEGIN

SET NOCOUNT ON

-----Reporting Drive details ....

select LDD.DriveLetter ,[VolumeName] ,[FileSystem]

,CAST(TotalSize/1024.0/1024/1024.0 AS DECIMAL(10,2)) [Capacity_GB]

,CAST(FreeSpace/1024.0/1024/1024.0 AS DECIMAL(10,2)) [FreeSpace_GB]

,CAST((CAST(FreeSpace/1024.0/1024/1024.0 AS DECIMAL(10,2))/CAST(TotalSize/1024.0/1024/1024.0 AS DECIMAL(10,2)) ) *100 AS DECIMAL(10,2)) [Free %]

, ISNULL( DBFiles.DataSize_GB,0) DataSize_GB,ISNULL( DBFiles.LogSize_GB,0) LogSize_GB

from master.[dbo].[ufn_LogicalDiskDrives]() LDD

full outer join

(

select ISNULL(DF.DriveLetter, LF.DriveLetter) DriveLetter, DF.DataSize_GB, LF.LogSize_GB from

(

select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024 AS DECIMAL(10,2)) DataSize_GB

from sys.master_files

where type_desc='ROWS'

group by left(physical_name, 1), type_desc

) DF

full outer join

(

select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024 AS DECIMAL(10,2)) LogSize_GB

from sys.master_files

where type_desc='LOG'

group by left(physical_name, 1), type_desc

) LF

on DF.DriveLetter=LF.DriveLetter

 

) DBFiles

ON DBFiles.DriveLetter =LDD.DriveLetter

SET NOCOUNT OFF

END

 

생성한 프로시저 sp_fixeddrives를 어느 데이터베이스에서나 호출하여 사용 할 수 있도록 시스템 오브젝트로 등록 한다.

USE [master]

GO

EXEC sys.sp_MS_marksystemobject 'sp_fixeddrives'

 

저장 프로시저를 실행하여 정보를 확인 한다.

EXEC sp_fixeddrives

 

 

매일 프로시저를 호출하여 디스크의 정보를 보고받는다면 디스크 공간으로 발생하는 장애는 미연에 방지 할 수 있으리라 생각한다.

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3517/reviewing-logical-hard-drive-details-using-tsql-and-ole-automation-procedures/

 

 

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

 

 

디스크 공간 확인, SQL Server, MSSQL, 논리 디스크 정보, disk free space, 데이터베이스 공간확인, SQL Data Size, SQL Log Size, 디스크 정보, xp_fixeddrives, 확장 저장 프로시저

날짜 참조 테이블 만들기

 

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

 

데이터를 검색 할 때 가장 많이 사용되는 조건 중 하나가 날짜 일 것이다. 다양한 통계 쿼리를 만들다 보면 날짜 범위에 관한 조건문이 많이 사용되는데 이때 해당 기간을 검색하기 위한 참조 테이블을 만들어 사용하면 편리하다. (흔히 날짜 차원 테이블이라고 부른다.)

 

날짜 참조 테이블을 생성한다.

USE TEMPDB

 

IF OBJECT_ID('dbo.#t') is not null

DROP TABLE dbo.#t;

CREATE TABLE #t (

[Date] datetime

, [Year] smallint

, [Quarter] tinyint

, [Month] tinyint

, [Day] smallint -- from 1 to 366 = 1st to 366th day in a year

, [Week] tinyint -- from 1 to 54 = the 1st to 54th week in a year;

, [Monthly_week] tinyint -- 1/2/3/4/5=1st/2nd/3rd/4th/5th week in a month

, [Week_day] tinyint -- 1=Mon, 2=Tue, 3=Wed, 4=Thu, 5=Fri, 6=Sat, 7=Sun

);

GO

 

날짜 정보를 생성 한다. 아래 스크립트의 경우 Week 컬럼의 시작 요일은 월요일이다. 시작 요일을 일요일로 바꾸고 싶으면 -1을 한다.

-- populate the table D_Date, and the day of week is defined as

-- 1=Mon, 2=Tue, 3=Wed, 4=Thu,5=Fri, 6=Sat, 7=Sun

;WITH C0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),

C1 AS (SELECT 1 AS c FROM C0 AS A CROSS JOIN C0 AS B),

C2 AS (SELECT 1 AS c FROM C1 AS A CROSS JOIN C1 AS B),

C3 AS (SELECT 1 AS c FROM C2 AS A CROSS JOIN C2 AS B),

C4 AS (SELECT 1 AS c FROM C3 AS A CROSS JOIN C3 AS B),

C5 AS (SELECT 1 AS c FROM C4 AS A CROSS JOIN C3 AS B),

C6 AS (select rn=row_number() over (order by c) from C5),

C7 as (select [date]=dateadd(day, rn-1, '19000101') FROM C6 WHERE rn <= datediff(day, '19000101', '99991231')+1)

INSERT INTO #t ([year], [quarter], [month], [week], [day], [monthly_week], [week_day], [date])

SELECT datepart(yy, [DATE]), datepart(qq, [date]), datepart(mm, [date]), datepart(wk, [date])

, datediff(day, dateadd(year, datediff(year, 0, [date]), 0), [date])+1

, datepart(week, [date]) -datepart(week, dateadd(month, datediff(month, 0, [date]) , 0))+1

, CASE WHEN datepart(dw, [date])+@@datefirst-1 > 7 THEN (datepart(dw, [date])+@@datefirst-1)%7

ELSE datepart(dw, [date])+@@datefirst-1 END

, [date]

FROM C7

--where [date] between '19900101' and '20990101'; -- if you want to populate a range of dates

GO

 

날짜 정보가 1900-01-01부터 9999-12-31일 까지 데이터가 생성되었다. (자그마치 2958464개의 행이다.)

 

 

[월별 첫째 날짜와 마지막 날짜 찾기]

-- find first day of prev/curr/next month with current date

declare @curr_dt datetime = '2015-02-11';

 

select distinct First_Mth_Day=first_value([Date]) over (partition by [month] order by [Date] asc)

, Last_Mth_Day=last_value([Date]) over (partition by [month] order by [Date] asc ROWS BETWEEN Current Row AND UNBOUNDED FOLLOWING )

from #t

where datediff(month, @curr_dt, [Date]) between -1 and 1

and datediff(year, @curr_dt, [Date]) between -1 and 1

order by 1

go

 

 

[토, 일요일을 제외한 첫 날짜와 마지막 날짜 구하기]

-- find the first/last weekday of prev/curr/next month

declare @curr_dt datetime = '2015-02-11';

select distinct First_Week_Day=first_value([Date]) over (partition by [month] order by [Date] asc)

, Last_Week_Day=last_value([Date]) over (partition by [month] order by [Date] asc ROWS BETWEEN Current Row AND 31 FOLLOWING )

from #t

where datediff(month, @curr_dt, [Date]) between -1 and 1

and datediff(year, @curr_dt, [Date]) between -1 and 1

and Week_Day between 1 and 5

order by 1

go

 

 

[주말(토요일 또는 일요일)의 첫 날짜와 마지막 날짜 구하기]

-- find the first / last weekend day of prev/curr/next month

declare @curr_dt datetime = '2015-02-11';

with c as (

select distinct First_Wknd_Day=first_value([Date]) over (partition by [month] order by [Date] asc)

, Last_Wknd_Day=last_value([Date]) over (partition by [month] order by [Date] asc ROWS BETWEEN Current Row AND UNBOUNDED FOLLOWING )

from #t

where datediff(month, @curr_dt, [Date]) between -1 and 1

and datediff(year, @curr_dt, [Date]) between -1 and 1

and Week_Day between 6 and 7

)

select [Month]=choose((row_number() over (order by First_wknd_day asc)), 'Prev_Mth', 'Curr_Mth', 'Next_Mth')

, First_Wknd_Day, Last_Wknd_day

from c

go

 

 

[매월 주 시작 날짜와 주 마지막 날짜 구하기]

-- find the first/last weekday in each month of all years

select distinct [Year], [Month], First_Week_Day=first_value([Date]) over (partition by [year], [month] order by [Date] asc)

, Last_Week_Day=last_value([Date]) over (partition by [year], [month] order by [Date] asc ROWS BETWEEN Current Row AND 32 FOLLOWING )

from #t

where Week_Day between 1 and 5 -- for weekend, change to: between 6 and 7

order by 1, 2

go

 

 

[검색 기간동안 토,일요일이 몇번있는지 구하기]

-- how many weekend days or weekdays between two dates

declare @start_day datetime ='2015-01-11', @End_day datetime ='2015-02-03'

select [Start_Date] = @Start_day, [End_Date]=@End_day, Total_weekend_days = count(*)

from #t

where (Week_Day between 6 and 7) -- for weekdays, use "between 1 and 5"

and ([Date] between @start_day and @end_day)

go

 

 

[매월 2번째 주 시작날짜 구하기]

-- find nth week/weekend day of each prev/curr/next month

-- eg. find the 2nd Monday of each prev/curr/next month

declare @curr_dt datetime = getdate();

; with c as (select rn=RANK() over (partition by [month] order by [date] ASC), [Date] -- attention to 'ASC'

from #t

where datediff(month, @curr_dt, [Date]) between -1 and 1

and datediff(year, @curr_dt, [Date]) between -1 and 1

and Week_Day = 1 -- 1=Mon, 2=Tue,... 7=Sun

)

select [Month]=choose((row_number() over (order by (select null))), 'Prev_Mth', 'Curr_Mth', 'Next_Mth')

,[2nd_Monday] = [Date] from c

where rn=2 -- nth, for example if finding the 3rd Monday, set rn=3

order by 2

go

 

 

[매월 마지막 2번쨰 주 마지막 날짜 구하기]

-- find the nth last week/weekend day of prve/curr/next month

-- eg. find the 2nd last Sat of prev/curr/next month

declare @curr_dt datetime = getdate();

; with c as (select rn=RANK() over (partition by [month] order by [date] DESC), [Date] -- attention to 'DESC'

from #t

where datediff(month, @curr_dt, [Date]) between -1 and 1

and datediff(year, @curr_dt, [Date]) between -1 and 1

and Week_Day = 6 -- 1=Mon, 2=Tus, ... 7=Sun

)

select [Month]=choose((row_number() over (order by (select null))), 'Prev_Mth', 'Curr_Mth', 'Next_Mth')

, [2nd_Last_Sat]=[Date] from c

where rn=2

order by 2

go

 

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3508/sql-server-date-time-calculation-examples/

 

 

2015-02-16 / 강성욱 / http://sqlmvp.kr

 

 

날짜 구하기, 날짜 테이블, 날짜 참조 테이블, 시간 차원, 요일 구하기, mssql, sqlserver, datetime, 월요일 구하기, 일요일 구하기, 매월 1일 구하기, 마지막 요일 구하기

인덱스 상세 정보 확인

 

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

 

테이블 또는 뷰의 인덱스에 관한 정보를 확인하기 위해서 sp_helpindex를 많이 사용한다. Sp_helpindex는 SQL Server 2005부터 지원하고 있다.

 

use AdventureWorks2012

go

 

exec sp_helpindex 'Sales.SalesOrderdetail'

 

 

열 이름

데이터 형식

설명

Index_name

Sysname

인덱스 이름

Index_description

Varchar(21)

인덱스가 있는 파일 그룹을 포함하는 인덱스 설명

Index_keys

Nvarchar(2078)

인덱스가 만들어진 테이블 또는 뷰의 열

 

 

Sp_helpindex의 경우 기본 정보만 확인 할 수 있으며 포괄열이나 인덱스 사이즈, Fill Factor, 인덱스 타입등은 나타내지 않는다. Sys.table, sys.indexes, sys.index_columns, sys.columns 테이블의 정보를 활용하여 인덱스의 다양한 정보를 확인 할 수 있다.

declare @SchemaName sysname=NULL

, @TableName sysname=NULL

, @IndexName sysname=NULL

, @dataspace sysname=NULL

 

set @SchemaName = 'Sales'

set @TableName = 'SalesOrderDetail'

--set @IndexName = 'AK_SalesOrderDetail_rowguid'

 

 

declare @_SchemaName varchar(100)

declare @_TableName varchar(256)

declare @_IndexName varchar(256)

declare @ColumnName varchar(256)

declare @is_unique varchar(100)

declare @IndexTypeDesc varchar(100)

declare @FileGroupName varchar(100)

declare @is_disabled varchar(100)

declare @IndexColumnId int

declare @IsDescendingKey int

declare @IsIncludedColumn int

 

 

-- getting the index sizes

SELECT schema_name(t.schema_id) [SchemaName],

OBJECT_NAME(ix.OBJECT_ID) AS TableName,

ix.name AS IndexName,

CAST( 8 * SUM(a.used_pages)/1024.0 AS DECIMAL(20,1))AS 'Indexsize(MB)'

INTO #IndexSizeTable

from sys.tables t

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

inner join sys.partitions AS p ON p.OBJECT_ID = ix.OBJECT_ID AND p.index_id = ix.index_id

inner join sys.allocation_units AS a ON a.container_id = p.partition_id

WHERE ix.type>0 and t.is_ms_shipped=0

and schema_name(t.schema_id)= isnull(@SchemaName,schema_name(t.schema_id)) and t.name=isnull(@TableName,t.name) AND ix.name=isnull(@IndexName, ix.name)

GROUP BY schema_name(t.schema_id), ix.OBJECT_ID,ix.name

ORDER BY OBJECT_NAME(ix.OBJECT_ID),ix.name

 

--getting important properties of indexes

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

cast( '' as varchar(max)) AS IndexKeys, casT('' as varchar(max)) AS IncludedColumns,

ix.is_unique

, ix.type_desc, ix.fill_factor as [Fill_Factor]

, ix.is_disabled , da.name as data_space,

ix.is_padded,

ix.allow_page_locks,

ix.allow_row_locks,

INDEXPROPERTY(t.object_id, ix.name, 'IsAutoStatistics') IsAutoStatistics ,

ix.ignore_dup_key

INTO #helpindex

from sys.tables t

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

inner join sys.data_spaces da on da.data_space_id= ix.data_space_id

where ix.type>0 and t.is_ms_shipped=0

and schema_name(t.schema_id)= isnull(@SchemaName,schema_name(t.schema_id)) and t.name=isnull(@TableName,t.name) AND ix.name=isnull(@IndexName, ix.name)

and da.name=isnull(@dataspace,da.name)

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

 

---getting the index keys and included columns

declare CursorIndex cursor for

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

from sys.tables t

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

where ix.type>0 and t.is_ms_shipped=0

and schema_name(t.schema_id)= isnull(@SchemaName,schema_name(t.schema_id)) and t.name=isnull(@TableName,t.name) AND ix.name=isnull(@IndexName, ix.name)

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

open CursorIndex

fetch next from CursorIndex into @_SchemaName, @_TableName, @_IndexName

while (@@fetch_status=0)

begin

declare @IndexColumns varchar(4000)

declare @IncludedColumns varchar(4000)

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 tb.is_ms_shipped=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 +', '

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

 

UPDATE #helpindex

SET IndexKeys = @IndexColumns, IncludedColumns=@IncludedColumns

WHERE [SchemaName]=@_SchemaName and TableName=@_TableName and IndexName=@_IndexName

 

fetch next from CursorIndex into @_SchemaName, @_TableName, @_IndexName

 

end

close CursorIndex

deallocate CursorIndex

 

--showing the results

SELECT hi.SchemaName, hi.TableName, hi.IndexName, hi.IndexKeys, hi.IncludedColumns, ixs.[Indexsize(MB)],

hi.is_unique, hi.type_desc,hi.data_space, hi.Fill_Factor, hi.IsAutoStatistics,

hi.is_disabled, hi.is_padded, hi.allow_page_locks, hi.allow_row_locks,hi.ignore_dup_key

FROM #helpindex hi

INNER JOIN #IndexSizeTable ixs ON hi.SchemaName=ixs.SchemaName and hi.TableName=ixs.TableName and hi.IndexName=ixs.IndexName

order by hi.SchemaName, hi.TableName, hi.IndexKeys, hi.IncludedColumns

 

drop table #helpindex

drop table #IndexSizeTable

 

 

 

위 스크립트를 프로시로 만들기

use [master]

go

 

create proc dbo.sp_helpindex2

( @SchemaName sysname=NULL

, @TableName sysname=NULL

, @IndexName sysname=NULL

, @dataspace sysname=NULL

)

AS

 

BEGIN

SET NOCOUNT ON

declare @_SchemaName varchar(100)

declare @_TableName varchar(256)

declare @_IndexName varchar(256)

declare @ColumnName varchar(256)

declare @is_unique varchar(100)

declare @IndexTypeDesc varchar(100)

declare @FileGroupName varchar(100)

declare @is_disabled varchar(100)

declare @IndexColumnId int

declare @IsDescendingKey int

declare @IsIncludedColumn int

 

-- getting the index sizes

SELECT schema_name(t.schema_id) [SchemaName],

OBJECT_NAME(ix.OBJECT_ID) AS TableName,

ix.name AS IndexName,

CAST( 8 * SUM(a.used_pages)/1024.0 AS DECIMAL(20,1))AS 'Indexsize(MB)'

INTO #IndexSizeTable

from sys.tables t

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

inner join sys.partitions AS p ON p.OBJECT_ID = ix.OBJECT_ID AND p.index_id = ix.index_id

inner join sys.allocation_units AS a ON a.container_id = p.partition_id

WHERE ix.type>0 and t.is_ms_shipped=0

and schema_name(t.schema_id)= isnull(@SchemaName,schema_name(t.schema_id)) and t.name=isnull(@TableName,t.name) AND ix.name=isnull(@IndexName, ix.name)

GROUP BY schema_name(t.schema_id), ix.OBJECT_ID,ix.name

ORDER BY OBJECT_NAME(ix.OBJECT_ID),ix.name

 

--getting important properties of indexes

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

cast( '' as varchar(max)) AS IndexKeys, casT('' as varchar(max)) AS IncludedColumns,

ix.is_unique

, ix.type_desc, ix.fill_factor as [Fill_Factor]

, ix.is_disabled , da.name as data_space,

ix.is_padded,

ix.allow_page_locks,

ix.allow_row_locks,

INDEXPROPERTY(t.object_id, ix.name, 'IsAutoStatistics') IsAutoStatistics ,

ix.ignore_dup_key

INTO #helpindex

from sys.tables t

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

inner join sys.data_spaces da on da.data_space_id= ix.data_space_id

where ix.type>0 and t.is_ms_shipped=0

and schema_name(t.schema_id)= isnull(@SchemaName,schema_name(t.schema_id)) and t.name=isnull(@TableName,t.name) AND ix.name=isnull(@IndexName, ix.name)

and da.name=isnull(@dataspace,da.name)

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

 

---getting the index keys and included columns

declare CursorIndex cursor for

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

from sys.tables t

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

where ix.type>0 and t.is_ms_shipped=0

and schema_name(t.schema_id)= isnull(@SchemaName,schema_name(t.schema_id)) and t.name=isnull(@TableName,t.name) AND ix.name=isnull(@IndexName, ix.name)

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

open CursorIndex

fetch next from CursorIndex into @_SchemaName, @_TableName, @_IndexName

while (@@fetch_status=0)

begin

declare @IndexColumns varchar(4000)

declare @IncludedColumns varchar(4000)

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 tb.is_ms_shipped=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 +', '

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

 

UPDATE #helpindex

SET IndexKeys = @IndexColumns, IncludedColumns=@IncludedColumns

WHERE [SchemaName]=@_SchemaName and TableName=@_TableName and IndexName=@_IndexName

 

fetch next from CursorIndex into @_SchemaName, @_TableName, @_IndexName

 

end

close CursorIndex

deallocate CursorIndex

 

--showing the results

SELECT hi.SchemaName, hi.TableName, hi.IndexName, hi.IndexKeys, hi.IncludedColumns, ixs.[Indexsize(MB)],

hi.is_unique, hi.type_desc,hi.data_space, hi.Fill_Factor, hi.IsAutoStatistics,

hi.is_disabled, hi.is_padded, hi.allow_page_locks, hi.allow_row_locks,hi.ignore_dup_key

FROM #helpindex hi

INNER JOIN #IndexSizeTable ixs ON hi.SchemaName=ixs.SchemaName and hi.TableName=ixs.TableName and hi.IndexName=ixs.IndexName

order by hi.SchemaName, hi.TableName, hi.IndexKeys, hi.IncludedColumns

 

drop table #helpindex

drop table #IndexSizeTable

 

set nocount off

end

 

모든 데이터베이스에서 실행 할 수 있도록 시스템 오브젝트로 등록한다.

use master

go

 

exec sys.sp_MS_marksystemobject sp_helpindex2

 

Sp_helpindex2 호출 시 다양한 파라메터 조합으로 인덱스 정보를 조회 할 수 있다.

exec sp_helpindex2

exec sp_helpindex2 @schemaName = 'Sales', @TableName = 'SalesOrderDetail'

exec sp_helpindex2 @schemaName = 'Sales', @TableName = 'SalesOrderDetail', @IndexName = 'AK_SalesOrderDetail_rowguid'

exec sp_helpindex2 @DataSpace = 'Primary'

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3450/sql-server-index-report-with-included-columns-storage-and-more-for-all-tables-in-a-database/

 

 

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

 

인덱스 정보, sp_helpindex, sqlserver, mssql, 인덱스, fill factor, sp_helpindex2, indexName, 테이블 인덱스 정보, DBA, DB 관리

DTC Transacntion 오버헤드

 

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

 

BEGIN DISTRIBUTED TRANSACTION은 분산 트랜잭션을 시작하도록 한다. SQL Server 데이터베이스 엔진 인스턴스는 트랜잭션 주관자로서 트랜잭션의 수행을 제어한다. 이후 세션에 대해 COMMIT TRANSACTON 또는 ROLLBACK TRANSACNTION 문을 실행하면 제어 인스턴스는 포함된 모든 인스턴스 간의 분산 트랜잭션 완료를 MS DTC에서 관리하도록 요청 한다.

 

일반적으로 많은 부분에서 DTC를 사용한다. 하지만 필요하지 않는 부분에서는 사용하지 않는 것이 가급적 오버헤드를 줄 일 수 있다. DTC는 single phase 또는 two phase 커밋을 필요로 한다. DTC 트랜잭션은 SQL Server 데이터베이스에서 자원관리자(RM)을 포함한다. 단일 자원 관리자가 트랜잭션에 포함되는 경우 2단계 커밋을 수행할 필요가 없다. DTC는 안전하게 단일 커밋을 수행 한다. 이는 RM와 DTC사이의 통신을 감소 시킨다.

 

그러나 DTC 관리자의 오버헤드는 여전히 네이티브 TSQL 트랜잭션보다 약간 느린 트랜잭션을 유발한다. 다음 실습을 통해 알아본다.

 

[Single Phase]

Single phase DTC commit example

Prepared 상태가 표시되지 않는 출력을 확인 할 수 있다. 이는 하나의 커밋 단계를 표시한다.

begin distributed tran

go

 

update dbTest.dbo.tblTest set object_id = 100

go

 

commit tran

go

 

 

 

[Tow Phase]

로컬 인스턴스(RM = 1)와 원격 인스턴스(RM = 2)를 포함한다. 2RM이 DTC에 참여하여 2단계의 커밋 프로토콜이 발생한다. Prepared 단계는 2단계 프로토콜의 커밋을 의미한다.

begin distributed tran

go

 

update MYREMOTESERVER.dbTest.dbo.tblTest set object_id = 100

go

 

commit tran

go

 

 

사용자의 시스템에서 실행되는 DTC 트랜잭션의 경우 불필요한 사용을 제거하여 오버헤드를 줄이는 것이 좋다.

 

[참고자료]

  • BEGIN DISTRIBUTED TRANSACTION :

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

  • Do I really need to use DTC Transactions? :

http://blogs.msdn.com/b/psssql/archive/2015/01/26/do-i-really-need-to-use-dtc-transactions.aspx

 

 

2015-01-27 / 강성욱 / http://sqlmvp.kr

 

 

트랜잭션, 원격 트랜잭션, DTC, DISTRIBUTED TRANSACTION, 분산 트랜잭션, 데이터베이스, SQL Server, mssql, DTC 오버헤드, DTC Overhead

대용량 로드를 위한 BULK INSERT 옵션

 

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

 

 

대용량 데이터를 로드하는 방법은 다양하게 있다. 그 중 많이 사용하는 BULK INSERT의 다양한 옵션을 통해서 더 빠르게 데이터를 삽입하는 방법에 대해서 알아 본다.

 

BULK INSERT를 위한 샘플 데이터 생성(데이터 생성 시간이 오래 걸립니다.)

-- Create sample table and indexes

CREATE TABLE testtable (

[col1] [int] NOT NULL primary key clustered,

[col2] [int] NULL,

[col3] [int] NULL,

[col4] [varchar](50) NULL);

 

CREATE INDEX idx_testtable_col2 on testtable (col2 asc);

CREATE INDEX idx_testtable_col3 on testtable (col3 asc);

 

-- Load sample data into table

DECLARE @val INT

SELECT @val=1

WHILE @val < 5000000

BEGIN

INSERT INTO testtable (col1, col2, col3, col4)

VALUES (@val,@val % 10,@val,'TEST' + CAST(@val AS VARCHAR))

SELECT @val=@val+1

END

GO

 

BULK INSERT에서 사용할 수 있도록 포맷 파일을 생성한다.

-- Create a format file

bcp sw_test.dbo.testtable format nul -c -t, -f c:\testtable_formatfile.fmt -T

 

 

 

 

BULK INSERT에 사용할 데이터 파일을 생성한다.

bcp sw_test.dbo.testtable out c:\testtable.dat -c -t, -f c:\testtable_formatfile.fmt -T -S

 

 

다음 4가지 시나리오르 BULK INSERT를 실행 한다. 그리고 각 실행 결과에 대한 성능을 측정한다. 성능 측정 방법은 SQL Profiler 또는 SET STATISTICS 를 사용 한다.

  1. BULK Load
  2. BULK Load with TabLock
  3. BULK Load with Tablock and drop/create indexes
  4. BULK Load with Tablock and drop/recreate indexes andchage recovery model

 

 

--1. BULK load

truncate table testtable

 

BULK INSERT dbo.testtable

FROM 'C:\testtable.dat'

WITH (FORMATFILE = 'C:\testtable_formatfile.fmt');

 

 

--2. BULK load with tablock

truncate table testtable

 

BULK INSERT dbo.testtable

FROM 'C:\testtable.dat'

WITH (FORMATFILE = 'C:\testtable_formatfile.fmt', TABLOCK);

 

 

--3. BULK load with tablock and drop/recreate indexes

truncate table testtable

 

DROP INDEX testtable.idx_testtable_col2;

DROP INDEX testtable.idx_testtable_col3;

 

BULK INSERT dbo.testtable

FROM 'C:\testtable.dat'

WITH (FORMATFILE = 'C:\testtable_formatfile.fmt', TABLOCK);

 

CREATE INDEX idx_testtable_col2 on testtable (col2 asc);

CREATE INDEX idx_testtable_col3 on testtable (col3 asc);

 

 

--4. BULK load with tablock and drop/recreate indexes and change recovery model

truncate table testtable

 

ALTER DATABASE SW_TEST SET RECOVERY SIMPLE;

 

DROP INDEX testtable.idx_testtable_col2;

DROP INDEX testtable.idx_testtable_col3;

 

BULK INSERT dbo.testtable

FROM 'C:\testtable.dat'

WITH (FORMATFILE = 'C:\testtable_formatfile.fmt', TABLOCK);

 

CREATE INDEX idx_testtable_col2 on testtable (col2 asc);

CREATE INDEX idx_testtable_col3 on testtable (col3 asc);

 

ALTER DATABASE SW_TEST SET RECOVERY FULL;

 

 

결과를 살펴보면 3번의 경우가 가장 빠르게 나타난 것을 확인 할 수 있다. 하지만 일부 실행 결과에서는 4번이 더 빠르게 나타나기도 하였다. 좀 더 정확한 정보를 얻기 위해서는 다양한 환경에서 많은 테스트를 통하여 평균치를 산정하여야 할 것이다.

 

BULK INSERT 사용시 일반적인 사용보다 환경에 따라 다양한 옵션을 사용하여 대량으로 로드하는 것이 많은 시간을 단축 할 수 있는 것을 확인 할 수 있다.

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3434/options-to-improve-sql-server-bulk-load-performance/

 

 

2015-01-14 / 강성욱 / http://sqlmvp.kr

 

SQL Server, BCP, BULK INSERT, 대량 로드, 벌크인서트, 대량 삽입, 대용량 데이터 처리, TABLOCK, BULK LOAD, DBA, mssql, 데이터베이스

SQL Server 2014 Diagnostic Information Queries

 

  • Version : SQL Server 2014

 

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

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

-- SQL Server 2014 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

-- This should not be an issue in SQL Server 2014, since 80 compatibility mode is not supported

-- 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, sql2014, sql 분석, DB 분석, DB 진단, DB튜닝, SQL Health Check, DBA

 

 

SQL Server 2008R2 Diagnostic Information Queries

 

  • Version : SQL Server 2008R2

 

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

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

-- SQL Server 2008 R2 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, sql2008R2, sql 분석, DB 분석, DB 진단, DB튜닝, SQL Health Check, DBA

 

 

SQL Server 2008R2 Diagnostic Information Queries

 

  • Version : SQL Server 2008R2

 

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

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

-- SQL Server 2008 R2 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, sql2008R2, sql 분석, DB 분석, DB 진단, DB튜닝, SQL Health Check, DBA

 

 

+ Recent posts