with SQL Server

http://support.microsoft.com/kb/196271

SQL Server DMV를 이용해서 상위 CPU 사용 개체를 메일로 보내주는 팁입니다.

SQL Server 메일구성이 되어 있다는 전제하에 사용하시길 바랍니다.

1. 통계 테이블 생성 후 DMV를 이용해서 CPU 사용률을 Insert

Use MonitorDB

GO

Create Table dbo.HighCPUUsage_DBpart (

           [AverageCPUused] bigint not null,

           [TotalCPUused] bigint not null,

           [Executioncount] bigint not null,

           [DatabaseName] nvarchar(50) not null,

           [ObjectName]  nvarchar(100) not null,

           [RegDate] date not null)

GO

Create Clustered Index CIX_HighCPUUsage_DBpart_RegDate on HighCPUUsage_DBpart(RegDate)

go

EXEC sp_msforeachdb

'USE [?]

IF EXISTS(SELECT NAME FROM SYS.SYSDATABASES WHERE NAME = ''?'' AND DBID > 4)

BEGIN

                     INSERT INTO MonitorDB.dbo.HighCPUUsage_DBpart(AverageCPUused, TotalCPUused, Executioncount,  

                 DatabaseName, ObjectName, RegDate)

                     SELECT    [Average CPU used] = SUM([Total CPU used]) / SUM([Execution count])

                                   , [Total CPU used] = SUM([Total CPU used])

                                   , [Execution count] = SUM([Execution count])

                                   , [DatabaseName], [ObjectName], CONVERT(DATE,GETDATE())

                     FROM (

                                          SELECT TOP 300

                                           [Average CPU used] = total_worker_time / qs.execution_count

                                          ,[Total CPU used] = total_worker_time

                                          ,[Execution count] = qs.execution_count

                                          ,[DatabaseName] = DB_NAME(qt.dbid)

                                          ,[ObjectName] =  OBJECT_NAME(qt.objectid)

                                          FROM sys.dm_exec_query_stats qs

                                          CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

                                          ORDER BY [Average CPU used] DESC) a

                     WHERE ObjectName IS NOT NULL

                     AND DatabaseName = ''?''

                     GROUP BY DatabaseName, ObjectName;

END'

2. sp_send_dbmail 을 사용해서 HTML 형식으로 메일 보내기

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =

    N'<H1>상위CPU 사용개체</H1>' +

    N'<table border="1">' +

    N'<tr><th>CPU 평균(초)</th><th>총CPU(초)</th><th>총실행횟수</th>' +

    N'<th>데이터베이스명</th><th>개체명(e.g프로시저)</th></tr>' +

    CAST ( (

                                SELECT td = AverageCPUused  / 1000000, '' --마이크로 초 변환

                                           , td = TotalCPUused / 1000000, ''

                                           , td = Executioncount, ''

                                           , td = DatabaseName, ''

                                           , td = ObjectName, ''

                                FROM dbo.HighCPUUsage_DBpart

                                ORDER BY AverageCPUused DESC

            FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @profile_name =  'MAIL', 

@recipients='Email@Email.com',

      @subject = N'상위CPU 사용개체',

      @body = @tableHTML,

      @body_format = 'HTML' ;

이번에 소개해 드릴 내용은 초 간단 팁 입니다.

ISNULL 함수 사용시 유의사항 입니다.

내용인 즉 ISNULL 함수에서 NULL 여부를 체크하는 컬럼형에 따라 데이터가 짤릴 수도 있기 때문에

COALESCE 함수를 사용하는 것을 권장합니다.

DEMO!

Use tempdb

go

CREATE TABLE dbo.T1(COL1 CHAR(3), COL2 CHAR(1))

INSERT INTO dbo.T1(COL1)

SELECT 'ABC'

SELECT COL1, COL2

FROM dbo.T1

SELECT ISNULL(COL2, COL1), COALESCE(COL2, COL1)

FROM dbo.T1

COL2 컬럼타입에 영향을 받아 “BC” 짤렸네요…

끝~

Reference : http://sqlblog.com/blogs/john_paul_cook/archive/2013/02/06/why-to-use-coalesce-instead-of-isnull.aspx

지난번 소개해 드린 Parameter Sniffing의 해결방법(?)에 대해서 공유합니다.

결론부터 말씀 드리면 3가지 정도가 있습니다.

1.     추적 플래그 4136 설정

2.     옵티마이저 쿼리힌트 설정

3.     프로시저 내 WITH RECOMPILE 옵션 추가

이번에도 마찬가지로 데모를 통해 하나하나 눈으로 확인해 보죠~

* 추적 플래그 4136 설정

Use AdventureWorks

go

--쿼리실행 시간 및 통계설정

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

DBCC TRACEON(4136, -1); --4136 추적 플래그 전역으로 ON!

- 추적 플래그는 SQL Server가 재시작 되면 초기화 되므로 시작 시 매개변수로 지정해서 자동으로 설정 가능

--SQL Server 캐시삭제(모든실행계획삭제)

DBCC FREEPROCCACHE

--인덱스 검색 후 풀스캔

EXEC user_GetCustomerShipDates '2001-07-10', '2001-07-11' -- 인덱스검색

EXEC user_GetCustomerShipDates '2001-07-08', '2004-08-07' -- 풀스캔(모든구간검색)

추적 플래그를 설정하기 전 인덱스 검색 후 풀스캔 했을 때 논리적 읽기 수가 94468 이였던 것에 반해 약 800배 이상이 감소했습니다.

참고로 추적 플래그 4136은 아래 버전이상에서만 포함되어 있습니다.

(This option was added in SQL Server 2008 SP1 CU7,

and SQL Server 2008 R2 CU2, and also back-ported into SQL Server 2005 in SP3 CU9)

추적 플래그 작동 방법을 요약하면 들어온 파라미터 값으로 통계 히스토그램(statistics histogram)을 통해

반환될 행 수를 미리 계산해서 실행하게 됩니다.

하지만 이와 같은 경우 정상적인 프로시저도 영향을 미칠 수 있으므로 Parameter Sniffing이 해결되지 않은

최후의 방법으로 사용해야 된다고 합니다.

* 옵티마이저 쿼리힌트 설정

DBCC TRACEOFF(4136, -1); --추적 플래그 OFF!

ALTER PROCEDURE [dbo].[user_GetCustomerShipDates]

(

@ShipDateStart DATETIME ,

@ShipDateEnd DATETIME

)

AS

SELECT  CustomerID ,

SalesOrderNumber

FROM Sales.SalesOrderHeader

WHERE ShipDate BETWEEN @ShipDateStart AND @ShipDateEnd

OPTION ( OPTIMIZE FOR ( @ShipDateStart = '2001-07-08',

@ShipDateEnd = '2004-08-07' ))

실행계획을 FOR 이후 일자를 이용해서 생성하도록 옵션을 설정합니다.

위에서는 어떤 범위가 값이 들어오던지 풀 스캔 하도록 했습니다~

--SQL Server 캐시삭제(모든실행계획삭제)

DBCC FREEPROCCACHE

-- 인덱스 검색 후 풀스캔

EXEC user_GetCustomerShipDates '2001-07-10', '2001-07-11' -- 인덱스검색

EXEC user_GetCustomerShipDates '2001-07-08', '2004-08-07' -- 풀스캔(모든구간검색)

결과는 풀스캔 때와 동일하므로 따로 캡쳐하지 않았습니다.

* 프로시저 내 WITH RECOMPILE 옵션 추가

ALTER PROCEDURE [dbo].[user_GetCustomerShipDates]

(

@ShipDateStart DATETIME ,

@ShipDateEnd DATETIME

)

WITH RECOMPILE –프로시저가 호출될 때 마다 실행 계획 재작성

AS

SELECT CustomerID ,

                   SalesOrderNumber

FROM Sales.SalesOrderHeader

WHERE ShipDate BETWEEN @ShipDateStart AND @ShipDateEnd

위와 같이 RECOMPILE 옵션 추가 후 프로시저를 호출해 보겠습니다.

--SQL Server 캐시삭제(모든실행계획삭제)

DBCC FREEPROCCACHE

--이번엔 반대로 인덱스 검색 후 풀스캔

EXEC user_GetCustomerShipDates '2001-07-10', '2001-07-11' -- 인덱스검색

EXEC user_GetCustomerShipDates '2001-07-08', '2004-08-07' -- 풀스캔(모든구간검색)

오오 파라미터 스니핑이 해결된 건 물론이고 각각 실행했을 때에 맞는(인덱스 탈 때 타고 풀스캔 해야 될 때 하는)

논리적 읽기 수를 보여주고 있네요. 실행계획도 정상적이고요~

그럼 피라미터 스니핑을 방지하기 위해서 모든 프로시저 내에 RECOMPILE 옵션 추가해도 될까요?

- 대답은 No!! 입니다 ㅎㅎ

맨 처음 들어가면서 프로시저의 큰 장점 중 하나가 실행계획의 재사용이라고 했는데요.

RECOMPILE 옵션을 사용하게 되면 정상적인 프로시저 또한 실행계획을 재사용하지 못하고 호출할 때 마다

실행계획을 다시 세우기 때문에 그만큼 비용이 낭비되게 됩니다.

하지만 파라미터 스니핑이 발생하는 프로시저에는 RECOMPILE 옵션을 추가하는 방법을 고려할 수 있습니다.

추가적인 RECOMPILE 옵션 Tip을 드리자면 Multiple queries로 되어있는 프로시저 내에는

문제가 되는 쿼리에만 RECOMPILE 옵션을 따로 지정할 수 있습니다.

CREATE PROCEDURE user_GetCustomerShipDates

(

@ShipDateStart DATETIME ,

@ShipDateEnd DATETIME

)

AS

SELECT CustomerID ,

SalesOrderNumber

FROM Sales.SalesOrderHeader

WHERE ShipDate BETWEEN @ShipDateStart AND @ShipDateEnd

OPTION ( RECOMPILE ) –쿼리 단위로 옵션 설정 가능

GO

이상으로 Parameter Sniffing의 원인과 해결방법 까지 모두 살펴보았습니다.

Reference : Troubleshooting SQL Server A Guide for the Accidental DBA

오늘은 지난 시간에 이어서 좀 더 깊이 있게 Parameter Sniffing이란 것을 소개해 드릴까 합니다.

오라클 DBMS에서는 Bind Peeking이라고도 불립니다.

우선, 파라미터 스니핑을 짧게 설명하자면 아래와 같습니다.

* Parameter Sniffing

- Parameter Sniffing은 SQL Server에서 프로시저를 처음 호출할 때(SQL Server 재 시작 등) 생성된 실행계획을

   플랜 캐시에 저장한 뒤 이후 프로시저가 호출되면 실행계획을 세우지 않고 재사용 합니다.

   그래서 성능을 고려해 프로시저를 이용하는 것이 큰 장점입니다.

   하지만 이와 같은 문제가 오히려 성능에 치명적인 영향을 미치는 경우가 발생합니다.

데모를 통해서 함께 확인해 보시죠~

일단 샘플 데이터 만드는 게 귀차니즘이므로 AdventureWorks 예제 DB를 사용하겠습니다.

AdventureWorks DB에는 Sales.SalesOrderHeader(판매주문) 라는 테이블이 있습니다.

위 테이블 내 ShipDate(출고일자) 컬럼을 이용해 검색하는 프로시저를 하나 만들어 보죠~

참고로 ShipDate에는 2001-07-08 ~ 2004-08-07 까지의 데이터만 존재합니다.

--물건 출고일자 내 고객ID, 주문수량 검색 프로시저

CREATE PROCEDURE Usp_GetCustomerShipDates(

@ShipDateStart DATETIME ,

@ShipDateEnd DATETIME)

AS

SELECT CustomerID ,

     SalesOrderNumber

FROM Sales.SalesOrderHeader

WHERE ShipDate BETWEEN @ShipDateStart AND @ShipDateEnd

GO

프로시저에서 검색할 때 유리하도록 인덱스도 만들어 주겠습니다.

--출고일자컬럼인덱스생성

CREATE NONCLUSTERED INDEX IDX_ShipDate_ASC

ON Sales.SalesOrderHeader (ShipDate)

GO

세팅은 끝났습니다~ Ho ya~

그럼 이제 프로시저를 호출 할건데요.

프로시저 호출 시 옵티마이저에서 실행계획을 두 가지를 비교해보기 위해서

한번은 출고일자를 처음부터 끝까지(풀스캔) 다른 하나는 하루치만 검색하도록 하겠습니다.(인덱스 검색)

통계를 확인하기 위해 옵션 ON! & 플랜캐시 삭제!

--쿼리실행 시간 및 통계설정

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

--SQL Server 캐시삭제(모든 실행계획 삭제)

DBCC FREEPROCCACHE

그럼 풀스캔 하는 프로시저와 인덱스 검색하는 프로시저를 차례로 실행해 보겠습니다.

--데이터 선택도에 따라 옵티마이저 풀스캔 or 인덱스검색

EXEC user_GetCustomerShipDates '2001-07-08', '2004-08-07' -- 풀스캔(모든구간검색)

EXEC user_GetCustomerShipDates '2001-07-10', '2001-07-11' -- 인덱스검색

아래 실행 결과 입니다.(실행계획은 스압으로 인해 캡쳐하지 않았습니다 -_-+)

들어가면서 쓴 것과 같이 최초 호출되는 실행계획(풀스캔하는 실행계획)이 플랜캐시에 등록되었으므로,

인덱스 검색을 해야 하는 상황에서도 실행계획 재사용으로 인해 인덱스를 타지 않고 풀스캔 된 것을 확인 하실 수 있습니다.

이번엔 반대로 인덱스 검색을 실행하고 풀스캔을 실행해보도록 하겠습니다.

--SQL Server 캐시삭제(모든 실행계획 삭제)

DBCC FREEPROCCACHE

--이번엔 반대로 인덱스 검색 후 풀스캔

EXEC user_GetCustomerShipDates '2001-07-10', '2001-07-11' – 인덱스 검색

EXEC user_GetCustomerShipDates '2001-07-08', '2004-08-07' -- 풀스캔(모든구간검색)

아래 실행 결과 입니다.

위에서도 인덱스를 타야 되는 실행구문이 풀스캔 되는 문제도 성능에 영향을 미치겠지만 이번 결과가 더 충격적입니다.

동일한 검색이지만 풀스캔 시에는 논리적 읽기 수가 706 인 것에 비해

최초 실행 된 인덱스 검색 실행계획으로 Parameter Sniffing이 발생해서 논리적 읽기 수가 94468 약 133배 증가 했습니다.(랜덤 IO 발생)

성능적인 이슈만 봤을 때 Parameter Sniffing으로 인해 프로시저 사용이 언제나 향상된 성능을 보장하진 않습니다.

(프로시저 사용하지 말자는 뜻이 절~대 아닙니다!)

모니터링을 통해 평소에 정상적이던 프로시저가 갑자기 실행시간이나 IO 발생이 높아진다면

Parameter Sniffing을 의심해봐야 합니다.

Reference : Troubleshooting SQL Server A Guide for the Accidental DBA

오늘 소개드릴 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