Few scripts related to Indexes and  fragmentation..

--to check the fragmentation on all tables LIMITED or DETAILED

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,

i.name AS IndexName,

indexstats.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') indexstats

INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID

AND i.index_id = indexstats.index_id

WHERE indexstats.avg_fragmentation_in_percent > 20

order by indexstats.avg_fragmentation_in_percent desc

--to check the fragmentation on specific tables

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,

i.name AS IndexName,

indexstats.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') indexstats

INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID

AND i.index_id = indexstats.index_id

WHERE indexstats.avg_fragmentation_in_percent > 0

and OBJECT_NAME(i.OBJECT_ID) = 'TABLENAME'

-- to get the details on last stats updated date

SELECT name AS Stats,

STATS_DATE(object_id, stats_id) AS LastStatsUpdate

FROM sys.stats

WHERE object_id = OBJECT_ID('dbo.SubbatchRef')

and left(name,4)!='PK_SubbatchRef';

 --create rebuilt script on all tables

 SELECT 'ALTER INDEX ALL ON ' + QUOTENAME(t.TABLE_SCHEMA) +'.' + QUOTENAME(t.TABLE_NAME) + ' REBUILD '

FROM INFORMATION_SCHEMA.TABLES t

WHERE t.TABLE_TYPE like '%BASE%'

--to get all index size in DB

SELECT

OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName,

OBJECT_NAME(i.OBJECT_ID) AS TableName,

i.name AS IndexName,

i.index_id AS IndexID,

8 * SUM(a.used_pages) AS Indexsize_KB

FROM sys.indexes AS i

JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id

JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

GROUP BY i.OBJECT_ID,i.index_id,i.name

ORDER BY Indexsize_KB desc 

--ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id

-- to get the  Index FillFactor_value

SELECT sysobjects.name as Table_name,

sysindexes.name as Index_name,  

INDEXPROPERTY(OBJECT_ID(sysobjects.name),sysindexes.name,'IndexFillFactor') as FillFactor_value

FROM   sysobjects INNER JOIN  

sysindexes ON sysobjects.id = sysindexes.id 

WHERE  xtype = 'U' 

and sysobjects.name='MeterData'

and sysindexes.name='MeterData_PK'

 --to check the fragmentation on all tables

SELECT S.name as 'Schema',

T.name as 'Table',

I.name as 'Index',

DDIPS.avg_fragmentation_in_percent,

DDIPS.page_count

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS

INNER JOIN sys.tables T on T.object_id = DDIPS.object_id

INNER JOIN sys.schemas S on T.schema_id = S.schema_id

INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id

AND DDIPS.index_id = I.index_id

WHERE DDIPS.database_id = DB_ID()

and I.name is not null

AND DDIPS.avg_fragmentation_in_percent > 0

ORDER BY DDIPS.avg_fragmentation_in_percent desc

-- To get the duplicate Indexes

;WITH MyDuplicate AS (SELECT

Sch.[name] AS SchemaName,

Obj.[name] AS TableName,

Idx.[name] AS IndexName,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 1) AS Col1,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 2) AS Col2,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 3) AS Col3,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 4) AS Col4,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 5) AS Col5,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 6) AS Col6,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 7) AS Col7,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 8) AS Col8,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 9) AS Col9,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 10) AS Col10,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 11) AS Col11,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 12) AS Col12,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 13) AS Col13,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 14) AS Col14,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 15) AS Col15,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 16) AS Col16

FROM sys.indexes Idx

INNER JOIN sys.objects Obj ON Idx.[object_id] = Obj.[object_id] INNER JOIN sys.schemas Sch ON Sch.[schema_id] = Obj.[schema_id] WHERE index_id > 0)

SELECT    MD1.SchemaName, MD1.TableName, MD1.IndexName,

MD2.IndexName AS OverLappingIndex,

MD1.Col1, MD1.Col2, MD1.Col3, MD1.Col4,

MD1.Col5, MD1.Col6, MD1.Col7, MD1.Col8,

MD1.Col9, MD1.Col10, MD1.Col11, MD1.Col12,

MD1.Col13, MD1.Col14, MD1.Col15, MD1.Col16

FROM MyDuplicate MD1

INNER JOIN MyDuplicate MD2 ON MD1.tablename = MD2.tablename

AND MD1.indexname <> MD2.indexname

AND MD1.Col1 = MD2.Col1

AND (MD1.Col2 IS NULL OR MD2.Col2 IS NULL OR MD1.Col2 = MD2.Col2)

AND (MD1.Col3 IS NULL OR MD2.Col3 IS NULL OR MD1.Col3 = MD2.Col3)

AND (MD1.Col4 IS NULL OR MD2.Col4 IS NULL OR MD1.Col4 = MD2.Col4)

AND (MD1.Col5 IS NULL OR MD2.Col5 IS NULL OR MD1.Col5 = MD2.Col5)

AND (MD1.Col6 IS NULL OR MD2.Col6 IS NULL OR MD1.Col6 = MD2.Col6)

AND (MD1.Col7 IS NULL OR MD2.Col7 IS NULL OR MD1.Col7 = MD2.Col7)

AND (MD1.Col8 IS NULL OR MD2.Col8 IS NULL OR MD1.Col8 = MD2.Col8)

AND (MD1.Col9 IS NULL OR MD2.Col9 IS NULL OR MD1.Col9 = MD2.Col9)

AND (MD1.Col10 IS NULL OR MD2.Col10 IS NULL OR MD1.Col10 = MD2.Col10)

AND (MD1.Col11 IS NULL OR MD2.Col11 IS NULL OR MD1.Col11 = MD2.Col11)

AND (MD1.Col12 IS NULL OR MD2.Col12 IS NULL OR MD1.Col12 = MD2.Col12)

AND (MD1.Col13 IS NULL OR MD2.Col13 IS NULL OR MD1.Col13 = MD2.Col13)

AND (MD1.Col14 IS NULL OR MD2.Col14 IS NULL OR MD1.Col14 = MD2.Col14)

AND (MD1.Col15 IS NULL OR MD2.Col15 IS NULL OR MD1.Col15 = MD2.Col15)

AND (MD1.Col16 IS NULL OR MD2.Col16 IS NULL OR MD1.Col16 = MD2.Col16)

ORDER BY MD1.SchemaName,MD1.TableName,MD1.IndexName

--check logical reads by SP

SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads],qs.total_physical_reads AS [TotalPhysicalReads],

qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count,

ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],

qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count

AS [avg_elapsed_time], qs.cached_time

FROM sys.procedures AS p

INNER JOIN sys.dm_exec_procedure_stats AS qs

ON p.[object_id] = qs.[object_id]

ORDER BY qs.total_logical_reads DESC;

ALTER INDEX PK_Notifications ON [dbo].Notifications REBUILD WITH (ONLINE = ON);

ALTER INDEX ALL ON [dbo].Notifications REBUILD WITH (ONLINE = ON);