• If you're parachuting into a situation where there is no monitoring in place, then, as was already mentioned, use the Dynamic Management Objects (DMO) to query the cache in order to understand the most frequently called, most expense, longest running queries.

    If you're living with a database over time, then setting up monitoring, preferably using a combination of Performance Monitor and Extended Events, to capture performance metrics about the system. This will lead you to understanding of the system in general.

    I would ensure that you have maintenance routines in place to take care of statistics and possibly defragment indexes. While we're on indexes, most tables in most databases absolutely should have a clustered index. Where to place it is a design issue you won't be able to address directly since you're not designing the system, but I'd be concerned about any tables missing indexes.

    From there, it's a slog. There are no shortcuts. See a query running long? Get the execution plan and see if modifying the structure or the T-SQL can help improve performance.

    For lots more detail, get a copy of my book.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning