SQL PM at Microsoft Ignite

-- Script to generate SQL commands for disabling unused or less used indexes
USE [pwLegalOffice]; -- Change this to your database name
GO

DECLARE @DisableCommands NVARCHAR(MAX);
SET @DisableCommands = '';

WITH IndexUsageStats AS (
    SELECT
        OBJECT_NAME(i.OBJECT_ID) AS TableName,
        i.name AS IndexName,
        SUM(user_seeks + user_scans + user_lookups) AS TotalReads,
        SUM(user_updates) AS TotalWrites,
        i.index_id,
        i.OBJECT_ID AS TableObjectId
    FROM
        sys.indexes AS i
    INNER JOIN
        sys.dm_db_index_usage_stats AS s ON i.OBJECT_ID = s.OBJECT_ID AND i.index_id = s.index_id
    WHERE
        OBJECTPROPERTY(i.OBJECT_ID, 'IsUserTable') = 1
        AND s.database_id = DB_ID() -- Ensure we're only looking at the current database
    GROUP BY
        i.OBJECT_ID, i.name, i.index_id
),
IndexPhysicalStats AS (
    SELECT
        OBJECT_NAME(OBJECT_ID) AS TableName,
        index_id,
        SUM(row_count) AS [RowCount],
        SUM(used_page_count) * 8 AS IndexSizeKB -- Convert pages to KB
    FROM
        sys.dm_db_partition_stats
    GROUP BY
        OBJECT_ID, index_id
),
UnusedIndexes AS (
    SELECT
        u.TableName,
        u.IndexName
    FROM
        IndexUsageStats AS u
    LEFT JOIN
        IndexPhysicalStats AS p ON u.TableName = p.TableName AND u.index_id = p.index_id
    WHERE
        u.TotalReads = 0 -- Indexes with zero reads
        OR u.TotalReads < u.TotalWrites -- Or, indexes with more writes than reads
)
SELECT
    @DisableCommands = @DisableCommands + 'ALTER INDEX [' + IndexName + '] ON [' + TableName + '] DISABLE;' + CHAR(13)
FROM
    UnusedIndexes;

-- Print out the commands to disable indexes
PRINT @DisableCommands;

-- Execute the command
--Exec @DisableCommands;

-- Index fragmentation information
SELECT  Tab.name  Table_Name 
			 ,IX.name  Index_Name
			 ,IX.type_desc Index_Type
			 ,Col.name  Index_Column_Name
			 ,IXC.is_included_column Is_Included_Column
			 ,IX.fill_factor 
			 ,IX.is_disabled
			 ,IX.is_primary_key
			 ,IX.is_unique
			 		  
           FROM  sys.indexes IX 
           INNER JOIN sys.index_columns IXC  ON  IX.object_id   =   IXC.object_id AND  IX.index_id  =  IXC.index_id  
           INNER JOIN sys.columns Col   ON  IX.object_id   =   Col.object_id  AND IXC.column_id  =   Col.column_id     
           INNER JOIN sys.tables Tab      ON  IX.object_id = Tab.object_id

-- Index structure information and statistics 

-- List all index in a sigle table
sp_helpindex '[dbo].[Contact]'

-- Update statistics in a single table using fullscan 
-- ( for more critical and most updated tables )
UPDATE STATISTICS tableName indexName WITH FULLSCAN;

-- Update statistics in a single table
UPDATE STATISTICS [dbo].[Contact];

-- Update statistics for all tables in a database
EXEC sp_updatestats;

-- Set automatically update statistics as needed
ALTER DATABASE [pwLegalOffice] SET AUTO_UPDATE_STATISTICS ON;

-- Set automatically update statistics as needed 
-- (This allows queries to use existing statistics while new statistics are being generated) 
ALTER DATABASE [pwLegalOffice] SET AUTO_UPDATE_STATISTICS_ASYNC ON;

-- Index usage information
SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name
	   ,IX.name AS Index_Name
	   ,IX.type_desc Index_Type
	   ,SUM(PS.[used_page_count]) * 8 IndexSizeKB
	   ,IXUS.user_seeks AS NumOfSeeks
	   ,IXUS.user_scans AS NumOfScans
	   ,IXUS.user_lookups AS NumOfLookups
	   ,IXUS.user_updates AS NumOfUpdates
	   ,IXUS.last_user_seek AS LastSeek
	   ,IXUS.last_user_scan AS LastScan
	   ,IXUS.last_user_lookup AS LastLookup
	   ,IXUS.last_user_update AS LastUpdate
FROM sys.indexes IX
INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id
WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1
GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update

-- Script to find unused or less used indexes in all tables of a SQL Server Database

USE [pwLegalOffice]; -- Change this to your database name
GO

;WITH IndexUsageStats AS (
    SELECT
        OBJECT_NAME(i.OBJECT_ID) AS TableName,
        i.name AS IndexName,
        SUM(user_seeks + user_scans + user_lookups) AS TotalReads,
        SUM(user_updates) AS TotalWrites,
        i.index_id,
        i.OBJECT_ID AS TableObjectId
    FROM
        sys.indexes AS i
    INNER JOIN
        sys.dm_db_index_usage_stats AS s ON i.OBJECT_ID = s.OBJECT_ID AND i.index_id = s.index_id
    WHERE
        OBJECTPROPERTY(i.OBJECT_ID, 'IsUserTable') = 1
        AND s.database_id = DB_ID() -- Ensure we're only looking at the current database
    GROUP BY
        i.OBJECT_ID, i.name, i.index_id
),
IndexPhysicalStats AS (
    SELECT
        OBJECT_NAME(OBJECT_ID) AS TableName,
        index_id,
        SUM(row_count) AS [RowCount],
        SUM(used_page_count) * 8 AS IndexSizeKB -- Convert pages to KB
    FROM
        sys.dm_db_partition_stats
    GROUP BY
        OBJECT_ID, index_id
)
SELECT
    u.TableName,
    u.IndexName,
    u.TotalReads,
    u.TotalWrites,
    p.[RowCount],
    p.IndexSizeKB
FROM
    IndexUsageStats AS u
LEFT JOIN
    IndexPhysicalStats AS p ON u.TableName = p.TableName AND u.index_id = p.index_id
WHERE
    u.TotalReads = 0 -- Indexes with zero reads
    OR u.TotalReads < u.TotalWrites -- Or, indexes with more writes than reads
ORDER BY
    u.TableName,
    u.IndexName;