Over the years I've gathered a list of FAQ's about SQL Server.
I'll try to post some of them from time to time. Please note that some content might be from other sources than myself, I will try to mention them as accurate as possible!

Tip 1:
A good query to view index information (© Kimberly Tripp)

SELECT object_name(si.[id]) AS [TableName]
, rowcnt AS [Row Count]
, CASE
WHEN si.indid = 0 then 'Heap'
WHEN si.indid = 1 then 'CL'
WHEN INDEXPROPERTY ( si.[id], si.[name], 'IsAutoStatistics') = 1 THEN 'Stats-Auto'
WHEN INDEXPROPERTY ( si.[id], si.[name], 'IsHypothetical') = 1 THEN 'Stats-HIND'
WHEN INDEXPROPERTY ( si.[id], si.[name], 'IsStatistics') = 1 THEN 'Stats-User'
WHEN si.indid between 2 and 250 then 'NC ' + RIGHT('00' + convert(varchar, si.indid), 3)
ELSE 'Text/Image'
END AS [IndexType]
, si.[name] AS IndexName, si.indid
, CASE
WHEN si.indid BETWEEN 1 AND 250 AND STATS_DATE (si.[id], si.indid) < DATEADD(m, -1, getdate())
THEN '!! More than a month OLD !!'
WHEN si.indid BETWEEN 1 AND 250 AND STATS_DATE (si.[id], si.indid) < DATEADD(wk, -1, getdate())
THEN '! Within the past month !'
WHEN si.indid BETWEEN 1 AND 250 THEN 'Stats recent'
ELSE ''
END AS [Warning]
, STATS_DATE (si.[id], si.indid) AS [Last Stats Update]
FROM sysindexes AS si
WHERE OBJECTPROPERTY(id, 'IsUserTable') = 1
ORDER BY [TableName], si.[indid]