select /*[name],*/ [is_auto_shrink_on], [is_auto_update_stats_on], [is_auto_update_stats_async_on], [is_parameterization_forced], [log_reuse_wait_desc] from sys.databases;

select /*[name],*/ [is_auto_shrink_on], [is_auto_update_stats_on], [is_auto_update_stats_async_on], [is_parameterization_forced], [log_reuse_wait_desc] from sys.databases;

select [counter_name], [instance_name], [cntr_value] from sys.dm_os_performance_counters where [object_name] = 'SQLServer:Locks' and [counter_name] in ('Lock Requests/sec', 'Lock Timeouts/sec', 'Number of Deadlocks/sec', 'Lock Waits/sec')
union
select [counter_name], [instance_name], [cntr_value] from sys.dm_os_performance_counters where [object_name] = 'SQLServer:Databases' and [counter_name] in ('Log Flush Waits/sec', 'Log Flush Wait Time')
union
select [counter_name], [instance_name], [cntr_value] from sys.dm_os_performance_counters where [object_name] = 'SQLServer:Wait Statistics'
order by [counter_name], [instance_name], [cntr_value] desc

  select [session_id], [exec_context_id], [blocking_session_id], [blocking_exec_context_id], [wait_duration_ms], [wait_type], [resource_description] 
  from sys.dm_os_waiting_tasks 
  where [blocking_session_id] is not null 
     or [wait_duration_ms] > 10000

with x as (
    select
	      pool_id,
		  cacheobjtype,
		  objtype,
		  size_in_bytes,
		  refcounts,
		  usecounts,
	      sum([size_in_bytes]) over (partition by pool_id, cacheobjtype, objtype order by 1/0) * 100 / sum([size_in_bytes]) over () as [percentage]
   from sys.dm_exec_cached_plans
)
select pool_id, 
	   cacheobjtype,
	   objtype,
       sum(size_in_bytes) as [bytes],
	   sum(refcounts) as refs,
	   count(1) as cnt,
	   sum(usecounts) as [uses],
	   max(percentage) as [percentage]
from x
group by pool_id, cacheobjtype, objtype

sp_configure 'optimize for ad hoc workloads'