Check DB Status

--########## Memory ##########--
-- check memory
SELECT total_physical_memory_kb/1024  [總記憶體(MB)],
available_physical_memory_kb/1024 [可用記憶體(MB)],
total_page_file_kb/1024    [總分頁檔(MB)],
available_page_file_kb/1024   [可用分頁檔(MB)],
system_cache_kb/1024    [系統快取記憶體(MB)],
system_high_memory_signal_state,
system_low_memory_signal_state,
system_memory_state_desc
FROM sys.dm_os_sys_memory
  
SELECT type,
SUM(single_pages_kb)/1024    [Single記憶體佔用大小(MB)],
SUM(multi_pages_kb)/1024    [Multi記憶體佔用大小(MB)],
SUM(virtual_memory_reserved_kb)/1024 [保留虛擬記憶體的數量(MB)],
SUM(virtual_memory_committed_kb)/1024 [使用虛擬記憶體的數量(MB)]
FROM sys.dm_os_memory_clerks
GROUP BY TYPE
ORDER BY [Single記憶體佔用大小(MB)] DESC

-- check buffer memory  
SELECT ISNull(DB_NAME(database_id),N'資料資料庫') AS [資料庫],
Count(*)/128 AS [耗用暫存記憶體(MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY 2 DESC


SELECT OBJECT_NAME(p.object_id) AS [物件名稱],
p.index_id,
Count(*)/128 AS [耗用暫存記憶體(MB)],
Count(*) AS [暫存區數量]
FROM sys.dm_os_buffer_descriptors dobd
JOIN sys.allocation_units au ON au.allocation_unit_id=dobd.allocation_unit_id
JOIN sys.partitions p ON au.container_id=p.hobt_id
WHERE dobd.database_id = DB_ID()
AND ObjectProperty(P.object_id,'ISSystemTable') = 0
GROUP BY p.object_id, P.index_id
ORDER BY 4 DESC


-- Buffer cache hit ratio  
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]
FROM (
SELECT cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio'
AND object_name = CASE 
WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:Buffer Manager'
ELSE 'MSSQL$' + rtrim(@@SERVICENAME) +':Buffer Manager' END
) a
CROSS JOIN (
SELECT cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
AND object_name = CASE
WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:Buffer Manager'
ELSE 'MSSQL$' + rtrim(@@SERVICENAME) +':Buffer Manager' END
) b;


--########## DB size ##########--
-- check db size
EXEC sp_spaceused

-- check db file size
DECLARE @tDBSize TABLE
(DBName SYSNAME DEFAULT(DB_NAME()), Fileid INT, FileGroup INT, TotalExtents INT, UsedExtents INT, Name SYSNAME, FileName NVARCHAR(4000))
DECLARE @tLogSize TABLE
(DBName sysname, logsize float, used float, status int)

INSERT @tDBSize(Fileid,FileGroup,TotalExtents,UsedExtents,Name,FileName)
EXEC ('DBCC showfilestats')
INSERT INTO @tLogSize
EXECUTE ('DBCC SQLPERF(LOGSPACE)')

SELECT D.DBName AS [DB Name],
D.Name AS [DB fiel name],
TotalExtents*64.0/1024 AS [data file size(MB)],
UsedExtents*64.0/1024 AS [data real size(MB)],
logsize AS [log file size(MB)],
(logsize*used/100) AS [log real size(MB)]
FROM @tLogSize L 
INNER JOIN @tDBSize D
ON L.DBName=D.DBName
ORDER BY [data file size(MB)] DESC
  

--########## Session ##########--
-- check session  
SELECT * FROM sys.dm_exec_sessions


-- check waiting session
SELECT * FROM sys.dm_os_waiting_tasks
SELECT * FROM sys.dm_os_wait_stats


--########## Index ##########--
SELECT id.name AS [Index Name],
id.type_desc AS [Index Type],
id.object_id AS [Object ID],
dius.user_seeks,
dius.user_scans,
dius.user_lookups,
dius.user_updates,
dius.last_user_seek,
dius.last_user_scan,
dius.last_user_lookup,
dius.last_user_update,
dius.last_system_scan
FROM sys.dm_db_index_usage_stats dius
INNER JOIN sys.indexes id
ON dius.object_id = id.object_id
WHERE user_seeks > 0 
AND user_scans > 0
ORDER BY 5 DESC



--########## Duration ##########--
-- top 10 waiting
SELECT TOP 10 wait_type,
waiting_tasks_count,
( wait_time_ms - signal_wait_time_ms ) AS resource_wait_time,
max_wait_time_ms,
CASE WHEN waiting_tasks_count = 0 THEN 0
ELSE wait_time_ms / waiting_tasks_count
END AS avg_wait_time
FROM sys .dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
AND wait_type NOT LIKE 'XE%'
AND wait_type NOT IN ( 'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH',
'KSOURCE_WAKEUP', 'BROKER_TASK_STOP', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'DBMIRROR_EVENTS_QUEUE', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',
'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE',
'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH' , 'LOGMGR_QUEUE',
'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS' )
ORDER BY wait_time_ms DESC


-- top 10 duration processes
SELECT TOP 10 *,
(total_worker_time / execution_count) AS avgworkertime
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY avgworkertime DESC


-- top 10 used processes
SELECT TOP 10 *,
(total_worker_time / execution_count) AS avgworkertime
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY avgworkertime DESC


-- top 10 CPU use processes
SELECT TOP 10 substring(st.text,
( qs.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset WHEN -1 THEN DATALENGTH (st.text) ELSE qs.statement_end_offset END -
statement_start_offset ) / 2 ) + 1 ) AS statement_text,
total_worker_time / 1000 AS total_worker_time_ms,
execution_count,
( total_worker_time / 1000 ) / execution_count AS avg_worker_time_ms,
total_logical_reads,
total_logical_reads / execution_count AS avg_logical_reads,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle ) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC


-- 參考
-- GOOGLE