'데이터베이스' 태그의 글 목록 (2 Page)

ALTER TRACE, CREATE LOGIN, DB튜닝, MSSQL, permission, SQL, SQL Profiler, SQL Server, SQL2008, sql2012, sql2014, SQL추적, 데이터베이스, 추적파일, 프로파일러

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

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

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

SQL Server Profiler : https://msdn.microsoft.com/ko-kr/library/ms181091.aspx

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 > SQL Server Tip' 카테고리의 다른 글

TDE 암호화된 데이터베이스 복원 (0)	2015.07.23
재해복구를 위한 SQL Server 역할 가져오기 (0)	2015.07.23
SQL Server Agent 공유 일정 생성하기 (0)	2015.07.23
인덱스 리빌드는 통계를 업데이트 할까? (0)	2015.07.23
인덱스 유지관리 작업과 SQL Server 쿼리 성능 (0)	2015.07.23

Distributed Transaction, dtc, DTC Overhead, DTC 오버헤드, MSSQL, SQL Server, 데이터베이스, 분산 트랜잭션, 원격 트랜잭션, 트랜잭션

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

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

날짜 참조 테이블 만들기 (0)	2015.07.23
인덱스 상세 정보 확인 (0)	2015.07.23
대용량 로드를 위한 BULK INSERT 옵션 (0)	2015.07.23
SQL Server 2014 Diagnostic Information Queries (0)	2015.07.23
SQL Server 2012 Contained Database (0)	2015.07.23

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

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

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

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

SQL Server로 데이터 가져오기 : http://sqlmvp.kr/140179374125
BULK INSERT : http://msdn.microsoft.com/ko-kr/library/ms188365.aspx

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 를 사용 한다.

BULK Load
BULK Load with TabLock
BULK Load with Tablock and drop/create indexes
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 > SQL Server Tip' 카테고리의 다른 글

인덱스 상세 정보 확인 (0)	2015.07.23
DTC Transacntion 오버헤드 (0)	2015.07.23
SQL Server 2014 Diagnostic Information Queries (0)	2015.07.23
SQL Server 2012 Contained Database (0)	2015.07.23
SQL Server 2008R2 Diagnostic Information Queries (0)	2015.07.23

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

클러스터된 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

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

Ad-hoc 쿼리와 실행계획 (0)	2015.07.23
로그인 계정이 접근할 수 있는 데이터베이스 확인 (0)	2015.07.23
sys.dm_tran_locks 를 이용한 잠금 정보 확인 (0)	2015.07.23
외래키 제약 조건 삭제 후 재작성 스크립트 생성하기 (0)	2015.07.23
페이지 ID로 테이블 이름 찾기 (0)	2015.07.23

Data Science Lab