SQL Server queries with DMVs for examining bottlenecks

http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1265214,00.html

SELECT * FROM SYS.SYSPERFINFO WHERE
OBJECT_NAME=’SQLSERVER:BUFFER MANAGER’ AND
(COUNTER_NAME=’TARGET PAGES’ OR
COUNTER_NAME=’TOTAL PAGES’ OR
COUNTER_NAME=’DATABASE PAGES’ OR
COUNTER_NAME=’STOLEN PAGES’ OR
COUNTER_NAME=’FREE PAGES’)

Use the following DMV query to determine which SQL Server components are consuming the most amount of memory, and observe how this changes over time:

SELECT TYPE, SUM(MULTI_PAGES_KB) FROM
SYS.DM_OS_MEMORY_CLERKS WHERE
MULTI_PAGES_KB != 0 GROUP BY TYPE

This query will show which SQL Server objects are consuming memory:

SELECT TYPE, PAGES_ALLOCATED_COUNT FROM
SYS.DM_OS_MEMORY_OBJECTS WHERE
PAGE_ALLOCATOR_ADDRESS IN (SELECT TOP 10
PAGE_ALLOCATOR_ADDRESS FROM
SYS.DM_OS_MEMORY_CLERKS ORDER BY
MULTI_PAGES_KB DESC) ORDER BY
PAGES_ALLOCATED_COUNT DESC

To get an idea of which individual processes are taking up memory, use the following query:

SELECT TOP 10 SESSION_ID, LOGIN_TIME, HOST_NAME,
PROGRAM_NAME, LOGIN_NAME, NT_DOMAIN,
NT_USER_NAME, STATUS, CPU_TIME, MEMORY_USAGE,
TOTAL_SCHEDULED_TIME, TOTAL_ELAPSED_TIME,
LAST_REQUEST_START_TIME,
LAST_REQUEST_END_TIME, READS, WRITES,
LOGICAL_READS, TRANSACTION_ISOLATION_LEVEL,
LOCK_TIMEOUT, DEADLOCK_PRIORITY, ROW_COUNT,
PREV_ERROR FROM SYS.DM_EXEC_SESSIONS ORDER
BY MEMORY_USAGE DESC