Finding out unused indexes

Check the readings according to dm_db_index_usage_stats.

WITH indexstats ([Table],[Index],[Reads],[Writes],[Rows])
AS ( SELECT usr.[name] + '.' + obj.[name] [Table],
ixs.[name] [Index] ,
usage.user_seeks + usage.user_scans + usage.user_lookups [Reads],
usage.[user_updates] [Writes],
(SELECT SUM(sp.[rows]) FROM sys.partitions sp
WHERE usage.OBJECT_ID = sp.object_id
AND sp.index_id = usage.index_id) [Rows]
FROM sys.dm_db_index_usage_stats usage
INNER JOIN sys.indexes ixs
ON usage.[object_id] = ixs.[object_id]
AND ixs.[index_id] = usage.[index_id]
INNER JOIN sys.objects obj ON usage.[object_id] =
obj.[object_id] INNER JOIN sys.sysusers usr ON obj.[schema_id] = usr.[uid]
WHERE usage.database_id = DB_ID()
AND usage.index_id > 0
AND OBJECTPROPERTY(usage.[object_id], 'IsUserTable') = 1 )
SELECT * FROM indexstats WHERE Reads = 0 ORDER BY [Rows] DESC, [Index]

Defragment

1. determin the level of fragment
(http://www.sql-server-performance.com/2006/detect-fragmentation-sql2000-sql2005/)
1.1 sql 2000
DBCC SHOWCONTIG
The fragmentation level of an index can be determined in two ways:
--Comparing the values of Extent Switches and Extents Scanned.
The value of Extent Switches should be as close as possible to that of Extents Scanned. This ratio is calculated as the Scan Density value, which should be as high as possible.

--Understanding Logical Scan Fragmentation and Extent Scan Fragmentation values.
Logical Scan Fragmentation and, to a lesser extent, Extent Scan Fragmentation values give the best indication of a table's fragmentation level. Both these values should be as close to zero as possible (although a value from 0% through 10% may be acceptable).

1.2 2005 an dabove
sys.dm_db_index_physical_stats

--list all indexes and their fragment level
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'xxx_db'), OBJECT_ID(N'xxxtable'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id
AND a.index_id = b.index_id

2. defragment
steal content from http://www.mssqltips.com/sqlservertip/1018/index-rebuilds-in-sql-server-2000-vs-sql-server-2005/

2.1 2000

CREATE INDEX with DROP_EXISTING - Creates a new index with the same name and drops the current index while ensuring the nonclustered indexes are not rebuilt twice.
CREATE CLUSTERED INDEX au_id_clidxON Authors (au_id) WITH DROP_EXISTINGGO

DROP INDEX and CREATE INDEX - Removes the au_id_ind index on the authors table.
DROP INDEX authors.au_id_indGO
CREATE INDEX au_id_indON Authors (au_id ASC) GO

DBCC DBREINDEX - Rebuild all of the indexes on the authors table with 80% fill factor.
DBCC DBREINDEX (authors, '', 80)GO

DBCC INDEXDEFRAG - Defragments the au_id_ind index on the Authors table.
DBCC INDEXDEFRAG (Pubs, Authors, au_id_ind)GO

2.2 2005 and above

CREATE INDEX with DROP_EXISTING - Creates a new index with the same name and drops the current index while ensuring the nonclustered indexes are not rebuilt twice.
CREATE CLUSTERED INDEX au_id_clidxON dbo.Authors (au_id) WITH (DROP_EXISTING = ON);GO

DROP INDEX and CREATE INDEX - Removes the au_id_ind index on the authors table, which is the equal functionality as SQL Server 2000.
DROP INDEX authors.au_id_ind;GO
CREATE INDEX au_id_indON Authors (au_id ASC); GO

ALTER INDEX - Rebuild all of the indexes on the Authors table with 80% fill factor, sort the intermediary data in TempDB and automatic updating of the statistics are enabled.
ALTER INDEX ALL ON AuthorsREBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF);GO


ALTER INDEX - Defragment the au_id_ind index on the Authors table which is intended to be a truly online operation.
ALTER INDEX au_id_ind ON dbo.Authors REORGANIZE; GO