Database Maintenance

 Index Fragmentation Info 

--All Indexes

select  object_schema_name(ps.object_id) as ObjectSchema,  object_name (ps.object_id) as ObjectName,  ps.object_id ObjectId,  i.name as IndexName,  ps.avg_fragmentation_in_percent,  ps.page_count from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ps inner join sys.indexes i  on i.object_id = ps.object_id and   i.index_id = ps.index_id where  avg_fragmentation_in_percent > 10   and ps.index_id > 0 and page_count >100 order by page_count desc

--Specific Index

select  object_schema_name(ps.object_id) as ObjectSchema,  object_name (ps.object_id) as ObjectName,  ps.object_id ObjectId,  i.name as IndexName,  ps.avg_fragmentation_in_percent,  ps.page_count from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ps inner join sys.indexes i  on i.object_id = ps.object_id and   i.index_id = ps.index_id where    ps.index_id > 0 and i.name like 'Index_Name' order by 5 desc

use Database_Name

--------------------------------------

 

Declare @TBname nvarchar(255),

        @schema nvarchar(255),

@DB_NAME nvarchar(255),

        @SQL nvarchar(max)

 

DECLARE @db_id SMALLINT;

DECLARE @object_id INT;

 

-------------------------------------->

SET @DB_NAME = db_name() -- 'DB_Name'

SET @TBname = '[ECM-CORP_A]';

SET @schema = 'dbo';

 

--------------------------------------

 

SET @db_id = db_id()-- DB_ID(N'PROD_EIMDW_Staging_Non_Branch_Channels');

SET @object_id = OBJECT_ID(N''+@DB_NAME+'.'+@schema+'.'+@TBname+'');

 

SET @SQL = N'ALTER INDEX ALL ON '+@DB_NAME+'.'+@schema+'.'+@TBname+' REBUILD'

 

exec sp_executeSQL @SQL

 

IF @db_id IS NULL

BEGIN;

    PRINT N'Invalid database';

END;

ELSE IF @object_id IS NULL

BEGIN;

    PRINT N'Invalid object';

END;

ELSE

BEGIN;

  --  SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');

 

 

select  object_schema_name(ps.object_id) as ObjectSchema,  object_name (ps.object_id) as ObjectName,  

ps.object_id ObjectId,  i.name as IndexName,  ps.avg_fragmentation_in_percent,  ps.page_count 

from sys.dm_db_index_physical_stats(@db_id, @object_id, null, null, null) ps inner join 

sys.indexes i  on i.object_id = ps.object_id and   

i.index_id = ps.index_id where    ps.index_id > 0   order by 5 desc

 

END;

GO

sp_IndexCleanup

This stored procedure helps identify unused and duplicate indexes in your SQL Server databases that could be candidates for removal. It analyzes index usage statistics and can generate scripts for removing unnecessary indexes.

IMPORTANT: This is currently a BETA VERSION. It needs extensive testing in real environments with real indexes to address several issues:

• Data collection accuracy
• Deduping logic
• Result correctness
• Edge cases

======================================================================================================================

Index Rebuild and Reorganize 

USE [DB_Name]

ALTER INDEX [Index_Name] ON [dbo].[Table_Name] REORGANIZE 

ALTER INDEX [Index_Name] ON [dbo].[Table_Name] REBUILD

ALTER INDEX ALL ON [dbo].[Table_Name] REBUILD WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ALTER INDEX ALL ON [dbo].[Table_Name] REORGANIZE ; 

Working Method

/* --DB Select

use EIMDW

use EIMDW_CXOH

*/

--Alter Index Query Generator 

select 'ALTER INDEX ALL ON ['+object_schema_name(ps.object_id)+'].['+object_name (ps.object_id)+'] REBUILD ;', object_schema_name(ps.object_id) as ObjectSchema,  object_name (ps.object_id) as ObjectName,  ps.object_id ObjectId,  i.name as IndexName,  ps.avg_fragmentation_in_percent,  ps.page_count from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ps inner join sys.indexes i  on i.object_id = ps.object_id and   i.index_id = ps.index_id

where  avg_fragmentation_in_percent > 15  and page_count > 200  and ps.index_id > 0 order by page_count desc

 

/*

--Alter index

ALTER INDEX ALL ON [dbo].[(SQList.ChangeLog)] REBUILD ;

ALTER INDEX ALL ON [dbo].[(SQList.ChangeLog)] REBUILD ; 

*/

To check Duplicate Index

SELECT t.name AS TableName, i1.name AS IndexName, i2.name AS DuplicateIndexName, i1.index_id AS IndexID, i2.index_id AS DuplicateIndexID FROM sys.indexes i1 INNER JOIN sys.indexes i2 ON i1.object_id = i2.object_id AND i1.index_id < i2.index_id INNER JOIN sys.tables t ON i1.object_id = t.object_id WHERE i1.index_id <> i2.index_id AND i1.type = i2.type AND EXISTS ( SELECT 1 FROM sys.index_columns ic1 INNER JOIN sys.index_columns ic2 ON ic1.object_id = ic2.object_id AND ic1.index_id = ic1.index_id AND ic1.column_id = ic2.column_id WHERE ic1.object_id = i1.object_id AND ic1.index_id = i1.index_id AND ic2.index_id = i2.index_id HAVING COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic WHERE ic.object_id = i1.object_id AND ic.index_id = i1.index_id) ) ORDER BY t.name, i1.name;

Max date of Archive_Historical Table   @Max_Arc_Hist_Date

WHILE EXISTS (SELECT 1 FROM Archive_Table WHERE Date_column < =  @Max_Arc_Hist_Date)

BEGIN

    DELETE TOP (100000)

    FROM Archive_Table

    WHERE Date_column <  = @Max_Arc_Hist_Date;

    -- Optional: Wait for a short time to reduce lock contention

    WAITFOR DELAY '00:00:05';

    Take a t_log backup of the DB 

    Shrink T-Log File*

END