Move and size temp DB

USE master;

SELECT
name
, physical_name 
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

/*
ALTER DATABASE [tempdb]
MODIFY FILE ( NAME = tempdev, FILENAME = 'F:\SqlTmp01\tempdb.mdf', SIZE = 2500MB, FILEGROWTH=0)
GO

ALTER DATABASE [tempdb]
ADD FILE ( NAME = tempdb2, FILENAME = 'F:\SqlTmp01\tempdb2.ndf', SIZE = 2500MB, FILEGROWTH=0)
GO

ALTER DATABASE [tempdb]
MODIFY FILE ( NAME = templog, FILENAME = 'G:\SqlLog01\templog.ldf')
GO
*/

Waitstates Analysis

Cool script from SQLServerCentral.com for looking at waitstates

-- Isolate top waits for server instance since last restart or statistics clear
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'))
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 OPTION (RECOMPILE); -- percentage threshold
GO

View Index Usage by DB and Table

--View Index Usage by DB and Table

USE [DB Name];

DECLARE @DBName nvarchar(50) = '[db name]';

SELECT
OBJECT_NAME(ustats.object_id) AS TableName
, idx.name
, user_seeks
, user_scans
, user_lookups
, user_updates
, last_user_seek
, last_user_scan
, last_user_lookup
, last_user_update
FROM sys.dm_db_index_usage_stats ustats
INNER JOIN sys.indexes idx
ON ustats.object_id = idx.object_id
AND ustats.index_id = idx.index_id
WHERE database_id = DB_ID(@DBName)
AND OBJECT_NAME(ustats.object_id) IN ( '', '');

Find Missing Index Reccomendations

Taken from DotNetVibes - This is derived from this excellent site with a couple very small modifications. I recommend his site.

I have also re-arranged the columns to match SAP DBA cockpit due to the limitations of DBACOCKPIT with SQL Server indexes.

USE master;

SELECT   DISTINCT 
mid.[statement] AS [Database.Schema.Table]
,OBJECT_NAME(mid.[object_id]) AS [Table Name]
,CONVERT(DECIMAL(18, 2) , user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )) AS [index_advantage]
,migs.avg_total_user_cost
,migs.avg_user_impact
,migs.user_seeks
,mid.equality_columns
,mid.inequality_columns
,mid.included_columns
,migs.last_user_seek
,migs.last_user_scan
,migs.unique_compiles
,migs.avg_total_system_cost
,migs.avg_total_user_cost
, (SELECT MAX(p.[rows]) FROM sys.partitions p WITH ( NOLOCK) WHERE [object_id] = mid.[object_id])  AS [Table Rows]
,'CREATE INDEX [Missing_IXNC_' + OBJECT_NAME(mid.[object_id], mid.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(mid.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
    WHEN mid.[equality_columns] IS NOT NULL
        AND mid.[inequality_columns] IS NOT NULL
        THEN '_'
    ELSE ''
    END
+ REPLACE(REPLACE(REPLACE(ISNULL(mid.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + mid.[statement] + ' (' + ISNULL(mid.[equality_columns], '') + CASE
    WHEN mid.[equality_columns] IS NOT NULL
        AND mid.[inequality_columns] IS NOT NULL
        THEN ','
    ELSE ''
    END
+ ISNULL(mid.[inequality_columns], '') + ')'
+ ISNULL(' INCLUDE (' + mid.[included_columns] + ')', '')
AS [ProposedIndex]
,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
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 index_advantage DESC
OPTION ( RECOMPILE );