/*============================================================================
  File:     1_Instance_Settings.sql

  Summary:  This script reviews how to view existing instance
			configuration settings and alter them.

  Date:     April 2018

  SQL Server Version: 2005/2008/2008R2/2012/2014/2016/2017
------------------------------------------------------------------------------
  Written by Erin Stellato, SQLskills.com

  For more scripts and other useful content, go to:
	http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Erin Stellato.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

/*
	version info:
	https://sqlserverbuilds.blogspot.com/
*/
/*
	Check current values for settings
*/
sp_configure;
GO

SELECT *
FROM [master].[sys].[configurations]
ORDER BY [name];
GO

sp_configure  'show advanced options', 1
GO
RECONFIGURE;                            
GO
sp_configure;
GO


/*
	Check current values for specific instance settings
*/
SELECT 
	[name],
	[description],
	[minimum],
	[maximum], 
	[value_in_use]
FROM [master].[sys].[configurations]
WHERE [name] in (
	'backup checksum default',
	'backup compression default',
	'cost threshold for parallelism',
	'Database Mail XPs', 
	'max degree of parallelism',
	'max server memory (MB)', 
	'optimize for ad hoc workloads',
	'remote admin connections')
ORDER BY [name];
GO

sp_configure 'backup checksum default'; /* 2014 and higher only */
GO
sp_configure 'backup compression default'; /* 2008 and higher only */
GO
sp_configure 'cost threshold for parallelism';
GO
sp_configure 'Database Mail XPs';
GO
sp_configure 'max degree of parallelism';
GO
sp_configure 'max server memory (MB)';
GO
sp_configure 'optimize for ad hoc workloads';
GO
sp_configure 'remote admin connections';
GO



/*
	To change settings...
*/


/*
	Enable DAC
*/
sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO

/*
	connect via SSMS using  ADMIN: IE0\SQL2017
*/
SELECT 
	[es].[session_id], 
	[es].[host_name], 
	[es].[program_name], 
	[ep].[endpoint_id], 
	[ep].[name], 
	[ep].[is_admin_endpoint]
FROM [sys].[dm_exec_sessions] [es]
JOIN [sys].[endpoints] [ep] ON [es].[endpoint_id] = [ep].[endpoint_id];
GO


/*
	Confirm memory on the server
	Know your Edition!
	Know features in use for SQL 2016 SP1 and higher!
*/
SELECT *
FROM sys.dm_os_sys_memory;
GO


/*
	Jonathan's calc for max memory...
		1 GB of RAM for the OS
		1 GB for each 4 GB of RAM installed from 4–16 
		1 GB for every 8 GB RAM installed above 16 GB RAM
*/
DECLARE @ServerMem AS INT;
DECLARE @LowRangeMem AS INT;
DECLARE @HighRangeMem AS INT;
DECLARE @MaxMem AS INT;

SET @ServerMem = 256; /* set to memory on server */

IF @ServerMem > 16
BEGIN
	SET @LowRangeMem = 3
	SET @HighRangeMem = (@ServerMem - 16)/8
	SET @MaxMem = @ServerMem - (1 + @LowRangeMem + @HighRangeMem)
	PRINT (@MaxMem)
END;
ELSE
BEGIN
	PRINT ('calculate manually')
END;
GO

DECLARE @ServerMem AS DECIMAL(20,2);
DECLARE @LowRangeMem AS INT;
DECLARE @HighRangeMem AS INT;
DECLARE @MaxMem AS INT;

SET @ServerMem = (SELECT (CAST(total_physical_memory_kb AS DECIMAL(20,2))/1024/1024) FROM sys.dm_os_sys_memory)

IF @ServerMem > 16
BEGIN
	SET @LowRangeMem = 3
	SET @HighRangeMem = (@ServerMem - 16)/8
	SET @MaxMem = @ServerMem - (1 + @LowRangeMem + @HighRangeMem)
	PRINT (@MaxMem)
END;
ELSE
BEGIN
	PRINT ('calculate manually')
END;
GO

sp_configure 'max server memory (MB)', 2048;
GO
RECONFIGURE;
GO


/*
	check schedulers
*/
SELECT *
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE';
GO

sp_configure 'max degree of parallelism', 2;
GO


/*
	Configure other settings
*/	
sp_configure 'backup checksum default', 1; /* 2014 and higher only */
GO
sp_configure 'backup compression default', 1; /* 2008 and higher only */
GO
sp_configure 'cost threshold for parallelism', 25;
GO
sp_configure 'optimize for ad hoc workloads', 1;
GO
RECONFIGURE;
GO


/*
	list all trace flags enabled for this connection
*/
DBCC TRACESTATUS(); 
GO 

/*
	list all trace flags enabled globally
*/ 
DBCC TRACESTATUS(-1); 
GO 

/*
	Enable TF 3226 globally
	**Note that this will only persist until next restart 
	Enable this through SQL Server Configuration Manager, not DBCC TRACEON!
*/
DBCC TRACEON (3226, -1);
GO



/*
	Reset to defaults
*/
sp_configure 'backup checksum default', 0;
GO
sp_configure 'backup compression default', 0;
GO
sp_configure 'cost threshold for parallelism', 5;
GO
sp_configure 'max degree of parallelism', 0;
GO
sp_configure 'max server memory (MB)', 2147483647;
GO
sp_configure 'optimize for ad hoc workloads', 0;
GO
sp_configure 'remote admin connections', 0;
GO
sp_configure  'show advanced options', 0
GO
RECONFIGURE;                            
GO
sp_configure;
GO
