--데이터베이스 버전 확인 SELECT @@VERSION

--데이터베이스 에디션 확인 SELECT SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('ProductLevel') AS ProductLevel, SERVERPROPERTY('ProductVersion') AS ProductVersion; GO

--데이터베이스 상태값 확인 DECLARE @BRPS BIGINT SELECT @BRPS=cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Batch Requests/sec%' WAITFOR DELAY '000:00:10' SELECT (cntr_value-@BRPS)/10.0 AS "Batch Requests/sec" FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Batch Requests/sec%' ;

--CPU 사용량

SELECT total_cpu_time, total_execution_count, number_of_statements, s2.text --(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text FROM (SELECT TOP 50 SUM(qs.total_worker_time) AS total_cpu_time, SUM(qs.execution_count) AS total_execution_count, COUNT(*) AS number_of_statements, qs.sql_handle --, --MIN(statement_start_offset) AS statement_start_offset, --MAX(statement_end_offset) AS statement_end_offset FROM sys.dm_exec_query_stats AS qs GROUP BY qs.sql_handle ORDER BY SUM(qs.total_worker_time) DESC) AS stats CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2

--가용 메모리 확인 SELECT available_physical_memory_kb/1024 as "Total Memory MB", available_physical_memory_kb/(total_physical_memory_kb*1.0)*100 AS "% Memory Free" FROM sys.dm_os_sys_memory ;

--서버 용량 확인 exec master.dbo.xp_fixeddrives;

--데이터베이스에 접속중인 접속자 수 확인 SELECT DB_NAME(dbid) as DB이름, COUNT(dbid) as 접속자수 FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid

-- 데이터베이스에 접속중인 연결 정보 확인 SELECT S.spid, S.loginame, S.login_time, S.last_batch, C.client_net_address FROM sys.sysprocesses S, sys.dm_exec_connections C WHERE S.spid = C.session_id