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

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

  Date:     April 2015

  SQL Server Version: 2005/2008/2008R2/2012/2014
------------------------------------------------------------------------------
  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.
============================================================================*/

/*
	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

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

/*
	connect via SSMS using  ADMIN: WIN2012SQL14\IE0
*/
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


/*
	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', 
	'max degree of parallelism',
	'max server memory (MB)', 
	'optimize for ad hoc workloads')
ORDER BY [name];
GO

sp_configure 'backup checksum default';
GO
sp_configure 'backup compression default';
GO
sp_configure 'cost threshold for parallelism';
GO
sp_configure 'max degree of parallelism';
GO
sp_configure 'max server memory (MB)';
GO
sp_configure 'optimize for ad hoc workloads';
GO


/*
	To change settings
*/
sp_configure 'max server memory (MB)', 4096;
GO
RECONFIGURE;
GO


sp_configure 'backup checksum default', 1;
GO
sp_configure 'backup compression default', 1;
GO
sp_configure 'cost threshold for parallelism', 25;
GO
sp_configure 'max degree of parallelism', 2;
GO
sp_configure 'optimize for ad hoc workloads', 1;
GO
RECONFIGURE;
GO



/*
	Change settings back to default
*/
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
RECONFIGURE;
GO



/*
	Disable DAC
*/
sp_configure 'remote admin connections', 0;
GO
RECONFIGURE;
GO

/*
	turn off advanced options
*/
sp_configure  'show advanced options', 0
GO
RECONFIGURE;                            
GO
sp_configure;
GO