Some useful DMVs

/*--Gives portion of buffer used by an object. Includes index ID (if 0 then heap)
--Breaks down buffers by object (table, index) in the buffer pool
--This should be run for individual database*/

USE TESTDB
GO
SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName], p.[object_id],
p.index_id, COUNT(*)/128 AS [Buffer size(MB)], COUNT(*) AS [Buffer_count]
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
GROUP BY p.[object_id], p.index_id
ORDER BY buffer_count DESC;

/*--Provides login name and session counts for that login.
--This is useful when there are multiple app users connecting*/
USE Master
GO
SELECT login_name as [Login Name] , COUNT(session_id) AS [Session count]
FROM sys.dm_exec_sessions
GROUP BY login_name
ORDER BY COUNT(session_id) DESC;

/*--Gives logical/physical CPU count, hyper threading and total memory*/
USE Master
GO
--SQL 2005
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)], sqlserver_start_time
FROM sys.dm_os_sys_info;
--SQL 2008
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)]
FROM sys.dm_os_sys_info;