Database Maintanance

create_index.sql

-- a) List the equality columns first (leftmost in the column list).
-- b) List the inequality columns after the equality columns (to the right of equality columns listed).
-- c) List the include columns in the INCLUDE clause of the CREATE INDEX statement.
-- d) To determine an effective order for the equality columns, order them based on their selectivity; that is, list the most selective columns first.

-- TODO: determine required index type
CREATE NONCLUSTERED INDEX [IDX_TEST] ON [dbo].[MYDB]
(
    -- equality_columns; start with most distinctive
	[TYPE] ASC,
	[FROM_CURR] ASC,
	
	-- inequality_columns; start with most distinctive
	[TO_C] ASC,
	[FROM_MONTH] ASC,
	[TO_M] ASC
)
INCLUDE (
    -- included_columns
	[ROJ]
) 
WITH (
	STATISTICS_NORECOMPUTE = OFF, 
	SORT_IN_TEMPDB = OFF, 
	IGNORE_DUP_KEY = OFF, 
	DROP_EXISTING = OFF, 
	ONLINE = OFF, 
	ALLOW_ROW_LOCKS = ON, 
	ALLOW_PAGE_LOCKS = ON) 
ON [PRIMARY]
GO

find_missing_indices.sql

SELECT   
	DISTINCT CONVERT(DECIMAL(18, 2) , user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )) AS [index_advantage],
	migs.last_user_seek,
	mid.[statement] AS [Database.Schema.Table],
	mid.equality_columns,
	mid.inequality_columns,
	mid.included_columns,
	migs.unique_compiles,
	migs.user_seeks,
	migs.avg_total_user_cost,
	migs.avg_user_impact,
	OBJECT_NAME(mid.[object_id]) AS [Table Name],
	p.rows AS [Table Rows]
FROM       sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle
INNER JOIN sys.partitions AS p WITH (NOLOCK) ON p.[object_id] = mid.[object_id]
WHERE    mid.database_id = DB_ID()
ORDER BY user_seeks DESC
OPTION (RECOMPILE);

find_unused_index.sql

SELECT  DB_NAME() AS DatabaseName
	   ,SCHEMA_NAME(s.schema_id) +'.'+OBJECT_NAME(i.OBJECT_ID) AS TableName
	   ,i.name AS IndexName
	   ,ius.user_seeks AS Seeks
	   ,ius.user_scans AS Scans
	   ,ius.user_lookups AS Lookups
	   ,ius.user_updates AS Updates
	   ,CASE WHEN ps.usedpages > ps.pages THEN (ps.usedpages - ps.pages) ELSE 0 
	  END * 8 / 1024 AS IndexSizeMB
	   ,ius.last_user_seek AS LastSeek
	   ,ius.last_user_scan AS LastScan
	   ,ius.last_user_lookup AS LastLookup
	   ,ius.last_user_update AS LastUpdate
FROM sys.indexes i
INNER JOIN sys.dm_db_index_usage_stats ius ON ius.index_id = i.index_id AND ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN (SELECT sch.name, sch.schema_id, o.OBJECT_ID, o.create_date FROM sys.schemas sch 
	 INNER JOIN sys.objects o ON o.schema_id = sch.schema_id) s ON s.OBJECT_ID = i.OBJECT_ID
LEFT JOIN (SELECT OBJECT_ID, index_id, SUM(used_page_count) AS usedpages,
	    SUM(CASE WHEN (index_id < 2) 
	  THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) 
	  ELSE lob_used_page_count + row_overflow_used_page_count 
	   END) AS pages
		FROM sys.dm_db_partition_stats
		GROUP BY object_id, index_id) AS ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE OBJECTPROPERTY(i.OBJECT_ID,'IsUserTable') = 1
--optional parameters
AND ius.database_id = DB_ID() --only check indexes in current database
AND i.type_desc = 'nonclustered' --only check nonclustered indexes
AND i.is_primary_key = 0 --do not check primary keys
AND i.is_unique_constraint = 0 --do not check unique constraints
--AND (ius.user_seeks+ius.user_scans+ius.user_lookups) < 1  --only return unused indexes
--AND OBJECT_NAME(i.OBJECT_ID) = 'tableName'--only check indexes on specified table
--AND i.name = 'IX_Your_Index_Name' --only check a specified index
 
 AND ius.user_seeks = 0 
 AND ius.user_scans = 0 
 AND ius.user_lookups = 0
 order by i.name