/*============================================================================
  File:     IE0_HomeworkDay1.sql

  Summary:  This script reviews how to view existing database
			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.
============================================================================*/


/*
	OS settings to check:

	Windows Power Plan
		Control Panel | Hardware | Power Options

	Instant File Initialization
		Start | Run | secpol | Local Policies | User Rights Assignment | Perform volume maintenance tasks
		(confirm that the account that runs the SQL Server service has the right)

	Lock Pages in Memory
		Start | Run | secpol | Local Policies | User Rights Assignment | Lock pages in memory
		(confirm that the account that runs the SQL Server service has the right)

*/


/*
	Check instance settings
*/
SELECT 
	[name],
	[description],
	[minimum],
	[maximum], 
	[value_in_use]
FROM [master].[sys].[configurations]
WHERE [name] in (
	'backup checksum default',			/* should be enabled, value_in_use = 1, 2014+ */
	'backup compression default',		/* should be enabled, value_in_use = 1 */
	'cost threshold for parallelism',	/* set to something higher than 5, like 25 */
	'Database Mail XPs',				/* should be enabled, value_in_use = 1 */
	'max degree of parallelism',		/* set to something other than 0, depends on server CPU configuration */
	'max server memory (MB)',			/* set to something other than 2147483647, depends on server memory */
	'optimize for ad hoc workloads',	/* should be enabled, value_in_use = 1 */
	'remote admin connections')			/* should be enabled, value_in_use = 1 */
ORDER BY [name];
GO


/*
	Jonathan's calc for max memory...
*/
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


/*
	list all trace flags enabled globally
	recommended: 3226 and 1118 
	others...it depends
*/ 
DBCC TRACESTATUS(-1); 
GO 

/*
	Check number of error logs (default is 6)
	Set to something higher like 20, 24, 30
	Also create a to cycle regularly
*/
DECLARE @LogCount INT
EXEC master.dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'NumErrorLogs', 
    @LogCount OUTPUT
SELECT @LogCount

/*
	check tempdb configuration
	default is one file, you need multiple, depends on CPU configuration
*/
USE [tempdb];
GO

SELECT 
	[file_id],	
	[type],	
	[type_desc],	
	[name],
	[physical_name],
	[size] [Size In Pages],
	[size]*8 [Size in KB],
	[max_size],	
	[growth],
	[is_percent_growth]
FROM [sys].[database_files];

/*
	Is Database Mail running?
*/
EXECUTE msdb.dbo.sysmail_help_status_sp 

/*
	Do you have any operators?
*/
SELECT 
	[id], 
	[name], 
	[enabled], 
	[email_address]
FROM [msdb].[dbo].[sysoperators];
GO

/*
	Do you have any agent alerts set up?
*/
SELECT	
	[id],
	[name],
	[event_source],
	[message_id],
	[severity],
	[enabled]
FROM [msdb].[dbo].[sysalerts];
GO


/*
	check database settings
	1 = ON
*/
SELECT 
	[database_id],		
	[name],
	[collation_name],
	[recovery_model_desc],				/* depends on RTO and RPO, to be continued */
	[compatibility_level],				/* does it match SQL version? */
	[is_auto_shrink_on],				/* should be disabled */
	[is_auto_create_stats_on],			/* should be enabled */
	[is_auto_update_stats_on],			/* should be enabled */
	[is_auto_update_stats_async_on],	/* Glenn says enable, you choose */
	[is_parameterization_forced],		/* should be set to SIMPLE */
	[page_verify_option_desc]			/* should be set to CHECKSUM */
FROM [master].[sys].[databases]
ORDER BY [database_id];
GO

/*
	check database-scoped settings
	**change DB name
*/
USE [AdventureWorks2017];
GO
SELECT 
	[configuration_id], 
	[name], 
	[value], 
	[value_for_secondary]
FROM [sys].[database_scoped_configurations]; 
GO

/*
	From Jon:
	what is listening on port 1433
	**run in cmd prompt
*/
sqlcmd -L 