Indexes not used, Missing index and stats, table fragmentation for sql server

0)

select
object_name(I.object_id) as TableName
,I.name
,'drop index '+ I.name + ' on '+ object_name(I.object_id) as DropIndexStatement
,I.object_id
,I.index_id
--,I.*
from sys.indexes I inner join sys.objects O
on I.object_id=O.object_id
left join (select object_id, index_id from sys.dm_db_index_usage_stats where database_id=db_id()) S on
I.object_id=S.object_id and I.index_id=S.index_id
where
-- only looking for no clustered and non primary key indexes
--type_desc='NONCLUSTERED'
I.type=2
and I.is_primary_key=0

-- index not used so far
and (S.object_id is null or S.index_id is null)
-- excluding system tables
--and I.object_id>100
-- a better way to identify system tables
-- this is confirmed by the fact that the following query always return empty result set
-- select * from sys.objects where object_id<100 and is_ms_shipped<>1
-- otherwise we don't need sys.objects at all
and O.is_ms_shipped<>1
order by 1,2

1)
http://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/

SELECT TOP 25
    dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact
    ,db_name(dm_mid.database_id) as DatabaseName
    ,OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName]
    ,dm_mid.equality_columns
    ,dm_mid.inequality_columns
    ,dm_mid.included_columns
    ,dm_migs.last_user_seek AS Last_User_Seek
    ,dm_mid.database_id AS DatabaseID
    ,'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
    CASE
    WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
    ELSE ''
    END
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
    + ']'
    + ' ON ' + dm_mid.statement
    + ' (' + ISNULL (dm_mid.equality_columns,'')
    + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
    '' END
    + ISNULL (dm_mid.inequality_columns, '')
    + ')'
    + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
    FROM    sys.dm_db_missing_index_groups dm_mig
            INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
            ON        dm_migs.group_handle = dm_mig.index_group_handle
            INNER JOIN sys.dm_db_missing_index_details dm_mid
            ON dm_mig.index_handle = dm_mid.index_handle
    WHERE    dm_mid.database_ID = DB_ID()
    ORDER BY 1 desc--Avg_Estimated_Impact DESC
go
-- Missing Index Script
-- Original Author: Pinal Dave (C) 2011
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

2)
SELECT

Last_Updated    = STATS_DATE(si.id, si.indid)

,TableName      = object_name(si.id)

,Name           = RTRIM(si.name)

,Size           = DATALENGTH (si.statblob)

FROM

sysindexes si WITH (nolock)

WHERE OBJECTPROPERTY(si.id, N'IsUserTable') = 1

--AND INDEXPROPERTY (si.id , si.name , 'IsAutoStatistics' ) = 0

order by last_updated, tablename

3)

if (object_id('tempdb..#IndexStat') is not null) drop table #IndexStat;
SELECT
    --top 10
    db_name(DB_ID()) as DatabaseName
    ,object_name(s.object_id, db_id()) as TableName
    ,i.name as IndexName
    ,s.object_id AS objectid
    ,i.index_id AS indexid
    ,partition_number AS PartitionNumber
    ,avg_fragmentation_in_percent AS ExternalFragmentation
    ,s.page_count
    ,s.record_count
    into  #IndexStat
    FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, 'LIMITED')  s  
            inner join sys.indexes i
            on s.index_id=i.index_id and s.object_id=i.object_id
    WHERE    --avg_fragmentation_in_percent > 5 AND
            s.index_id > 0 and
            s.database_id=DB_ID()
    --order by avg_fragmentation_in_percent desc;

SELECT top 10 * from #IndexStat order by ExternalFragmentation desc;

http://sqlserverplanet.com/dmv-queries/find-table-fragmentation
--
-- table fragmentation
SELECT

Last_Updated    = STATS_DATE(si.id, si.indid)

,TableName      = object_name(si.id)

,Name           = RTRIM(si.name)

,Size           = DATALENGTH (si.statblob)

FROM

sysindexes si WITH (nolock)

WHERE OBJECTPROPERTY(si.id, N'IsUserTable') = 1

--AND INDEXPROPERTY (si.id , si.name , 'IsAutoStatistics' ) = 0

order by last_updated, tablename

Or you can still do it the old fashioned way. Just substitute the name of the table or index below. It should not be in quotes.
–Just the table
DBCC SHOWCONTIG (tablename)
–Table with the index
DBCC SHOWCONTIG (tablename, indexname)
–Do not block anything (run during prod hours)
DBCC SHOWCONTIG (tablename, indexname) WITH FAST –(2005)