/*******************************************************************************************************

					SQL SERVER - Initial Checks.

********************************************************************************************************

	Description: 
	Purpose:  
	Author: Ian Stirk.
	Date: May 2013.
	
	Notes: 1. Some steps may fail if you dont have permissions.			
  
*********************************************************************************************************/

-- Do not lock anything, and do not get held up by any locks. 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- Get SQL Version  
DECLARE @SQLVersion INT 
SELECT @SQLVersion = CAST(SUBSTRING(@@VERSION, 22, 4) AS INT)  -- Example @@Version: Microsoft SQL Server 2008 (SP3) - 10.0.5768.0 (X64) 

-- This routine needs SQL Server 2005 or higher to run.
IF @SQLVersion < 2005 
BEGIN
	RAISERROR('This routine needs SQL Server 2005 or higher to run. Routine has exited', 16,1)
	RETURN
END


-- Step counter value, used to separate pieces of outout. 
DECLARE @StepCounter AS INT
SET @StepCounter = 0
	

/************************************************************************************/
/*       																			*/
/* Purpose: Show datetime of run.							   						*/
/* Notes: 1. xxxxx																	*/
/*       																			*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' + 'DateTime of run' 

SELECT CONVERT(VARCHAR(33), GetDate(), 109) AS RunDateTime


/************************************************************************************/
/*       																			*/
/* Purpose: Show the version of SQL Server.							   			    */
/* Notes: 1. Running the expected version of SQL Server?							*/
/*       																			*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' + 'SQL Server version' 

SELECT @@VERSION 


/************************************************************************************/
/*       																			*/
/* Purpose: Get property info about server instance.								*/
/* Notes: 1. IsIntegratedSecurityOnly: 1 = windows only     						*/
/*        2. ProductLevel - contains RTM or SP level. Is SP the correct one?!		*/
/*        3. ProductVersion - major.minor.build										*/
/*       																			*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' + 'Property info about server instance' 

SELECT SERVERPROPERTY('ServerName') AS [ServerName]
	,SERVERPROPERTY('InstanceName') AS [InstanceName]
	,SERVERPROPERTY('MachineName') AS [MachineName]
	,SERVERPROPERTY('Edition') AS [Edition]
	,SERVERPROPERTY('ProductVersion') AS [ProductVersion]
	,SERVERPROPERTY('ProductLevel') AS [ProductLevel]
	,SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly]
	,SERVERPROPERTY('IsClustered') AS [IsClustered]


/************************************************************************************/
/*       																			*/
/* Purpose: Get OS information.														*/
/* Notes: 1. CPU/Memory/last reboot time useful.									*/
/*       																			*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' + 'OS information' 

SELECT cpu_count AS [Logical CPUs]
, cpu_count / hyperthread_ratio AS [Physical CPUs]
, CAST(physical_memory_in_bytes / 1024.0 / 1024.0 /1024.0 AS DECIMAL(28,2)) AS [Memory (GB)]
, DATEADD(ss, -(ms_ticks / 1000), GetDate()) AS [Start DateTime]
--	, sqlserver_start_time AS [Start DateTime]  -- In 2008+
FROM sys.dm_os_sys_info


/************************************************************************************/
/*       																			*/
/* Purpose: Show SQL Server configuration info.						   			    */
/* Notes: 1. priority boost - should be off (0)										*/
/*        2. cost threshold for parallelism	(evaluate with MAXDOP)					*/
/*        3. max degree of parallelism (1 or 8?)									*/
/*        4. max server memory (MB) - evaluate in context of server memory			*/
/*        5. clr enabled - generally disable, unless needed							*/
/*        6. optimize for ad hoc workloads - often recommended to have on.			*/
/*       																			*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' + 'Configuration - specifics' 

SELECT name, description, value_in_use 
FROM sys.configurations 
WHERE NAME IN(
  'clr enabled'
, 'max degree of parallelism'	
, 'cost threshold for parallelism'	
, 'max server memory (MB)'			-- Set appropriately
, 'optimize for ad hoc workloads'	-- should be 1.
, 'priority boost'					-- should be 0
)
ORDER BY name


/************************************************************************************/
/*       																			*/
/* Purpose: Identify what is causing the waits.										*/
/* Notes: 1. Check Tom Davidson's 2005 article to decode WAIT Types.                */
/*       																			*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' +  'Most common waits' 

;WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','ONDEMAND_TASK_QUEUE'))

SELECT W1.wait_type, 
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99; -- percentage threshold


/************************************************************************************/
/*       																			*/
/* Purpose: Signal Waits - CPU pressure.             								*/
/* Notes: 1. How much time is spent swapping threads - above 20% is bad		        */
/*       																			*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' +  'Signal Waits - CPU pressure' 

-- Signal Waits for instance
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits],
CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits]
FROM sys.dm_os_wait_stats WITH (NOLOCK) OPTION (RECOMPILE);


/************************************************************************************/
/*       																			*/
/* Purpose: Show SQL Server database info.				     		   			    */
/* Notes: 1. Check compatibility level 												*/
/*        2. is_read_committed_snapshot_on	(1 is good for concurrency)				*/
/*        3. recovery_model (want simple on non-prod boxes. Bulk_logged for prod)	*/
/*        4. page_verify - want CHECKSUM											*/
/*       																			*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' + 'sys.databases' 

SELECT name, compatibility_level, recovery_model_desc, page_verify_option_desc, is_read_committed_snapshot_on
FROM sys.databases ORDER BY name


/************************************************************************************/
/*       																			*/
/* Purpose: CPU utilization usage per object database.								*/
/* Notes: 1. Is a single DB hogging CPU? Maybe needs a separate server				*/
/*       																			*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' + 'CPU usage by database' 

-- Get CPU utilization by database
;WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], 
 SUM(total_worker_time) AS [CPU_Time_Ms]
 FROM sys.dm_exec_query_stats AS qs
 CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 
              FROM sys.dm_exec_plan_attributes(qs.plan_handle)
              WHERE attribute = N'dbid') AS F_DB
 GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
       DatabaseName, [CPU_Time_Ms], 
       CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) 
	   OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY [CPUPercent] DESC OPTION (RECOMPILE);


/************************************************************************************/
/*       																			*/
/* Purpose: Memory usage per database.										        */
/* Notes: 1. Is a single DB hogging memory? Maybe needs a separate server			*/
/*       																			*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' + 'Memory usage by database' 

SELECT 
	ISNULL(DB_NAME(database_id), 'ResourceDb') AS DatabaseName
	, CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2))  AS [Size (MB)] 
	, CAST(COUNT(row_count) * 8.0 / (1024.0) / 1024.0 AS DECIMAL(28,2)) AS [Size (GB)] 
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY [Size (MB)] DESC  

