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