with SQL Server

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' ;

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