MS SQL Server 2005 onwards provides quite a lot of views and functions which can be used to keep a tab on the database engine and also gives you statistics which helps you in making informed decision about the database schemas.
This are collectively called as DMV (Dynamic management views).
For example - the following DMV can help you show the state of indexes in the database.It shows you the table name, the number of indexes on that table, how many of those indexes have been unused since either the last restart or the index was (re)created, and how many indexes SQL Server thought it would like to have on the table. In a well tuned database, the unused and missing counts would both be 0. If they are, you have exactly the right number of indexes for the workloads hitting your tables.
SELECT CONVERT(VARCHAR(30),so.name) AS TABLE_NAME,
COALESCE(Unused.IdxCount, 0) AS IDXCOUNT,
COALESCE(Unused.UnusedCount, 0) AS UNUSEDCOUNT,
COALESCE(Missing.MICOUNT, 0) AS MISSINGCOUNT,
COALESCE(CONVERT(DECIMAL(6,1),(CONVERT(DECIMAL(10,2),Unused.UnusedCount)/CONVERT(DECIMAL(10,2),Unused.IdxCount))* 100), 0) AS UnusedPct
FROM sys.objects so
LEFT JOIN
( SELECT s.OBJECT_ID,
COUNT(*) AS idxcount,
SUM(CASE WHEN s.user_seeks = 0
AND s.user_scans = 0
AND s.user_lookups = 0
THEN 1
ELSE 0 END) AS UnusedCount
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i
ON s.OBJECT_ID = i.OBJECT_ID
AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND OBJECTPROPERTY(s.OBJECT_ID,‘IsMsShipped’) = 0
GROUP BY s.OBJECT_ID
) Unused
ON Unused.OBJECT_ID = so.OBJECT_ID
LEFT JOIN ( SELECT d.OBJECT_ID,
COUNT(*) AS MICOUNT
FROM sys.dm_db_missing_index_groups g
JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
WHERE d.database_id = DB_ID()
GROUP BY d.OBJECT_ID
)Missing
ON Missing.OBJECT_ID = so.OBJECT_ID
WHERE so.type_desc = ‘USER_TABLE’
AND (Missing.MICOUNT > 0
OR Unused.UnusedCount > 0)
ORDER BY UnusedPct DESC
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
This returns the following in sample database =
TABLE_NAME IDXCOUNT UNUSEDCOUNT MISSINGCOUNT UnusedPct
Table_1 1 1 0 100.0
Table_2 1 1 0 100.0
Table_3 1 0 4 0.0
Table_4 1 0 3 0.0
Table_5 1 0 1 0.0
Table_6 1 0 5 0.0