with SQL Server

SQL Server SSMS에서 사용하는 초 간단 팁을 소개합니다.

쿼리 창에서 Alt 키를 누른 채 드래그하면 원하는 블록만 선택할 수 있습니다.

예를 들면 아래와 같이 전체 스크립트 중 일부 구문만 선택해서 바꿔야 할 때

변경 할 블록을 선택 후 변경

- 일부 UNION ALL 부분을 UNION으로 변경

• 변경 전

• 변경 후

해당 블록만 변경된 것을 확인 가능합니다.

오늘 소개해 드릴 a tiny tip은 OUTPUT 절 입니다.

OUTPUT “절”로 말씀 드린 것처럼 프로시저나 동적 쿼리의 OUTPUT과는 다른 구문입니다.

이미 알고 계신다면 복습차원으로 봐주세요~

BOL (http://technet.microsoft.com/ko-kr/library/ms177564(v=sql.105).aspx)에선 OUTPUT 절을

INSERT, UPDATE, DELETE 또는 MERGE 문의 영향을 받는 각 행의 정보 또는 각 행을 기반으로 하는 식을 반환합니다.

라고 하는데요. 이해하기 쉽게 다음 예제를 통해 알아보겠습니다.

--테스트 테이블 생성

CREATE TABLE dbo.Output_Test (

           seq int not null identity(1,1) primary key,

           name nvarchar(10) not null,

           title nvarchar(10) not null)

--Output 절을 이용한 INSERT

INSERT INTO dbo.Output_Test (name, title)

OUTPUT INSERTED.seq, INSERTED.name, INSERTED.title --INSERT 된 행의 컬럼을 출력

VALUES ('김해', '팀장'),

           ('원미', '파트장'),

           ('백승', '팀원'),

           ('서일', '팀원')

----------------------------------------------------------------------

위 결과와 같이 데이터 입력과 동시에 적용된 데이터를 확인 가능합니다.

--Output 절을이용한DELETE

DELETE FROM dbo.Output_Test

OUTPUT DELETED.seq, DELETED.name --DELETE 된행의컬럼을출력

WHERE name = '서일'

----------------------------------------------------------------------

SELECT seq, name, title

FROM dbo.Output_Test

----------------------------------------------------------------------

삭제 시에도 영향을 받은 데이터를 출력할 수 있습니다.

--Output 절을이용한UPDATE

UPDATE dbo.Output_Test

SET title = '사장'

OUTPUT INSERTED.name, DELETED.title as before, INSERTED.title as after

WHERE name = '백승'

----------------------------------------------------------------------

SELECT seq, name, title

FROM dbo.Output_Test

업데이트 문에서는 Inserted, Deleted 접두사를 이용해 before & after 데이터를 한번에 확인 가능합니다.

OUTPUT INTO 절을 사용하면, 영향을 받는 데이터가 동시에 테이블로 복사 됩니다.

--로그테이블생성

CREATE TABLE dbo.Output_Test_LOG (

           seq int not null primary key,

           name nvarchar(10) not null,

           title nvarchar(10) not null,

           kind nvarchar(10) not null)

DELETE FROM dbo.Output_Test

OUTPUT DELETED.*, 'DELETE' as kind INTO dbo.Output_Test_LOG

--DELETE 된행을로그테이블로복사

WHERE name in ('김해', '원미')

SELECT seq, name, title, kind

FROM dbo.Output_Test_LOG

----------------------------------------------------------------------

위 결과와 같이 삭제된 행이 로그테이블로 복사된 것을 확인할 수 있습니다.

제약사항이나 더 자세한 사항은 BOL 참고하시면 좋을 것 같습니다.

OUTPUT 절이 유용하게 쓰일 수 있는 곳을 생각나는 대로 나열하자면,

Auto increment하게 증가되는 값이 있다면, INSERT 후 입력된 값을 알기 위해 SELECT 없이 반환 받을 수 있습니다.

응용 프로그램에서 데이터 처리 후 처리된 값을 반환 받아야 될 때도 마찬가지 입니다.

또한 테이블로의 INTO 절이 가능하기 때문에 로그 데이터 처리 시에도 유용하다고 판단됩니다.

프로시저를 이용한 집합기반의 처리가 가능할까요?

보통 프로시저 매개변수는 단일한 값으로 처리되기 때문에 다수의 값 처리를 위해서는

루프 형태로 만들어 처리가 완료 될 때 까지 반복적으로 프로시저를 호출합니다.

프로시저이기 때문에 매번 실행계획을 세우진 않겠지만

클라이언트와 서버간의 Round Trip은 증가됩니다.

하지만 SQL Server 2008 이상부터 추가된 테이블 반환 매개변수를 이용해 집합기반의 처리가 가능합니다.

직원전용 포인트 지급하는 간단한 예를 들어 살펴보도록 하겠습니다. Check this out!

1. TestEmpPoint 이란 테이블에 직원이름과 보유한 포인트가 있습니다.

2. 프로시저에서 테이블 반환 매개변수를 이용하려면 사용자 테이블 형식을 사용하기 때문에

아래와 같이 만듭니다.

/* 직원 포인트 지급 테이블 형식 만들기*/

CREATE TYPE EmpPointTableType AS TABLE

( EmpName VARCHAR(10)

, Point INT );

GO

3. 직원전용 포인트를 지급하는 프로시저를 생성합니다.

CREATE PROCEDURE usp_UpdateEmpPoint

    @Point EmpPointTableType READONLY –위에서 만든 직원 포인트 지급 테이블 형식

    --★테이블 반환 매개변수에 대해선 READOLNY만 가능, DML 작업 불가능

    AS

    SET NOCOUNT ON;

    UPDATE a

           SET a.Point = a.Point + b.Point          

    FROM  dbo.TestEmpPoint a

           INNER JOIN @Point b

           ON a.EmpName = b.EmpName

GO

4. 사용자 테이블 형식으로 테이블 변수를 선언하고 지급할 포인트를 입력한 후 프로시저를 호출합니다.

/* 위에서 만든 직원 포인트 지급 테이블 형식으로 테이블 변수 선언*/

DECLARE @UpdatePoint AS EmpPointTableType;

/* 테이블 변수에 지급 할 포인트 입력*/

INSERT INTO @UpdatePoint

SELECT '김해', 20000

UNION ALL

SELECT '원미', 10000

UNION ALL

SELECT '백승', 50000

UNION ALL

SELECT '서일', 3000

/* 테이블 반환 매개변수로 프로시저 실행 */

EXEC usp_UpdateEmpPoint @UpdatePoint;

GO

5. 포인트 지급 확인!

SELECT EmpName, Point

FROM dbo.TestEmpPoint

----------------------------------

위와 같이 정상적으로 처리된 것을 확인 가능합니다.

만약 1,000명의 직원이 존재했다면 프로시저를 1,000번 호출했을 텐데

테이블 반환 매개변수를 이용하면 한번으로 줄일 수 있습니다.

끝~

자세한 사항은 아래 URL 참고해 주세요~

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

오늘 얘기할 부분은 복합 인덱스 설정 시 발생할 수 있는 성능 차이에 대해서 말해 보겠습니다.

가끔 Primary Key 또는 인덱스 설정 시에 하나의 컬럼이 아닌 다수 컬럼을 묶어 인덱스를 설정하는 경우가 있습니다.

이때 컬럼 순서에 따라 의도하지 않게 성능 저하가 일어날 수 있습니다.

예를 들어 설명해 보죠.

col1, col2, col3 이라는 컬럼이 존재하고 3개의 컬럼이 PK로 잡혀야 하는 비즈니스 입니다.

처음으로 할 테스트는 컬럼 순서대로 인덱스를 설정한 경우입니다.

--테이블생성

CREATE TABLE dbo.PKDesign_Test (

        col1 INT  not null,

        col2 INT IDENTITY(1, 10) not null,

        col3 CHAR(3) not null )

--컬럼순서대로PK 설정 

ALTER TABLE dbo.PKDesign_Test ADD  CONSTRAINT [PK_PKDesign_Test] PRIMARY KEY CLUSTERED

(

        col1, col2, col3 ASC

)

--테스트데이터넣기

DECLARE @i INT = 1

WHILE @i < 1000       

        BEGIN

                   INSERT INTO dbo.PKDesign_Test(col1, col3)

                   SELECT @i, REPLICATE(LEFT(NEWID(), 1), 3)

                   SET @i = @i + 1

        END

그런데 아래와 같이 해당 테이블을 조회할 때 사용되는 필터링 컬럼은 col2, col3라고 하는군요.

SELECT col1, col2, col3

FROM dbo.PKDesign_Test

WHERE col2 > 5000 and col3 = 'EEE'

위 실행계획에서 보시는 것과 같이 복합 인덱스 첫 번째 컬럼이 필터조건에 존재하지 않으면,

Index Scan 즉 Full Scan이 일어나 인덱스를 사용할 수 없습니다.

결국 인덱스를 설정했지만 사용하지도 못하는 인덱스가 돼버렸네요.

그렇기 때문에 인덱스 설정 시 필터 대상 컬럼은 반드시 첫 번째 위치에 존재해야 합니다.

아마 이건 다 아시는 내용일 수도 있겠네요.

사실 제가 말하고자 한 내용은 이제부터 입니다.

Col2, col3를 필터조건으로 걸 때에도 두 컬럼의 순서에 따라 성능 차이가 있을 수 있습니다.

그럼 col2 컬럼이 첫 번째인 인덱스와 col3 컬럼이 첫 번째인 인덱스를 갖는 테이블을 각각 생성해 보겠습니다.

CREATE TABLE dbo.PKDesign_Test1 (

        col1 INT  not null,

        col2 INT IDENTITY(1, 10) not null,

        col3 CHAR(3) not null );

--col2 컬럼이첫번째     

ALTER TABLE dbo.PKDesign_Test1 ADD  CONSTRAINT [PK_PKDesign_Test1] PRIMARY KEY NONCLUSTERED

(

        col2, col3, col1 ASC

);

CREATE TABLE dbo.PKDesign_Test2 (

        col1 INT  not null,

        col2 INT IDENTITY(1, 10) not null,

        col3 CHAR(3) not null );

--col3 컬럼이첫번째     

ALTER TABLE dbo.PKDesign_Test2 ADD  CONSTRAINT [PK_PKDesign_Test2] PRIMARY KEY NONCLUSTERED

(

        col3, col2, col1 ASC

);

--dbo.PKDesign_Test 테이블에서데이터복사

INSERT INTO dbo.PKDesign_Test1(col1, col3)

SELECT col1, col3

FROM dbo.PKDesign_Test;

INSERT INTO dbo.PKDesign_Test2(col1, col3)

SELECT col1, col3

FROM dbo.PKDesign_Test;

테이블 생성과 동일한 데이터를 두 테이블에 입력하였습니다.

그럼, 아래와 같이 각각의 테이블에 동일한 쿼리를 만들어 실행계획을 살펴 볼까요?

SELECT col1, col2, col3

FROM dbo.PKDesign_Test1

WHERE col2 > 5000 and col3 = 'EEE'

SELECT col1, col2, col3

FROM dbo.PKDesign_Test2

WHERE col2 > 5000 and col3 = 'EEE'

위 실행계획을 보면 필터 조건에 복합 인덱스 첫 번째 행이 포함되어 있기 때문에

두 쿼리 모두 Index Seek(인덱스에서 행 검색)을 확인하실 수 있습니다.

다만, 쿼리 비용은 58%와 42%로 차이가 발생합니다.

실행계획을 자세히 보도록 하겠습니다.

            [PKDesign_Test1] 테이블                           [PKDesign_Test2] 테이블

보시면 아시겠지만 Test1 테이블(col2 첫번째 컬럼)은 인덱스 검색을 col2 > 5000 보단 큰 값을

쭉~ 검색하면서 col3 = ‘EEE’ 인 Row을 찾아 냅니다.

이 와는 다르게 Test2 테이블(col3 첫번째 컬럼)은 인덱스 검색을 col3 = ‘EEE’ 인 값을 우선 찾고

그 중에 col2 > 5000 인 Row를 찾아 냅니다.

어떤 게 더 유리할까요?

Col2 > 5000 인 rows는 499개 이며, col3 = ‘EEE’ 인 rows는 63개 입니다.(실제 테이블이 더 크다면 차이도 더 클 겁니다.)

당연히 비교적 낮은 row 수를 갖는 col3 컬럼을 먼저 찾아 내는 게 유리합니다.

보통 범위연산(부등호) 보다 ‘=’ 연산이 좁은 범위의 rows을 갖기 때문에

복합 인덱스 설정 시에는 컬럼 순서 조정을 고려해서 디자인하는 것이

성능에 매우 유리합니다.

끝~

오늘 얘기할 부분은 Truncate Table 시 롤백이 가능할까? 입니다.

어떻게 생각하시나요? 가능할까요? 아님 안될까요?

보통 Big테이블 전체를 싹 날려버릴 때 Delete문을 이용하는 것보다

속도가 훨~씬 빠르기 때문에 가끔 쓰실 겁니다. 물론 그만큼 주의하셔서 쓰시겠지만요…

그럼 Delete문은 왜 Truncate Table 보다 속도가 확연히 느릴까요?

그렇습니다. Delete문은 행 마다(Single-row) 트랜잭션 로그에 기록을 하면서 삭제되기 때문에 느리죠.

반면 Truncate Table은 행 마다 트랜잭션을 로그에 기록하지 않습니다.

Delete 시 row-lock과 Truncate Table 시 page-lock의 영향도 있지만 이번 주제와 관계없기 때문에 넘어가죠~

어쨌든 Truncate Table은 트랜잭션 로그에 기록하지 않기 때문에 롤백이 불가능하다고 생각됩니다.

과연 그럴까요? 본론으로 돌아와서 결론을 말씀 드리면 롤백이 가능합니다.

트랜잭션 로그에 기록되지 않는데 어떻게 롤백이 가능할까? 라고 생각되기도 하지만

Truncate Table 역시 트랜잭션 로그에 최소한 로그(minimally logged)을 기록합니다.

- 최소한 로그? 직역하니 좀 이상하네요…ㅎ

여기서 최소한 로그(minimally logged)는 Truncate Table 시 할당이 취소된(deallocated) pages와 extents 입니다.

           * page(SQL Server 최소 단위 8KB) * 8 = 1 Extent

보지 않고는 믿을 수 없다!라고 생각하시는 분들을 위해서 간단한 테스트를…ㅎ

--1. AdventureWorks DB에서 Product 테이블을 Products로 복사 합니다.

USE AdventureWorks

GO

IF EXISTS (SELECT * FROM sys.tables  WHERE name = 'Products' AND schema_id = 1)

    DROP TABLE dbo.Products;

GO

SELECT * INTO dbo.Products

FROM Production.Product;

GO

--2. Products 테이블의 카운트와 할당 된 page를 확인 합니다.

SELECT COUNT(*) FROM dbo.Products;

GO

DBCC IND('AdventureWorks', 'Products', -1);

GO

- 504건과 할당된 페이지들이 보이는군요.

--3. 트랜잭션을 걸고 Products 테이블을 Truncate 한 후

--   count(*), page, extent 정보와 AdventureWorks DB의 lock 정보를 한번에 볼까요?

BEGIN TRAN

TRUNCATE TABLE dbo.Products;

SELECT COUNT(*) FROM dbo.Products;

DBCC IND('AdventureWorks', 'Products', -1);

DBCC EXTENTINFO('AdventureWorks', 'Products', -1);

SELECT resource_type, resource_description,

        request_mode FROM sys.dm_tran_locks

WHERE  resource_type IN ('EXTENT', 'PAGE')

AND   resource_database_id = DB_ID('AdventureWorks');

- Truncate Table 후 0건이고, page와 extent는 할당이 취소되어서 정보가 없습니다.

   (여기서 할당 취소한 최소한 로그(minimally logged)를 트랜잭션 로그에 기록합니다.)

   하지만 해당 page와 extent 재사용 하지 못하게 배타적 잠금(eXclusive lock) X을 요청하고 있군요.

--4. 롤백합니다.

ROLLBACK TRAN;

GO

SELECT COUNT(*) FROM dbo.Products;

DBCC IND('AdventureWorks', 'Products', -1);

 - Check result yourself :)

끝~

Reference : http://sqlblog.com/blogs/kalen_delaney/default.aspx?p=2

DBCC CHECKDB문은 운영중인 서버에서 실행하지 않는 것이 권고사항입니다.(단, SQL Server 2005이상)

SQL Server 2000이하는 테이블 레벨에서 스키마 잠금이 일어납니다.

테이블에 대한 DML, DDL 작업이 차단됩니다… 덜덜

그렇기 때문에 SQL Server 2000 버전에서 운영 중 실행은 절대 금지사항입니다.

SQL Server 2005에서는 왜? 실행하지 않는 게 권고사항일까요?

눈치 빠른 분들은 아시겠지만 2005부터 스냅숏이 생겼죠~

그래서 엔진 내부에서 읽기 전용 데이터베이스 스냅숏을 만들어 동작하기 때문에

DBCC CHECKDB가 실행될 때 블로킹(차단) 및 동시성 문제를 방지할 수 있습니다.

“어 그럼 DBCC CHECKDB를 실행해도 되잖아요?”

“안됩니다!”

DBCC CHECKDB의 동작 매커니즘을 잠깐 살펴보자면…

해당 DB 내 모든 개체의 무결성 및 일관성을 확인하기 위해

페이지(DB파일의 최소단위 8KB)를 디스크에서 메모리로 퍼 올립니다.

메모리가 왕창 크면 좋겠지만 우리의 메모리는 데이터 보다 작아요..ㅠㅠ

그래서 데이터 중 일부를 tempdb로 스풀링하게 됩니다.

그때 tempdb가 위치한 디스크에서 IO 작업 병목현상이 발생하게 됩니다.(tempdb는 저 혼자 쓰는게 아니니까요~)

또한 CheckDB 실행 시 디스크에서 데이터를 최적화해서 읽으려 하기 때문에 같은

디스크(파일그룹) 부분에 Access하는 쿼리는 위와 같은 이유로 느려질 수 있습니다.

지금까지 DBCC CheckDB에 대해서 알아보았습니다.

끝~

Reference : http://msdn.microsoft.com/ko-kr/library/ms176064.aspx

이번에 얘기할 내용은 COALESCE 함수 입니다.

일단, MSDN에서는….

- 해당 인수 중에서 Null이 아닌 첫 번째 식을 반환합니다.

라고 하는데요. 무슨 뜻이죠? -_-a 직접 보시죠~ Check this out!

WITH PlatformTeam

AS (

             SELECT '김해' as Name, '010-테이크-빵빵빵꾸' as cell_phone, '02-집전화-빵꾸똥꾸' as phone, '툴파트' as [Group]

             UNION all

             SELECT '원미' as Name, Null, '031-집전화-빵꾸꾸빵', '디비파트'

             UNION all

             SELECT '백승' as Name, '010-갤럭시-똥똥꾸꾸', Null, '디비파트'

             UNION all

             SELECT '서일' as Name, Null, Null, '디비파트'

             )

SELECT Name, cell_phone, phone, COALESCE(cell_phone, phone) as '둘중있는번호'

FROM PlatformTeam

위와 같이 cell_phone과 phone 컬럼에서 Null 아닌 값을 확인가능 합니다.

사실 오늘 하려던 얘기는 이게 아니라 COALESCE 함수를 이용한 Pivot 입니다.

다시 테이블에서 누가 어느 파트에 속해 있는지 확인해 보겠습니다.

SELECT Name, [Group]

FROM PlatformTeam

---------------------------------

그럼 디비파트분들 이름을 Pivot 해보겠습니다.

여러가지 방법이 있겠지만 이번엔 COALESCE 함수가 주인공이니…

DECLARE @name VARCHAR(100)

SELECT @NAME = COALESCE(@NAME, '') + Name + ' | '

FROM PlatformTeam

WHERE [Group] = '디비파트'

SELECT @NAME as 'Pivot'

-------------------------------------------------------------------

위와 같이 디비파트분들이 Pivot된 것을 확인 가능합니다.

Reference : http://www.mssqltips.com/sqlservertip/1521/the-many-uses-of-coalesce-in-sql-server/

끝~

DB를 운영하다 보면 Lock이나 기타 이유로 해당 세션을 KILL하는 경우가 가끔 있는데요.

트랜잭션 양이 많은 세션을 KILL할 경우

Rollback 시간이 얼마나 소요되는지 확인하는 옵션을 공유합니다.

★ 테스트 GoGo!

1. 임시 테이블을 만들고 10만건을 입력!(트랜잭션은 열어두세요… 롤백해야죠…)

2. 해당 세션을 죽인 다음 제대로 죽었는지 확인해 볼까요?

KILL 60;

SP_WHO2

엇! KILL했으면 60번은 안나와야 되는데…(Rollback할 데이터양이 많아서 표시가 되고 있네요)

3. 언제 끝날껀지 만이라도 알려줘~

KILL 60 WITH STATUSONLY;

500초...기다리라고 하네요…ㅠㅠ

WITH STATUSONLY 옵션은 상태만을 나타냅니다~ 롤백상태를 종료하거나 하지 않아요~

4. 결론

KILL하고 세션이 바로 안 죽으면 WITH STATUSONLY 옵션으로 확인하세요~

사실 단어하나 설명하는 건데 길어졌네요.

Reference는 어디다 뒀는지...ㅠㅠ

끝~