데이터베이스에서 사용자 삭제 오류

 

  • Version : SQL Server

 

SQL Server에서 사용자를 삭제할때 삭제하려는 사용자 계정이 특정 개체를 소유하거나 사용 권한을 가지고 있을때 다음과 같은 오류메시지와 함께 작업이 실패한다.

Msg 15136, Level 16, State 1, Line 2

The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped.

 

이런 경우 특정 사용자 계정이 어떤 개체를 소유하거나 권한을 가지고 있는지 확인하여 해당 권한을 삭제한 후 계정을 삭제해야 한다.

 

SQL Server에는 다양한 시스템 뷰가 있으며execute_as_principal_id 컬럼은 EXECITE AS 데이터베이스 보안주체 ID를 나타낸다. 아래 스크립트는 시스템뷰에서 execute_as_principal_id를 사용하는 모든 뷰의 목록을 확인할 수 있다.

select object_name(object_id) 'view name' from sys.system_views

where object_definition (object_id) like '%execute_as_principal_id%'

 

 

위의 뷰 목록을 기반으로 아래 스크립트를 실행하여 특정 계정이 사용하는 뷰 목록을 확인할 수 있다.

select user_name(execute_as_principal_id) 'execute as user', * from sys.system_sql_modules where execute_as_principal_id is not null

select user_name(execute_as_principal_id) 'execute as user', * from sys.service_queues where execute_as_principal_id is not null

select user_name(execute_as_principal_id) 'execute as user', * from sys.assembly_modules where execute_as_principal_id is not null

select user_name(execute_as_principal_id) 'execute as user', * from sys.sql_modules where execute_as_principal_id is not null

select user_name(execute_as_principal_id) 'execute as user', * from sys.server_assembly_modules where execute_as_principal_id is not null

select user_name(execute_as_principal_id) 'execute as user', * from sys.server_sql_modules where execute_as_principal_id is not null

select user_name(execute_as_principal_id) 'execute as user', * from sys.all_sql_modules where execute_as_principal_id is not null

 

 

 

[참고자료]

https://blogs.msdn.microsoft.com/psssql/2016/11/15/unable-to-drop-a-user-in-a-database/

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

 

 

2016-12-07 / 강성욱 / http://sqlmvp.kr

 

SQL Server, MS SQL, 사용자 삭제, 시스템 뷰, principal, execute_as_principal_id, SQL Tip, DBA, 카탈로그 뷰

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

SQL Server 2016 과 추적 플래그 –T8048  (0) 2017.03.13
SQL Server가 논리 및 물리 프로세서를 결정하는 방법  (0) 2017.03.13
SQL Server 2016 자동 통계 업데이트 임계값 변경  (1) 2016.10.29
Sys.dm_exec_query_plan 에서 query_plan 컬럼의 NULL 값 반환  (0) 2016.10.12
SQL Server 2016 Multiple Log Writer Workers  (0) 2016.10.08

SQL Server 2016 자동 통계 업데이트 임계값 변경

 

  • Version : SQL Server 2016

 

SQL Server 2016에서 자동 통계 업데이트를 위한 기본값이 변경되었다. 기존의 자동 통계 업데이트를 위한 기본 값은 테이블 전체 행의 약 20% 행 변경이 발생하였을때 통계 업데이트가 이루어진다. 예를 들면 1억 행이 있는 테이블의 경우 2천만 행이상 변경이 발생해야 통계 업데이트가 이루어진다. 대형 테이블의 경우 20%에 대한 행 변경이 매우 크기 때문에 기본적으로 잘 사용하지 않는다.

위와 같은 사유 때문에 Trace Flag 2371이 소개되었고 SQL Server 2008R2 SP1 이후 적용할 수 있다. 이 추적플래그는 테이블 행변화에 대한 비율을 극적으로 감소시켰다. 예를 들면 1억 행이 있는 테이블의 경우 100만 행 이상 변경이 발생할때 통계 업데이트가 이루어진다. 하지만 임계감 감소로 인해 빈번한 업데이트를 일으킬 수 있다. 이 Trace Flag 2371은 기본적으로 비활성화 되어 있으며 SQL Server 2016에서 호환성 수준 130 데이터베이스에 대해서는 기본적으로 활성화 되어 있다.

 

즉 한마디로 정리하면 아래와 같다.

  • SQL Server 2014 이하 : 임계값은 이전의 데이터베이스와 동일하다. (테이블 행 변경20%) 새로운 임계값을 활성화 하기 위해서 Trace Flag 2371을 사용할 수 있다.
  • SQL Server 2016 : 데이터베이스 호환성이 130 미만인 경우 이전 임계값이 사용되며, 130 이상은 TF 2371을 사용하지 않아도 새로운 임계값이 기본으로 활성화 되어 있다.

 

만약 SQL Server 2016을 사용하는데 빈번한 통계 업데이트로 인해 성능에 영향이 발생한다면 자동 통계 업데이트를 False로 구성하여 사용할 수 있도록 한다.

 

 

아래 실습을 통계 자동 통계 업데이트가 이루어지는 시점을 확인할 수 있다. 우선 실습을 위한 샘플 데이터 10억건을 생성한다.

--setup a table and insert 100 million rows

drop database testautostats

go

create database testautostats

go

use testautostats

go

create table t (c1 int)

go

set nocount on

declare @i int

set @i = 0

begin tran

while @i < 100000000

begin

declare @rand int = rand() * 1000000000

if (@i % 100000 = 0)

begin

while @@trancount > 0 commit tran

begin tran

end

insert into t values (@rand)

set @i = @i + 1

end

commit tran

 

go

create index ix on t (c1)

go

 

현재 마지막 통계가 이루어진 시점을 확인한다.

--run this query and query stats property

--note the last_updated column

select count (*) from t join sys.objects o on t.c1=o.object_id

go

select * from sys.stats st cross apply sys.dm_db_stats_properties (object_id, stats_id)

where st.object_id = object_id ('t')

 

SQL Server 2016에서 자동 통계 업데이트가 변경되는 시점을 확인하기 위해 백만건의 데이트를 삭제 한다.

--delete 1 million row

--run the same query and query stats property

--note that last_updated column changed

delete top (1000000) from t

go

select count (*) from t join sys.objects o on t.c1=o.object_id

 

go

select * from sys.stats st cross apply sys.dm_db_stats_properties (object_id, stats_id)

where st.object_id = object_id ('t')

 

SQL Server 2016 미만의 버전(호환성 수준 120)에서 자동 통계 업데이트가 변경되는 시점을 확인 한다.

--now switch DB compt level to 120

--delete 1 million row

--note that stats wasn't updated (last_updated column stays the same)

alter database testautostats SET COMPATIBILITY_LEVEL=120

go

delete top (1000000) from t

go

select * from sys.stats st cross apply sys.dm_db_stats_properties (object_id, stats_id)

where st.object_id = object_id ('t')

 

 

[참고자료]

 

 

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

 

 

SQL Server 2016, MS SQL, SQL 2016, TF2371, Trace Flag, 자동 통계 업데이트, Auto update statistics, 통계 업데이트 임계치, 쿼리튜닝, DB튜닝, SQL 튜닝

 

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

SQL Server가 논리 및 물리 프로세서를 결정하는 방법  (0) 2017.03.13
데이터베이스에서 사용자 삭제 오류  (0) 2016.12.08
Sys.dm_exec_query_plan 에서 query_plan 컬럼의 NULL 값 반환  (0) 2016.10.12
SQL Server 2016 Multiple Log Writer Workers  (0) 2016.10.08
SQL Server 2016 Larger Data File Writes  (0) 2016.10.06

Sys.dm_exec_query_plan 에서 query_plan 컬럼의 NULL 값 반환

 

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

 

SQL Server에서 쿼리 계획을 확인하기 위한 방법에는 DMV를 사용하는 방법이 있다. Sys.dm_exec_query_plan DMV는 XML 형태의 실행 계획을 반환한다. 플랜 핸들로 지정된 계획은 캐시되거나 현재 실행 중일 수 있다. 이 DMV는 아래와 같은 정보를 반환한다.

컬럼명

데이터 형식

설명

dbid

smallint

컴파일 당시 데이터베이스 ID. (NULL허용)

Objectid

Int

저장 프로시저나 사용자 정의 함수와 같은 개체 ID. ad-hoc 및 prepared 일괄처리의 경우 NULL 반환. (NULL 허용)

Number

Smallint

번호가 매겨진 저장프로시저 정수. Ad-hoc 및 prepared 일괄처리의 경우 NULL값 반환. (NULL허용)

Encrypted

Bit

해당 저장 프로시저가 암호화 되었는지 여부를 나타냄.

0 = 암호화 되지 않음

1= 암호화됨

(NULL 비허용)

Query_plan

xml

Plan_handle로 지정한 쿼리 실행 계획의 컴파일 시간 실행 계획을 포함. 실행계획은 XML로 표시. (NULL 허용)

 

아래와 같이query_plan의 결과 값이 특정 상황에서는 NULL이 반환되는 경우가 있다.

  • Plan_handle을 이용하여 지정한 쿼리 계획이 캐시에서 삭제된 경우 NULL 값을 반환한다. 예를 들어 플랜 핸들을 캡처한 시간과 sys.dm_exec_query_plan에 사용한 시간 사이에 지연이 있을 경우 발생할 수 있다.
  • 대량 작업문이나 8KB를 넘는 리터럴이 포함된 문자과 같은 일부 T-SQL문은 캐시 되지 않는다. 이러한 XML 실행 계획은 캐시에 없기 때문에 일괄 처리가 현재 실행되고 있지 않으면 sys.dm_exec_query_plan을 사용하여 검색 할 수 없다.
  • EXEC(string)을 사용하는 경우와 같이 T-SQL 일괄 처리 또는 저장프로시저에 사용자 정의 함수 호출이나 동적 SQL 호출이 포함된 경우 사용자 정의 함수에 대해 컴파일된 XML 실행 계획은 해당 일괄 처리 또는 저장 프로시저에 대해 sys.dm_exec_query_plan으로 반환되는 테이블에 포함되지 않는다. 대신 사용자 정의 함수에 해당하는 플랜 핸들에 대해 sys.dm_exec_query_plan을 별도로 호출 해야 한다.

 

Ad-hoc쿼리에서 간단한 매개변수화 또는 강제 매개변수화를 사용하는 경우 query_plan 열에는 텍스트만 포함되고 실제 쿼리 계획은 포함되지 않는다. 쿼리 계획을 반환하려면 매개 변수가 있는 준비된 쿼리의 계획 핸들에 대한 sys.dm_exec_query_plan을 호출해야 한다. Sys.syscacheobjects 뷰의 SQL 열 또는 sys.dm_exec_sql_text 동적 관리 뷰의 텍스트 열을 참조하여 쿼리가 매개 변수화 되었는지 확인할 수 있다.

 

XML 데이터 형식에서 허용된 중첩 수준 수의 제한으로 인해 sys.dm_exec_query_plan은 중첩 요소의 128개 수준을 충족하거나 초과하는 쿼리 계획을 반환할 수 없다. SQL Server 2005 SP2 이하 버전에서는 6335 오류가 반환되며 그 이상 버전에서는 NULL 값이 반환 된다.

 

아래 실습을 통해서 query_plan 값이 NULL 이 반환되는 경우를 살펴본다. 아래 스크립트는 리컴파일을 실행하여 일부 실행계획을 반환하지 못하는 경우이다.

use tempdb

go

 

create table t1 (c1 int)

go

 

create table t2 (c1 int)

go

 

create procedure p_test @option int

as

 

if @option >= 2

select * from t1 option (recompile)

 

if @option >=1

select * from t2 option (recompile)

go

 

exec p_test 1

go

 

 

SELECT

    plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, s3.query_plan AS text_query_plan

FROM sys.dm_exec_cached_plans s1

    CROSS APPLY sys.dm_exec_sql_text(plan_handle) s2

    CROSS APPLY sys.dm_exec_query_plan(plan_handle) s3

WHERE object_name ( s2.objectid, s2.dbid) = 'p_test'

 

 

 

 

[참고자료]

 

 

2016-10-11 / 강성욱 / http://sqlmvp.kr

 

 

MS SQL, SQL Server, Query Plan, sys.dm_exec_query_plan, 실행계획, 쿼리 튜닝, 쿼리 플랜, DMV

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

데이터베이스에서 사용자 삭제 오류  (0) 2016.12.08
SQL Server 2016 자동 통계 업데이트 임계값 변경  (1) 2016.10.29
SQL Server 2016 Multiple Log Writer Workers  (0) 2016.10.08
SQL Server 2016 Larger Data File Writes  (0) 2016.10.06
SQL Server 2016 향상된 업데이트 스케줄링 알고리즘  (0) 2016.09.29

SQL Server 2016 Multiple Log Writer Workers

 

  • Version : SQL Server 2016

 

SQL Server 2016에서 소개된 multiple log writer 작업은 최대 4개의 작업자가 로그 쓰기를 가능하도록 확장되었다. 즉 LDF 쓰기 성능이 개선되었다. 수년동안 로그장치는 미디어의 스핀들 및 하드웨어 캐시의 능력에 의해 제한이 있었다. 최근 하드웨어의 발전과 SSD, Flash유형의 스토리지의 성능 발전으로 기존의 로그쓰기를 더 많이 수용할 수 있게 되었다.

 

SQL Server 2016에서는 아래 명령을 사용하여 SQL 서버에서 현재의 로그 쓰기 작업자 수를 확인할 수 있다.

select

    session_id, status, command, scheduler_id, task_address, wait_type

from sys.dm_exec_requests

where command = 'LOG WRITER'

 

 

SQL Server의 Log writer 작업자의 생성수는 SQL Server가 시작될때 하드웨어의 NUMA 노드갯수에 따라 달라진다. 하나의 NUMA 노드가 있는경우 하나의 Log writer가 생성된다. 여러 NUMA 노드 시스템 경우 최대 4개의 log write 스레드가 생성된다.

 

만약 affinity I/O mask가 구성되어 있다면(추천하지는 않음) log writer 스레드는 CPU에 바인딩되어 affinity I/O mask가 구성된다. 그렇지 않으면 log write 스레드는 숨겨진 스케줄러에 의해 분리되어 만들어진다. 숨겨진 스케줄러는 항상 NUMA 노드 0으로 바운드 된다. 이러한 최대 log writer 수는 로그 플러시 큐에 엑세스하는 log writer 작업자의 광범위한 성능 테스트를 기반으로 결정된다.

 

[참고자료]

https://blogs.msdn.microsoft.com/psssql/2016/04/19/sql-2016-it-just-runs-faster-multiple-log-writer-workers/

 

 

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

 

SQL Server 2016, MS SQL, Log writer, multiple log writer, NUMA, 로그쓰기, 다중 로그 쓰기, SQL 로그, DB

 

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

SQL Server 2016 자동 통계 업데이트 임계값 변경  (1) 2016.10.29
Sys.dm_exec_query_plan 에서 query_plan 컬럼의 NULL 값 반환  (0) 2016.10.12
SQL Server 2016 Larger Data File Writes  (0) 2016.10.06
SQL Server 2016 향상된 업데이트 스케줄링 알고리즘  (0) 2016.09.29
SSMS에서 유효하지 않은 소유자로 데이터베이스 정보가 보이지 않는 증상  (0) 2016.09.10

SQL Server 2016 Larger Data File Writes

 

  • Version : SQL Server 2016

 

SQL Server의 WriteFileGather는 대량의 데이터파일 쓰기 요청에 사용된다. 이 로직은 더티페이지를 싱글 I/O 요청에 통합하는 것이다. 예를 들어 페이지 1:13과 1:12는 더티페이지로 하나의 WriteFileGather 작업에 통합한다.

 

SQL Server 2012 and 2014에서연속 블록을 만들려면 32페이지또는 그 이상 더이페이지에 대해 해시룩업(Hash lookups)을 수행한다.

  • Is Page 1:14 in memory (hashed) and dirty NO – End forward search for near pages
  • Is Page 1:12 in memory (hashed) and dirty YES – Include in write request
  • Is Page 1:11 in memory (hashed) and dirty NO – End of backward search for near pages

     

10년 이상의 하드웨어에서는 64K 또는 128K 에서 가장 잘 수행된다. 일부 새로운 SSD 및 플래쉬에서는1MB ~ 4MB 내부 블록킹 사이즈를 가지고 있다. 데이터파일의 전체 크기를 늘리면 확장성 및 성능이 향상되어 신속하게 읽기(Read), 수정(Modify), 쓰기(Write) 동작을 기록 한다.

 

SQL Server 2016(X64 Installations)에서8K 페이지를32페이지에서 128(1MB)페이지로 연속적으로 증가시킬때 (Lazy, Checkpoint, select into, create index and bulk insert write 작업등) 이러한 쓰기 작업은 데이터파일 쓰기 작업의 95%+ 를 차지한다. SQL Server 2016 데이터베이스에서는 하드웨어 확장을 통해 파일의 쓰기 동작을 증가시킨다.

 

[참고자료]

https://blogs.msdn.microsoft.com/psssql/2016/04/15/sql-2016-it-just-runs-faster-larger-data-file-writes/

 

 

2016-10-03 / 강성욱 / http://sqlmvp.kr

 

 

SQL Server, MSSQL, SQL 2016, Larger Data File Write, WriteFileGather, Dirty Page, 대량 파일 쓰기

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

Sys.dm_exec_query_plan 에서 query_plan 컬럼의 NULL 값 반환  (0) 2016.10.12
SQL Server 2016 Multiple Log Writer Workers  (0) 2016.10.08
SQL Server 2016 향상된 업데이트 스케줄링 알고리즘  (0) 2016.09.29
SSMS에서 유효하지 않은 소유자로 데이터베이스 정보가 보이지 않는 증상  (0) 2016.09.10
SQL Server 2016 Automatic Soft NUMA  (0) 2016.09.07

SQL Server 2016 향상된 업데이트 스케줄링 알고리즘

 

  • Version : SQL Server 2016, Azure SQL Server

 

SQL Server 2016에서는 업데이트 스케줄링에 대한 알고리즘이 향상되었다. 기존에는 큰 CPU 퀀텀 작업자(Large CPU Quantum)와 작은 작업자(Short CPU Quantum) 사이에서 스케줄링에 의해 자원 액세스에 대한 불균형이 발생할 수 있었다. 이 테스트는 SQL Server 2012 및 2014에서 백분위(percentile) 스케줄링 기반의 알고리즘에서 발견되었다.

 

다음 예제를 보면 작업자1(W1)은 읽기 및 인메모리 데이터베이스 페이지의 미리 읽기 등의 큰 작업을 하고 작업자2(W2)는 짧은 작업을 한다. 예를 들어 작업자1은 버퍼풀에서 이미 정보를 찾아 I/O 작업을 위해 양보할 필요가 없다. 작업자1은 전체 CPU Quantum을 소모할 수 있다. 한편 작업자2는 스케줄링을 배당받기 위한 작업을 수행한다. 리소스거버너 및 기타 활동에 대한 스케줄링 패턴을 그림으로 표현하면 다음과 같다.

작업자1은 작업자2보다 5배 더 많은 CPU 사이클을 받고 있다. 테스트에서는 다양한 워크로드 및 시스템 작업에서 문제를 발견했다. 작업자2가 로그쓰기(Log write)인 경우 차단 및 잠금을 보유하고 로그를 기록하는 이슈에 대해서 더 오래 걸리는 문제가 발생한다.

 

SQL Server 2016 및 Windows Azure SQL Database(WASD)는 모든 작업자가 공정한 스케줄링을 받을 수 있도록 Quantum 패턴을 모니터링 한다. SQL Server 2016에서는 스케줄링 패턴이 아래 그림과 같이 나타난다. 이 단순한 예제에서 작업자2는 비우호적인 작업자1의 독점적인 반복 quantum 작업을 방지할 수 있다.

참고 : 스케줄러 변경 사항은 2014년 3월 Windows Azure SQL Server 데이터베이스에 배포되었다.

 

[참고자료]

https://blogs.msdn.microsoft.com/psssql/2016/04/01/sql-2016-it-just-runs-faster-updated-scheduling-algorithms/

 

2016-09-28 / 강성욱 / http://sqlmvp.kr

 

 

SQL Server, MS SQL, SQL 2016, update scheduling algorithms, CPU scheduling, quantum process, 퀀텀 프로세스, 업데이트 알고리즘,

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

SQL Server 2016 Multiple Log Writer Workers  (0) 2016.10.08
SQL Server 2016 Larger Data File Writes  (0) 2016.10.06
SSMS에서 유효하지 않은 소유자로 데이터베이스 정보가 보이지 않는 증상  (0) 2016.09.10
SQL Server 2016 Automatic Soft NUMA  (0) 2016.09.07
NUMA Architecture  (0) 2016.09.07

SSMS에서 유효하지 않은 소유자로 데이터베이스 정보가 보이지 않는 증상

 

  • Version : All Version

 

SSMS(SQL Server Management Tool)에서 GUI를 사용하여 데이터베이스 속성을 확인하려고 할때, 다음과 같은 오류가 발생하는 경우가 있다.

Cannot show requested dialog.

 

Additional information:

Cannot show requested dialog.(SqlMgmt)

Property Owner is not available for Database'[XXXX]'. This property may not exist for this

object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

 

위와 같은 오류가 발생하는 원인은 설치 초기에 Windows 계정으로 생성하였는데 해당 Windows 계정이 삭제됨으로써 SQL Server에서는 알수 없는 계정으로 분류되어 GUI에서 오류가 발생한것이다. 위와 같은 오류가 발생 할 때 sp_helpdb 를 사용하여 해당 데이터베이스의 정보를 살펴보면 아래 그림처럼 소유자가 UNKNOW로 설정되어 있는 것을 확인 할 수 있다.

 

이 문제를 해결 하기 위한 방법으로 sp_changedbowner명령을 사용하여 유효한 계정으로 소유자를 변경한다.

 

소유자 변경을 하고 데이터베이스 정보를 조회해 보면 변경된 소유자를 확인할 수 있으며 GUI에서 정상적인 데이터베이스 정보 조회가 가능하다.

 

데이터베이스 소유자 속성이 유효하지 않은 경우 아래와 같은 메뉴에서 GUI 속성 페이지를 시작 할 수 없다.

  • 데이터베이스 속성
  • 데이터베이스 미러링
  • 로그전달
  • 기타 다른 데이터베이스 속성 창

 

[참고자료]

https://www.mssqltips.com/sqlservertip/2477/property-owner-is-not-available-for-database-ssms-error/

 

2016-09-09 / 강성욱 / http://sqlmvp.kr

 

MS SQL, SQL Server, SSMS, SSMS Error, 데이터베이스 소유자, sp_changedbowner, Property Owner

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

SQL Server 2016 Larger Data File Writes  (0) 2016.10.06
SQL Server 2016 향상된 업데이트 스케줄링 알고리즘  (0) 2016.09.29
SQL Server 2016 Automatic Soft NUMA  (0) 2016.09.07
NUMA Architecture  (0) 2016.09.07
SQLCMD 유틸리티 사용하기  (0) 2016.08.31

SQL Server 2016 Automatic Soft NUMA

 

  • Version : SQL Server 2016

 

각 프로세서 그룹에는 자체 메모리가 있으며 자체 I/O 채널이 있는 경우도 있다. 각 CPU는 일관된 방법으로 다른 그룹과 연결된 메모리에 액세스 한다. 각 그룹을 NUMA 노드라 한다. 다른 NUMA 노드와 연결된 메모리보다 로컬 메모리를 액세스하는것이 훨씬 더 빠르다. NUMA 하드웨어에서는 일부 메모리 영역이 실제로 나머지 영역과 다른 버스에 있다. NUMA는 로컬 메모리와 외부메모리를 사용하므로 다른 영역에 비해 일부 메모리 영역에 액세스하는 시간이 오래 걸린다.

 

 

SQL Server 2016에서는 하드웨어 레이아웃 정보를 바탕으로 NUMA노드당 8개 이상의 CPU 보고시 자동으로 Soft NUMA를 구성한다. 파티션 트리거는 다양한 조절을 통해 데이터베이스 엔진의 향상된 확장성 및 성능을 조절한다. 자동 Soft NUMA는 논리적 CPU 비율, 총 CPU 개수 및 기타 요소를 고려하여 논리적으로 8개 이하의 CPU를 포함하는 각각의 노드를 만든다.

 

 

Soft NUMA가 적용되면 에러로그에서 다음과 같은 문구를 확인할 수 있다.

Automatic soft-NUMA was enabled because SQL Server has detected hardware NUMA nodes with greater than 8 logical processors.

 

DMV를 통해서도 NUMA 활성에 대한 값을 확인할 수 있다.

select cpu_count, softnuma_configuration_desc, softnuma_configuration from sys.dm_os_sys_info

 

아래 그림은 CPU 30 core를 할당하였을때와60core를 할당하였을때의 자동으로 NUMA그룹이 할당된 것을 나타낸 것으로 CPU 할당은 SQL 시작 매개변수를 지정하여 테스트하였다.

 

[CPU 30 core]

 

[CPU 60 core]

 

 

자동 Soft NUMA 동작은 하이퍼스레딩(HT/logical processor)을 인지하여 최적의 노드 배치를 결정할때 CPU논리 정보를 조회화고 노드에서의 성능 변동을 초래할 수 있는논리적 및 물리적 노드 그룹의 교차 액세스를 방지하기 위해 사용된다. 또한 백그라운드 프로세스의 대부분은 각 노드내에서 생성된다. 분할 및 추가 노드의 생성은 백그라운드로 처리된다. 예를 들어 각 노드는 네트워크 활동을 수신할 수 있는 작업자를 포함하고 암호화 작업을 수행한다. Soft NUMA 구성으로 생성된 추가 노드는 리스너, 스케일링 및 네트워크 및 암호화 기능의 수를 증가 시킨다.

 

[참고자료]

 

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

 

SQL Server, SQL 2016, NUMA, Soft NUMA, Non uniform memory access, NUMA 아키텍처, Automatic Soft NUMA, Memory Node, NUMA NODE

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

SQL Server 2016 향상된 업데이트 스케줄링 알고리즘  (0) 2016.09.29
SSMS에서 유효하지 않은 소유자로 데이터베이스 정보가 보이지 않는 증상  (0) 2016.09.10
NUMA Architecture  (0) 2016.09.07
SQLCMD 유틸리티 사용하기  (0) 2016.08.31
In-Memory 최적화 테이블을 사용하여 임시 테이블 및 테이블 변수 성능 향상  (0) 2016.08.25

SQL Server DBA

(SQL Server Technical Assistance Group)

 

작성일자(2012년, 01월, 28일)

문서등급(일반)

 

 

문서 정보

문서 정보

NUMA 설정 가이드

 

연락처

SQLTAG (SQL Server Technical Assistance Group) http://www.sqltag.org

 

문서 히스토리

버전

일자

이력사항

작성자

승인자

1.0

2012.01.28

최초작성

강성욱

 

.

.

.

.

.

 

 

저작권

Copyright 2012 SQLTAG All Rights Reserved.

 

SQLTAG는 이 문서의 내용을 예고 없이 변경할 수 있습니다.

 

 

목차

1.    NUMA    5

1.1    NUMA(Non-Uniform Memory Access)    6

1.1.1    NUMA 와SQL Server버전    6

1.2    NUMA 아키텍처    7

1.2.1    NUMA 와 SMP 차이점    8

1.2.2    NUMA 구성에 영향을 주는 설정    9

1.3    하드웨어 NUMA    10

1.3.1    하드웨어 NUMA 구성 무시    10

1.4    소프트 NUMA    11

1.4.1    소프트 NUMA 구성 무시    11

1.5    하드웨어 NUMA 와 소프트 NUMA의 메모리 공유 차이점    12

1.6    NUMA 시나리오    13

1.6.1    NUMA 선도호에 대한 포트 없음.    13

1.6.2    우선 순위 응용 프로그램의 성능 향상을 위해 여러 개의 노드에 단일 포트 연결.    13

1.6.3    여러 개의 노드에 여러 개의 포트 연결.    14

1.7    SQL Server의 NUMA 설정    15

1.7.1    소프트 NUMA 레지스트리 설정    15

1.7.2    NUMA 노드에 TCP/IP 매핑    15

1.7.3    어플리케이션 사용    18

1.8    활용    20

1.8.1    배채(Batch)작업 활용    20

1.8.2    게임 서비스에서 활용    20

1.9    참고 자료.    21

 

  1. NUMA

Microsoft SQL Server는 NUMA(Non-Uniform Memory Access)를 인식하며 특수한 구성 없이 NUMA 하드웨어에서 원활하게 작동한다. 클럭 속도와 프로세서 수가 증가할수록 이러한 추가 처리 능력을 사용하는 데 필요한 메모리 대기 시간을 줄이기가 더 어려워 진다. 이러한 문제를 피하기 위해 하드웨어 공급업체에서는 대용량의 L3 캐시를 제공하지만 이는 제한적인 해결책일 뿐이다. NUMA 아키텍처는 이 문제에 대한 포괄적인 해결책을 제공 한다. SQL Server는 응용 프로그램을 변경할 필요 없이 NUMA 기반 컴퓨터를 활용하도록 디자인 되었다. 이제 그 구성을 자세히 알아보고 현재 운영중인 서버에서 비효율적인 구성이 있으면 고쳐보길 바란다. 먼저 당부하고 싶은 것은 서버의 설정을 바꾸는 것은 대단히 위험한 행위이다. 구성 변경 시 충분한 검토와 테스트를 수행 한 후 적용하길 바란다.

  1. NUMA(Non-Uniform Memory Access)

    작은 프로세서 집합에 사용되는 시스템 버스를 여러 개 구성하는 것이 일반적인 하드웨어 추세이다. CPU 개수가 증가하고 메모리 용량이 증가하는 추세에 대용량 서비스를 할 때 기존의 UMA 방식으로 메모리를 공유하여 사용하게 되면 메모리 대기 시간이 길어 진다. 이를 해결하기 위해 공유 메모리를 전용 메모리로 할당 함으로써 효율성을 높인다.

     

    SQL Server는 응용 프로그램을 변경할 필요 없이 NUMA 기반 컴퓨터를 활용하도록 디자인 되었다.

  2. NUMA 와SQL Server버전

    소프트 NUMA 구성시 SQL Server 2000 SP4 이상 지원되며 SQL Server 2005 이상 사용 할 것을 권장 한다.

     

  3. NUMA 아키텍처

    각 프로세서 그룹에는 자체 메모리가 있으며 자체 I/O 채널이 있는 경우도 있다. 그러나 각 CPU는 일관된 방법으로 다른 그룹과 연결된 메모리에 액세스 한다. 각 그룹을 NUMA노드라 한다.

    다른 NUMA 노드와 연결된 메모리보다 로컬 메모리를 액세스 하는 것이 훨씬 빠르다. NUMA 하드웨어에서는 일부 메모리 영역이 실제로 나머지 영역과 다른 버스에 있다. NUMA는 로컬 메모리와 외부 메모리를 사용하므로 다른 영역에 비해 일부 메모리 영역에 액세스 하는 시간이 오래 걸린다.

  • 로컬 메모리 : 현재 스레드를 실행 중인 CPU와 같은 노드에 있는 메모리.
  • 외부 메모리 : 현재 실행중인 노드에 속하지 않는 메모리 (원격 메모리 라고도 함)
  • NUMA 비율 : 로컬 메모리 액세스 비용에 대한 외부 메모리 액세스 비용의 비율. (비율이 1이면 SMP(대칭 다중 처리). 비율이 높을수록 외부 메모리에 액세스 하는 비용이 증가.)

     

 

.

 

  1. NUMA 와 SMP 차이점

    NUMA 아키텍처는 SMP(Symmetric Multiprocessor) 아키텍처의 확장성 제한을 극복하기 위해 고안 되었다. SMP를 사용하면 모든 메모리 액세스가 같은 공유 메모리 버스에 게시 된다, CPU 수가 비교적 적을 때는 문제가 없지만 수 많은 CPU가 공유 메모리 버스 액세스를 위해 경쟁 할 때는 이 기능이 제대로 작동 하지 않는다.

    NUMA는 특정 메모리 버스의 CPU 개수를 제한하고 고속 연결로 여러 개의 노드를 연결하여 SMP의 병목 현상을 해결 한다.

 

  1. NUMA 구성에 영향을 주는 설정

    1. 하드웨어 NUMA는 컴퓨터 제조업체에서 제공.
    2. 소프트 NUMA(SQL Server 메모리)는 레지스트리를 사용.
    3. CPU 선호도는 affinity mask 옵션 사용.
    4. NUMA 선호도에 대한 포트구성은 TCP/IP 포트 매핑 사용.

  2. 하드웨어 NUMA

    하드웨어 NUMA가 적용된 컴퓨터 에서는 여러 개의 시스템 버스가 있다. 각 프로세서 그룹에는 자체 메모리가 있으며 자체 I/O 채널이 있는 경우도 있지만 CPU는 일관된 방법으로 다른 그룹과 연결된 메모리에 액세스 한다. NUMA 노드 내의 CPU 수는 하드웨어 공급업체에 따라 다르다. 하드웨어 NUMA가 있는 경우 NUMA 대신 인터리브 메모리를 사용하도록 구성 할 수 있다. 그러면 Windows와 SQL Server에서는 NUMA로 인식 되지 않는다.

     

     

    SQL Server에서 사용 가능한 NUMA 개수 확인 DMV

SELECT DISTINCT memory_node_id FROM sys.dm_os_memory_clerks

 

 

결과 값이 하나의 메모리 노드(노드 0)만 반환되면 하드웨어 NUMA가 없거나 해당 하드웨어가 인터리브로 구성되어 있다는 뜻 이다.

 

 

  1. 하드웨어 NUMA 구성 무시

    SQL Server는 하드웨어 NUMA에 대해 4개 이하의CPU가 있고 CPU가 하나뿐인 노드가 하나 이상 있는 경우 NUMA 구성을 무시 한다.

  2. 소프트 NUMA

    SQL Server에서는 CPU를 NUMA라는 노드로 그룹화 할 수 있다. 많은 CPU가 있고 하드웨어 NUMA가 없는 컴퓨터에서 I/O 및 지연기록 병목을 해결 할 수 있다. 4개의 물리적 NUMA 노드를 구성하면 I/O스레드와 지연 기록기 스레드가 각각 4개가 되므로 성능이 향상 된다. 하드웨어 NUMA와 결합하여 NUMA 그룹을 세분화 할 수 있다. SQL Server 스케줄러와 SQL Server 네트워크 인터페이스(SNI)만 소프트 NUMA를 인식 한다.

     

     

     

     

NODE_ID 64는 관리자 전용 DAC 이다.

 

 

  1. 소프트 NUMA 구성 무시

    여러 하드웨어 NUMA 노드의 CPU를 포함하는 소프트 NUMA를 만들 수 없다. 하지만 CPU 선호도(TCP 매핑)를 사용하여 여러 하드웨어 NUMA 노드에 액세스가 가능하다.

    EX) 예를 들어 하드웨어에 8개의 CPU(0..7)가 있고 하드웨어 NUMA 노드가 두 개(0-3 및 4-7)이면 CPU(0,1)과 CPU(2,3)을 결합하여 소프트 NUMA를 만들 수 있다. CPU(1, 5)를 사용하여 소프트 NUMA를 만들 수는 없지만 CPU 선호도를 사용하여 SQL Server 인스턴스의 선호도를 여러 NUMA 노드의 CPU로 설정할 수 있다. SQL Server에 CPU 0-3이 사용되는 경우 I/O 스레드와 지연 기록기 스레드가 각각 하나씩 있다. SQL Server에 CPU 1, 2, 5, 6이 사용되는 경우 두 개의 NUMA 노드에 액세스하게 되며 I/O 스레드와 지연 기록기 스레드가 각각 두 개씩 있다.

  2. 하드웨어 NUMA 와 소프트 NUMA의 메모리 공유 차이점

    하드웨어 NUMA는 CPU와 메모리가 하나의 세트로 할당되어 동작 하지만 소프트 NUMA의 경우에는 CPU 할당은 가능 하지만 메모리는 공유하여 사용한다.

    예를 들면 SMP 컴퓨터에 8개의 CPU가 있으며 각각 두 개의 CPU를 가진 소프트 NUMA 노드를 4개 만들면 메모리 노드 하나에서 4개의 NUMA를 모두 처리 한다.

     

     

  3. NUMA 시나리오

  4. NUMA 선도호에 대한 포트 없음.

하드웨어 NUMA 및 SQL Server의 단일 인스턴스가 있는 컴퓨터에서의 기본 상태. 모든 트랙픽은 단일 포트를 통해 들어오며 사용 가능한 모든 NUMA 노드에 라운드 로빈 방식으로 배포 된다. NUMA는 액세스 효율을 높이며 I/O 및 지연 기록기 스레드를 증가 시킨다. 설정된 연결은 해당 노드에 적용 되므로 NUMA 노드에서 자동으로 균형이 조정된다.

  1. 우선 순위 응용 프로그램의 성능 향상을 위해 여러 개의 노드에 단일 포트 연결.

 

포트 하나의 선호도를 주 우선 순위 응용 프로그램에 사용할 여러 개의 하드웨어 NUMA 노드로 설정한다. 두 번째 포트의 선호도를 두 번째 부 응용 프로그램에 사용할 다른 하드웨어 NUMA 노드로 설정 한다. 두 응용 프로그램의 메모리 및 CPU 리소스는 불균형하게 고정되어 부 응용프로그램보다 3배 많은 로컬 메모리 및 CPU 리소스를 주 응용 프로그램에 제공한다. 이 기능은 우선 적용되는 연결에 추가 리소스를 할당하여 일종의 우선 순위 스레드 실행을 제공 할 수 있다.

 

  1. 여러 개의 노드에 여러 개의 포트 연결.

 

둘 이상의 포트를 동일한 NUMA 노드로 매핑 할 수 있다. 이렇게 하면 각 포트에서 서로 다른 권한을 구성 할 수 있다. 예를 들어 해당 TCP의 끝점에서 사용 권한을 제어하여 포트를 통해 제공되는 액세스를 제한 할 수 있다. 하지만 두 포트는 똑같이 NUMA를 완벽하게 활용 할 수 있다.

 

 

  1. SQL Server의 NUMA 설정

    SQL Server는 Windows에 표시되는 하드웨어 NUMA 경계를 기반으로 CPU 그룹화에 매핑할 스케줄러를 그룹화 한다. 특정 하드웨어 NUMA 노드에서 실행되는 스레드가 메모리를 할당하는 경우 SQL Server의 메모리 관리자는 참조 효율을 위해 해당 NUMA 노드와 연결된 메모리에서 메모리 할당을 시도 한다. 마찬가지로 버퍼 풀 페이지도 하드웨어 NUMA 노드에 분산 된다. 스레드가 로컬에 할당된 버퍼 페이지의 메모리에 액세스하는 것이 외부 메모리를 액세스 하는 것보다 효율 적이다.

    각 NUMNA 노드(하드웨어 NUMA 또는 소프트 NUMA)에는 네트워크 I/O 처리에 사용되는 I/O 완료 포트가 연결되어 있어 여러 포트에 네트워크 I/O 처리를 분산하는 데 도움이 된다.

     

  2. 소프트 NUMA 레지스트리 설정

    레지스트리에 다음과 같이 CPU affinity mask를 지정 한다.

    (SQL Server 2008 R2 화면 이다. SQL 2008의 경우에는 GROUP 값을 삭제 한다.)

     

    (MSDN : http://msdn.microsoft.com/ko-kr/library/ms345357.aspx)

     

     

  3. NUMA 노드에 TCP/IP 매핑

    NUMA(Non-Uniform Memory Access) 노드 선호도에 대한 TCP/IP 포트는 SQL Server 구성 관리자에서 서버 설정으로 구성된다. 한 개 또는 여러 개의 노드에 TCP/IP 주소와 포트를 설정하려면 포트 번호 뒤에서 괄호 안에 노드 확인 비트맵(선호도 마스크)을 추가한다. 십진수나 16진수 형식으로 노드를 지정할 수 있다.

     

    설정 변경 후 서비스를 반드시 재시작 하여야 한다.

     

    (MSDN : http://msdn.microsoft.com/ko-kr/library/ms345346.aspx)

     

     

     

     

    데이터베이스가 시작 될 때 데이터베이스 엔진에서 오류 로그에 노드 정보를 기록한다. 사용할 노드의 번호를 확인하려면 오류 DMV 뷰의 노드 정보를 확인한다..

sp_readerrorlog

 

 

select * from sys.dm_os_schedulers

 

 

  1. 어플리케이션 사용

    SQL Server NUMA 구성이 완료 되었으면 매핑된 포트로 접속하여 사용 한다.

    1. 0 노드 구성 사용.

     

    1. 0노드, 2노드 구성 사용.

     

  2. 활용

    하나의 SQL Server에서 다수의 인스턴스 또는 쿼리가 수행되는 경우에 원활한 서비스를 위하여 물리적인 리소스(CPU) 분산이 필요 할 때 사용 할 수 있다.

  3. 배채(Batch)작업 활용

    CPU 부하는 작지만 자주 요청되는 쿼리(OLTP)와 자주 요청되지는 않지만 CPU 부하가 큰 쿼리(집계쿼리)가 있을 때 각각의 쿼리 타입마다 CPU 리소스를 할당하여 다른 타입의 쿼리가 같은 CPU 사용을 방지하여 성능을 보장한다.

  4. 게임 서비스에서 활용

    게임 퍼블리셔를 예로 들어 보자. 하이엔드급의 SQL Server 한 대에 여러 개의 인스턴스를 설치하여 게임 서비스를 한다고 가정 하자. 모든 자원을 동일하게 사용하는가? 인기가 높은 게임은 많은 자원을 사용 할 것이고 상대적으로 인기가 낮은 게임은 자원의 사용량이 작을 것이다.

    이때 인기가 높은 게임 하나로 인하여 다른 게임서비스에 영향을 받을 때 각 인스턴스에 NUMA 노드를 할당 함으로써 나머지 다른 서비스의 영향을 최소화 할 수 있다.

     

  5. 참고 자료.

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

SSMS에서 유효하지 않은 소유자로 데이터베이스 정보가 보이지 않는 증상  (0) 2016.09.10
SQL Server 2016 Automatic Soft NUMA  (0) 2016.09.07
SQLCMD 유틸리티 사용하기  (0) 2016.08.31
In-Memory 최적화 테이블을 사용하여 임시 테이블 및 테이블 변수 성능 향상  (0) 2016.08.25
SQL Server 2016 Tempdb 환경 설정  (0) 2016.08.16

SQLCMD 유틸리티 사용하기

 

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

 

SQLCMD 유틸리티를 사용하면 명령 프롬프트, SQLCMD 모드의 쿼리 편집기, Windows 스크립트파일 또는 SQL Server 에이전트 작업의 운영 체제(cmd.exe) 작업 단계에서 T-SQL 문, 시스템 프로시저 및 스크립트 파일을 입력 할 수 있다.

 

SQLCMD는 OLE DB 공급자를 사용하고, SSMS는 .NET Framework SqlClient를 사용하므로 서로 다른 기본 옵션이 적용될 수 있으므로 동일한 쿼리를 실행하더라도 실행되는 툴에 따라 서로 다른 동작이 수행 될 수 있다.

 

기본 구문은 아래 표와 같다.

sqlcmd

[{ { -U login_id [ -P password ] } | –E trusted connection }]

 

[ -N encrypt connection ][ -C trust the server certificate ]

[ -z new password ] [ -Z new password and exit]

[ -S [protocol:]server[\instance_name][,port] ] [ -H wksta_name ] [ -d db_name ]

[ -l login time_out ] [ -A dedicated admin connection]

[ -i input_file ] [ -o output_file ]

[ -f < codepage > | i: < codepage > [ < , o: < codepage > ] ]

[ -u unicode output] [ -r [ 0 | 1 ] msgs to stderr ]

[ -R use client regional settings]

[ -q "cmdline query" ] [ -Q "cmdline query" and exit]

[ -e echo input ] [ -t query time_out ]

[ -I enable Quoted Identifiers ]

[ -v var = "value"...] [ -x disable variable substitution ]

[ -h headers ][ -s col_separator ] [ -w column_width ]

[ -W remove trailing spaces ]

[ -k [ 1 | 2 ] remove[replace] control characters ]

[ -y display_width ] [-Y display_width ]

[ -b on error batch abort] [ -V severitylevel ] [ -m error_level ]

[ -a packet_size ][ -c cmd_end ]

[ -L [ c ] list servers[clean output] ]

[ -p [ 1 ] print statistics[colon format]]

[ -X [ 1 ] ] disable commands, startup script, enviroment variables [and exit]

[ -? show syntax summary]

 

커맨드 관리자를 실행하고 sqlcmd /?를 입력하면 각 항목에 대한 설명을 확인 할 수 있다.

 

SQLCMD의 기본 사용방법에 대해서 알아본다.

[로그인 옵션]

옵션

설명

-U

사용자 로그인 ID

-P

사용자 암호 (대소문자 구분)

기본적으로 –U (사용자 ID) 옵션과 –P (비밀번호) 옵션을 사용한다. 대소문자를 구분하므로 주의한다. 패스워드가 모니터에 그대로 나타나므로 보안에 주의할 수 있도록 한다. -U, -P 옵션을 지정하지 않은 경우 sqlcmd는 Windows 인증 모드를 사용하여 연결한다. 인증은 sqlcmd를 실행하는 사용자의 Windows 계정을 기반으로 수행 된다.

 

 

-U 옵션만 사용하였을 경우 PASSWORD를 입력하라는 메시지가 표시된다. 이때 패스워드를 입력하면 입력한 암호가 모니터에 나타나지 않는다.

 

 

옵션

설명

-S

인스턴스를 지정하여 연결

sqlcmd -U someuser -P s0mep@ssword –S ServerName\InstanceName

 

 

 

[비밀번호 변경]

옵션

설명

-z

사용자 암호 변경 (대소문자 주의)

sqlcmd -U someuser -P s0mep@ssword -z a_new_p@a$$w0rd

-Z

사용자 암호 변경 후 SQLCMD 종료 (실제 테스트 시 종료 안됨)

sqlcmd -U someuser -P s0mep@ssword -Z a_new_p@a$$w0rd

 

 

 

[쿼리 실행 옵션]

옵션

설명

-q"cmdline query"

Sqlcm가 시작될 때 쿼리를 실행, 세미콜론으로 구분된 여러 쿼리를 실행 가능. 쿼리에 GO 종결자를 사용하지 않도록 주의한다.

sqlcmd -U someuser -P s0mep@ssword -d master –q "select * from sys.sysobjects where xtype = 'u'"

-Q"cmdline query"

-q 옵션 기능과 동일하며 쿼리가 실행되고 SQLCMD가 종료된다.

 

 

[파일 출력]

옵션

설명

-o "cmdline query"

-u를 지정하면 output_file이 유니코드 형식으로 저장되며 여러sqlcmd 프로세스가 같은 파일에 동시에 쓸 수 없다. 이전 세션에서 동일한 파일이 존재할 경우 덮어 쓰여진다.

공백이 포함된 경로는 따옴표로 묶어야 한다.

 

아래 스크립트는 c:\sql_data 폴더에 test.txt라는 파일을 생성하고 sysobjects 테이블의 조회 결과를 파일에 저장한다.

:out c:\sql_data\test.txt

Select * from sys.sysobjects

Go

 

 

 

아래 스크립트는 sqlcmd 접속 상태에서 데이터베이스를 백업한다.

Backup database master to disk='c:\sql_data\master.bak'

Go

 

 

이 외에도 SQL 스크립트파일을 읽어 실행할 수도 있으며 CMD 모드로 DAC 접속을 사용하여 비상시 대처도 가능하다.

 

 

 

[참고자료]

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

https://www.simple-talk.com/sql/sql-tools/sql-server-sqlcmd-basics/

https://www.mssqltips.com/sqlservertip/2478/connecting-to-sql-server-using-sqlcmd-utility/

http://www.howtogeek.com/50295/backup-your-sql-server-database-from-the-command-line/

 

 

2016-08-30 / 강성욱 / http://sqlmvp.kr

 

SQL Server, MS SQL, SQLCMD, sql command, SSMS

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

SQL Server 2016 Automatic Soft NUMA  (0) 2016.09.07
NUMA Architecture  (0) 2016.09.07
In-Memory 최적화 테이블을 사용하여 임시 테이블 및 테이블 변수 성능 향상  (0) 2016.08.25
SQL Server 2016 Tempdb 환경 설정  (0) 2016.08.16
SQL Server 2016 향상된 즉시 파일 초기화  (0) 2016.07.07

In-Memory 최적화 테이블을 사용하여 임시 테이블 및 테이블 변수 성능 향상    

 

  • Version : SQL Server 2014, 2016

 

SQL Server tempdb는 임시 테이블, 테이블 변수, 정렬, 중간값 저장 등에 사용되며 여러 병목 구간 중 하나 이다.

2014부터 도입된 In-Memory 기술은 OLTP 환경에서 메모리 기술을 사용하여 워크로드의 성능을 향상 시켰다. 임시 테이블 및 테이블 변수 또한 In-Memory 테이블을 사용하여 성능을 개선 할 수 있다.

 

기존의 전통적인 tempdb 기반의 테이블 유형은 아래와 같은 방법으로 생성한다.

CREATE TYPE dbo.test_disk AS TABLE

(c1 INT NOT NULL,

c2 CHAR(10));

 

메모리 최적화 테이블 유형을 만드는 방법은 매우 간단하다. 옵션에 'memory_optimized=on'을 추가하면 된다. 기존 유형에 인덱스가 존재하지 않는 경우 인덱스를 추가한다. (메모리 최적화 테이블은 하나 이상의 인덱스가 필요하다.)

CREATE TYPE dbo.test_memory AS TABLE

(c1 INT NOT NULL INDEX ix_c1,

c2 CHAR(10))

WITH (MEMORY_OPTIMIZED=ON);

 

인메모리 최적화 테이블 타입은 기존 테이블 형식, 프로시저 파라메터 정의, 테이블 변수 정의, T-SQL 모듈, ad-hoc 배치에서 사용할 수 있다.

만약 인라인 테이블 변수 선언이 있으면 어떻게 해야할까? 메모리 최적화 테이블 변수는 인라인으로 선언할 수 없기 때문에 선행 작업으로 테이블 타입을 생성해야 한다. 예를 들면 저장 프로시저 또는 ad-hoc에서 변수 선언을 할 수 있다.

DECLARE @tv TABLE

( c1 INT NOT NULL ,

c2 CHAR(10));

 

메모리 최적화 테이블 변수는 위와 같이 메모리 최적화 타입을 선언하고 테이블 변수 생성시 아래와 같이 테이블 타입변수를 선언한다.

DECLARE @tv dbo.test_memory;

 

기존의 전통적인 테이블 변수와 메모리 최적화 테이블 변수에 대한 성능 비교를 위해 아래와 같은 실험을 진행 하였다.

Test Disk

Test Memory

SET NOCOUNT ON

GO

DECLARE @tv dbo.test_disk

INSERT @tv VALUES ( 1, 'n' )

INSERT @tv VALUES ( 2, 'm' )

DELETE FROM @tv

GO 10000

DECLARE @tv dbo.test_memory

INSERT @tv VALUES ( 1, 'n' )

INSERT @tv VALUES ( 2, 'm' )

DELETE FROM @tv

GO 10000

 

내가 실습한 VM 환경은 Windows Hyper-v 환경의 SQL Server 2016이었으며 4Core, 8GB Memory 이다.

Test Disk의 경우 평균 15초의 실행 시간이 소모되었으며Test Memory 의 경우 평균 2초의 실행 시간이 소모되었다. (개인마다 편차가 있기 때문에 몇 배의 성능향상이 있다고 정의하지는 않겠다.) 임시 테이블 및 테이블 변수를 메모리 최적화 테이블로 우회함으로써 성능상 많은 이점을 얻을 수 있는것을 확인 할 수 있다.

 

SCHEMA_ONLY 메모리 최적화 테이블은 기존의 글로벌(##global_temp) 및 세션 레벨(#session_temp)의 임시 테이블을 대체하여 성능을 향상 시킬 수 있으며 둘 의 차이점은 아래와 같다.

  • 메모리 최적화 테이블은 사용자 데이터베이스의 메모리 공간을 사용하며 기존의 글로벌 및 세션 임시테이블은 tempdb를 사용한다.
  • 메모리 최적화 테이블은 사용자 테이블이며 사용자 데이터베이스의 일부로 유지된다. 마지막 세션이 끊어질때 세션 및 글로벌 임시 테이블의 스키마는 사리진다.
  • 메모리 최적화 테이블은 배포시에 런타임이 없다.
  • 메모리 최적화 테이블은 하나 이상의 인덱스가 필요하다.

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/21/improving-temp-table-and-table-variable-performance-using-memory-optimization/

 

2016-08-24 / 강성욱 / http://sqlmvp.kr

 

 

SQL Server, SQL 2014, SQL 2016, In-Memory optimizer, 메모리 최적화 테이블, 임시 테이블, 테이블 변수, temp table

 

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

NUMA Architecture  (0) 2016.09.07
SQLCMD 유틸리티 사용하기  (0) 2016.08.31
SQL Server 2016 Tempdb 환경 설정  (0) 2016.08.16
SQL Server 2016 향상된 즉시 파일 초기화  (0) 2016.07.07
SQL Server 2016 LDF 생성 또는 증가 시 변경된 스탬프 패턴  (1) 2016.06.14

SQL Server 2016 Tempdb 환경 설정

 

  • Version : SQL Server 2016

 

SQL Server 2016에서는 tempdb 설정에 대해 사용자 편리하게 다양한 속성을 구성할 수 있도록 기능이 확장 되었다. 이번 기능은 DB의 성능에 대한 부분보다 사용자 편리성에 초점이 맞추어진 기능으로 변경된 기능에 대해서 설명한다. (이미 많은 DBA들은 예전 부터 tempdb에 대한 기본 값을 직접 수정하여 사용 하였다.)

 

아래 그림은 tempdb의 기본 옵션 및 설정 화면을 보여주는 것으로 SQL Server 2016 CTP3에 적용된 화면이다.

 

  1. 데이터베이스 엔진 구성 단계에서 tempdb 설정 전용탭이 워크플로우 단계에 추가 되었다.
  2. 구성 옵션
    1. Number of file : 기본설정 값 8 또는 논리적 코어 수 중에 낮은 값이 기본설정 값으로 입력된다. 보조 데이터 파일의 이름은 tempdb_mssql_#.ndf 이름을 따른다.
    2. Initial size : 지정된 크기에 따라 tempdb의 각 데이터 파일에 적용된다. 이렇게 하면 모든 파일을 같은 크기로 구성할 수 있다.
    3. Auto growth : 자동 증가는 MB로 지정되며 각 파일에 적용된다. 64MB 디폴트 값은 하나의 PFS 간격을 커버하도록 선택되었다. 이후 TF 1117 옵션이 기본적으로 활성화 되어 있다. (참고 : http://sqlmvp.kr/220726468600)
    4. Data directories :여러 폴더/볼륨에 데이터 파일을 분산하여 구동할 수 있다. 각 파일은 라운드로빈방식으로 배치된다. 예를들어 8개의 데이터 파일과 3개의 볼륨으로 지정한 경우 1,4,7은 볼륨1에 파일을 위치하며 2,5,8은 볼륨2에 파일을 위치하며 3,6은 볼륨3에 지정된다.
  3. 로그 파일
    1. Initial size : MB 크기로 로그파일이 생성된다.
    2. Auto growth : 백분율 보다 고정의 MB로 성장하는 것이 기본 값으로 적용되었으며 초기 작성시 가상 로그파일의 개수가 적게 관리되도록 적절한 크기로 제공된다.

 

Command line 명령을 이용하여 SQL Server를 설치할 경우 아래와 같은 명령중을 사용하여 tempdb 옵션을 설정 할 수 있다.

setup.exe /Q /ACTION="INSTALL" /IACCEPTSQLSERVERLICENSETERMS /FEATURES="SQL" /INSTANCENAME="SQL2016″ .. /SQLTEMPDBFILECOUNT="8″ /SQLTEMPDBFILESIZE="16″ /SQLTEMPDBFILEGROWTH="256″ /SQLTEMPDBDIR="C:\tempdb" "D:\tempdb" /SQLTEMPDBLOGFILESIZE="256″ /SQLTEMPDBLOGFILEGROWTH="0″ /SQLTEMPDBLOGDIR="E:\tempdblog"

 

 

[참고자료]

 

 

2016-08-15 / 강성욱 / http://sqlmvp.kr

 

SQL Server 2016, MS SQL, MS DBA, SQL, tempdb, T1117, tempdb configuration, SQL 2016, DB Tunning

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

SQLCMD 유틸리티 사용하기  (0) 2016.08.31
In-Memory 최적화 테이블을 사용하여 임시 테이블 및 테이블 변수 성능 향상  (0) 2016.08.25
SQL Server 2016 향상된 즉시 파일 초기화  (0) 2016.07.07
SQL Server 2016 LDF 생성 또는 증가 시 변경된 스탬프 패턴  (1) 2016.06.14
SQL Server 2016 Tempdb 성능 향상  (0) 2016.06.03

SQL Server 2016 향상된 즉시 파일 초기화

 

  • Version : SQL Server 2016

 

SQL Server는 데이터베이스 파일을 생성하거나 파일 크기가 증가할 때 파일을 0으로 초기화 하는 작업이(file initialization) 진행된다. 이때 오버헤드가 발생하기 때문에 사전에 미리 파일 크기를 증가시켜 놓거나 스토리지 성능에 따라 작은 크기로 계속해서 증가시키는 등의 방법을 사용하기도 한다.

 

SQL Server 2005부터는 즉시 파일 초기화 기능을 제공되었다. 기존 삭제된 데이터 파일을 0으로 채우지 않고 그 위에 덮어씀으로써 운영체제에서 초기화 과정을 거치지 않았다. 하지만 즉시 초기화 작업을 하기 위해서는 SQL Server 계정이 윈도우의 Administrators 그룹 권한을 가지고 볼륨 관리 작업을 수행해야 한다.

 

SQL Server 2016에서는 이전 버전까지 기본적으로 해제되어 있던 볼륨 권한 관리 제공되어 사용자가 따로 권한 설정을 하는 번거로움을 덜어준다. 볼륨 관리 권한은 로컬 보안 정책에 속하므로 장애조치클러스터(WFCS)를 사용하는 경우 각 노드에 대해 개별적으로 구성된다.

 

커맨드 명령이나 구성파일을 사용하여 SQL Server를 설치하는 경우 SQL Server 서비스 계정에 대한 즉시 파일 초기화 SQLSVCINSTANTFILEINIT 매개변수를 True로 설정한다.

Ex) setup.exe /Q /ACTION="INSTALL" /IACCEPTSQLSERVERLICENSETERMS /FEATURES="SQL" /INSTANCENAME="SQL2016″ .. /SQLSVCINSTANTFILEINIT

 

즉시 파일 초기화가 설정되면 SQL Server를 시작할 때 아래와 같은 메시지가 에러로그에 기록된다.

Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.

 

Azure에서 해당 옵션을 적용하여 테스트 한 결과 아래와 같은 성능 차이를 나타내었다.

Machine

56GB RAM, 8 Core Hyper-threaded enabled 2.2Ghz (Azure A7 Virtual Machine)

SQL Server

Out of the box, default installation

 

declare @dtStart datetime = GetUTCDate()

 

create database dbTest

ON( name = 'dbTestMDF', filename = 'd:\test\dbTest16.mdf', size=10240MB)

LOG ON( name = 'dbTestLDF', filename = 'd:\test\dbTest16.ldf')

 

select datediff(ms, @dtStart, GetUTCDate()) as [Elapsed MS]

go

 

Enabled

17844ms

Disabled

309193ms

 

 

[참고자료]

 

 

2016-07-06 / 강성욱 / http://sqlmvp.kr

 

SQL Server 2016, MS SQL, SQL Server, SQL 튜닝, SQL 즉시 파일 초기화, File Initialization, DB 튜닝, Azure 성능 튜닝, Azure 성능 최적화

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

In-Memory 최적화 테이블을 사용하여 임시 테이블 및 테이블 변수 성능 향상  (0) 2016.08.25
SQL Server 2016 Tempdb 환경 설정  (0) 2016.08.16
SQL Server 2016 LDF 생성 또는 증가 시 변경된 스탬프 패턴  (1) 2016.06.14
SQL Server 2016 Tempdb 성능 향상  (0) 2016.06.03
SQL Server Job Agent는 몇 개까지 실행이 가능할까?  (0) 2016.05.27

SQL Server 2016 LDF 생성 또는 증가 시 변경된 스탬프 패턴

 

  • Version : SQL Server 2016

 

SQL Server 데이터베이스에서 로그 파일(LDF)이 생성 또는 증가하는 경우 특정 바이트(0x00) 패턴의 스탬프가 기록된다. 이 패턴은 로그블록 레이아웃의 마지막 로그마커 표시이다.

SQL Server 7.0에 변경된 LDF 포맷 디자인은Sybase 데이터베이스의 2K 단위로 로그 블럭을 0(0x00)으로 섹터를 정렬하는 디자인을 따른 것으로, LDF 파일이 새로 생성되거나 확장할 경우 확장 로그에 대해 0x00 바이트 로그를 기록 한다.

 

SQL Server 2016에서는 0x00대신 0xC0로 스탬프를 변경하였다. (2014년 이후의 Azure SQL 경우 변경된 스탬프를 사용하고 있다.) 그렇다면 0x00에서 0xC0으로 변경되었을때 어떤 성능향상이 있을까?

 

새로운 하드웨어는 대부분 0x00 패턴으로 감지한다. 미디어 공간을 안정적으로 0으로 기록한 다음 백그라운드에서 하드웨어 기반의 가비지컬렉터를 통해 블록을 회수한다. SQL Server는 LDF확장하거나 생성할 때 트랜잭션 활동을 지원하기 위해 새로운 세그먼트를 생성해야 한다.

  • 지역 할당
  • 0x00 패턴으로스탬프 기록
  • 하드웨어는 새로 할당된 영역을 확보
  • SQL Server는 새로운 트랜잭션 레코드를 작성

 

하드웨어는 새로운 트랜잭션 로그 레코드가 공간을 반복적으로 획득하는 것을 강제로 회수할 수있다. 또 다른 사실은 스탬프 활동에 따른 비동기 I/O 간섭이다. SQL Server 는 게시(비동기 I/O핸드오프)와 작업자가 계속해서 처리할 수 있도록 설계되었다. 스탬프 활동은 자주 I/O 요청을 동기 방식으로 완료한다. 이 패턴은 SQL Server의 스케줄링에 방해를 주는 동작으로 성능 감소가 발생할 수 있다.

 

SQL Server 2016에서 변경된 0xC0 패턴 변경은 일반적인 스탬프 기법을 피하여 성능을 개선하는 것이다. 주의사항은 SQL Server 데이터파일을 저장하는데 사용되는 프로비저닝 환경이나 중복 제거 서브시스템 경우 성능에 미치는 영향을 검토해야 한다.

 

(개선된 디자인이 얼마나 성능 향상이 되었는지는 필자도 잘은 모르겠다. LDF는 메인터넌스 작업때 적절하게 미리 늘려서 할당하는것이 최선의 방법인 듯 하다.)

 

 

[참고자료]

https://blogs.msdn.microsoft.com/psssql/2016/03/22/sql-2016-it-just-runs-faster-ldf-stamped/

 

2016-06-13 / 강성욱 / http://sqlmvp.kr

 

Msslq, sql2016, sqlserver, LDF, Log design pattern, 로그 바이트 패턴, LDF Stamp, log block, DBA

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

SQL Server 2016 Tempdb 환경 설정  (0) 2016.08.16
SQL Server 2016 향상된 즉시 파일 초기화  (0) 2016.07.07
SQL Server 2016 Tempdb 성능 향상  (0) 2016.06.03
SQL Server Job Agent는 몇 개까지 실행이 가능할까?  (0) 2016.05.27
확장이벤트 사용시 주의사항  (0) 2016.05.14

SQL Server 2016 Tempdb 성능 향상

 

  • Version : SQL Server 2016

 

SQL Server에서 Tempdb는 성능 관점에서 매우 중요한 부분이다. 쿼리를 실행할 때 중간 값을 저장하기 위한 용도, 테이블변수, 임시 테이블, 정렬, 해쉬 등에 사용된다.

 

아래 링크는 SQL Server 2012, 2014에서 권장하는 tempdb 최적화 구성 방법이다.

 

Tempdb 성능 최적화에서 가장 많이 사용되는대표적인 옵션인 –T1117, -T1118 옵션으로 tempdb를 할당할때 균일 익스텐트를 할당하여 혼합 익스텐트로 인한 경합을 방지하는데 있다.

 

SQL Server 2016에서는 tempdb 의 성능 최적화를 위해 디자인이 변경되었다. 간단히 요약하면 위 두 가지 옵션이 기본적으로 적용되어 있다.

 

-T1118에 해당하는 옵션 설정은 아래 명령으로 대체되었다. (기본값OFF)

MIXED_PAGE_ALLOCATION 옵션 상태는sys.databses의is_mixed_page_allocation_on 컬럼값으로 확인 가능하다.

 

-T1117에 해당하는 옵션 설정은 아래 명령으로 대체 되었다. (기본값 : AUTOGROW_SINGLE_FILE)

 

 

Filegorup 옵션 상태는 sys.filegroups의is_autogrow_all_files 컬럼값으로 확인 가능하다.

 

[참고자료]

 

 

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

 

SQL Server, SQL 2016, mssql, tempdb, _T1117, _T1118, extents allocation, tempdb option, DB 튜닝, SQL 튜닝, DBA, SQL 최적화

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

SQL Server 2016 향상된 즉시 파일 초기화  (0) 2016.07.07
SQL Server 2016 LDF 생성 또는 증가 시 변경된 스탬프 패턴  (1) 2016.06.14
SQL Server Job Agent는 몇 개까지 실행이 가능할까?  (0) 2016.05.27
확장이벤트 사용시 주의사항  (0) 2016.05.14
Unix(Linux) timestamp(bigint) 형식을 datetime 으로 변경하기  (0) 2016.05.05

SQL Server Job Agent는 몇 개까지 실행이 가능할까?

 

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

 

SQL Server Job Agent는 몇개까지 실행이 가능할까? SQL Server Job Agent실행할 수 있는 제한범위를 초과하면 다음과 같은 에러를 나타낸다.

SQLServerAgent Error: Request to run job Job_id (from User distributor_admin) refused because the job is already running from a request by User distributor_admin. Changed database context to 'db_name'. (Microsoft SQL Server, Error: 22022)

 

SQL Agent Log에는 아래와 같은 메시지가 기록 된다

251] Step %ld of job %s is being queued for the %s subsystem

 

이러한 문제는 Job Agent의 동시 작업이 가능한 max_worker_thread 한계에 도달 했기 때문이다. sp_enum_sqlagent_subsystems 시스템 저장프로시저는 각 하위 시스템에 대한 현재 설정되어 있는 max_worker_thread 정보를 나타낸다.

 

Max_worker_thread를 변경하기 위해서는 레지스트리를 수정하여 해결 할 수 있다. 레지스트리를 잘못 수정하면 심각한 문제가 발생할 수 있으므로 주의힌다. 레지스트리를 수정하기 전에는 항상 백업본 생성하여 만약의 경우 시스템을 원래 상태로 복원할 수 있어야 한다.

 

[max_worker_thread 레지스트리 경로]

   

32비트 기본 인스턴스

인스턴스HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent\Subsystems

32비트 명명된 인스턴스

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<YourInstanceName>\SQLServerAgent\Subsystems\LogReader

64비트 기본 인스턴스

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\SQLServerAgent\Subsystems

64비트 명명된 인스턴스

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\<YourInstanceName>\SQLServerAgent\Subsystems\LogReader

 

참고사항으로 T-SQL 하위시스템 키는 기본적으로 생성되지 않는다. T-SQL 하위키를 수정하려면 Transact REG_SZ에 상수값을 추가한다. 이 숫자는 Transact SQL 하위 시스템에 대한 max_worker_thread 설정을 나타낸다.

예) TSQL:REG_SZ:100

 

 

[참고자료]

 

 

 

2016-05-25 / 강성욱 / http://sqlmvp.kr

 

SQL Server, MSSQL, SQL, DBA, SQL Agent, Job Agent, DB Agent, SQL Agent worker thread

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

SQL Server 2016 LDF 생성 또는 증가 시 변경된 스탬프 패턴  (1) 2016.06.14
SQL Server 2016 Tempdb 성능 향상  (0) 2016.06.03
확장이벤트 사용시 주의사항  (0) 2016.05.14
Unix(Linux) timestamp(bigint) 형식을 datetime 으로 변경하기  (0) 2016.05.05
SQL Server 2016 DBCC CHECK 작업 성능 향상  (0) 2016.03.27

확장이벤트 사용시 주의사항

 

  • Version : SQL Server 2012, 2014, 2016

 

SQL Serve Extended Event (xevent, 확장이벤트)는 다양한 이슈 및 성능을 해결하기 위한 매우 좋은 도구이다. 하지만 확장이벤트는 사용방법에 따라 오버헤드가 발생하기 때문에 주의하여야 한다.

 

아래 사례는 Microsoft CSS SQL Server Engineers 공식 블로그에 게시된 내용으로 고객 사례를 바탕으로 주의점을 설명하였다. 자세한 내용은 원문을 참고 한다.

 

고객 사례는 간단한 쿼리를 실행하는데 오랜 시간이 걸리데, 덤프 및 호출 스택을 분석한 결과 xevent가 포함된 것을 확인하였고 높은 오버헤드를 일으킬 수 있는 활성화된 이벤트를 발견하였다. 확장이벤트에는 scan_start, scan_stop, wait_info등 다양한 이벤트가 매분마다 수백만 이벤트가 캡처되고 있었다. 특히 모든 이벤트에 대해 SQL_TEXT를 수집하여 높은 오버헤드를 유발 하였다. (아래 그림 참고)

 

 

 

 

위에 언급된 이벤트(scan_started, scan_stopped, wait_info)는 장시간 캡처에는 적합하지 않다. 특히 SQL_TEXT를 포함한 wait_info 캡처는 스케줄러 대기가 발생할때마다 생성되므로 매우 빈번하게 이벤트가 발생하여 이번 오버헤드의 주요 원인 이었다. 이처럼 대규모 작업에서는 SQL_TEXT를 배제하는 것이 좋다.

 

확장이벤트는 다양한 문제를 해결하는데 좋은 도구이지만 무분별한 사용은 많은 오버헤드를 발생시키므로 필터를 잘 적용하여 오버헤드가 발생하지 않도록 설정해서 사용해야 한다.

 

[참고자료]

https://blogs.msdn.microsoft.com/psssql/2016/02/24/not-every-extended-event-is-suited-for-all-situations/

 

 

2016-05-13 / 강성욱 / http://sqlmvp.kr

 

 

MS SQL, SQL Server, 확장이벤트, Xevent, Extended Event, DB 튜닝, DB 성능, DB 트러블슈팅, DB 모니터링

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

SQL Server 2016 Tempdb 성능 향상  (0) 2016.06.03
SQL Server Job Agent는 몇 개까지 실행이 가능할까?  (0) 2016.05.27
Unix(Linux) timestamp(bigint) 형식을 datetime 으로 변경하기  (0) 2016.05.05
SQL Server 2016 DBCC CHECK 작업 성능 향상  (0) 2016.03.27
Query Rule Off를 사용한 SQL Server 옵티마이저 비활성화  (0) 2016.03.03

Unix(Linux) timestamp(bigint) 형식을 datetime 으로 변경하기

 

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

 

데이터를 운용하다 보면 시간(날짜)와 관련해서 다양한 데이터 형식 사용된다. 이번 포스트는 유닉스(리눅스)에서 전달받은 timestamp 형식을 SQL Server에서 datetime 형식으로 변경하는 방법에 대해서 알아본다.

 

Timestamp 값은 1700-01-01 00:00:00부터 시작하는 밀리세컨드(ms)값이다. dateadd() 함수를 사용하여 간단히 해결 할 수 있다. Dateadd() 함수는 int 형식을 지원하므로 ms -> s로 변경하기 위해 timestamp/1000으로 계산해야 한다.

declare @timestamp bigint

set @timestamp = 1334034120644

 

Select dateadd(S, @timestamp/1000, '1970-01-01')

 

 

아래와 같이 함수로 만들어서 편리하게 사용할 수 있다.

CREATE FUNCTION dbo.fn_TimestampToDateTime (@Timestamp BIGINT)

RETURNS DATETIME

AS

BEGIN

SET @Timestamp = @Timestamp/1000

RETURN (SELECT DATEADD(second,@Timestamp, CAST('1970-01-01 00:00:00' AS datetime)))

END;

GO

 

 

[참고자료]

http://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server

 

 

2016-05-04 / 강성욱 / http://sqlmvp.kr

 

SQL Server, SQL, Timestamp, datetime, convert timestamp to datetime, dateadd(), 시간함수, 타임스탬프

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

SQL Server Job Agent는 몇 개까지 실행이 가능할까?  (0) 2016.05.27
확장이벤트 사용시 주의사항  (0) 2016.05.14
SQL Server 2016 DBCC CHECK 작업 성능 향상  (0) 2016.03.27
Query Rule Off를 사용한 SQL Server 옵티마이저 비활성화  (0) 2016.03.03
시스템 관리자 권한이 없는 특정 프로그램에서 Trace Flag 사용하기  (0) 2016.02.01

SQL Server 2016 DBCC CHECK 작업 성능 향상

  • Version : SQL Server 2016

대부분의 SQL Server (MULTI_OBJECT_SCANNER* base) 환경에서 DBCC CHECKS* (checkdb, checktable,…) 검사를 실행하는 동안 대기(wait)를 경험하게 된다. 내부적으로 DBCC CHECKS*를 실행하면 페이지 스캔 코디네이터 디자인(MultiObjectScanner)을 사용한다. SQL Server 2016 버전부터는 DBCC 작업시 이전보다 훨씬 더 확장 할 수 있도록 내부설계를 인모메리 최적화(Hekaton) 객체에 사용되는 것과 유사한 락(lock) 디자인을 적용하여 작업 성능을 향상 시켰다.

다음 차트는 동일한 1TB 데이터베이스의 테스트 결과를 나타낸다.

  • MultiObjectScanner : SQL Server 2016 이전 버전의 디자인
  • CheckScanner : SQL Server 2016 적용된 새로운 디자인

새롭게 적용된 디자인은 8코어 이상의 CPU에서 더 나은 결과를 제공한다. 하지만 8코어 이상에서는 성능이 크게 나아지지 않았다. 기존 디자인의 경우 코어가 많을수록 더 많은 실행 시간이 요구 되었다.

[데모 시나리오]

  1. SSMS에서 SQL Server 인스턴스에 연결 (2012 또는 2014)
  2. 새 쿼리창에서 데모 스크립트를 실행하고 실행 시간을 기록
  3. 동일한 하드웨어에서 SQL Server 2016 CTP 3.0 이상의 버전 인스턴스를 사용하여 데모 스크립트를 실행.

[시스템 사양]

Machine

32GB RAM, 4 Core Hyper-threaded enabled 2.8Ghz, SSD Storage

SQL Server

Out of the box, default installation

[데모 결과 (7번 실행)]

SQL Server 2014

12880ms

SQL Server 2016

1676ms

[데모 스크립트]

use tempdb

go

set nocount on

go

if(0 <> (select count(*) from tempdb.sys.objects where name = 'tblDBCC') )

begin

    drop table tblDBCC

end

go

create table tblDBCC (

iID int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,

strData nvarchar(2000) NOT NULL

)

go

--Insert data to expand to a table that allows DOP activities

print 'Populating Data'

go

begin tran

go

insert into tblDBCC (strData) values ( replicate(N'X', 2000) )

while(SCOPE_IDENTITY() < 100000)

begin

    insert into tblDBCC (strData) values ( replicate(N'X', 2000) )

end

go

commit tran

go

-- CheckDB

declare @dtStart datetime

set @dtStart = GETUTCDATE();

dbcc checkdb(tempdb)

select datediff(ms, @dtStart, GetUTCDate()) as [Elapsed DBCC checkdb (ms)]

go

[참고자료]

https://blogs.msdn.microsoft.com/psssql/2016/02/25/sql-2016-it-just-runs-faster-dbcc-scales-7x-better/

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

SQL Server 2016, DBCC CHECKDB, DBCC 성능 향상, DBCC Scales 7x Better, MS SQL, MULTI_OBJECT_SCANNER,

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

확장이벤트 사용시 주의사항  (0) 2016.05.14
Unix(Linux) timestamp(bigint) 형식을 datetime 으로 변경하기  (0) 2016.05.05
Query Rule Off를 사용한 SQL Server 옵티마이저 비활성화  (0) 2016.03.03
시스템 관리자 권한이 없는 특정 프로그램에서 Trace Flag 사용하기  (0) 2016.02.01
In-Memory OLTP 환경에서 체크포인트 작업과 디스크 부족 경고  (0) 2016.01.26

Query Rule Off를 사용한 SQL Server 옵티마이저 비활성화

 

  • Version : SQL Server 2012, 2014, 2016

 

SQL Server는 쿼리를 실행 할 때 다양한 비용을 계산하여 최적의 계획을 생성하고 사용한다. 여러 사용 가능한 실행 계획 중에 비용이 가장 적게 드는 하나의 실행 계획을 선택하는 프로세스를 최적화라 한다.

 

쿼리 처리에 대한 아키텍처는 아래 링크를 참고 한다.

 

SQL Server에서 쿼리를 실행 할 때 옵티마이저가 최적의 실행 계획을 만들어 사용할 수도 있지만 쿼리를 튜닝하는 과정에서 옵티마이저의 최적화를 사용하지 않도록 비활성화 할 수 있다. 문서화 되지 않은 Query Rule Off 명령을 사용하여 옵티마이저를 비활성화 하는 방법에 대해서 살펴본다.

 

아래 스크립트를 실행하면 현재 SQL Server의 옵티마이저 규칙을 확인할 수 있다. 매우 많은 규칙 목록이 있음을 확인할 수 있다.

USE master

GO

 

DBCC TRACEON(3604)

GO

 

DBCC SHOWONRULES

GO

 

DBCC SHOWOFFRULES

GO

 

 

규칙 이름은 대부분 해석하기 쉽게 되어 있다. 몇 가지 규칙에 대한 설명을 살펴보면 다음과 같다.

Rule Name

Description

JNtoNL

Join to Nested Loop

JNtoHS

Join to Hash

JNtoSM

Join to Sort Merge

LOJNtoNL

Left Outer Join to Nested Loop

LSJNtoHS

Left Semi join to Hash

LASJNtoSM

Left Anti Semi Join to Sort Merge

 

Query Rule OFF를 적용하면 어떻게 쿼리 실행계획이 변경되는지 실습을 통해서 알아본다. 이번 실습은 AdventureWorks2012를 사용하였다.

 

아래 스크립트를 실행하여 쿼리 실행 계획을 확인해보자. 옵티마이저는 최적화 규칙으로 해시조인을 사용하여 조인 순서를 결정하였다.

USE AdventureWorks2012

GO

 

SELECT

    c.CustomerID ,

    c.PersonID ,

    c.StoreID ,

    c.TerritoryID ,

    c.AccountNumber ,

    c.rowguid ,

    c.ModifiedDate

FROM Sales.SalesOrderHeader OH

    INNER JOIN Sales.Customer C ON OH.CustomerID = C.CustomerID

WHERE OH.ShipMethodID = 1

GO

 

 

아래 스크립트는 QUERYRULEOFF JoinCommute를 사용하여 옵티마이저 비활성화와 함께 조인의 순서를 결정하지 않도록 하였다. 조인 순서가 변경된 것을 확인할 수 있다.

USE AdventureWorks2012

GO

 

SELECT c.CustomerID ,

c.PersonID ,

c.StoreID ,

c.TerritoryID ,

c.AccountNumber ,

c.rowguid ,

c.ModifiedDate

FROM Sales.SalesOrderHeader OH

INNER JOIN Sales.Customer C ON OH.CustomerID = C.CustomerID

WHERE OH.ShipMethodID = 1

OPTION( QUERYRULEOFF JoinCommute )

GO

 

 

위의 두 실행계획에서 더 나은 실행 계획을 비교하기 위해 예상 하위 트리 비용을 비교해보았다. 이렇게 하면 JoinCommute를 사용한 쿼리 계획이 더 낮은 비용을 사용하였음을 확인할 수 있다.

 

아래 스크립트는 옵티마이저가 JNtoHS 규칙을 사용하지 않도록 하였다. 옵티마이저는 JNtoHS 규칙을 제외한 나머지 규칙에서 최적화 계획은 Merge를 사용한 것을 확인할 수 있다.

USE AdventureWorks2012

GO

 

SELECT c.CustomerID ,

c.PersonID ,

c.StoreID ,

c.TerritoryID ,

c.AccountNumber ,

c.rowguid ,

c.ModifiedDate

FROM Sales.SalesOrderHeader OH

INNER JOIN Sales.Customer C ON OH.CustomerID = C.CustomerID

WHERE OH.ShipMethodID = 1

OPTION( QUERYRULEOFF JNtoHS )

GO

 

 

하위 트리 비용을 살펴보면 위의 두 상황보다 나쁜 선택을 한 것을 알 수 있다.

 

 

옵티마이저가 최적의 실행계획을 선택하기 위해서는 다양한 정보 (통계, 인덱스 등등)를 고려하여 판단하기 때문에 우리가 생각하는 이상의 복잡도를 가지고 있다. 옵티마이저를 비활성화 하여 사용하는 경우는 옵타미이저의 특성에 대해서 잘 알고 내가 의도하는 방향으로 실행 계획을 유도할 수 있을때만 사용할 수 있도록 한다.

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4175/disabling-sql-server-optimizer-rules-with-queryruleoff/

 

2016-03-03 / 강성욱 / http://sqlmvp.kr

 

SQL Server, MS SQL, 옵타미이저, Optimizer, 쿼리 최적화, 쿼리 튜닝, TRACEON, QUERYRULEOFF, Optimizer Rule, DB튜닝, SQL튜닝

 

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

Unix(Linux) timestamp(bigint) 형식을 datetime 으로 변경하기  (0) 2016.05.05
SQL Server 2016 DBCC CHECK 작업 성능 향상  (0) 2016.03.27
시스템 관리자 권한이 없는 특정 프로그램에서 Trace Flag 사용하기  (0) 2016.02.01
In-Memory OLTP 환경에서 체크포인트 작업과 디스크 부족 경고  (0) 2016.01.26
DeadLock(교착상태) 모니터 하기  (0) 2016.01.11

시스템 관리자 권한이 없는 특정 프로그램에서 Trace Flag 사용하기

 

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

 

관리자 권한이 없는 계정을 사용하는 특정 어플리케이션이 실행될 때 예약된 추적 플래그를 실행하는 방법에 대해서 알아본다.

 

유사한 케이스로 프로파일러 사용법도 있다.

 

시나리오는 다음과 같다. 특정 시스템에 추적 플래그를 설정 해야 한다. 하지만 서버에 추적 플래그를 설정하게 되면 다른 어플리케이션에 영향이 있다. 따라서 해당 어플리케이션이 실행될 때 추적플래그를 실행하고 싶다. 그런데 해당 어플리케이션을 사용하는 계정은 관리자 권한이 없다.

 

아래 예제 스크립트와 같이 프시저와 트리거를 생성하여 특정 어플리케이션이 실행될 때 트리거가 작동되어 추적 플래그를 실행할 수 있다. 아래 예제는 TF 9481을 적용하는 예제이다.

alter database master set trustworthy on

go

 

use master

 

go

create procedure proc_enable_tf

with execute as owner

as

Exec('dbcc traceon(9481)')

 

go

grant execute on proc_enable_tf to public

go

 

create TRIGGER trigger_enable_tf

ON ALL SERVER

FOR LOGON

AS

BEGIN

IF app_name()= 'Microsoft SQL Server Management Studio – Query' — replace this with your application name

begin

exec master.dbo.proc_enable_tf

end

END;

 

 

[참고자료]

https://blogs.msdn.microsoft.com/psssql/2015/12/30/wanting-your-non-sysadmin-users-to-enable-certain-trace-flags-without-changing-your-app/

 

 

2016-02-01 / 강성욱 / http://sqlmvp.kr

 

추적플래그, DBCC TRACE, TF 9481, SQL Server, MS SQL, 트리거, Trigger, with execute as owner, FOR LOGON

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

SQL Server 2016 DBCC CHECK 작업 성능 향상  (0) 2016.03.27
Query Rule Off를 사용한 SQL Server 옵티마이저 비활성화  (0) 2016.03.03
In-Memory OLTP 환경에서 체크포인트 작업과 디스크 부족 경고  (0) 2016.01.26
DeadLock(교착상태) 모니터 하기  (0) 2016.01.11
Spool 연산자와 추적 플래그 8690  (0) 2016.01.11

In-Memory OLTP 환경에서 체크포인트 작업과 디스크 부족 경고

 

  • Version : SQL Server 2014, 2016

 

SQL Server 2014 버전부터 제공되는 인메모리 OLTP 기능을 사용하는 동안에도 여전히 충돌이나 재시작시 복구 시간을 줄일수 있는 방법이 필요하다. 디스크기반의 테이블 경우 체크포인트가 발생할때 디스크에 기록되지 않은 더티페이지가 디스크에 기록(플러시) 된다. 인메모리 OLTP의 경우에도 별도의 체크포인트 파일 세트가 있으며 체크포인트 파일은 MEMORY_OPTIMIZED_DATA 파일 그룹을 생성할 때 지정한 디렉토리에 생성된다.

 

인메모리 테이블에서 체크포인트시 디스크가 부족할 경우 데이터베이스는 온라인 상태로 유지되지만 아래와 같은 오류메시지가 나타난다.

2015-12-23 21:38:23.920 spid11s     [ERROR] Failed to extend file 'f:\temp\imoltp_mod1\7ef8758a-228c-4bd3-9605-d7562d23fa76\a78f6449-bd73-4160-8a3f-413f4eba8fb300000ad-00013ea0-0002′ ('GetOverlappedResult'). Error code: 0x80070070. (d:\b\s1\sources\sql\ntdbms\hekaton\sqlhost\sqllang\fsstgl

2015-12-23 21:40:49.710 spid11s     [ERROR] Database ID: [6]. Failure to allocate cache file. Error code: 0x80070070. (d:\b\s1\sources\sql\ntdbms\hekaton\engine\hadr\ckptagent.cpp : 890 – 'ckptAgentAllocateCfp')

 

수동으로 체크포인트를 실행할 경우 아래와 같은 오류메시지가 나타난다.

Msg 41315, Level 16, State 0, Line 5

 

Checkpoint operation failed in database 'testdb'.

 

디스크 부족 경고가 발생하면 다음과 같은 방법으로 해결할 수 있다.

  1. 디스크를 추가할 수 있으면 디스크를 추가한다.
  2. 디스크를 추가할 수 없으면 더 큰 공간이 있는 디스크에 MEMORY_OPTIMIZED_DATA 파일을 추가 생성한다.

ALTER DATABASE testdb ADD FILE (name='imoltp_mod1′, filename='f:\checkpoint\imoltp_mod1′) TO FILEGROUP imoltp_mod

 

  1. 1단계 또는 2단계 작업이 완료되었으면 수동으로 체크포인트를 실행한다.

 

 

[참고자료]

https://blogs.msdn.microsoft.com/psssql/2015/12/23/what-to-do-when-you-run-out-of-disk-space-for-in-memory-oltp-checkpoint-files/

 

 

2016-01-26 / 강성욱 / http://sqlmvp.kr

 

 

SQL Server, SQL 2014, SQL2016, 인모메리 테이블, 메모리 최적화 테이블, 체크포인트, MEMORY_OPTIMIZED_DATA, In-Memory table

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

Query Rule Off를 사용한 SQL Server 옵티마이저 비활성화  (0) 2016.03.03
시스템 관리자 권한이 없는 특정 프로그램에서 Trace Flag 사용하기  (0) 2016.02.01
DeadLock(교착상태) 모니터 하기  (0) 2016.01.11
Spool 연산자와 추적 플래그 8690  (0) 2016.01.11
.NET 4.6.1에 변경된 Multisubnet 기본 수신기 동작  (0) 2015.12.04

DeadLock(교착상태) 모니터 하기

 

DeadLock(교착상태)?

한 태스크에서 잠근 리소스를 다른 태스크에서 잠그려고 하여 둘 이상의 태스크가 서로 영구적으로 차단하는 현상. (서로 맞물린 상태의 차단)

  • 순환 교착(cycle DeadLock) : 서로 다른 개체를 차단할 때 발생
  • 변환 교착(Conversion DeadLock) : 같은 대상에 대해 둘 이상의 세션이 동시에 잠금을 변경하려고 할 때 발생

 

교착 상태를 일으킬 수 있는 리소스

  • 잠금 : 개체, 페이지, 행, 메타데이터, 응용 프로그램 등의 리소스에 대한 잠금을 획득하려고 대기 하는 경우.
  • 작업자 스레드 : 사용 가능한 작업자 스레드를 대기하는 태스크가 교착 상태를 일으킬 수 있음.대기 태스크가 모든 작업자 스레드를 차단하는 리소스를 소유하는 경우 교착상태 발생.
  • 메모리 : 동시 요청이 사용 가능한 메모리보다 많은 메모리 부여를 대기하는 경우.
  • 병렬 쿼리 실행 관련 리소스 : 병렬쿼리에 속하지 않는 하나 이상의 다른 프로세스를 포함할 경우 서로 차단하여 발생. 서버에서 새 쿼리 실행이 시작되거나 시스템에 작업자 스레드가 부족하여 시스템 작업이 예기치 않게 변경되면 교착 상태가 발생.
  • MARS(Multiple Active Result Sets)리소스 : MASRS에서 여러 활성 요청의 인터리브를 제어하는데 사용.

DeadLock 감지하기.

교착 상태 검색은 데이터베이스 엔진 인스턴스의 모든 태스크에 대한 검색을 주기적으로 시작하는 잠금 모니터에서 수행합니다.

  • 기본 간격은 5초로 수행.
  • 잠금 모니터 스레드가 교착 상태를 발견하면 잠금 상태의 빈도에 따라 5초에서 최하 100밀리초까지 교착 상태 검색 간격이 짧아 짐.
  • 잠금 모니터 스레드가 교착 상태 검색을 중지하면 데이터베이스 엔진은 검색 간격을 다시 5초로 늘림.

 

실습 환경

CREATE PROC SESSION_1

AS

BEGIN TRAN

    UPDATE TBL_A SET COL5 = COL5 * 2 WHERE COL1 = 100

    

    WAITFOR DELAY '00:00:03';

    

    UPDATE TBL_B SET COL5 = COL5 * 2 WHERE COL1 = 100

ROLLBACK TRAN

GO

 

CREATE PROC SESSION_2

AS

BEGIN TRAN

    UPDATE TBL_B SET COL5 = COL5 * 2 WHERE COL1 = 100

    

    WAITFOR DELAY '00:00:03';

    

    UPDATE TBL_A SET COL5 = COL5 * 2 WHERE COL1 = 100

ROLLBACK TRAN

GO

 

서로 다른 세션에서 SESSION_1, SESSION_2 실행

  1. sp_lock

     

  2. DBCC TRACEON (1204, -1)

    교착 상태와 관련된 각 노드에 의해 형식이 지정된 교착 상태 정보를 보고.

     

  3. DBCC TRACEON (1222, -1) --SQL Server 2005 이상사용

    프로세스별 및 리소스별 순서로 교착상태의 정보를 보고

    

 

  1. Profiler

     

  2. Perfmon

     

  3. DMV

SELECT TEXT, BLOCKING_SESSION_ID, COMMAND, DATABASE_ID, WAIT_TYPE,WAIT_RESOURCE, *

FROM SYS.DM_EXEC_REQUESTS AS REQUEST

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(REQUEST.SQL_HANDLE) AS SQL_TEXT

WHERE SESSION_ID > 50 AND SESSION_ID <> @@SPID

AND BLOCKING_SESSION_ID <> 0

 

 

  1. Job Alert

 

 

DeadLocK 최소화 하기

  • 적절한 인덱스 설정.
  • 자원의 한쪽 방향 액세스,
  • 짧은 트랜잭션
  • 테이블 크기 최소화
  • 잠금 제한 시간 설정(SET LOCK_TIMEOUT)
  • 적절한 격리 수준(READ UNCOMMITTED)
  • 하드웨어 성능 향상
  • 지속적인 모니터링

 

 

그런데…실제 서비스에선 이렇게 단순한 그래프로 남지 않는다는거…

아놔..bb

 

참조 및 참고 사이트

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

시스템 관리자 권한이 없는 특정 프로그램에서 Trace Flag 사용하기  (0) 2016.02.01
In-Memory OLTP 환경에서 체크포인트 작업과 디스크 부족 경고  (0) 2016.01.26
Spool 연산자와 추적 플래그 8690  (0) 2016.01.11
.NET 4.6.1에 변경된 Multisubnet 기본 수신기 동작  (0) 2015.12.04
SQL Server 2014 온라인 인덱스 리빌드와 잠금 우선순위 옵션  (0) 2015.11.26

Spool 연산자와 추적 플래그 8690

 

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

 

쿼리 실행계획을 확인하다 보면 스풀(spool) 연산자(인덱스 스풀 또는 테이블 스풀)를 볼 수 있다. 스풀 연산자는 중간 결과를 저장하기 때문에 SQL의 다시 스캔 하거나, 또는 반복 사용을 위해 다시 계산할 필요가 없도록하여 쿼리 성능을 개선하는데 도움이 된다.

 

스풀 테이블은 tempdb 데이터베이스 저장되어 쿼리 사용 기간 중에만 존재한다. 예를 들어 Nested Loops 연산자로 연산자를 다시 수행하지만 다시 바인딩할 필요가 없을 경우 입력 사항을 다시 검색하는 대신 스풀된 데이터를 사용한다. 스풀 실행 계획은 아래 그림과 같은 아이콘으로 표시 된다.

 

스풀의 경우 대부분의 성능을 향상 시킬 수 있다. 그러나 데이터의 분포가 고르지 못하거나 또는 왜곡된 데이터 추정을 사용하여 성능 저하의 원인이 되기도 한다.

추적플래그 8690은 중첩루프 안쪽의 스풀을 사용하지 않도록 설정할 수 있다. 대부분의 상황에서는 수동으로 추적플래그를 사용하여 스풀을 해제할 필요가 없다.

 

 

[참고자료]

https://blogs.msdn.microsoft.com/psssql/2015/12/15/spool-operator-and-trace-flag-8690/

https://technet.microsoft.com/ko-kr/library/ms181032(v=sql.105).aspx

 

 

2016-01-11 / 강성욱 / http://sqlmvp.kr

 

 

SQL Server, MSSQL, DB튜닝, 쿼리 튜닝, Table Spool, Trace 8690, Spool operator, tempdb

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

In-Memory OLTP 환경에서 체크포인트 작업과 디스크 부족 경고  (0) 2016.01.26
DeadLock(교착상태) 모니터 하기  (0) 2016.01.11
.NET 4.6.1에 변경된 Multisubnet 기본 수신기 동작  (0) 2015.12.04
SQL Server 2014 온라인 인덱스 리빌드와 잠금 우선순위 옵션  (0) 2015.11.26
함수 통계 정보 확인 (sys.dm_exec_function_stats)  (0) 2015.11.24

.NET 4.6.1에 변경된 Multisubnet 기본 수신기 동작

  • Version : SQL Server 2012, 2014, 2016

 

SQL Server에서 AlwaysOn을 구성하여 사용할 때 리스너는 여러 개의 IP 주소를 감지하여 가용성 그룹에 정의되어 있는 IP주소에 연결을 시도하는데 간헐적으로 연결시간 초과가 발생할 수 있다. SQL 클라이언트의 기본 동작은 DNS에서 반환된 모든 IP 주소에 연결을 시도하는데 DNS 구성에 의존하기 때문에 몇 가지 문제가 발생할 수 있다. 가장 빈번하게 발생하는 문제가 정확하지 않은 IP 반환 또는 온라인되어 있지 않은 IP 반환이다. 기본적으로 TCP 연결 시도에 대한 기본 제한시간은 21초이며 IP 주소가 온라인 상태가 아닌 경우 다음 IP를 시도하기까지 21초를 기다려야 한다.

 

.NET 4.6.1에 업데이트된 내용은 SQL 클라이언트 제공자의 기본값이 MultiSubnetFailover=True 이며 이 동작은 모둔 IP 주소를 검색하고 병렬로 모든 연결을 시도한다. IP가 온라인 상태이면 성공적으로 연결이 되며 장애조치의 경우 가용성 그룹에 다시 연결하는 최적의 방법으로 동작한다.

 

자세한 업데이트 내용 및 .NET 패키지 다운로드는 아래 링크를 참고 한다.

http://blogs.msdn.com/b/dotnet/archive/2015/11/30/net-framework-4-6-1-is-now-available.aspx

 

새로운 릴리즈를 적용하지 않아도 이전의 SQL Server 2012 가용성 그룹 수신기 또는 장애조치 클러스터 인스턴스 연결을 사용할 때 MultiSubnetFailover=True를 지정하면 장애조치 클러스터 인스턴스 또는 모든 가용성 그룹에 대해 병렬로 연결을 시도하여 서브넷 장애조치가 시행 되는동안 신속하게 TCP 연결을 다시 시도한다.

 

[참고자료]

http://blogs.msdn.com/b/alwaysonpro/archive/2015/12/01/improved-multisubnet-listener-behavior-with-newly-released-sql-client-provider-in-net-4-6-1.aspx

 

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

 

 

SQL Server, AlwaysOn, 가용성 그룹, Multisibnet, Failover Cluster, 장애조치, 고가용성, 멀티서브넷, SQL 클라이언트 공급자

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

DeadLock(교착상태) 모니터 하기  (0) 2016.01.11
Spool 연산자와 추적 플래그 8690  (0) 2016.01.11
SQL Server 2014 온라인 인덱스 리빌드와 잠금 우선순위 옵션  (0) 2015.11.26
함수 통계 정보 확인 (sys.dm_exec_function_stats)  (0) 2015.11.24
NULL 데이터가 포함된 데이터 사용 시 주의점  (0) 2015.11.20

SQL Server 2014 온라인 인덱스 리빌드와 잠금 우선순위 옵션

 

  • Version : SQL Server 2014

 

SQL Server에서 인덱스를 다시 작성하면 인덱스가 삭제된 다음 다시 생성된다. 인덱스가 생성되는 과정에서 조각화가 제거되고 지정된 채우기 비율 또는 기존 채우기 비율 설정을 기준으로 페이지를 압축하여 디스크 공간을 회수하고 인덱스 행을 연속된 페이지로 다시 정렬할 수 있다. 익스텐트가 128개 이상인 인덱스를 다시 작성하면 데이터베이스 엔진에서 실제 페이지 할당 취소와 해당 관련 잠금이 트랜잭션 커밋후까지 지연된다. 인덱스의 크기가 작은 경우에는 혼합 익스텐트에 저장되기 때문에 리빌드 후에도 조각화가 줄어들지 않는 경우가 있다.

 

 

SQL Server 2014에서 온라인 인덱스 리빌드시 처리 과정에서 SCH-M 잠금과 SCH-S 잠금을 처리해야 하기 때문에 블록이 발생한다. SCH-M 잠금은 다른 잠금 때문에 차단 시스템 둔화의 원인이 되는데 테이블에 잠금이 부여되는 다른 모든 프로세스와 호환되지 않는다. SQL Server 2014 Enterprise에서 온라인 인덱스 리빌드시 잠금 우선순위 매커니즘을 소개한다.

 

실습을 위해 테스트 테이블을 생성한다. 실습에서는 SQL Server Enterprise 또는 SQL Server Developer 에디션을 사용해야 한다.

-- Create a table with primary key

create table randomdata (

id int identity,

randomNumeric numeric (18,12),

randomDatetime datetime2,

randomVarchar varchar(100)

);

create clustered index cl_idx_id on randomdata(id)

 

--fill the table with data--on my machine this took just under 2 minutes.

set nocount on

declare @loopcounter int = 1

declare @randomNumeric numeric(18,12)

declare @randomDatetime datetime2

declare @randomVarchar varchar(1000)    

declare @randomString VARCHAR(1000)

set @randomString = 'zi g upi vsm trsf yjod o jsbr vpmvrtmd eoyj jpe ;pmh upi

br nrrm eptlomh pm upit vp,[iyrt/ [rtsj[d oyd yo,r pgt s bsvsyopm/ mpy vtoyovodomh/ kidy pbrtdtbomh js[[u gsvr'

    

while @loopcounter < 1000000

    begin

        set @randomNumeric = round(rand() * 100,10)

        set @randomDatetime = dateadd(minute,@randomnumeric,getdate())

        set @randomVarchar = SUBSTRING(@randomString,convert(int,@randomNumeric),100 )

        insert randomdata

            ( randomNumeric, randomDatetime, randomVarchar)

            values

            (@randomNumeric, @randomDatetime, @randomVarchar)

        set @loopcounter = @loopcounter + 1

end

 

온라인 인덱스 리빌드 동작을 시뮬레이션 하기위해 SSMS에서 3개의 다른 창을 생성하고 각각의 스크립트를 추가한다.

--Window 1

begin transaction

    select top 1000 * from randomdata with (holdlock)

 

--commit --Commented out on purpose so the transaction holds a lock on the table

--Window 1

begin transaction

    select top 1000 * from randomdata with (holdlock)

 

--commit --Commented out on purpose so the transaction holds a lock on the table

--Window 3

select top 10 * from randomdata

 

쿼리창1에서 스크립트를 실행하여 트랜잭션을 시작한다. 커밋 구문은 잠금 동작을 입증하기 위해서 주석처리한다. 그리고 쿼리창3을 실행하면 쿼리창1에서 트랜잭션이 실행중임에도 불구하고 쿼리3에서 데이터가 조회되는 것을 확인할 수 있다. 쿼리창1의 SPID로 sp_lock을 조회해보면 IS 및 S 잠금을 확인할 수 있다.

 

SSMS에서 쿼리창2를 실행하여 온라인 인덱스 리빌드를 실행한다. (현재 쿼리창1에서는 트랜잭션이 그대로 유지되고 있다.) 다른 창을 실행하여 현재 실행중인 쿼리2의 SPID에 대한 잠금 정보를 조회한다. 스키마 잠금을 보유하고 있지만 다음 잠금을 위한 변화를 기다리는 동안 인덱스 작업은 계속 된다.

 

기존의 인덱스가 교체될 작업이 끝나면 SCH-M 잠금을 획득해야 다음 인덱스를 다시 작성하는 과정을 진행하고 SQL Server는 SCH-S 잠금을 유지한다. 잠금 호환성에 대한 표는 마이크로소프트 웹사이트를 참고한다.

 

 

현재 쿼리창1에서 홀드 잠금이 진행중인 상태에서 쿼리창2의 온라인 인덱스 리빌드 작업이 진행 중이다. 온라인 인덱스 리빌드 작업이 SCH-M 잠금을 획득한 상황에서 쿼리창3을 실행하면 이전과 달리 데이터가 조회되지 않는다. sp_who2를 사용하여 쿼리창3의 SPID를 조회하면 쿼리창2의 SPID에 의해 차단된 것을 확인할 수 있다.

 

쿼리창1에서 현재 트랜잭션이 실행되고 있는 프로세스 커밋이 완료되면 쿼리창2의 온라인 인덱스 리빌드 작업이 다음 잠금을 획득하여 프로세스를 완료할 수 있다. 그리고 잠금으로 인해 대기하고 있던 쿼리창3도 데이터조회가 완료 된다.

 

SQL Server 2014 엔터프라이즈 버전에서는 온라인 인덱스 리빌드시 잠금에 관한 우선순위를 적용할 수 있다.

WAIT_AT_LOW_PRIORITY

온라인 인덱스 리빌드는 다른 잠금요청보다 낮은 우선순위로 대기

MAX_DURATION

분단위로 기다리는 시간

ABORT_AFTER_WAIT

MAX_DURATION이 충족된 후에 조취를 결정

  • None : 이전 동작을 따라 잠금 큐를 입력
  • Self : 온라인 인덱스 리빌드 작업 중지
  • Blockers : 블록킹 SPID를 강제 종료하고 온라인 인덱싱을 진행

 

온라인 인덱스 리빌드시 잠금 우선순위를 사용하기 위해 쿼리창2의 스크립트를 아래 스크립트로 변경한다. 변경된 스크립트는 최대 1분을 기다린 후 다음 작업을 수행한다.

--Window 2

alter index cl_idx_id on randomdata rebuild

with (ONLINE = ON(WAIT_AT_LOW_PRIORITY

        (MAX_DURATION = 1 MINUTES , ABORT_AFTER_WAIT = SELF )), fillfactor = 80)

 

이전과 같이 쿼리창1, 쿼리창2, 쿼리창3을 실행하면 이전과 다른 동작을 확인할 수 있다. 쿼리창2에 적용된 새로운 잠금 우선순위로 낮은 잠금 우선위로 실행중인 쿼리창3이 실행되고 쿼리창2의 작업은 일정 시간이 지난뒤 중지된다.

 

쿼리창2의 스크립트 옵션을 블록킹 세션을 강제로 종료 후 실행하도록 수정한 다음 실행해보자.

--Window 2

alter index cl_idx_id on randomdata rebuild

with (ONLINE = ON(WAIT_AT_LOW_PRIORITY

            (MAX_DURATION = 1 MINUTES , ABORT_AFTER_WAIT = BLOCKERS )), fillfactor = 80)

 

 

우선순위 옵션에서 MAX_Duration = 0을 사용했을 경우 아래와 같은 오류가 나타났다.

--Window 2

alter index cl_idx_id on randomdata rebuild

with (ONLINE = ON(WAIT_AT_LOW_PRIORITY

            (MAX_DURATION = 0 MINUTES , ABORT_AFTER_WAIT = SELF )), fillfactor = 80)

 

 

SQL Server에서 트랜잭션의 잠금 관리는 매우 중요하다. 잠금 옵션의 사용으로 유지보수시 효율적인 작업을 진행 할 수 있도록 한다.

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4085/using-sql-server-managed-lock-priority-for-online-index-rebuilds/

 

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

 

SQL Server, MSSQL, SQL 2014, 온라인 인덱스 리빌드, Online Index Rebuild, 인덱스 재구성, 인덱스 조각화

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

Spool 연산자와 추적 플래그 8690  (0) 2016.01.11
.NET 4.6.1에 변경된 Multisubnet 기본 수신기 동작  (0) 2015.12.04
함수 통계 정보 확인 (sys.dm_exec_function_stats)  (0) 2015.11.24
NULL 데이터가 포함된 데이터 사용 시 주의점  (0) 2015.11.20
통계정보와 실제 데이터 분포 확인하기  (0) 2015.11.17

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

 

  • Version : SQL Server 2016

 

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

 

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

 

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

USE AdventureWorks2014

GO

 

SELECT

    OH.PurchaseOrderNumber ,

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

    OD.UnitPRice ,

    OD.OrderQty ,

    OD.LineTotal ,

    dbo.ufnGetStock(OD.ProductID) RemainingStock

FROM Sales.SalesOrderHeader OH

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

GO

 

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

USE AdventureWorks2014

GO

 

SELECT *

FROM Sales.SalesOrderHeader OH

CROSS APPLY dbo.ufnGetContactInformation(OH.SalesPersonID)

GO

 

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

USE MASTER

GO

 

SELECT DB_NAME(database_id) + '.' +

        OBJECT_SCHEMA_NAME(OBJECT_ID, database_id) +

'.' + OBJECT_NAME(OBJECT_ID, database_id)

        AS Function_Name,

QS.last_execution_time ,

QS.max_worker_time ,

QS.max_physical_reads ,

QS.max_logical_reads ,

QS.max_logical_writes ,

T.Text

FROM sys.dm_exec_function_stats QS

CROSS APPLY sys.dm_exec_sql_text(sql_handle) T

 

 

 

[참고자료]

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

 

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

 

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

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

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

NULL 데이터가 포함된 데이터 사용 시 주의점

 

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

 

SQL Server에서 NULL은 0 값도 아니며 공백도 아닌 그냥 빈 값이다. 데이터를 조회할 때 NULL 값이 포함된 경우 사용자가 원하는 값이 조회되지 않을 수 있다. NULL이 포함된 데이터 조회시 어떻게 값이 다르게 표현되는지 알아본다.

 

실습에 사용된 옵션은 시스템 기본값인 SET ANSI_NULLS ON 상태이다.

 

아래 스크립트는 두 개의 테이블을 생성하여 #TestTableB 테이블에만 NULL값이 포함된 데이터를 입력 한다.

--Creating the first temporary table for testing

IF(OBJECT_ID('tempdb..#TestTableA') IS NOT NULL)

    DROP TABLE #TestTableA

 

CREATE TABLE #TestTableA (ID INT)

 

--Creating the second temporary table for testing

IF(OBJECT_ID('tempdb..#TestTableB') IS NOT NULL)

    DROP TABLE #TestTableB

 

CREATE TABLE #TestTableB (ID INT)

 

--Inserting data

INSERT INTO #TestTableA(ID) VALUES (1),(2),(5),(9),(3)

INSERT INTO #TestTableB(ID) VALUES (2),(7),(NULL),(9)

 

[NOT IN 주의]

쿼리는 서브쿼리를 사용하여 #TestTableB 테이블에 포함되어 있지 않는 데이터를 #TestTableA 에서 조회한다. 조회된 결과가 예상했던 결과인가? #TestTableB 테이블의 NULL 값으로 인해 빈 결과 집합을 생성한다.

--Checking the result of the query

SELECT ID FROM #TestTableA WHERE ID NOT IN (SELECT ID FROM #TestTableB)    

 

 

 

[집계 함수 사용시 주의]

NULL 값이 포함된 경우 집계 함수에서 결과가 다르게 표시될 수 있다. Count(*) 과 Count(Column) 의 조회 결과가 어떻게 다른지 알아본다. 테스트 테이블을 생성한다.

--Creating temporary table for testing

IF(OBJECT_ID('tempdb..#TestTable') IS NOT NULL)

    DROP TABLE #TestTable

GO

 

CREATE TABLE #TestTable ( ID INT )

 

Count(id), count(*)의 집계 결과를 조회한다. 결과가 다른 것을 확인할 수 있다. 집계 함수에 컬럼을 사용할 경우 NULL 값은 무시되는 것을 확인할 수 있다.

--Inserting data for testing

INSERT INTO #TestTable(ID) VALUES (1),(2),(NULL),(4)

 

SELECT COUNT(ID) AS 'The result of Count(ID)' FROM #TestTable

 

SELECT COUNT(*) AS 'The result of Count(*)' FROM #TestTable

 

 

 

--Inserting only NULLs

TRUNCATE TABLE #TestTable

 

INSERT INTO #TestTable(ID) VALUES (NULL),(NULL),(NULL),(NULL)

 

SELECT COUNT(ID) AS 'The result of Count(ID) when ID column contains only NULL values' FROM #TestTable

 

SELECT COUNT(*) AS 'The result of Count(*) when ID column contains only NULL values' FROM #TestTable

 

 

 

--Min , Max, AVG

TRUNCATE TABLE #TestTable

 

INSERT INTO #TestTable(ID) VALUES (1),(2),(NULL),(4),(5)

 

SELECT AVG(ID) AS Average FROM #TestTable

 

 

 

[변수 사용시 주의]

SET을 사용하여 변수에 값을 할당 할 때 SELECT 방법에 따라 결과 값이 다르게 나타난다.

DECLARE @var INT= 0

 

--Creating temporary table for testing

IF(OBJECT_ID('tempdb..#TestTable') IS NOT NULL)

    DROP TABLE #TestTable

 

CREATE TABLE #TestTable (ID INT)

 

--Inserting data for testing

INSERT INTO #TestTable(ID) VALUES (1),(2),(4)

 

--Using SELECT

SELECT @var=ID FROM #TestTable WHERE ID=3

 

SELECT @var AS 'Variable value after SELECT'

 

--Using SET

SET @var = ( SELECT ID FROM #TestTable WHERE ID=3 )

 

SELECT @var AS 'Variable value after SET'

 

 

 

DECLARE @var1 INT, @var2 INT=7

 

SET @var2 = @var1 + @var2

 

SELECT @var2 As Variable2    

 

 

 

[Group by 주의]

NULL을 포함한 그룹화에 NULL 값은 결과 집합의 하나로 포함된다.

IF(OBJECT_ID('tempdb..#TestTable') IS NOT NULL)

    DROP TABLE #TestTable

GO

 

CREATE TABLE #TestTable ( ID INT, Value INT )

 

INSERT INTO #TestTable(ID, Value) VALUES (1, 10),(2, 20),(NULL, 70),(NULL, 90),(4, 50),(5, 60)

 

SELECT ID, AVG(Value) AS Average, MAX(Value) As Maximum

FROM #TestTable

GROUP BY ID    

 

 

 

[<> (!=) 비교]

비교 연산자의 경우 대부분 NULL 값과 상수 비교 시 동일하지 않기 때문에 아래 결과가 ture 일거라고 생각한다. 하지만 알 수 없는 값과 비교 시 항상 false를 반환한다. (SET ANSI_NULLS OFF 경우 결과는 다르게 나타난다.)

 

DECLARE @a INT=1,

@b INT

 

SELECT @a AS a, @b AS b

 

IF(@a <> @b)

    SELECT 1 AS result

ELSE

    SELECT 0 AS result

 

 

 

NULL 작업을 할 때 일부 혼란스러운 경우가 있으므로 반드시 NULL 데이터 여부를 확인하고 원하는 값을 조회 할 수 있도록 특성을 파악해서 사용하는 것이 중요하다.

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4082/some-tricky-situations-when-working-with-sql-server-nulls/

 

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

 

SQL Server, SET ANSI NULL, NULL, 데이터 조회, 널값 조회, 널

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

SQL Server 2014 온라인 인덱스 리빌드와 잠금 우선순위 옵션  (0) 2015.11.26
함수 통계 정보 확인 (sys.dm_exec_function_stats)  (0) 2015.11.24
통계정보와 실제 데이터 분포 확인하기  (0) 2015.11.17
SQL Server Failover Cluster 설치시 네트워크 이름으로 인한 설치 오류  (0) 2015.11.12
SSIS 실행 로그 남기기  (0) 2015.11.09

통계정보와 실제 데이터 분포 확인하기

 

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

 

통계(Statistics)는 검색 조건이 주어질 때 해당 컬럼으로 구성되는 인덱스를 사용할지 여부를 옵티마이저가 판단의 근거로 활용되는 자료이다. 통계는 데이터가 있는 컬럼에 대해 인덱스가 만들어질 때 이 컬럼 값의 히스토그램 및 관련정보를 이용하여 만들어진다.

 

 

통계의 경우 데이터가 변경됨에 따라 그 정보도 업데이트되는데 일정 비율이상 데이터가 변경될 업데이트 된다. 간혹 특정 시점에서 쿼리가 느린 경우가 발생하는데 통계정보와 실제 데이터의 분포가 다를 경우 옵티마이저가 잘못된 판단을 하여 최적화된 플랜을 사용하지 못한 경우이다.

 

이번 실습을 통해서 현재 통계 정보를 저장하고 실재 데이터 분포를 비교하여 통계 정보가 현재의 분포를 잘 반영하고 있는지 확인해본다. 이번 실습에서는 AdventureWorks2012 데이터베이스를 사용하였다.

 

통계를 확인하는 방법은 DBCC SHOW_STATISTICS 구문을 사용한다. 이 데이터를 테이블에 저장하여 실제 데이터와 비교한다. 우선 통계 정보를 저장할 수 있는 임시테이블을 생성한다.

create table #tblHistogram

(

vData sql_variant,

range_rows bigint,

eq_rows bigint,

distinct_range_rows bigint,

avg_range_rows bigint,

actual_eq_rows bigint DEFAULT(NULL),

actual_range_rows bigint DEFAULT(NULL)

)

go

 

통계 정보를 조회하는 프로시저를 생성한다.

create procedure #spHistogram

@strTable sysname,

@strIndex sysname

as

 

dbcc show_statistics(@strTable, @strIndex) with HISTOGRAM

go

 

통계 정보를 조회하여 위에서 생성한 임시테이블에 데이터를 저장한다. 실습테이블로 Person.person 테이블을 사용하였다.

truncate table #tblHistogram

go

 

insert

into #tblHistogram (vData, range_rows, eq_rows, distinct_range_rows, avg_range_rows)

exec #spHistogram '[Person].[Person]', 'PK_Person_BusinessEntityID'

go

 

select * from #tblHistogram

 

 

각 단계의 샘플링된 최대 값이 실제 데이터의 값과 비교할 수 있도록 정보를 업데이트 한다.

-- EQ_ROWS

update #tblHistogram set actual_eq_rows = (select count(*) from [Person].[Person] with(NOLOCK) where BusinessEntityID = h.vData)

from #tblHistogram h;

 

 

 

각 샘플링 구간에 대한 실제 행수가 현재 데이터의 실제 행수와 비교한다.

-- RANGE_ROWS

with BOUNDS (LowerBound, UpperBound)

as

(

select LAG(vData) over(order by vData) as [LowerBound], vData [UpperBound] from #tblHistogram

)

update

#tblHistogram

set actual_range_rows = ActualRangeRows

from (select LowerBound, UpperBound,

(select count(*) from [Person].[Person] with(NOLOCK) where BusinessEntityID > LowerBound and BusinessEntityID < UpperBound) as ActualRangeRows from BOUNDS

) as t

where vData = t.UpperBound

go

 

 

아래 스크립트는 통계 정보와 실제 데이터 분포가 다른 구간에 대해서 조회하여 최신 통계가 반영되지 않은 정보를 확인할 수 있다.

select

vData, eq_rows, actual_eq_rows, range_rows, actual_range_rows from #tblHistogram

where eq_rows <> actual_eq_rows or range_rows <> actual_range_rows

order by vData

go

 

최신 통계가 반영되어 있지 않은 경우 통계 업데이트를 진행하여 옵티마이저가 최적의 쿼리를 수행 할 수 있도록 해야 한다. 단 통계를 업데이트하기전에 발생할 수 있는 상황을 고려하여 기존 비즈니스에 영향을 주지 않도록 해야한다.

 

[참고자료]

http://blogs.msdn.com/b/psssql/archive/2015/11/09/are-my-statistics-correct.aspx

 

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

 

MS SQL, SQL Server, DB 튜닝, DB 통계, Statistics, DBCC SHOW_STATISTICS, 데이터 샘플링, 옵티마이저, SQL 최적화, SQL 튜닝

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

함수 통계 정보 확인 (sys.dm_exec_function_stats)  (0) 2015.11.24
NULL 데이터가 포함된 데이터 사용 시 주의점  (0) 2015.11.20
SQL Server Failover Cluster 설치시 네트워크 이름으로 인한 설치 오류  (0) 2015.11.12
SSIS 실행 로그 남기기  (0) 2015.11.09
확장이벤트를 사용한 실행 계획 캡처  (0) 2015.10.29

SQL Server Failover Cluster 설치시 네트워크 이름으로 인한 설치 오류

 

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

 

SQL Server Failover Cluster 인스터스를 설치할 때 발생하는 일반적인 오류에 대해서 다루어본다. SQL Server를 설치할 때 설치시 인스턴스 구성페이지에서 아래와 같은 오류 메시지를 쉽게 볼 수 있다.

 

설치 과정에서 SQL Server 네트워크 이름 및 인스턴스 이름을 입력 후 클릭하였을 때 이 시점에서 설치 프로그램이 몇 가지 유효성을 수행한다. 그 검증이 실패할 경우 화면 하단에 오류 메시지를 확인할 수 있다. 오류 메시지를 클릭하면 메시지에 포함된 몇 가지 추가 정보를 볼 수 있다.

 

대부분 네트워크 주소가 잘못되었거나 액세스 거부가 주된 이유이다. 이러한 상황에 대한 문제 해결방법은 오류 메시지의 마지막 부문이 무엇을 의미하는지에 따라 달라진다. 이제 설치 프로그램이 네트워크 이름의 유효성 검사를 수행하는 방법에 대해 간략히 살펴본다.

 

설치 프로그램이 NetServerGetInfo라는 Windows API를 호출하고 이때 두 개의 매개변수를 전달한다. 이때 API는 다양한 결과를 반환한다.

  1. API 호출이 OS 오류코드 53을 반환한 경우는 [네트워크 경로를 찾을 수 없습니다]라는 뜻이다. 이 경우는 설치 프로그램에서 제공하는 네트워크 이름을 현재 네트워크에 같은 이름을 사용하지 않기 때문에 사용하는 것이 좋다고 알려주는 것이다. 이 경우 설치 프로그램은 다음 단계로 진행 할 수 있다.
  2. API 호출이 성공을 반환한다. 이미 같은 이름을 가진 활성 컴퓨터가 있으며 설정 화면에서 제공되는 네트워크 이름을 사용할 수 없다. 중복되지 않은 다른 이름을 사용하여 해결 할 수 있다.
  3. API 호출에서 오류코드 RPC 1701(네트워크 주소가 잘못되었습니다.), 오류코드 5(액세스 거부)를 반환한 경우

 

3번의 경우 오류를 해결 하기 위해 Active Directory에 적절한 계정이 구성되어 있는지 확인이 필요하다. 또한 서로 다른 IP 주소로 네트워크 이름을 가리키는 DNS서버에 같은 이름으로 등록된 것이 없는지 확인해야 한다.

 

반환되는 오류코드를 확인하는 방법으로는 NetServerGetInfo를 호출하는 샘플 콘솔 응용프로그램을 만들어 설치시 사용한 동일한 파라메터를 호출하여 반한되는 결과값으로 분석할 수 있다.

 

 

[참고자료]

http://blogs.msdn.com/b/psssql/archive/2015/11/10/the-given-network-name-is-unusable-because-there-was-a-failure-trying-to-determine-if-the-network-name-is-valid-for-use-by-the-clustered-sql-instance.aspx

 

 

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

 

SQL Server, MS SQL, Failover Cluster, SQL설치 오류, 장애조치 구성, NetServerGetInfo

 

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

NULL 데이터가 포함된 데이터 사용 시 주의점  (0) 2015.11.20
통계정보와 실제 데이터 분포 확인하기  (0) 2015.11.17
SSIS 실행 로그 남기기  (0) 2015.11.09
확장이벤트를 사용한 실행 계획 캡처  (0) 2015.10.29
테이블 외래키 트리 확인 및 데이터 삭제하기  (0) 2015.10.26

+ Recent posts

티스토리툴바