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

 

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

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튜닝

 

시스템 관리자 권한이 없는 특정 프로그램에서 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

함수 통계 정보 확인 (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

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

 

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

 

SSIS 실행 로그 남기기

 

  • Version : SSIS Server 2005, 2008, 2008R2, 2012, 2014, 2015

 

SSIS 패키지를 운영할 때 중요한 요소 중 하나는 로깅이다. SSIS에서는 다양한 로깅 방법을 지원한다.

  • 텍스트 파일 – 가장 기본적인 로깅
  • SQL Server 테이블 – 가장 큰 장점은 쉽게 로그 결과를 조회할 수 있다.
  • SQL Server 프로파일러 – 이벤트 프로파일 캡처할 수 있다. 추적에 기록된다.
  • Windows 이벤트 로그
  • XML 파일

 

[SQL Server 테이블에 로깅]

SQL Server 테이블에 로깅하는 방법에 대해서 알아본다. (다른 옵션들을 선택해도 설정 방법은 유사하기 때문에 응용하여 사용할 수 있다.) 로깅을 테스트하기 위해 SSIS 제어흐름에서 SQL 실행 작업과 매우 간단한 패키지를 생성하였다. 이 SQL 구문은 0으로 나누기하여 오류를 발생시키도록 하였다.

 

제어흐름에서 마우스 오른쪽 버튼을 클릭하고 로깅을 선택하거나 작업표시줄에서 SSIS 메뉴로 이동하여 로깅을 선택한다.

 

로깅의 공급자 메뉴에서SSIS log provider for SQL Server 을 선택한다.

 

 

로그 공급자를 추가한다. 하나의 패키지에 여러 공급자를 추가하는 것이 가능하다. 로그 테이블을 유지하려는 데이터베이스 연결관리자를 지정한다. 테이블을 지정하는 것은 불가능하다. SSIS에서는 특정 이름으로 테이블을 자동으로 생성한다.

 

 

작업의 왼쪽 창에서 작업을 취소하여 구성할 수 있다. 오른쪽 창에서 로그 공급자를 선택해야 한다.

 

 

마지막 단계는 로그 공급자가 SQL Server에 기록할 이벤트를 선택한다. 세부정보는 아래 탭에서 선택할 수 있다.

 

설정이 완료되면 SSIS는 dbo.sysssislog 이름으로 데이터베이스에 테이블을 생성한다.

 

각 열의 정의는 다음과 같다.

  • ID : 테이블의 기본 ID 열 (기본키)
  • Event : 로그 이벤트 유형 (예: OnError)
  • Computer : 패키지를 실행한 호스트명
  • Operator : 패키지를 실행한 사용자 ID
  • Source : 이벤트를 생성한 작업 또는 패키지명
  • Sourceid : 소스 작업 도는 패키지의 GUID
  • Executionid : 패키지를 실행한 GUID
  • Starttime : 작업의 시작시간
  • Endtime : 작업의 종료 시간
  • Datacode : 태스크 또는 컨테이너의 실행 결과를 포함할 수 있는 임의의 번호 (예 0 성공, 1 실패)
  • Databytes : 로그메시지에 대한 바이트 배열
  • Message : 로그 이벤트 메시지

 

로그 테이블 조회는 아래 스크립트로 확인할 수 있다.

SELECT

     [event]

    ,[computer]

    ,[operator]

    ,[source]

    ,[starttime]

    ,[endtime]

    ,[message]

FROM [dbo].[sysssislog];

 

 

[스크립트 작업에서 로깅]

일부 작업은 특정이벤트가 포함되어 있는 경우가 있다. 스크립트 작업에서도 ScriptTaskLogEntry 이벤트를 사용하여 로깅을할 수 있다.

 

실제로 스크립트 작업에서 로깅하려면 세개의 매개 변수를 받아들이는 Dts.log 이벤트를 사용한다.

  • Log message
  • Datacode
  • Databytes

 

첫 번째 값은 매우 중요하여 나머지 두 값은 더미 값이 될 수 있다.

 

패키지를 실행할 때 로깅 테이블에서 사용자 지정 로그를 확인할 수 있다.

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4070/integrated-logging-with-the-integration-services-package-log-providers/

 

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

 

 

MS SQL Server, MS SQL, SSIS, SSIS 로깅, Integrated Logging, SQL Server Integration Services,ETL, BI

SSRS 2016 향상된 구독(Subscription) 기능

 

  • Version : SSRS 2016

 

SQL Server 2016 Reporting Service에서 몇 가지 향상된 기능이 추가 되었다. 그 중 구독(Subscription) 기능에 대해서 향상된 부분을 알아본다.

 

[구독의 활성 및 비활성]

SSRS를 운용할 때 일시적으로 특정 레포트의 구독을 중지 해야하는 경우가 발생한다. SSRS 2016에서는 다음과 같이 구독을 선택하고 활성 및 비활성 할 수 있다.

 

구독을 선택하고 비활성화 할 경우 Status 항목에 Disabled 라고 표시되며 녹색 동그라미 부분이 비활성 상태로 표시 된다.

 

구독을 활성활 할 경우 Status는 Ready 상태로 표시된다.

 

[구독 소유자 변경]

SSRS 2016에서 현재의 소유자를 변경할 수 있다. 가입 소유권을 변경하기 위해 구독을 선택하고 각각의 소유자를 편집 한다.

 

소유자 필드에 유효하지 않은 사용자를 사용하려고 하면 다음과 같은 오류가 발생한다.

 

[구독에 대한 설명 추가]

SSRS 구독에 대한 설명을 추가할 수 있다.

 

구독 설명을 추가하면 구독 요약 목록에서 설명이 표시 된다.

 

 

[단일 자격증명으로 공유 파일 저장소에 보고서 내보내기]

Active Directory에 기반한 서비스 또는 수 많은 환경에서 계정의 비밀번호를 변경해야하는 경우가 수시로 발생한다. 이때 기존에는 각각의 구독에 대해서 비밀번호를 변경해야하는 이슈가 발생하였지만 SSRS 2016에서는 단일 자격증명으로 관리할 수 있다. 관리자 메뉴에서 Subscription Settings에서 파일 공유를 선택한 후 계정과 암호를 입력하여 사용한다.

 

구독을 만들 때 파일 공유 영역에 액세스하는데 사용되는 자격증명을 선택하여 사용할 수 있다.

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4069/sql-server-reporting-services-2016-subscription-enhancements/

 

 

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

 

MS SQL, SQL Server, SSRS, Reporting Service, 리포팅 서비스, SSRS 2016, SQL Server 2016, SSRS 구독, 리포트 구독

테이블 외래키 트리 확인 및 데이터 삭제하기

 

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

 

SQL Server에서 데이터 무결성을 유지하기 위해 생성한 외래키 제약조건이 있는 경우 데이터를 삭제 할 때 제약 조건에 위배되지 않도록 순차적으로 작업을 해야 한다. 테이블에 생성되어있는 외래키 연결에 대해 트리 구조로 살펴보고 순차적으로 데이터를 삭제할 수 있는 방법에 대해서 알아본다.

 

실습을 위해 테이블을 생성한다. 스크립트를 실행 하면 그림과 같은 외래키로 구성된 테이블이 생성된다.

-- sample tables with FKs

create table dbo.M (id int primary key, M_c1 int not null, M_c2 datetime not null, M_c3 char(10) not null, M_c4 datetime);

create unique index ux1_A on dbo.M (M_c1);

create unique index ux2_A on dbo.M (M_c2, M_c3);

 

create table dbo.N1 (id int primary key, N1_c1 int, N1_c2 datetime, N1_c3 char(10), N1_c4 datetime);

create unique index ux1_N1 on dbo.N1 (N1_c1, N1_c3);

create unique index ux2_N1 on dbo.N1 ( N1_c2);

alter table dbo.N1 add constraint FK_N1_M foreign key (N1_c1) references dbo.M (M_c1);

 

create table dbo.N2 (id int primary key, N2_c1 int, N2_c2 datetime, N2_c3 char(10), N2_c4 datetime);

create unique index ux1_N2 on dbo.N2 (N2_c1);

create unique index ux2_N2 on dbo.N2 (N2_c3, N2_c4);

alter table dbo.N2 add constraint FK1_N2_M foreign key (N2_c2, N2_c3) references dbo.M (M_c2, M_c3);

 

create table dbo.P1 (id int primary key, P1_c1 int, P1_c2 datetime, P1_c3 char(10), P1_c4 datetime);

create unique index ux1_P1 on dbo.P1 (P1_c1);

alter table dbo.P1 add constraint FK_P1_N1 foreign key (P1_c1, P1_c3) references dbo.N1 (N1_c1, N1_c3);

 

create table dbo.P2 (id int primary key, P2_c1 int, P2_c2 datetime, P2_c3 char(10), P2_c4 datetime);

create unique index ux1_P2 on dbo.P2 (P2_c1);

create unique index ux2_P2 on dbo.P2 (P2_c2, P2_c3);

alter table dbo.P2 add constraint FK_P2_N1 foreign key (P2_c2) references dbo.N1 (N1_c2);

alter table dbo.P2 add constraint FK_P2_N2 foreign key (P2_c3, P2_c4) references dbo.N2 (N2_c3, N2_c4);

 

create table dbo.P3 (id int primary key, P3_c1 int, P3_c2 datetime, P3_c3 char(10), P3_c4 datetime);

alter table dbo.P3 add constraint FK_P3_N2 foreign key (P3_c1) references dbo.N2 (id);

 

create table dbo.Q1 (id int primary key, Q1_c1 int, Q1_c2 datetime, Q1_c3 char(10), Q1_C4 datetime);

alter table dbo.Q1 add constraint FK_Q1_P1 foreign key (Q1_c1) references dbo.P1 (P1_c1);

 

create table dbo.Q2 (id int primary key, Q2_c1 int, Q2_c2 datetime, Q2_c3 char(10), Q2_c4 datetime);

alter table dbo.Q2 add constraint FK_Q2_P2 foreign key (Q2_c1) references dbo.P2 (id);

 

create table dbo.Q3 (id int primary key, Q3_c1 int, Q3_c2 datetime, Q3_c3 char(10), Q3_c4 datetime);

alter table dbo.Q3 add constraint FK1_Q3_N2 foreign key (Q3_c1) references dbo.N2 (id);

alter table dbo.Q3 add constraint FK2_Q3_P2 foreign key (Q3_c2, Q3_c3) references dbo.P2 (P2_c2, P2_c3);

GO

 

-- populate all tables

insert into dbo.M (id, M_c1, M_c2, M_c3, M_c4)

select 1, 10, '2015-01-01', 'AB1', '2015-01-02'

union all

select 2, 20, '2015-01-02', 'AB2', '2015-01-03'

union all

select 3, 30, '2015-01-03', 'AB3', '2015-01-04';

 

insert into dbo.N1 (id, N1_c1, N1_c2, N1_c3, N1_c4)

select 11, 20, '2015-01-01', 'CD1', '2015-01-02'

union all

select 21, 30, '2015-01-02', 'CD2', '2015-01-03'

union all

select 31, 10, '2015-01-03', 'CD3', '2015-01-04';

 

insert into dbo.N2 (id, N2_c1, N2_c2, N2_c3, N2_c4)

select 11, 11, '2015-01-01', 'AB1', '2015-01-02'

union all

select 12, 22, '2015-01-02', 'AB2', '2015-01-03'

union all

select 13, 33, '2015-01-03', 'AB3', '2015-01-04';

 

insert into dbo.P1 (id, P1_c1, P1_c2, P1_c3, P1_c4)

select 100, 20, '2014-01-01', 'CD1', '2015-01-02'

union all

select 101, 30, '2014-01-02', 'CD2', '2015-01-03'

union all

select 102, 10, '2014-01-03', 'CD3', '2015-01-04'

 

insert into dbo.P2 (id, P2_c1, P2_c2, P2_c3, P2_c4)

select 200, 20, '2015-01-01', 'AB1', '2015-01-02'

union all

select 201, 30, '2015-01-02', 'AB2', '2015-01-03'

union all

select 202, 10, '2015-01-03', 'AB3', '2015-01-04'

 

insert into dbo.P3 (id, P3_c1, P3_c2, P3_c3, P3_c4)

select 301, 11, '2010-01-01', 'EF1', '2015-02-02'

union all

select 302, 13, '2010-01-02', 'EF2', '2015-02-03'

union all

select 303, 12, '2010-01-03', 'EF3', '2015-02-04'

 

 

아래 스크립트는 테이블의 외래키 연결을 트리 구조로 나타내는 프로시저를 생성한다.

use SW_TEST

go

 

if object_id('dbo.usp_searchFK', 'P') is not null

    drop proc dbo.usp_SearchFK;

go

create proc dbo.usp_SearchFK

@table varchar(256) -- use two part name convention

, @lvl int=0 -- do not change

, @ParentTable varchar(256)='' -- do not change

, @debug bit = 1

as

begin

    set nocount on;

    declare @dbg bit;

    set @dbg=@debug;

    if object_id('tempdb..#tbl', 'U') is null

        create table #tbl (id int identity, tablename varchar(256), lvl int, ParentTable varchar(256));

    declare @curS cursor;

    if @lvl = 0

        insert into #tbl (tablename, lvl, ParentTable)

        select @table, @lvl, Null;

    else

        insert into #tbl (tablename, lvl, ParentTable)

        select @table, @lvl,@ParentTable;

    if @dbg=1    

        print replicate('----', @lvl) + 'lvl ' + cast(@lvl as varchar(10)) + ' = ' + @table;

    

    if not exists (select * from sys.foreign_keys where referenced_object_id = object_id(@table))

        return;

    else

    begin -- else

        set @ParentTable = @table;

        set @curS = cursor for

        select tablename=object_schema_name(parent_object_id)+'.'+object_name(parent_object_id)

        from sys.foreign_keys

        where referenced_object_id = object_id(@table)

        and parent_object_id <> referenced_object_id; -- add this to prevent self-referencing which can create a indefinitive loop;

 

        open @curS;

        fetch next from @curS into @table;

 

        while @@fetch_status = 0

        begin --while

            set @lvl = @lvl+1;

            -- recursive call

            exec dbo.usp_SearchFK @table, @lvl, @ParentTable, @dbg;

            set @lvl = @lvl-1;

            fetch next from @curS into @table;

        end --while

        close @curS;

        deallocate @curS;

    end -- else

    if @lvl = 0

        select * from #tbl;

    return;

end

go

 

 

외래키 트리 구조를 나타내는 프로시저를 실행한다. 프로시저는 소유자를 포함한 테이블명을 사용한다. 결과 탭에서 메시지의 내용을 보면 부모 테이블에서 마지막 자식 테이블까지 외래키로 연결된 구조를 트리 구조로 확인할 수 있다.

exec dbo.usp_SearchFK 'dbo.M'

 

 

아래 스크립트는 트리 구조로 연결된 테이블에서 순차적으로 데이터를 삭제할 수 있도록 삭제 스크립트를 생성해주는 스크립트이다. 결과 탭에서 출력해주는 스크립트를 다른 SSMS창에서 실행하면 데이터 삭제가 가능하다.

if object_id('tempdb..#tmp') is not null

    drop table #tmp;

create table #tmp (id int, tablename varchar(256), lvl int, ParentTable varchar(256));

 

insert into #tmp

exec dbo.usp_SearchFK @table='dbo.M', @debug=0;

 

declare @where varchar(max) ='where M.id=2' -- if @where clause is null or empty, it will delete tables as a whole with the right order

declare @curFK cursor, @fk_object_id int;

declare @sqlcmd varchar(max)='', @crlf char(2)=char(0x0d)+char(0x0a);

declare @child varchar(256), @parent varchar(256), @lvl int, @id int;

declare @i int;

declare @t table (tablename varchar(128));

declare @curT cursor;

if isnull(@where, '')= ''

begin

    set @curT = cursor for select tablename, lvl from #tmp order by lvl desc

    open @curT;

    fetch next from @curT into @child, @lvl;

    while @@fetch_status = 0

    begin -- loop @curT

        if not exists (select 1 from @t where tablename=@child)

            insert into @t (tablename) values (@child);

        fetch next from @curT into @child, @lvl;

    end -- loop @curT

    close @curT;

    deallocate @curT;

 

    select @sqlcmd = @sqlcmd + 'delete from ' + tablename + @crlf from @t ;

    print @sqlcmd;

end

else

begin

    declare curT cursor for

    select lvl, id

    from #tmp

    order by lvl desc;

 

    open curT;

    fetch next from curT into @lvl, @id;

    while @@FETCH_STATUS =0

    begin

        set @i=0;

        if @lvl =0

        begin -- this is the root level

            select @sqlcmd = 'delete from ' + tablename from #tmp where id = @id;

        end -- this is the roolt level

 

        while @i < @lvl

        begin -- while

 

            select top 1 @child=TableName, @parent=ParentTable from #tmp where id <= @id-@i and lvl <= @lvl-@i order by lvl desc, id desc;

            set @curFK = cursor for

            select object_id from sys.foreign_keys

            where parent_object_id = object_id(@child)

            and referenced_object_id = object_id(@parent)

 

            open @curFK;

            fetch next from @curFk into @fk_object_id

            while @@fetch_status =0

            begin -- @curFK

 

                if @i=0

                    set @sqlcmd = 'delete from ' + @child + @crlf +

                    'from ' + @child + @crlf + 'inner join ' + @parent ;

                else

                    set @sqlcmd = @sqlcmd + @crlf + 'inner join ' + @parent ;

 

                ;with c as

                (

                    select child = object_schema_name(fc.parent_object_id)+'.' + object_name(fc.parent_object_id), child_col=c.name

                    , parent = object_schema_name(fc.referenced_object_id)+'.' + object_name(fc.referenced_object_id), parent_col=c2.name

                    , rnk = row_number() over (order by (select null))

                    from sys.foreign_key_columns fc

                    inner join sys.columns c

                    on fc.parent_column_id = c.column_id

                    and fc.parent_object_id = c.object_id

                    inner join sys.columns c2

                    on fc.referenced_column_id = c2.column_id

                    and fc.referenced_object_id = c2.object_id

                    where fc.constraint_object_id=@fk_object_id

                )

                    select @sqlcmd =@sqlcmd + case rnk when 1 then ' on ' else ' and ' end

                    + @child +'.'+ child_col +'=' + @parent +'.' + parent_col

                    from c;

                    fetch next from @curFK into @fk_object_id;

            end --@curFK

            close @curFK;

            deallocate @curFK;

            set @i = @i +1;

        end --while

        print @sqlcmd + @crlf + @where + ';';

        print '';

        fetch next from curT into @lvl, @id;

    end

    close curT;

    deallocate curT;

end

 

go

 

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4059/script-to-delete-data-from-sql-server-tables-with-foreign-key-constraints/

 

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

 

SQL Server, MS SQL, 외래키, 제약조건, 외래키 테이블 삭제, 외래키 구조 보기, Foreign Key Constraint,

기본 추적을 사용한 SQL Server 스키마 변경사항 캡처

 

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

 

SQL Server에서 스키마 변경이 발생하였을 때 이를 캡처하고 확인할 수 있는 방법에 대해서 알아본다.

 

기본적으로 SSMS에서는 스키마 변경 기록 보고서를 제공한다. 이 보고서에서는 DDL 작업에 대한 내용과 시간, 로그인 사용자, 사용자 이름 등 유용한 정보가 표시되지만 SQL Server가 시작된 이후의 내용만 확인할 수 있다.

 

 

 

 

SQL Server가 다시 시작되더라도 변경 내용을 히스토리로 관리할 수 있도록 사용자 테이블에 변경 내역을 저장하는 방법에 대이다. 우선 변경 내용을 저장할 테이블을 생성한다.

-- create table to hold DDL history

CREATE TABLE sw_test.[dbo].[DDL_History](

    [database_name] [nvarchar](256) NULL,

    [start_time] [datetime] NULL,

    [login_name] [nvarchar](256) NULL,

    [user_name] [nvarchar](256) NULL,

    [application_name] [nvarchar](256) NULL,

    [ddl_operation] [nvarchar](40) NULL,

    [object] [nvarchar](257) NOT NULL,

    [type_desc] [nvarchar](60) NULL

) ON [PRIMARY]

GO

 

아래 스크립트를 실행하면 변경된 내역을 위에 생성한 사용자 테이블로 저장한다.

USE [master]

GO

--Declare variables

SET NOCOUNT ON;

DECLARE @dbid INT;

DECLARE @dbname VARCHAR(100);

DECLARE @execstr VARCHAR(4000);

-- Declare a cursor.

DECLARE dbs CURSOR FOR

SELECT database_id,name from sys.databases where name not in ('master','tempdb');

 

-- Open the cursor.

OPEN dbs;

-- Loop through all the tables in the database.

FETCH NEXT

FROM dbs

INTO @dbid,@dbname;

WHILE @@FETCH_STATUS = 0

BEGIN;

 

-- Check default trace for any DDL in specific database

SELECT @execstr='if (select convert(int,value_in_use) from sys.configurations where name = ''default trace enabled'') = 1

begin

declare @d1 datetime;

declare @diff int;

declare @curr_tracefilename varchar(500);

declare @base_tracefilename varchar(500);

declare @indx int ;

declare @temp_trace table (

obj_name nvarchar(256)

, obj_id int

, database_name nvarchar(256)

, start_time datetime

, event_class int

, event_subclass int

, object_type int

, server_name nvarchar(256)

, login_name nvarchar(256)

, user_name nvarchar(256)

, application_name nvarchar(256)

, ddl_operation nvarchar(40)

);

 

select @curr_tracefilename = path from sys.traces where is_default = 1 ;

set @curr_tracefilename = reverse(@curr_tracefilename)

select @indx = PATINDEX(''%\%'', @curr_tracefilename)

set @curr_tracefilename = reverse(@curr_tracefilename)

set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + ''\log.trc'';

 

insert into @temp_trace

select ObjectName

, ObjectID

, DatabaseName

, StartTime

, EventClass

, EventSubClass

, ObjectType

, ServerName

, LoginName

, NTUserName

, ApplicationName

, ''temp''

from ::fn_trace_gettable( @base_tracefilename, default )

where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID = '+cast(@dbid as varchar)+' ;

 

update @temp_trace set ddl_operation = ''CREATE'' where event_class = 46;

update @temp_trace set ddl_operation = ''DROP'' where event_class = 47;

update @temp_trace set ddl_operation = ''ALTER'' where event_class = 164;

 

select @d1 = min(start_time) from @temp_trace

set @diff= datediff(hh,@d1,getdate())

set @diff=@diff/24;

 

        insert into sw_test.dbo.DDL_History (database_name,start_time,login_name,user_name,application_name,ddl_operation,object,type_desc)

        select database_name,start_time,login_name,user_name,application_name,ddl_operation,s.name+''.''+o.name as "object",o.type_desc

        from @temp_trace tt inner join

         '+@dbname+'.sys.objects o on tt.obj_id=o.object_id inner join

             '+@dbname+'.sys.schemas s on s.schema_id=o.schema_id

     where object_type not in (21587) -- don''t bother with auto-statistics as it generates too much noise

and start_time > (select case when max(start_time) is NULL then ''1900-01-01'' else max(start_time) end from sw_test.dbo.DDL_History)

end'

 

EXEC (@execstr)

 

FETCH NEXT

FROM dbs

INTO @dbid,@dbname;

END;

-- Close and deallocate the cursor.

CLOSE dbs;

DEALLOCATE dbs;

 

 

위 스크립트를 주기적으로 실행하도록 설정하여 DDL 변경 사항에 대해서 이력관리를 할 수 있도록 한다.

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4057/capture-sql-server-schema-changes-using-the-default-trace/

 

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

 

MS SQL, SQL Server, 스키마 변경, DDL 캡처, DB 보안, DB 접근, DDL 사용자 확인, DB 변경 이력 관리

SQL Server 임시 테이블 특성

 

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

 

임시 테이블은 tempdb 데이터베이스에 저장되었다가 더 이상 사용되지 않을 때 자동으로 삭제 된다. 그 외 기능은 일반 테이블과 비슷하다. 임시 테이블에는 전역과 로컬 두 유형이 있으며 로컬은 # 기호로 시작하며 전역은 ## 기호로 시작한다.

 

로컬 임시테이블은 현재 사용자의 연결에만 표시되고 사용자가 SQL Server 인스턴스와의 연결을 종료하면 삭제 된다.

 

전역은 테이블 생성 후 모든 사용자에게 표시되고 테이블을 참조하는 모든 사용자가 SQL Server인스턴스와 연결을 종료하면 삭제 된다. 즉 테이블을 생성한 사용자가 SQL Server와 연결을 끊어도 다른 사용자가 해당 테이블을 사용하고 있으면 다른 사용자의 사용이 종료될 때까지 기다렸다가 삭제된다.

 

실습을 통해서 전역 임시테이블의 특성에 대해서 살펴보자. 전역 임시 테이블은 테이블 이름 앞에 ## 기호로 식별되며 tempdb 데이터베이스에 저장된다.

CREATE TABLE ##tmpTest

(

    Value INT

)

 

 

테이블 생성 후 전역 임시 테이블은 모든 사용자와 모든 연결에서 볼 수 있다. 전역 임시테이블들은 수동으로 drop table 명령으로도 삭제 할 수 있다. 작성자 세션이 종료되어도 다른 세션에서 해당 테이블이 활성세션에 참조 되어 있는 경우 SQL Server 기준에서 마지막 T-SQL 문이 완료 될 때까지 삭제되지 않으며 참조 세션 문을 실행하는 동안 테이블은 새 연결에 사용할 수 없다.

 

SSMS에서 새쿼리 창을 실행하여 아래 스크립트(쿼리1)를 실행한다. 스크립트는 임시 테이블을 생성하고 데이터를 삽입한다.

--Query 1

 

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

    DROP TABLE ##tmpTest

 

CREATE TABLE ##tmpTest

(

    Value INT

)

 

DECLARE @i INT =1

 

WHILE (@i <= 1000000) --Value 1000000 is used to facilitate testing process, please be careful in choosing this value for your server to avoid overloading the server

BEGIN

 

    INSERT INTO ##tmpTest(Value) VALUES(@i)

 

    SET @i=@i+1

END

 

새 쿼리 창을 열고 아래 스크립트(쿼리2)를 실행 한다. 정상적으로 값이 조회되는 것을 확인할 수 있다.

--Query 2

 

SELECT TOP 1 * FROM ##tmpTest

 

 

새 쿼리창을 실행하여 아래 스크립트(쿼리3)를 실행 한다. 그리고 쿼리1의 세션을 종료 한다.

--Query 3

 

SELECT * FROM ##tmpTest

 

 

쿼리3이 실행되는 동안 쿼리2를 조회해 보면 정상적으로 값이 조회되는 것을 확인할 수 있다. 하지만 쿼리3의 작업이 완료되면 전역 임시테이블은 삭제되어 쿼리2에서 오류가 발생하는 것을 확인할 수 있다.

 

반복문의 경우 어떻게 작동하는지 알아본다. 쿼리 1을 실행하고 아래 스크립트(쿼리4)를 실행한다. 쿼리4가 진행 되는 동안 쿼리1의 세션을 종료 한다.

DECLARE @i INT =0,

        @val INT =0

 

WHILE (@i <= 1000000)

BEGIN

 

    SELECT @val=Value FROM ##tmpTest WHERE Value=@i

 

    SET @i=@i+1

 

    SELECT @val

END

 

 

쿼리4의 경우 하나의 T-SQL 명령문을 사용하지만 While 구문을 실행하면서 반복해서 테이블을 참조하는데 임시테이블이 삭제 후 참조하려고 했기 때문에 오류가 발생하였다.

 

이처럼 로컬 임시 테이블과 전역 임시 테이블은 비슷하지만 다른 특성이 있어 잘 파악하고 사용하여야 하며 쿼리를 개발 할 때 While 같은 경우 한 세션에서 실행되지만 임시 테이블이 삭제된 경우 반복 과정에서 오류가 날 수 있다는 점을 주의해야 한다.

 

 

[참고 자료]

 

 

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

 

 

MS SQL, SQL Server, 임시 테이블, Temp table, 전역 임시 테이블, 로컬 임시 테이블, temp table, Global temporary table

SQL Server 2016 Query Store

  • 실행된 쿼리에 대한 실행 계획 및 통계 정보를 확인하자
  • Version : SQL Server 2016

 

SQL Server 2016에 도입된 Query Store는 요청된 쿼리가 실행 될 때 실행 된 쿼리를 저장 및 실행 계획 등을 함께 저장하여 통계 및 계획을 추적하는데 도움이 된다.

 

쿼리를 저장하기 위해서는 Query Store를 활성화 해야 한다. SSMS 및 T-SQL을 사용하여 쿼리 스토어 기능을 활성화할 수 있다.

 

ALTER DATABASE [DEMO_1] SET QUERY_STORE = ON

GO

 

 

쿼리 스토어를 활성화하면 쿼리 저장과 관련된 다른 옵션을 설정할 수 있다. SSMS 및 T-SQL을 사용하여 설정을 변경할 수 있다.

 

ALTER DATABASE [DEMO_1]

SET QUERY_STORE (OPERATION_MODE = READ_ONLY,

                CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 367),

                DATA_FLUSH_INTERVAL_SECONDS = 900,

                INTERVAL_LENGTH_MINUTES = 60,

                MAX_STORAGE_SIZE_MB = 100,

                QUERY_CAPTURE_MODE = AUTO,

                SIZE_BASED_CLEANUP_MODE = AUTO)

GO

 

설정 변경 옵션에 대한 자세한 내용은 MSDN을 참고 한다.

 

 

쿼리 스토어 속성 정보에서 현재 쿼리 스토어가 사용하고 있는 디스크 사용량을 확인할 수 있다. [Purge Query Data] 버튼을 사용하여 쿼리 스토어의 내용을 제거하거나 스크립트를 사용하여 제거할 수 있다. (참고로 CTP 버전에서는 sp_query_store_flush_db에 몇 가지 버그가 있다고 한다.)

 

ALTER DATABASE [DEMO_1] SET QUERY_STORE CLEAR ALL

GO

 

EXEC sys.sp_query_store_flush_db

 

 

아래 스크립트는 쿼리 저장 시스템 개체 및 새로운 확장 이벤트를 확인할 수 있다.

SELECT name, type_desc FROM sys.all_objects

WHERE name LIKE '%query_store%' or name= 'query_context_settings'

 

 

확장이벤트

설명

query_store_background_task_persist_started

Fired if the background task for Query Store data persistence started execution

query_store_background_task_persist_finished

Fired if the background task for Query Store data persistence is completed successfully

query_store_load_started

Fired when query store load is started

query_store_db_data_structs_not_released

Fired if Query Store data structures are not released when feature is turned OFF.

query_store_db_diagnostics

Periodically fired with Query Store diagnostics on database level.

query_store_db_settings_changed

Fired when Query Store settings are changed.

query_store_db_whitelisting_changed

Fired when Query Store database whitelisting state is changed.

query_store_global_mem_obj_size_kb

Periodically fired with Query Store global memory object size.

query_store_size_retention_cleanup_started

Fired when size retention policy clean-up task is started.

query_store_size_retention_cleanup_finished

Fired when size retention policy clean-up task is finished.

query_store_size_retention_cleanup_skipped

Fired when starting of size retention policy clean-up task is skipped because its minimum repeating period did not pass yet.

query_store_size_retention_query_deleted

Fired when size based retention policy deletes a query from Query Store.

query_store_size_retention_plan_cost

Fired when eviction cost is calculated for the plan.

query_store_size_retention_query_cost

Fired when query eviction cost is calculated for the query.

query_store_generate_showplan_failure

Fired when Query Store failed to store a query plan because the showplan generation failed.

query_store_capture_policy_evaluate

Fired when the capture policy is evaluated for a query.

query_store_capture_policy_start_capture

Fired when an UNDECIDED query is transitioning to CAPTURED.

query_store_capture_policy_abort_capture

Fired when an UNDECIDED query failed to transition to CAPTURED.

query_store_schema_consistency_check_failure

Fired when the Query Store schema consistency check failed.

 

자세한 내용은 MSDN을 참고 한다.

 

데이터베이스 속성에서 쿼리 스토어를 활성화 하면 SSMS에서 Query Store 컨테이너를 확인할 수 있다.

 

쿼리 저장 컨테이너에서 마우스 오른쪽을 클릭하면 기본으로 제공되는 리포트를 확인할 수 있다.

 

컨테이너를 확장하여 수집된 쿼리 스토어에 대한 정보를 확인할 수 있다.

 

수집된 데이터 중에 Top Resource Consuming Queries 정보를 살펴보자. (대부분 구성은 비슷하다.)

 

왼쪽의 차트는 수직 및 수평축에 대해 선택된 통계를 기반으로 전체 통계를 표시한다. 메트릭(1)과 통계(2)의 드롭다운 버튼으로 통계를 선택할 수 있다.

 

오른쪽 차트는 다른 색상으로 각 계획을 표시한다. 마우스 포인터를 올려놓으면 (3) 개체에 대한 query id 또는 Plan id에 대한 자세한 정보를 참조할 수 있다. 선택된 메트릭에 따라 상세 정보는 다르다.

 

 

오른쪽 차트의 버블 크기(4)는 실행의 총 수에 따라 달라진다. 다른 계획을 클릭하면(3, 4) 창 하단의 부분(5)이 특정 plan id에 대한 실행 계획을 표시한다. (6)은 창 이름을 나타내며 (7), (8), (9)는 현재 리포트 정보를 나타낸다.

 

 

아래 그림은 드롭 다운 메뉴를 살펴 본다. 모니터 해상도가 작을 경우 오른쪽의 일부 메뉴(6)가 숨겨질 수 있다. 숨겨진 메뉴는 확장해서 볼 수 있다.

 

Track Query(1) 버튼은 추적 쿼리 창을 보여준다.

 

View Query(2)는 T-SQL 스크립트로 새로운 SSMS창에 보여준다.

 

 

Detail Grid(3), Grid(4) 버튼은 통계 정보를 제공한다.

 

구성(5) 버튼은 한 곳에서 모든 창을 구성할 수 있다.

 

 

여러 계획 쿼리가 있는 경우 Compare Plans 버튼을 사용하여 왼쪽 차트와 실행계획을 비교해볼 수 있다.

 

강제 계획 버튼을 눌러 해당 계획을 사용할 수 있다.

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4009/sql-server-2016-query-store-introduction/

 

 

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

 

 

MS SQL, SQL 2016, SQL Query Store, 쿼리 튜닝, DB튜닝, 쿼리 스토어, 실행계획 비교, 쿼리 모니터링

SQL Server 2016 Temporal Table – 데이터 변경 내용 추적

 

  • Version : SQL Server 2016

 

SQL Server 2016에서 새롭게 소개된 Temporal Table 대해서 알아본다. 여기서 소개하는 임시테이블은 임시 테이블(Temporal Table)과 임시 테이블(temporary tables)을 착각하지 않도록 주의해야 한다.

 

SQL Server 2016의 Temporal Table은 테이블의 기록을 데이터로 보존할 수 있도록 하는 시스템 테이블의 새로운 이름이다. 일반 테이블은 현재 데이터를 반환 할 수 있지만 시스템 테이블은 업데이트와 삭제된 버전의 데이터를 조회할 수 있다. 만약 데이터를 5에서 10으로 변경하는 경우 일반 테이블을 조회하면 10이라는 값을 검색할 수 있지만 임시테이블(히스토리 테이블)은 변경된 기록을 유지하여 이전 값인 5를 검색할 수 있다. 이 히스토리 테이블은 레코드가 활성화 된 때를 표시하는 시작 및 종료 데이터와 함께 이전 데이터를 저장한다.

 

[시스템 버전 테이블 만들기]

새 임시 테이블을 생성 할 때 전체 조건의 몇 가지 사항을 충족해야 한다.

  • 기본 키를 정의 해야 한다.
  • 두 열은 Datetime2 타입의 시작 및 종료 날짜를 기록하도록 정의되어야 한다. 이 열은SYSTEM_TIME 기간 열이라고 한다.
  • INSTEAD OF 트리거는 허용되지 않는다.
  • In-Memory OLTP는 사용할 수 없다.

 

다음은 몇 가지 제한 사항이다.

  • 임시테이블 및 히스토리 테이블은 파일 테이블일 수 없다.
  • 히스토리 테이블은 constraints 제약이 없다.
  • INSERT 및 UPDATE 문은 SYSTEM_TIME 기간 열을 참조 할 수 없다.
  • 히스토리 테이블의 데이터는 수정할 수 없다

 

제약사항에 대한 자세한 내용은 MSDN을 참고한다.

 

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

CREATE TABLE dbo.TestTemporal (

ID int primary key

,A int

,B int

,SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL

,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL

,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) WITH(SYSTEM_VERSIONING = ON

);

 

테이블이 생성되었을 때 히스토리 테이블은 dbo.MSSQL_TemporalHistoryFor_XXXXXX로 생성되는데 XXXXX는 개체 ID이다. 히스토리 테이블의 이름을 지정하지 않으면 SQL Server는 자동으로 다음과 같은 구조를 생성한다.

 

 

히스토리 테이블 컬럼은 동일한 세트를 가지고 있으며 자신만의 인덱스와 통계 세트가 있다. 이러한 히스토리 테이블에 클러스터 컬럼스토어 인덱스를 생성하면 성능을 크게 향상 시킬 수 있다.

 

이제 테이블에 데이터를 삽입하여 테이블의 시간적 버전 기능을 테스트한다.

-- Initial Load

INSERT INTO dbo.TestTemporal(ID, A, B)

VALUES     (1,2,3)

        ,(2,4,5)

        ,(3,0,1);

 

SELECT * FROM dbo.TestTemporal;

 

 

이제 하나의 행을 삭제하고 다른 데이터는 업데이트 작업을 진행 한다.

-- Modify Data

DELETE FROM dbo.TestTemporal

WHERE ID = 2;

 

UPDATE dbo.TestTemporal

SET A = 5

WHERE ID = 3;

 

SELECT * FROM dbo.TestTemporal;

 

 

히스토리 테이블을 조회해보면 이전 버전의 데이터를 확인할 수 있다.

select * from dbo.MSSQL_TemporalHistoryFor_1253579504

 

 

 

[시스템 버전 테이블의 스키마 변경]

시스템 버전 테이블을 사용하는 경우 테이블 수정이 제한된다.

  • ALTER TABLE….REBUILD
  • CREATE INDEX
  • CREATE STATISTICS

 

다른 모든 스키마 변경도 허용되지 않는다. 예를 들면 임시테이블 삭제도 허용되지 않는다.

drop table dbo.TestTemporal

 

메시지 13552, 수준 16, 상태 1, 줄 14

테이블 삭제 작업은 시스템 버전 관리 임시 테이블에서 지원되는 작업이 아니므로 'SW_Test.dbo.TestTemporal' 테이블에서 테이블 삭제 작업을 수행할 수 없습니다.

 

 

히스토리 테이블에 새로운 열을 추가작업이나 스키마 변경을 위해서는 시스템 버전을 먼저 제거해야 한다.

ALTER TABLE dbo.TestTemporal SET (SYSTEM_VERSIONING = OFF);

 

스키마 변경 후 히스토리 테이블은 동기화를 유지하기 위해 시스템 버전을 다시 시작하여야 한다.

ALTER TABLE dbo.TestTemporal SET (SYSTEM_VERSIONING = ON

(HISTORY_TABLE=dbo.MSSQL_TemporalHistoryFor_1253579504,DATA_CONSISTENCY_CHECK=[ON/OFF])

);

 

 

[참고자료]

 

 

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

 

 

 

SQL Server 2016, SQL 2016 New Feature, MS SQL, 데이터베이스, SQL 2016 신기능, 변경내역 추적, Temporal Table, 히스토리 테이블

SQL Server 2016 Feature in CTP2

 

  • Version : SQL Server 2016

 

SQL Server 2016에 포함된 새로운 기능들에 대해서 알아 본다.

 

[데이터베이스 엔진]

 

[Azure]

 

 

[Business Intelligence]

  • 마스터 데이터 서비스(MDS) 향상. MDS의 백엔드에 상당한 변화가 있다. (http://sqlblog.com/blogs/mds_team/archive/2015/05/27/what-s-new-in-sql2016-ctp2-release.aspx)
    • 향상된 성능 : 더 큰 모델을 작성하고 데이터를 로드하여 더 나은 성능을 나타낸다. 수 있다. 엑셀용 추가 기능(add in)은 성능 업그레이드를 가지고 더 많은 수천개의 개체를 처리 할 수 있다.
    • 향상된 관리 : 이름 속성 50자 이상 지원, 이름 속성을 숨기거나 변경 가능
    • 보안 향상 : 서버 관리자와 동일한 권한을 가진 새로운 슈퍼 사용자 기능이 있어 권한이 더 세분화된 수준의 정의된 읽기 업데이트 생성 및 삭제 작업을 진행 한다.
    • 더 나은 트랜잭션 로그 유지 관리
    • 향상된 문제 해결
    • 병렬 파티션 처리
    • 새로운 DAX 함수. 평균과 백분위 기능 뿐만 아니라 날짜 테이블, 달력(자동) 등
    • 새로운 DBCC 명령어로 SSAS 멀티디멘젼 모델의 커럽션을 확인할 수 있다.
    • SSAS에서 파워 피봇에 대한 쉬운 셋업
    • .NET 프레임워크 4 지원
    • 보고서 작성기의 높은 DPI 제공
    • 구독 향상
      • 사용 및 구독 해제(현재 기본 모드에서 지원)
      • 설명(SharePoint 및 기본 모드)
      • 변경 등록 소유자 (SharePoint 및 기본 모드)
      • 구독에 재사용 할 수 있는 하나의 파일 공유 계정을 정의할 수 있는 기능(기본모드)
    • SSIS에 대한 증분 패키지 배포

 

 

[참고자료]

What's New in Database Engine : https://msdn.microsoft.com/en-us/library/bb510411.aspx

 

 

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

 

 

SQL Server 2016, SQL 2016 New Feature, MS SQL, 데이터베이스, SQL 2016 신기능

SQL Server 2016 설치 변경 사항

 

  • Version : SQL Server 2016

 

SQL Server 2016 CTP 2에서 설치 시 변경된 사항에 대해서 알아 본다. SQL Server 2016에 대한 설치방법은 이전 포스트를 참고 한다.

 

[PolyBase 조회 서비스]

기능 선택 페이지에서 외부 데이터에 대한 PolyBase 쿼리 서비스를 선택할 수 있는 옵션이 추가 되었다. 이 기능은 표준 T-SQL문을 사용하여 하둡 데이터 및 SQL Server 데이터를 통해 통합 질의를 할 수 있다.

 

외부 데이터에 대한 PolyBase 쿼리 서비스를 설치하려면 아래 요구사항을 만족해야 한다.

  • SQL Server 64비트
  • .NET Framework 4.0 이상
  • 최소 요구 메모리 : 4GB
  • 최소 요구 디스크 공간 : 2GB
  • 오라클 자바 SE 런타임 환경(JRE) 버전 7.51 이상. (JRE가 존재하지 않는 경우 설치 오류 발생)

 

PolyBase를 설치하면 DWConfiguration, DWDiagnostics, DWQueue 3개의 사용자 데이터베이스가 생성된다. 이들은 내부에서 PolyBase를 실행하는데만 사용된다.

 

테이블을 클릭하고 외부 확장 테이블을 새로 만들 수 있다.

 

[임시 데이터베이스 구성(TempdB 구성)]

SQL Server 이전 버전에서는 설치 중에 코어 수에 따라 여러 임시 데이터베이스 파일을 작성하는 기능을 가지고 있지 않았지만 SQL Server 2016부터는 코어수 만큼 기본적으로 데이터 파일이 생성되며 데이터베이스 엔진 구성 페이지에서 옵션을 변경하는 기능이 포함 되었다.

 

 

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/3670/sql-server-2016-installation-changes/

 

 

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

 

MS SQL, SQL Server, SQL 2016, PolyBase, TempDB File 옵션, SQL 설치

최신 SQL Server Native Client 설치하기

 

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

 

SQL Server용 서비스팩(SP) 또는 누적 업데이트(CU)를 설치하는 경우 SQL Server Native Client가 업데이트 되지 않는 것을 알 수 있다. 또한 업데이트를 찾기가 어려울 수 있다. SP또는 CU 패키지에서 가져오는 방법에 대해서 알아본다.

 

SP 또는 CU 패키지를 실행할 때 설치될 폴더를 확인할 수 있다. 확인된 폴더의 위치를 탐색기를이용하여 이동하면 SQL 설치 랜딩 페이지를 볼 수 있다.

 

 

여기에서 해당 지역의 폴더로 이동한다. KOR의 경우 1042 폴더이며 ENU는 1033 폴더이다.

 

여기서 /x64/setup/x64로 이동하면 sqlncli.msi를 확인할 수 있다. SQL이 32비트인 경우 폴더는 x86폴더가 될 것이다.

 

여기에서 실행하는데 필요로하는 MSI를 복사하여 필요한 곳에서 실행 할 수 있다.

 

[참고자료]

http://blogs.msdn.com/b/psssql/archive/2015/07/14/getting-the-latest-sql-server-native-client.aspx

 

 

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

 

 

MS SQL, SQL Server, 네이티브 클라이언트, sqlncli.msi, SQL 업데이트

Sys.dm_os_waiting_tasks를 활용한 실행중인 병렬쿼리 확인

 

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

 

Sys.dm_os_waiting_tasks를 활용하여 실행중인 병렬쿼리의 정보를 확인한다.

SELECT

[owt].[session_id],

[owt].[exec_context_id],

[ot].[scheduler_id],

[owt].[wait_duration_ms],

[owt].[wait_type],

[owt].[blocking_session_id],

[owt].[resource_description],

CASE [owt].[wait_type]

WHEN N'CXPACKET' THEN

RIGHT ([owt].[resource_description],

CHARINDEX (N'=', REVERSE ([owt].[resource_description])) - 1)

ELSE NULL

END AS [Node ID],

--[es].[program_name],

[est].text,

[er].[database_id],

[eqp].[query_plan],

[er].[cpu_time]

FROM sys.dm_os_waiting_tasks [owt]

INNER JOIN sys.dm_os_tasks [ot] ON

[owt].[waiting_task_address] = [ot].[task_address]

INNER JOIN sys.dm_exec_sessions [es] ON

[owt].[session_id] = [es].[session_id]

INNER JOIN sys.dm_exec_requests [er] 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

ORDER BY

[owt].[session_id],

[owt].[exec_context_id];

GO

 

 

[참고자료]

http://www.sqlskills.com/blogs/paul/updated-sys-dm_os_waiting_tasks-script/

 

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

 

 

MS SQL, SQLServer, 병렬쿼리, DMV, sys.dm_os_waiting_tasks, parallelism, CXPACKET

로그인 계정이 접근할 수 있는 데이터베이스 확인

 

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

 

SQL Server 로그인 계정은 특정한 데이터베이스에 접근할 수 있다. 생성된 로그인 계정이 접근 할 수 있는 데이터베이스를 확인하는 방법에 대해서 알아본다.

 

실습을 위해 로그인 계정을 생성한다.

USE [master];

GO

 

-- add "boss" to sysadmin:

CREATE LOGIN boss WITH PASSWORD = 'x', CHECK_POLICY = OFF;

ALTER SERVER ROLE sysadmin ADD Member boss;

 

-- add "dev1" to serveradmin:

CREATE LOGIN dev1 WITH PASSWORD = 'x', CHECK_POLICY = OFF;

ALTER SERVER ROLE serveradmin ADD Member dev1;

 

-- add "dev2" to dbcreator:

CREATE LOGIN dev2 WITH PASSWORD = 'x', CHECK_POLICY = OFF;

ALTER SERVER ROLE dbcreator ADD Member dev2;

 

-- "peon1" will only be in public

CREATE LOGIN peon1 WITH PASSWORD = 'x', CHECK_POLICY = OFF;

 

-- "peon2" will be in public *and* granted explicit access to AW2014:

CREATE LOGIN peon2 WITH PASSWORD = 'x', CHECK_POLICY = OFF;

GO

 

USE [AdventureWorksDW2012];

GO

CREATE USER peon2 FROM LOGIN peon2;

GO

 

 

 

생성된 계정이 접근할 수 있는 데이터베이스를 확인하기 위해 SSMS에서 Peon1로 로그인 하여 특정 데이터베이스를 접근한다. 데이터베이스가 보이지만 접근은 되지 않는다.

 

Peon1 계정에 대해 모든 데이터베이스 거부를 설정하면 데이터베이스 목록을 확인 할 수 없다.

USE [master];

GO

DENY VIEW ANY DATABASE TO peon1;

 

 

다음 스크립트는 특정 계정에 대해 접근할 수 있는 모든 데이터베이스에 대한 액세스를 확인 할 수 있다.

EXECUTE AS LOGIN = N'peon1';

GO

SELECT name FROM sys.databases WHERE HAS_DBACCESS(name) = 1;

GO

REVERT;

 

 

다음 스크립트는 SQL Server에 생성된 전체 계정에 대하여 접근할 수 있는 데이터베이스 목록을 확인 할 수 있다.

-- table for collecting data

 

CREATE TABLE #x([login] SYSNAME, db SYSNAME, error INT);

 

-- table of logins we care about today

DECLARE @logins TABLE([login] SYSNAME);

INSERT @logins([login]) VALUES(N'boss'),(N'peon1'),(N'peon2');

 

-- build SQL commands for read attempts to each online db

DECLARE @sql NVARCHAR(MAX) = N'', @cmd NVARCHAR(MAX) = N'';

 

SELECT @sql += N'

BEGIN TRY

INSERT #x SELECT TOP (1) SUSER_SNAME(),N''' + REPLACE(name,'''','''''')

+ N''',0 FROM ' + QUOTENAME(name) + N'.sys.all_objects;

END TRY

BEGIN CATCH

INSERT #x SELECT SUSER_SNAME(),N''' + REPLACE(name,'''','''''')

+ ''', ERROR_NUMBER();

END CATCH;' FROM sys.databases

WHERE [state] = 0;

 

SELECT @cmd += N'

EXECUTE AS LOGIN = N'''

+ REPLACE([login], '''', '''''') + N''';

' + @sql + N'

REVERT;' FROM @logins;

 

EXEC [master].sys.sp_executesql @cmd;

 

-- report

SELECT [login], [db], [Access?] = CASE error

WHEN 0 THEN 'Yes'

WHEN 229 THEN 'Access denied on sys.all_objects'

WHEN 916 THEN 'Cannot connect to database'

ELSE 'No - ' + CONVERT(VARCHAR(11), error) END

FROM #x ORDER BY [login],db;

GO

 

--DROP TABLE #x;

 

 

위 스크립트를 활용하여 특정 계정에 대해 데이터베이스 접근 권한을 확인하고 불필요한 계정의 접근을 미리 차단하여 보안에 주의 할 수 있도록 한다.

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3399/verify-the-databases-a-sql-server-login-can-see--and-why/

 

 

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

 

SQL Server 사용자 계정, SQL Server 계정, 사용자 보안, 로그인, SQL 로그인, 데이터베이스 접근, SQL Server, ms sql, create login, server role

RANDBETWEEN 함수 만들기

 

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

 

엑셀에 포함되어 있는 함수인 RANDBETWEEN(범위 난수 발생)을 SQL에서 구현하는 방법에 대해서 알아 본다.

 

엑셀에서는 다음과 같이 RANDBETWEEN 함수를 사용하여 범위에 포함되어 있는 난수를 생성하는 함수가 있다.

 

SQL Server에서도 RAND() 함수를 사용하여 두 수 사이의 난수를 생성할 수 있다.

select cast(round((75-25)*rand()+25, 0) as integer) as RandBetweenValue

 

 

위의 쿼리를 원하는 범위에 따라 재사용이 가능하도록 함수를 생성한다. 다음과 같은 오류가 발생 할 것이다.

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

returns int

 

as

 

begin

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

end

go

 

 

 

이 문제는 임의의 숫자를 포함하는 뷰를 사용하면 해결할 수 있다.

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

 

select dbo.randbetween(25, 75)

 

 

 

함수에서 사용할 수 없는 오퍼레이터를 뷰로 해결하여 사용자가 원하는 범위 내애서 난수를 발생시키는 방법에 대해서 알아 보았다.

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3297/create-your-own-randbetween-function-in-tsql/

 

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

 

 

MS SQL, SQLServer, 난수 발생, RAND(), 랜덤 생성, RandBetween, 함수 생성,SQL tip, 데이터베이스

+ Recent posts