How to find when index was created?

SELECT STATS_DATE(OBJECT_ID([TablName]), (SELECT index_id FROM sys.indexes WHERE name = [Index Name]))

How to query SQL Profiler's trace file?

1. Read from the trace file into temp table in DB
2. Query table

SELECT * INTO MyTraceTemp

FROM ::fn_trace_gettable('c:\x\MyTrace.trc', default)

SELECT Reads, Duration, * FROM MyTraceTemp ORDER BY Reads DESC

see post How to read a SQL Profiler trace file into a SQL table

How to find sp modified in last N dates

-- updated in last 6 days
SELECT name
FROM sys.objects
WHERE type = 'I'
AND DATEDIFF(D,modify_date, GETDATE()) < 7


-- created in last 6 days
SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,create_date, GETDATE()) < 7

How to find most expensive query?

See http://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/

from the link

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

This seems to give stats for cached query. See discussion