Overall DMVs

The first stop when you want to know how a SQL Server instance is doing at a high level. This collection covers four DMVs: sys.dm_os_wait_stats (what the server has been waiting on since restart or stats clear), sys.dm_os_performance_counters (a list of every counter name available), sys.dm_os_sys_info (physical memory, CPU count, start time) and sys.dm_os_sys_memory (current memory pressure). The wait-stats query uses Paul Randal's classic "top waits with running percentage" pattern - it filters out the harmless background waits and stops once the top waits account for 99% of the total.

-------------------------------
-- Get list of DMVs
-------------------------------
SELECT name, type_desc
FROM sys.all_objects
WHERE name LIKE 'dm%'
ORDER BY name


/*
sys.dm_os_wait_stats
sys.dm_os_performance_counters
sys.dm_os_sys_info
sys.dm_os_sys_memory
*/



-------------------------------
-- sys.dm_os_wait_stats
--
-- Get overall wait stats - http://sqlserverperformance.wordpress.com/2010/11/18/sql-server-2008-and-2008-r2-diagnostic-information-queries-2/
--                          http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
-------------------------------
-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
   WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP'))
SELECT W1.wait_type, 
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99; -- percentage threshold



-------------------------------
-- sys.dm_os_performance_counters
--
-- Get overall performance counters - tip 2042
-------------------------------
SELECT DISTINCT [object_name]  
FROM sys.[dm_os_performance_counters]  
ORDER BY[object_name]; 



-------------------------------
-- sys.dm_os_sys_info
--
-- Get system information - tip 2289
-------------------------------
SELECT physical_memory_kb /1024 as Memory_MB, scheduler_count as CPUs, sqlserver_start_time, virtual_machine_type_desc
FROM sys.dm_os_sys_info



-------------------------------
-- dm_os_sys_memory
-------------------------------   
select total_physical_memory_kb / 1024 as total_physical_memory_MB, available_physical_memory_kb / 1024 as available_physical_memory_MB, system_memory_state_desc
from sys.dm_os_sys_memory