Do you know the top issues as well as in which order worth focusing on to consider while optimizing (performance tuning, troubleshooting) a current (but unknown for you) database?
Which actions/measures inside your previous optimizations gave probably the most effect (with probably the the least work) ?

Let me partition this into following groups (so as of great interest in my experience):

  1. one should show the performance boost (enhancements) within the least time. i.e. most cost-effective techniques/actions
  2. non-intrusive or least-difficult best techniques (without altering existing schemas, etc.)
  3. intrusive techniques

Suppose I've got a copy of the database on dev machine without use of production atmosphere to see stats, most used queries, performance counters, etc. in tangible use.
This really is development-related although not DBA-related question.
Imagine that the database was created by others and was handed in my experience for optimisation (review) prior to being shipped to production.
It is extremely usual to possess outsourced development detached from finish-customers.

Besides, there's a database design paradigm that the database, as opposed to application data storage, ought to be something by itself individually on specific programs which use it or on context of their use.

Update3: Because of all answerers! All of you pressed me to spread out subquestion
How can you stress load dev database (server) in your area?

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

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

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

  • Identify any excessively complex queries, or individuals using gradually carrying out constructs or designs. (non-intrusive to recognize, use SQL Profiler and/or code assessments possibly intrusive if transformed, may need substantial re-testing)

  • Assess your hardware

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

  • Measure and rival your baseline.

  • For those who have large databases, or extreme operating conditions (for example 24/7 or ultra high query loads), consider the top end features provided by your RDBMS, for example table/index partitioning.

This might be of great interest: How Do I Log and discover probably the most Costly Queries?