Machine

select 
  serverproperty('ComputerNamePhysicalNetBIOS'),
  cpu_count,
  physical_memory_kb
from [master].[sys].[dm_os_sys_info];

Instance

select 
  serverproperty('ServerName') as ServerName,
  serverproperty('INSTANCENAME') as instance_name,
  serverproperty('ProductVersion') as ProductVersion,
  serverproperty('ProductLevel') as ProductLevel,
  serverproperty('Edition') as Edition,
  sqlserver_start_time as StartTime,
  committed_kb,
  @@version as Version,
  serverproperty('COLLATION') as collation,
  CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   
    WHEN 1 THEN 'Windows Authentication'   
    WHEN 0 THEN 'Windows and SQL Server Authentication'   
  END as AuthenticationMode
from [master].[sys].[dm_os_sys_info];

Port

select 
  ip_address,
  port,
  state_desc
from [master].[sys].[dm_tcp_listener_states]
where is_ipv4 = 1;

Cluster Nodes

select 
  nodename,
  status,
  status_description,
  is_current_owner
from [master].[sys].[dm_os_cluster_nodes]
order by nodename;

MEMORY

Memory Configuration

SELECT name, value, value_in_use, [description] 
FROM sys.configurations
WHERE name like '%server memory%'
ORDER BY name;

Memory Usage

select
  (committed_kb/1024) AS commited_mb
from [master].[sys].[dm_os_sys_info];

select (physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB 
from sys.dm_os_process_memory;

Active Sessions

select
  s.session_id,
  s.host_name,
  s.program_name,
  s.login_name,
  s.login_time,
  s.cpu_time,
  s.reads,
  s.writes,
  s.logical_reads,
  s.row_count,
  s.total_elapsed_time,
  db.name as db_name
from sys.dm_exec_sessions s
join sys.databases db on s.database_id = db.database_id
order by s.cpu_time desc, s.host_name, s.program_name, s.login_name, s.login_time;

PROCESSES

Active Sessions

select
  s.session_id,
  s.host_name,
  s.program_name,
  s.login_name,
  s.login_time,
  s.cpu_time,
  s.reads,
  s.writes,
  s.logical_reads,
  s.row_count,
  s.total_elapsed_time,
  db.name as db_name
from sys.dm_exec_sessions s
join sys.databases db on s.database_id = db.database_id
order by s.cpu_time desc, s.host_name, s.program_name, s.login_name, s.login_time;

STORAGE

Database Size

select
  database_id,
  db_name(database_id) as db_name,
  floor(sum(case type when 0 then size else 0 end)*8/1024) as data_size_mb,
  floor(sum(case type when 1 then size else 0 end)*8/1024) as log_size_mb
from sys.master_files
group by database_id, db_name(database_id)
order by 1;

File Size

select 
  db_name(f.database_id),
  f.file_id,
  f.physical_name,
  f.type_desc,
  floor(f.size*8/1024) as file_size_mb,
  (case f.growth when 0 then 'N' else 'Y' end) as auto_grow,
  floor((case f.max_size when -1 then 0 else f.max_size end)/1024*8) as file_max_size_mb,
  volume_mount_point,
  floor(total_bytes/1024/1024) as total_mb,
  floor(available_bytes/1024/1024) as available_mb
from [master].[sys].[master_files] f
cross apply [master].[sys].[dm_os_volume_stats](f.database_id,f.file_id);

PERFORMANCES

Performance Counters

select 
  object_name,
  counter_name,
  instance_name,
  cntr_value
from master.sys.dm_os_performance_counters
where counter_name in (
'Database pages','Active Temp Tables','User Connections','Processes blocked',
'Data File(s) Size (KB)','Log File(s) Size (KB)','Log File(s) Used Size (KB)',
'Database Cache Memory (KB)','Free Memory (KB)','Reserved Server Memory (KB)','Target Server Memory (KB)','Total Server Memory (KB)'
)
order by object_name, counter_name, instance_name;

Wait Statistics

select top 10 *
from master.sys.dm_os_wait_stats
order by wait_time_ms desc;

Latch Statistics

select *
from master.sys.dm_os_latch_stats
order by wait_time_ms desc;

Disk Latency

SELECT  LEFT(physical_name, 1) AS drive,
        CAST(SUM(io_stall_read_ms) / 
            (1.0 + SUM(num_of_reads)) AS NUMERIC(10,1)) 
                          AS 'avg_read_disk_latency_ms',
        CAST(SUM(io_stall_write_ms) / 
            (1.0 + SUM(num_of_writes) ) AS NUMERIC(10,1)) 
                          AS 'avg_write_disk_latency_ms',
        CAST((SUM(io_stall)) / 
            (1.0 + SUM(num_of_reads + num_of_writes)) AS NUMERIC(10,1)) 
                          AS 'avg_disk_latency_ms'
FROM    sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
        JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
                                       AND mf.file_id = divfs.file_id
GROUP BY LEFT(physical_name, 1)
ORDER BY avg_disk_latency_ms DESC;

Top 5 requests by CPU time

SELECT TOP 5 
  total_worker_time/execution_count AS [Avg CPU Time],
  last_execution_time,
  execution_count,
  last_rows,
   SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;