select Properties collate Cyrillic_General_CI_AS 'Properties' , convert(varchar(100),[Values]) 'Values' 
from (
select '*** [ Host Info ] ***' [Properties],replicate('*',50)  [Values]
union all
select  'Is Clustered', case when serverproperty('IsClustered')=1 then 'YES' else 'NO' end
union all
select 'Physical Memory (MB)', ceiling(physical_memory_in_bytes/1024./1024.) 
		from sys.dm_os_sys_info 
union all
select 'CPUs, Total', cpu_count from master.sys.dm_os_sys_info 
union all
select 'CPUs, Physical', cpu_count/hyperthread_ratio from master.sys.dm_os_sys_info 
union all
select 'CPUs per socket', hyperthread_ratio from master.sys.dm_os_sys_info 
union all
select 'OS uptime (days hh:mm:ss)',
	Case When ms_ticks/1000 >= 86400
					Then Convert(VarChar(5), ms_ticks/1000/86400) 
					+ ' days ' 
					Else ''
					End
		   + Convert(VarChar(8), DateAdd(Second, ms_ticks/1000, 0), 108)
	FROM sys.[dm_os_sys_info] DOSI
union all

select '*** [ SQL Server Info ] ***',replicate('*',50)
union all
select 'Start Time (Local)', crdate
from master.sys.sysdatabases where name ='tempdb'
union all
select 'SQL uptime (days hh:mm:ss)',
	Case When datediff(ss,create_date,getdate()) >= 86400
					Then Convert(VarChar(5), datediff(ss,create_date,getdate())/86400) 
					+ ' days ' 
					Else ''
					End
		   + Convert(VarChar(8), DateAdd(Second, datediff(ss,create_date,getdate()), 0), 108)
	FROM master.sys.databases
	where database_id=2
union all 

select '*** [ Memory ] ***',replicate('*',50)
union all

select 'Current SQL Memory (MB)', cntr_value/1024 from master.sys.sysperfinfo 
where counter_name = 'Total Server Memory (KB)'
union all
select 'Target SQL Memory (MB)', cntr_value/1024 from master.sys.sysperfinfo 
where counter_name = 'Target Server Memory (KB)'
union all 

select description, value_in_use
from master.sys.configurations  
where name in ('min server memory (MB)','max server memory (MB)')
union all

select 'Page Life Expectancy (min)', convert(money,cntr_value/60.) 
from sys.dm_os_performance_counters 
where [object_name] like '%:Buffer Manager%' 
and counter_name='Page life expectancy'
union all

select 'Buffer Cache Size (MB)', convert(numeric(10,2),count(*)/128.)
from sys.dm_os_buffer_descriptors
union all

select 'Procedure Cache Size (MB)',	ceiling(sum(cast(size_in_bytes as numeric(10,2)))/1024./1024.)
FROM sys.dm_exec_cached_plans		
union all

select '*** [ Configuration ] ***',replicate('*',50)
union all

select description, value_in_use
from master.sys.configurations  
where name in ('cost threshold for parallelism','max degree of parallelism','priority boost','xp_cmdshell', 'scan for startup procs','remote admin connections','Agent XPs','Database Mail XPs')
union all
select description, value_in_use
from master.sys.configurations  
where name='default trace enabled'
union all
select 'default trace path', path
	from sys.traces 
where is_default=1 
union all

select 'Total traces', count(1) from sys.traces 
union all
select 'Traces '+ case when status=0 then 'STOPPED' 
				when status=1 then 'RUNNING'
				else 'UNKNOWN' end, count(1)
  from sys.traces 
 group by status

) as q

	select 'Current Memory consumption:' [Memory]
	
	select convert(money,cntr_value/60.) [Page Life Expectancy (min)] 
	FROM sys.dm_os_performance_counters 
	WHERE [object_name] like '%:Buffer Manager%' 
	AND counter_name='Page life expectancy'


	select 'Usage by DB:  ' + convert(varchar(20),convert(numeric(10,2),count(*)/128.)) +' (MB)' [Buffer Cache] 
		FROM sys.dm_os_buffer_descriptors
		
	SELECT 
		CASE database_id 
			WHEN 32767 THEN 'ResourceDb' 
			ELSE db_name(q.database_id) 
			END AS [Database],
		(select convert(numeric(10,2),count(*)/128.) from sys.dm_os_buffer_descriptors where database_id=q.database_id and is_modified=0) [Clean (MB)],
		(select convert(numeric(10,2),count(*)/128.) from sys.dm_os_buffer_descriptors where database_id=q.database_id and is_modified=1) [Dirty (MB)],
		(select convert(numeric(10,2),count(*)/128.) from sys.dm_os_buffer_descriptors where database_id=q.database_id) [Total (MB)]
	FROM sys.dm_os_buffer_descriptors q
	GROUP BY database_id
	ORDER BY 2 DESC


	select 'Usage by all objects (Grouped):  ' +  
			convert(varchar(20),ceiling(sum(cast(size_in_bytes as numeric(10,2)))/1024./1024.)) + ' (MB)' [Procedure Cache]
	FROM sys.dm_exec_cached_plans

	select  objtype [Object Type], 
			convert(numeric(10,2),sum(size_in_bytes/1024.)/1024.) [Size (MB)]
			, avg(usecounts) AS [Avg Use Count]
        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024./1024. AS [Total MB - USE Count 1]
        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
	from sys.dm_exec_cached_plans 
	group by objtype
	order by 2 desc

	select 'Usage by all objects (Detailed):' [Procedure Cache]
	SELECT db_name(sco.dbid) [Database],
			sco.cacheobjtype [Cache Object Type],
			sco.objtype [Object Type], 
			convert(numeric(10,2),sum(sco.pagesused)/128.) [Size MB],
			COUNT(1) [Count] 
	from master.sys.syscacheobjects sco
	WHERE sco.dbid<>32767
	GROUP BY sco.dbid, sco.cacheobjtype, sco.objtype
	ORDER BY 4 desc