How to Get SQL Server Database performance Information?

Question

Are there any standard queries that can be run that will show the performance of a SQL Server 2005 database?

Note: I need to know the performance of every aspect of the database.

EDIT: I am looking for a way to measure the time it takes for typical queries to execute. I am then going to apply indexing to certain tables in the database and then time how long the same queries take to execute and see if there is a significant difference. Is there an easy way to do this?

Thanks!

Answer 1

There are some dynamic management views and functions build in:

http://msdn.microsoft.com/en-us/library/ms188754%28SQL.90%29.aspx

select * from sys.dm_db_index_usage_stats 
select * from sys.dm_os_memory_objects

Answer 2

(Edited, link hopefully fixed)

For general background research/analysis of SQL Server performance, I prefer to watch how SQL is performing as it is performing. The best tools for that are SQL Profiler and sometimes Windows System Monitor (aka Performance Monitor aka PerfMon). Alas, neither are particularly simple, let alone simple queries against the system -- though some PerfMon counters are exposed through a few DMViews I can't dig up just now.

BOL has reasonable information on these; a good top-level (online) page for this is here. Be wary, there is serious DBA stuff beyond that point