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.
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 4192Target 117091Hashed 2985InternalReservation 25ExternalReservation 0Min Free 64Visible 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 DESCCOMPUTE SUM (COUNT(*)) Total Pages database_id----------- -----------557 7449 6342 32767301 4265 5263 1179 2127 856 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.NameFROM ( 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 ) ALEFT OUTER JOIN sys.indexes B ON B.object_id = A.objid AND B.index_id = A.IndIdORDER BY 2,1COMPUTE 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.