SQL Server team has done some great amount of work in SQL Server 2005 to make the performance analysis of server easy. I am going to post a series of code snippets which I find useful in my day to day work.

Memory Analysis

Let us assume we have a scenario which executes a bunch of queries. For a high level analysis of a given scenario, execute the following command -
 
DBCC MEMORYSTATUS
 
Buffer Counts                  Buffers
------------------------------ -----------
Committed                      4192
Target                         117091
Hashed                         2985
InternalReservation            25
ExternalReservation            0
Min Free                       64
Visible                        117091
 
See http://support.microsoft.com/Default.aspx?id=271624 for help on DBCC MEMORYSTATUS command and its output. For a description of changes in the command for SQL Server 2005, see http://blogs.msdn.com/slavao/comments/372309.aspx.
 
This shows the distribution of pages in the SQL Server cache. “Hashed Pages” states the number of data pages in the buffer pool. This query can be executed before and after the scenario to see the change in the buffer pool distribution.
 
The distribution of pages across databases can be found by executing
 
SELECT
COUNT(*) "Total Pages", database_id
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY 1 DESC
COMPUTE SUM (COUNT(*))
 
Total Pages database_id
----------- -----------
557         7
449         6
342         32767
301         4
265         5
263         1
179         2
127         8
56          3
 
-----------
2539

Once the database with the maximum pages has been identified, executing the following command for the offending database should give the distribution of hashed pages in the buffer pool on per object basis-
 
SELECT
            A.Count, Object_Name(ObjID), ObjId, B.Name
FROM
      (    
      SELECT COUNT(*) count, p.object_id ObjID, p.index_id IndId
      FROM sys.partitions p
      JOIN sys.allocation_units a
        ON p.partition_id = a.container_id
      JOIN sys.dm_os_buffer_descriptors c
        ON  a.allocation_unit_id = c.allocation_unit_id
       AND database_id = db_id()
      GROUP BY p.object_id , p.index_id
      ) A
LEFT OUTER JOIN sys.indexes B 
  ON  B.object_id = A.objid
 AND B.index_id = A.IndId
ORDER BY 2,1
COMPUTE SUM(A.Count)

After finding out the table for which the maximum pages are present in the database, we can attack the queries which are running queries on the table in question.