SQL Profiler is a great tool that allows you to see what's going on inside SQL Server.  You can find out what your worst performing queries are, how often a query is executed, and loads of other stuff.  I'm typically interested in the things that are impacting performance such as long durations of stored procedures.

One important thing to know when running a trace using SQL Profiler is that the trace can degrade performance.  To minimize its impact, you should follow these best practices:

1. Never run SQL Profiler on the database server
2. Never trace directly to a table, instead trace to a file (You can query the active trace file using this technique)
3. Filter the results, such as "Duration > 1000"
4. Include only the events that you are interested in, such as SP:Completed and SQL:BatchCompleted

For more information on SQL Profiler, check out this article.

You should also know that a server-side trace, SQL Trace, can use even less resources than SQL Profiler.  SQL Trace allows you to create a trace programmatically by calling system stored procedures.  Due to this, you can automatically collect trace data via SQL jobs. 

For more information on SQL Trace, start here.