Here’s a TSQL snippet that will give you the sizes of the indexes that the query optimiser isn’t using.  Note that the NULL values are SINCE THE LAST RESTART.  So if you just restarted your server then this is rather a waste of time.

 
SELECT object_name(ui.object_id), si.name as [Index Name],ui.index_id, ui.last_user_seek, ui.last_user_scan, ui.user_seeks, ui.user_scans,
ui.user_lookups,ui.user_updates,
8 * sa.used_pages AS [Index Size]
from sys.dm_db_index_usage_stats ui
inner join sys.indexes si on ui.object_id=si.object_id and ui.index_id=si.index_id
INNER JOIN
sys.partitions sp ON si.object_id = sp.OBJECT_ID AND si.index_id = sp.index_id
INNER JOIN sys.allocation_units AS sa ON sa.container_id = sp.partition_id
where database_id=7
and object_name(ui.object_id) not like ‘sys%’
and ui.last_user_seek is NULL
and ui.last_user_scan is NULL
ORDER by 10 desc

Something I have run into a few times now which is an annoying timewaster is the following error:

Error While Enabling Windows Feature netfx3

You’ll get it on windows 2012 installations when installing SQL 2012(And probably lower versions) and it’s caused by dotnet 3.5not being enabled on the windows installation. It’s frustrating because it happens during the actual installation and is not flagged in any of the pre-requisite checks.  This is because Windows assumes it either has access to the install media to install it from, or can download it from the internet.  On your SQL Servers you shouldn’t have direct internet access, and you are unlikely to have the install media lying around.  At any rate – here’s how to fix it:

1.  Place the install media back to it’s original location(Most likely the CD drive).

2.  From Server Manager choose ‘Add Roles and Features’.

3.  Add ‘.NET framework 3.5 features’.

4.  Now redo your SQL Install and you should be fine.