sql server - What are the first issues to check while optimizing an existing database?

Question

What are the top issues and in which order of importance to look into while optimizing (performance tuning, troubleshooting) an existing (but unknown to you) database?
Which actions/measures in your previous optimizations gave the most effect (with possibly the minimum of work) ?

I'd like to partition this question into following categories (in order of interest to me):

1. one needs to show the performance boost (improvements) in the shortest time. i.e. most cost-effective methods/actions;
2. non-intrusive or least-troublesome most effective methods (without changing existing schemas, etc.)
3. intrusive methods

Update:
Suppose I have a copy of a database on dev machine without access to production environment to observe stats, most used queries, performance counters, etc. in real use.
This is development-related but not DBA-related question.
Update2:
Suppose the database was developed by others and was given to me for optimization (review) before it was delivered to production.
It is quite usual to have outsourced development detached from end-users.

Besides, there is a database design paradigm that a database, in contrast to application data storage, should be a value in itself independently on specific applications that use it or on context of its use.

Update3: Thanks to all answerers! You all pushed me to open subquestion
How do you stress load dev database (server) locally?

Answer 1

• Create a performance Baseline (non-intrusive, use performance counters)

• Identify the most expensive queries (non-intrusive, use SQL Profiler)

• Identify the most frequently run queries (non-intrusive, use SQL Profiler)

• Identify any overly complex queries, or those using slowly performing constructs or patterns. (non-intrusive to identify, use SQL Profiler and/or code inspections; possibly intrusive if changed, may require substantial re-testing)

• Assess your hardware

• Identify Indexes that would benefit the measured workload (non-intrusive, use SQL Profiler)

• Measure and compare to your baseline.

• If you have very large databases, or extreme operating conditions (such as 24/7 or ultra high query loads), look at the high end features offered by your RDBMS, such as table/index partitioning.

This may be of interest: How Can I Log and Find the Most Expensive Queries?