개기획자

쿼리 변경이력 확인

--쿼리 변경이력 확인 SELECT * FROM sys.sql_modules sm inner join sys.all_objects ao on sm.object_id = ao.object_id order by modify_date desc

30%이상 조각난 인덱스 조회

SELECT DB_NAME() AS 'DatabaseName' , OBJECT_NAME(S.OBJECT_ID) AS 'TableName' , I.NAME AS 'IndexName' , ROUND(AVG_FRAGMENTATION_IN_PERCENT, 2) AS 'Fragmentation %' FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) AS S INNER JOIN SYS.INDEXES AS I ON S.OBJECT_ID = I.OBJECT_ID AND S.INDEX_ID = I.INDEX_ID WHERE S.DATABASE_ID = DB_ID() -- 현재 데이터베이스 AND I.NAME IS NOT NULL -- HEAP은 무시 AND S.AVG_FRAGMENTATION_IN_PERCENT > 30 AND OBJECTPROPERTY(S.OBJECT_ID, 'IsMsShipped') = 0 -- 시스템 개체 무시 ORDER BY [Fragmentation %] DESC

작업스케줄러 실패 모니터링

SELECT sysjobs.name, sysjobs.description, sys_srvs.last_run_outcome, sys_srvs.last_outcome_message, sys_srvs.last_run_date, sysjobschedules.next_run_date FROM msdb.dbo.sysjobs AS sysjobs INNER JOIN msdb.dbo.sysjobservers AS sys_srvs ON sysjobs.job_id = sys_srvs.job_id LEFT OUTER JOIN msdb.dbo.sysjobschedules as sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id WHERE sys_srvs.last_run_outcome = 0; --실패JOB