/*******************************************************************************************************
 
					SQL SERVER - Initial Checks.
 
********************************************************************************************************
 
	Description: 
	Purpose:  
	Author: Ian Stirk.
	Date: May 2013.
	
	Notes: 1. Some steps may fail if you dont have permissions.			
	       2. Change YourDatabaseNameHere to the name of your own database (or run script in the database you want info on).			
  
*********************************************************************************************************/
 
-- Do not lock anything, and do not get held up by any locks. 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
--USE [YourDatabaseNameHere]
 
-- Step counter value, used to separate pieces of outout. 
DECLARE @StepCounter AS INT
SET @StepCounter = 10 -- part 1 has 10 steps...
 
 
/************************************************************************************/
/*       																			*/
/* Purpose: What are the physical disk metric values.								*/
/* Notes: 1. Under 20ms is ok, above this need investigating	   					*/
/*       																			*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' +  'Disk physical average read/write timings.' 
 
SELECT DB_NAME(database_id) AS DatabaseName
	, file_id
	, io_stall_read_ms / num_of_reads AS 'Average read time'
	, io_stall_write_ms / num_of_writes AS 'Average write time'
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
WHERE num_of_reads > 0 
AND num_of_writes > 0
ORDER BY DatabaseName
 
 
/************************************************************************************/
/*       																			*/
/* Purpose: Reads per write, by database.										    */
/* Notes: 1. Separate out a DB or for OLAP/OLTP?                                    */
/*        2. OLAP often needs more indexes											*/
/*        3. NULL DatabaseName means this query was run adhoc or prepared.			*/
/*       																			*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' + 'Reads per write, by database' 
 
SELECT TOP 10 
        DB_NAME(qt.dbid) AS DatabaseName
        , SUM(total_logical_reads) AS [Total Reads]
        , SUM(total_logical_writes) AS [Total Writes]
        , SUM(total_logical_reads) / CASE WHEN SUM(total_logical_writes) = 0 THEN 1 ELSE SUM(total_logical_writes) END AS [Reads Per Write]
        , SUM(qs.execution_count) AS [Execution count]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Reads Per Write] DESC;
 
 
/************************************************************************************/
/*       																			*/
/* Purpose: Space used.																*/
/* Notes: 1. What is DB size, and how much is unused?		             			*/
/*       																			*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' + 'Space used' 
 
EXEC sp_SpaceUsed
 
 
/************************************************************************************/
/*       																			*/
/* Purpose: Database statistics settings.										    */
/* Notes: 1. is_auto_create_stats_on should be on (1).								*/
/*        2. is_auto_update_stats_on should be on (1).								*/
/*        3. is_auto_update_stats_async_on should be off (0)     					*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' + 'Database statistics settings' 

SELECT name
, is_auto_create_stats_on AS [AutoCreateStatistics]
, is_auto_update_stats_on AS [AutoUpdateStatistics]
, is_auto_update_stats_async_on AS [AutoUpdateStatisticsAsync]
FROM master.sys.databases
ORDER BY name
 
 
/************************************************************************************/
/*       																			*/
/* Purpose: State of your statistics.										        */
/* Notes: 1. When last updated?! Maybe needs intelligent stats utility?				*/
/*       																			*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' +  'State of statistics settings (current database)'
 
SELECT 
	ss.name AS SchemaName
	, st.name AS TableName
	, s.name AS IndexName 
	, STATS_DATE(s.id,s.indid) AS 'Statistics Last Updated'
	, s.rowcnt AS 'Row Count'				
	, s.rowmodctr AS 'Number Of Changes'	
	, CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0)
				 AS DECIMAL(28,2)) AS '% Rows Changed'
FROM sys.sysindexes s 
INNER JOIN sys.tables st ON st.[object_id] = s.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE s.id > 100	-- user table/index
AND s.indid > 0		-- Clustered and non-clustered indexes
AND s.rowcnt >= 500	-- want at least 500 rows
ORDER BY 'Statistics Last Updated' DESC 
 
 
/************************************************************************************/
/*       																			*/
/* Purpose: 																		*/
/* Notes: 1. Missing indexes can be indicative of other bad practices in a given DB.*/
/*       																			*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' +  'Databases with the most missing indexes' 
 
SELECT 
	DB_NAME(database_id) AS DatabaseName
	, COUNT(*) AS [Missing Index Count]
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY [Missing Index Count] DESC
 
 
/************************************************************************************/
/*       																			*/
/* Purpose: What are the most costly missing indexes?								*/
/* Notes: 1. Indexes prime method to improve performance							*/
/*        2. Look at indexes without include_columns first.							*/
/*       																			*/
/*       																			*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' +  'Most costly missing indexes' 
 
SELECT TOP 40 -- Too many INCLUDE cols in top entries...  
	ROUND(avg_total_user_cost * avg_user_impact 		
		* (user_seeks + user_scans),0) AS [Total Cost]  
	, [statement] AS [Table Name]
	, equality_columns
	, inequality_columns
	, included_columns
FROM		sys.dm_db_missing_index_groups g 
INNER JOIN	sys.dm_db_missing_index_group_stats s 
ON s.group_handle = g.index_group_handle 
INNER JOIN	sys.dm_db_missing_index_details d 
ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC
 
 
/************************************************************************************/
/*       																			*/
/* Purpose: Identify missing PKs													*/
/* Notes: 1. Most tables should have PK (for RI, FK), unless good reason.			*/
/*       																			*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' +  'Missing Primary Keys' 
 
SELECT DB_NAME(DB_ID()) AS DatabaseName, SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY SchemaName, TableName;
 
 
 
/************************************************************************************/
/*       																			*/
/* Purpose: Identify heaps that have non-clustered indexes.							*/
/* Notes: 1. Often Heaps with Non-clustered Indexes better as Cluster based on NC.  */
/*       																			*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' +  'Heaps with Non-clustered Indexes' 
 
SELECT 
	DB_NAME(DB_ID()) AS DatabaseName
	, ss.name AS SchemaName
	, st.NAME AS TableName
	, i.NAME AS IndexName
	, i.type_desc
	, si.rowcnt
INTO #HeapWithIndexes2
FROM sys.indexes I 
LEFT JOIN sys.sysindexes SI ON SI.indid = I.index_Id AND i.object_id = si.id
INNER JOIN sys.tables st ON st.[object_id] = si.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE 1=2
 
-- tables names that have a heap...
SELECT DISTINCT ss.name AS schemaName, st.name
INTO #Heaps
FROM sys.indexes I 
LEFT JOIN sys.sysindexes SI ON SI.indid = I.index_Id 
							AND i.object_id = si.id
INNER JOIN sys.tables st ON st.[object_id] = si.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE si.id > 100	
AND i.type_desc = 'HEAP'
 
INTERSECT
 
SELECT DISTINCT ss.name AS schemaName, st.name
FROM sys.indexes I 
LEFT JOIN sys.sysindexes SI ON SI.indid = I.index_Id 
							AND i.object_id = si.id
INNER JOIN sys.tables st ON st.[object_id] = si.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE si.id > 100	
AND i.type_desc != 'HEAP'
 
 
-- ALL tables and their indexes...
INSERT INTO #HeapWithIndexes2 
SELECT
	DB_NAME(DB_ID()) AS DatabaseName
	, ss.name AS SchemaName
	, st.NAME AS TableName
	, i.NAME AS IndexName
	, i.type_desc
	, si.rowcnt
FROM sys.indexes I 
LEFT JOIN sys.sysindexes SI ON SI.indid = I.index_Id AND i.object_id = si.id
INNER JOIN sys.tables st ON st.[object_id] = si.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
INNER JOIN #Heaps h ON st.name = h.name AND ss.name = h.schemaName
WHERE si.id > 100 
 
SELECT * FROM #HeapWithIndexes2 ORDER BY DatabaseName, SchemaName, TableName, IndexName
 
DROP TABLE #Heaps
DROP TABLE #HeapWithIndexes2
 
 
/************************************************************************************/
/*       																			*/
/* Purpose: 																		*/
/* Notes: 1. Allows targeted improvments, based on query duration.   				*/
/*        2. Query plan may offer hints for improvements.							*/
/*       																			*/
/************************************************************************************/
SET @StepCounter = @StepCounter + 1
SELECT 'Step ' + CAST(@StepCounter AS VARCHAR(10)) + ': ' +  'Queries taking the longest total time to run' 
 
SELECT TOP 20 
	CAST(total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [Total Duration (s)]
	, CAST(total_worker_time * 100.0 / total_elapsed_time AS DECIMAL(28, 2)) AS [% CPU]
	, CAST((total_elapsed_time - total_worker_time)* 100.0 / total_elapsed_time AS DECIMAL(28, 2))	AS [% Waiting]	
	, execution_count
	, CAST(total_elapsed_time / 1000000.0 / execution_count AS DECIMAL(28, 2)) AS [Average Duration (s)]
	, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1, 
		  ((CASE WHEN qs.statement_end_offset = -1 
		  THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
		  ELSE qs.statement_end_offset
		  END - qs.statement_start_offset)/2) + 1) AS [Individual Query] 
	, SUBSTRING(qt.text,1,100) AS [Parent Query]
	, DB_NAME(qt.dbid) AS DatabaseName 
	, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
INNER JOIN sys.dm_exec_cached_plans as cp 
      ON qs.plan_handle=cp.plan_handle
WHERE total_elapsed_time > 0
ORDER BY total_elapsed_time DESC