DBA들이 사무실에 출근하면 시스템들이 정상적으로 수행되고 있는지 확인하기 위한 체크리스트들입니다.
DBA가 수동이나 예약된 스크립트 형태로 모니터링해야 하는 항목을 설명하였습니다.
참고 : 사용된 모든 쿼리는 SQL Server 2008에서 테스트 되었고 이전 버전에서는 제대로 작동하지 않을 수 있습니다.
1번 항목 - SQL Server 서비스가 정상적으로 수행되고 있나요?
xp_servicecontrol 프로시저로 service가 올라왔는지, 실행되고 있는지를 확인 할 수 있습니다.
1 | exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'MSSQLServer' |
2 | exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent' |
3 | exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLBrowser' |
2번 항목 - SQL Agent 의 모든 작업들이 성공하였습니까?
이 내용은 msdb의 데이터베이스에서 아주 간단한 쿼리로 확인이 가능합니다.
첫번째 컬럼은 작업의 실패여부에 대한 상태값을 나타내고 뒤의 부분은 작업에 대한 내용들을 나타냅니다.
SQL Agent를 통해 데이터베이스 백업을 수행한다면, 백업 작업의 실패를 확인 할 수 있는 좋은 방법이 될 수 있다.
03 | select 'FAILED' as Status, cast(sj.name as varchar(100)) as "Job Name", |
04 | cast(sjs.step_id as varchar(5)) as "Step ID", |
05 | cast(sjs.step_name as varchar(30)) as "Step Name", |
06 | cast(REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,sjh.run_date)),102),'.','-')+' '+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),5,2) as varchar(30)) 'Start Date Time', |
07 | sjh.message as "Message" |
10 | on sj.job_id = sjs.job_id |
12 | on sj.job_id = sjh.job_id and sjs.step_id = sjh.step_id |
13 | where sjh.run_status <> 1 |
14 | and cast(sjh.run_date as float)*1000000+sjh.run_time > |
15 | cast(convert(varchar(8), getdate()-1, 112) as float)*1000000+70000 |
17 | select 'FAILED',cast(sj.name as varchar(100)) as "Job Name", |
19 | 'MAIN' as "Step Name", |
20 | cast(REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,sjh.run_date)),102),'.','-')+' '+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),5,2) as varchar(30)) 'Start Date Time', |
21 | sjh.message as "Message" |
24 | on sj.job_id = sjh.job_id |
25 | where sjh.run_status <> 1 and sjh.step_id=0 |
26 | and cast(sjh.run_date as float)*1000000+sjh.run_time > |
27 | cast(convert(varchar(8), getdate()-1, 112) as float)*1000000+70000 |
3번 항목 - 데이터베이스의 최신 백업을 해야 합니까?
두 쿼리는 24시간동안 백업하지 않은 데이터베이스나 한번도 백업하지 않는 데이터베이스를 나타냅니다.
첫번째 쿼리는 전체백업을 두번째 쿼리는 트랜젝션로그백업을 확인합니다. ( 단, 복구 모델이 전체복구모델일 경우)
01 | SELECT d.name AS "Database", |
02 | ISNULL(CONVERT(VARCHAR,b.backupdate,120),'NEVER') AS "Last Full Backup" |
04 | LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate FROM backupset |
06 | GROUP BY database_name,type) b on d.name=b.database_name |
07 | WHERE (backupdate IS NULL OR backupdate < getdate()-1) |
08 | SELECT d.name AS "Database", |
09 | ISNULL(CONVERT(VARCHAR,b.backupdate,120),'NEVER') AS "Last Log Backup" |
11 | LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate FROM backupset |
13 | GROUP BY database_name,type) b on d.name=b.database_name |
14 | WHERE recovery_model = 1 |
15 | AND (backupdate IS NULL OR backupdate < getdate()-1) |
4번 항목 - SQL Server Error Log에 Error Log가 있습니까?
SQL Server Error Log를 확인하기 위해 xp_readerrorlog 프로시저를 사용합니다. 아래 쿼리는 현재부터 2일전까지의 로그를 보여줍니다.
01 | declare @Time_Start datetime; |
02 | declare @Time_End datetime; |
03 | set @Time_Start=getdate()-2; |
04 | set @Time_End=getdate(); |
06 | CREATE TABLE #ErrorLog (logdate datetime |
07 | , processinfo varchar(255) |
08 | , Message varchar(500)) |
10 | INSERT #ErrorLog (logdate, processinfo, Message) |
11 | EXEC master.dbo.xp_readerrorlog 0, 1, null, null , @Time_Start, @Time_End, N'desc'; |
13 | SELECT LogDate, Message FROM #ErrorLog |
14 | WHERE (Message LIKE '%error%' OR Message LIKE '%failed%') AND processinfo NOT LIKE 'logon' |
5번 항목 -SQL Server의 디스크 공간이 부족합니까?
xp_fixeddrives 프로시저를 사용하여 드라이브의 공간정보를 확인 할 수 있습니다.
1 | exec master.dbo.xp_fixeddrives |
6번 항목 - SQL Server이 메모리가 부족합니까?
dm_os_sys_memory 동적관리뷰(Dynamic management view : DMV)를 사용하여 서버의 메모리를 확인 할 수 있습니다.
1 | SELECT available_physical_memory_kb/1024 as "Total Memory MB", |
2 | available_physical_memory_kb/(total_physical_memory_kb*1.0)*100 AS "% Memory Free" |
3 | FROM sys.dm_os_sys_memory |
7번 항목 - SQL Server의 캐쉬에 튜닝이 필요한 쿼리가 있습니까?
아래의 쿼리는 성능적으로 저조한 쿼리를 식별합니다.
IO, CPU, Elapsed Time 등을 기준으로 "order by" 구문을 사용하여 관심이 있는 성능값 순서로 확인 할 수 있습니다.
01 | SELECT top 10 text as "SQL Statement", |
02 | last_execution_time as "Last Execution Time", |
03 | (total_logical_reads+total_physical_reads+total_logical_writes)/execution_count as [Average IO], |
04 | (total_worker_time/execution_count)/1000000.0 as [Average CPU Time (sec)], |
05 | (total_elapsed_time/execution_count)/1000000.0 as [Average Elapsed Time (sec)], |
06 | execution_count as "Execution Count", |
07 | qp.query_plan as "Query Plan" |
08 | FROM sys.dm_exec_query_stats qs |
09 | CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st |
10 | CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp |
11 | order by total_elapsed_time/execution_count desc |
8번 항목 - SQL Server instance에 얼마나 많은 연결(connections)이 되어 있습니까?
아래 쿼리는 시스템에 블로킹이 발생할 경우, 정보를 제공하지 않을 수 있습니다. 그러나, 이 쿼리를 통해 어플리케이션에서의 연결이 평소보다 많은지를 확인하고 이 수치를 통해 문제를 짐작할 수 있다.
1 | SELECT spid, kpid, blocked, d.name, open_tran, status, hostname, |
2 | cmd, login_time, loginame, net_library |
4 | INNER JOIN sys.databases d |
5 | on p.dbid=d.database_id |
9번 항목 - SQL Server 에 얼마나 많은 요청이 있는가?
쿼리를 통해 얻어지는 수치를 보관하고 나중에 비교하기 위한 기준으로 사용할 수 있습니다. 일반적으로 1000/sec이면 SQL Server가 부하상태라고 하지만, 이것은 하드웨어에 따라 많이 다릅니다.경우에 따라 100/sec 가 Instance가 처리하기에 많은 양일 수도 있습니다.
아래의 쿼리를 사용하면 SQL Server에 관련된 OS 성능 정보를 조회 할 수 있습니다.
3 | FROM sys.dm_os_performance_counters |
4 | WHERE counter_name LIKE 'Batch Requests/sec%' |
5 | WAITFOR DELAY '000:00:10' |
6 | SELECT (cntr_value-@BRPS)/10.0 AS "Batch Requests/sec" |
7 | FROM sys.dm_os_performance_counters |
8 | WHERE counter_name LIKE 'Batch Requests/sec%' |
출처 : http://iheedol.tistory.com/entry/
