Clear abandoned statistics

Based on Brent Ozar's query along with a cursor to clear them
https://www.brentozar.com/blitz/hypothetical-indexes-index-tuning-wizard/

Eliminates stats that cannot be updated.


USE [dbname]
GO

DECLARE @tblDrops TABLE  (
tbl  nvarchar(100)
, Index_or_Statistics nvarchar(200)
, DropCmd nvarchar(500)
);
DECLARE @sqlDrop nvarchar(500);


WITH hi AS (
SELECT
QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(i.[object_id])) AS [Table]
, QUOTENAME([i].[name]) AS [Index_or_Statistics]
, 1 AS [Type]
FROM sys.[indexes] AS [i]
JOIN sys.[objects] AS [o]
ON i.[object_id] = o.[object_id]
WHERE 1=1
AND INDEXPROPERTY(i.[object_id], i.[name], 'IsHypothetical') = 1
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1

UNION ALL

SELECT
QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(i.[object_id])) AS [Table]
, QUOTENAME([i].[name]) AS [Index_or_Statistics]
, 2 AS [Type]
FROM sys.[stats] AS [s]
JOIN sys.[objects] AS [o]
ON [o].[object_id] = [s].[object_id]
WHERE [s].[user_created] = 0
AND [o].[name] LIKE '[_]dta[_]%'
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1
)
INSERT INTO @tblDrops
SELECT
[hi].[Table] ,
[hi].[Index_or_Statistics] ,
CASE [hi].[Type]
       WHEN 1 THEN 'DROP INDEX ' + [hi].[Index_or_Statistics] + ' ON ' + [hi].[Table] + ';'
       WHEN 2 THEN 'DROP STATISTICS ' + hi.[Table] + '.' + hi.[Index_or_Statistics] + ';'
       ELSE 'DEAR GOD WHAT HAVE YOU DONE?'
END AS [T-SQL Drop Command]
FROM [hi]

SELECT DropCmd
FROM @tblDrops;

/*
--Uncomment to run the purge
DECLARE db_cursor CURSOR FOR 
SELECT DropCmd
FROM @tblDrops;

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @sqlDrop

WHILE @@FETCH_STATUS = 0  
BEGIN  
      EXEC (@sqlDrop);

      FETCH NEXT FROM db_cursor INTO @sqlDrop
END 

CLOSE db_cursor  
DEALLOCATE db_cursor 
*/

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 );

Scripts for moving SQL Server DB files

These are simple but I always forget them.If you are using Always On make sure you run this on all machines and move the files on all machines.Unless you are replicating master.


USE master;

SELECT
name
, physical_name 
FROM sys.master_files
WHERE database_id = DB_ID('[DB Name]');

ALTER DATABASE [DB Name]
MODIFY FILE ( NAME = [Logical Name of File ], FILENAME = '[Filepath]')
GO

Script to Check is a Schema Exists and Create New

USE [master]
GO

IF NOT EXISTS(SELECT schema_id FROM sys.schemas where name = 'DBA')
BEGIN
DECLARE @SQLcmd NVARCHAR(50) = 'CREATE SCHEMA [DBA] AUTHORIZATION [db_owner]';
EXEC sp_executesql @SQLcmd;
END