Gather DB and Server Stats

To find how well a database is performing
Database: Buffer Cache Hit Ratio:
Buffer Cache Hit Ratio counter indicates how often SQL Server goes to the buffer, not the hard disk, to get data. This ratio should exceed 90%, and ideally be over 99%.If your buffer cache hit ratio is lower than 90%, you need to go out and buy more RAM today. If the ratio is between 90% and 99%, then you should seriously consider purchasing more RAM, as the closer you get to 99%, the faster your SQL Server will perform. In some cases, if your database is very large, you may not be able to get close to 99%, even if you put the maximum amount of RAM in your server.

select DB_name(), GetDate() as EventTime ,
((CONVERT(decimal(6,2),t1.cntr_value)/CONVERT(decimal(6,2),t2.cntr_value))*100) as HitRatio
from sys.dm_os_performance_counters t1,
sys.dm_os_performance_counters t2
where
t1.object_name LIKE '%Buffer Manager%'
and t1.object_name = t2.object_name
and t1.counter_name='Buffer cache hit ratio'
and t2.counter_name='Buffer cache hit ratio base'

--NB: Sometimes this percentage will show greater than 100% (The key is to look for dips in Hit Ratio starting below 99-98%)


Get Server Stats: CPU Utilization

declare @ts_now bigint
select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info

select top 1 @@SERVERNAME,record_id,
dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,
SQLProcessUtilization,
SystemIdle,
100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
from (
select
record.value('(./Record/@id)[1]', 'int') as record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
timestamp
from (
select timestamp, convert(xml, record) as record
from sys.dm_os_ring_buffers
where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
and record like '%%') as x
) as y
order by record_id desc
End

(NB: You can either set up a job or use perfmon to gather these)

Performance Counters for Server Audit

Performance counter: Processor(_Total)\%Processor Time gives CPU Utilization.
Threshold: Should be always below 100%, ocassional peaks are fine.
If it shows a constant 100%, there will be a bottleneck, bad response times, reponse failures etc.

Performance counter: Memory\Pages/Sec
Threshold: Sustained values higher than five indicate a bottleneck.
Significance: This counter indicates the rate at which pages are read from or written to disk to resolve hard page faults.

Performance counter: Memory\Available Mbytes
Threshold: A consistent value of less than 20 to 25 percent of installed RAM is an indication of insufficient memory.
Significance: This indicates the amount of physical memory available to processes running on the computer. Note that this counter displays the last observed value only. It is not an average.

Performance counter: Memory\Pages faults/Sec
The Page Faults/sec value is made up of hard and soft page faults (If the page is loaded in memory at the time the fault is generated, but its status is not updated as 'present' in hardware, then it is called a minor or soft page fault). So it is a little misleading value.
If page faults is too high, check the pages\sec value. If that is high means you have hard page faults and an immidiate action is required. If that is less than 5, then possibly the number is only soft page faults and nothing to worry about. Though most admins will want it to be below 15-20.

More explanation on performance counters