보미네

SELECT
    @@servername servername
     , SERVERPROPERTY('Edition') as edition
     , cpu_count / hyperthread_ratio AS sockets
     , hyperthread_ratio AS logical_cores_per_socket
     , cpu_count AS total_logical_cpu_count
     , @@version version
     , CONVERT (varchar(256), SERVERPROPERTY('collation')) collation
FROM sys.dm_os_sys_info;
go
select * from  sys.dm_os_sys_info;

select *
from sysprocesses a
    cross apply 
		(
		select * from sys.dm_exec_sql_text(a.sql_handle)
		) b
where spid > 50 and kpid <> 0 and spid <> @@spid and loginame <> 'lazylog'
    go

SELECT
        cpu_idle = record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int'),
        cpu_sql = record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')
FROM (
         SELECT TOP 1 CONVERT(XML, record) AS record
         FROM sys.dm_os_ring_buffers
         WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
           AND record LIKE '% %'
         ORDER BY TIMESTAMP DESC
     ) as cpu_usage


    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT '01' OrderKey,'OS_MEMORY_TOTAL' MemType, physical_memory_kb PagesKB FROM SYS.DM_OS_SYS_INFO
UNION ALL
SELECT '02' OrderKey,'SQL_VISIBLE_TARGET_KB' MemType, visible_target_kb PagesKB FROM SYS.DM_OS_SYS_INFO
UNION ALL
SELECT '03' OrderKey,'SQL_COMMITTED_KB' MemType, committed_kb PagesKB FROM SYS.DM_OS_SYS_INFO
UNION ALL
SELECT '04' OrderKey,'OS_MEMORY_CLERKS_TOTAL' MemType, SUM(pages_kb) + sum(virtual_memory_committed_kb)+sum(awe_allocated_kb)+sum(shared_memory_committed_kb) PagesKB FROM SYS.DM_OS_MEMORY_CLERKS
UNION ALL
SELECT *
FROM
    (
        SELECT TOP 20 '04' OrderKey, type MemType, SUM(pages_kb)  +sum(virtual_memory_committed_kb)+sum(awe_allocated_kb)+sum(shared_memory_committed_kb) PagesKB  FROM SYS.DM_OS_MEMORY_CLERKS
        GROUP BY TYPE
        ORDER BY 3 DESC
    ) A
ORDER BY 1,3 DESC

log_reuse_wait_desc  에 oldest_page 라고 뜨면서 log 가 재사용되지 못하면 아래 스크립트를 이용해 해결한다. 

alter database a set target_recovery_time = 0 seconds 
go

select name, target_recovery_time_in_seconds, log_reuse_wait_desc from sys.databases 
go

checkpoint 
go

backup database a to disk = 'NUL'
go

select name, target_recovery_time_in_seconds, log_reuse_wait_desc from sys.databases 
go

프로파일러 SQLAgent JobStep id 값으로 어떤 Job 인지 구분 하려면 아래 쿼리를 이용한다. 

select master.dbo.fn_varbintohexstr(job_id), b.*
from msdb.dbo.sysjobs b