with SQL Server

오늘 소개드릴 DMV를 이용해서 누락된 인덱스 찾기!! 입니다.

SELECT  user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )

AS [index_advantage] ,

        migs.last_user_seek ,

        mid.[statement] AS [Database.Schema.Table] ,

        mid.equality_columns ,

        mid.inequality_columns ,

        mid.included_columns ,

        migs.unique_compiles ,

        migs.user_seeks ,

        migs.avg_total_user_cost ,

        migs.avg_user_impact

FROM    sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )

        INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK )

           ON migs.group_handle = mig.index_group_handle

        INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK )

           ON mig.index_handle = mid.index_handle

WHERE   mid.database_id = DB_ID()

ORDER BY index_advantage DESC

위 결과에서 보시면 인덱스 누락으로 인한 사용 비용이 제일 높은 1, 2위가

TConsign(위탁판매) 테이블의 seller_id와 item_kind 컬럼입니다.

아! 아래 3위 아래는 시간을 보시면 아시겠지만 정기점검 중 ad-hoc으로 일괄 처리된 사항이니까

실 서비스와는 상관없는 내용이니. Pass하죠~

결과가 확인 됐으니 이제 분석을 하고 튜닝을 해볼까요?

첫번째 seller_id 컬럼부터 확인해 보겠습니다.

DMV에 등록된걸 봐서는 분명 프로시저 어디선가 필터링 조건일 겁니다.

그래서 어디어디서 쓰나 찾아보니 아래 프로시저에서 실제 필터링 조건으로 사용 중입니다.

-       ConsignBuyItem --Seller_id 필터링조건

-       ConsignGetItemList --Seller_id 필터링조건

-       ConsignUnregistItem --Seller_id 필터링조건

-       GetConsignItemList --Seller_id Join 조건절

그러므로 Seller_id 컬럼은 다음주 점검 때 인덱스 생성 예정입니다.

재밌는건 두번째 item_kind 컬럼입니다.

Item_kind 컬럼은 이미 인덱스가 생성되어 있고 사용하는 프로시저는

TConSign_Get_List --item_kind 필터링 조건절 입니다.

그런데 어쩔 땐 인덱스를 안탑니다.. 왜 그럴까요? 뭐 옵티마이저가 타지 말라고 했겠죠..

그럼 왜 인덱스가 있는데 사용을 안 했을까요..

인덱스를 타지 않을 때와 인덱스를 강제로 태웠을 때 실행계획을 보시죠~

1.     인덱스 타지 않고 풀 스캔

2.     강제로 인덱스 사용

비용은 안 나왔지만 강제로 인덱스 사용을 했을 때가 Key Lookup이 발생해서

풀 스캔 때보다 증가합니다. 역시 똑똑한 옵티마이저네요..ㅎㅎ

Key Lookup은 오늘 주제와 관계 없으므로 다음에 기회가 되면 알아보도록 하죠

결론을 말씀 드리면 옵티마이저는 인덱스가 존재하는 컬럼을 필터링 할때에도

필터링할 값이 선택도가 높으냐 낮으냐를 미리 계산한 뒤 인덱스를 태울지 안 태울지 결정합니다.

위 실행계획 처럼 선택도가 낮은 필터링 조건은 그냥 한번에 풀스캔에서 찾으면 되지

인덱스를 태워서 하나하나 루프조인으로 풀리는게 더 비용이 많이 소모되기 때문입니다.

Reference : SQL Server DMV StarterPack

* tempdb를 제외한 인스턴스 내 모든 DB 백업

EXEC sp_msforeachdb

'IF EXISTS(SELECT NAME FROM SYS.SYSDATABASES WHERE NAME = ''?'' AND DBID <> 2 AND VERSION IS NOT NULL)

BACKUP DATABASE [?] TO DISK = ''Backup Path'' WITH INIT, COMPRESSION;'

참고 : 위 스크립트에 사용된 sp_msforeachdb(시스템 저장 프로시저)는 BOL에 문서화 되어 있지 않지만,

       인스턴스 내 모든 DB Name을 반환하는 프로시저 입니다.

Reference : http://www.sqlservercentral.com/scripts/Mehmet+G%26%23220%3bZEL/75505

가끔 DB서버가 언제 재 시작 되었는지 확인할 때가 있습니다.

재 시작 확인 방법은 SQL 최신 로그 생성일자, 로그 데이터 확인, 이벤트 로그 확인 등

여러 방법이 있겠지만, 아래 스크립트를 이용해 확인 가능합니다.

아시다시피 SQL Server가 재 시작 되면, tempdb는 매번 재 생성됩니다.

--SQL Server 2000

USE master

GO

DECLARE @db_cr_date DATETIME,

@hour int,

@minut int,

@days int

SELECT @db_cr_date=crdate

FROM sysdatabases

WHERE NAME='tempdb'

SET @minut = DATEDIFF (mi,@db_cr_date,GETDATE())

SET @days= @minut / 1440

SET @hour = (@minut / 60) - (@days * 24)

SET @minut= @minut - ( (@hour + (@days * 24)) * 60)

Select @@SERVERNAME

+N' 서버가'

+ CAST(@db_cr_date as varchar(30))

+ N' 재시작되었고현재: '

+ ltrim(str(@days)) +N' 일,'

+ ltrim(str(@hour)) +N' 시간, '

+ ltrim(str(@minut)) + N' 분이흘렀습니다. ' as [Total uptime]

--SQL Server 2005 이상

DECLARE @db_cr_date DATETIME,

@hour int,

@minut int,

@days int

SELECT @db_cr_date=crdate

FROM master.sys.sysdatabases

WHERE NAME='tempdb'

SET @minut = DATEDIFF (mi,@db_cr_date,GETDATE())

SET @days= @minut / 1440

SET @hour = (@minut / 60) - (@days * 24)

SET @minut= @minut - ( (@hour + (@days * 24)) * 60)

Select @@SERVERNAME

+N' 서버가'

+ CAST(@db_cr_date as varchar(30))

+ N' 재시작되었고현재: '

+ ltrim(str(@days)) +N' 일,'

+ ltrim(str(@hour)) +N' 시간, '

+ ltrim(str(@minut)) + N' 분이흘렀습니다. ' as [Total uptime]

SQL Server 2000과 2005 이상의 스크립트 차이는 시스템 카탈로그명이 달라졌을 뿐

동일합니다. ㅎㅎ

Reference : http://www.sqlservercentral.com/scripts/Administration/93335

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