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

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

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