SQL Server Performance matrix queries

To start with, these are some queries which i have been using to find out some of the key things regarding performance from sql server. (I have tested these only on SQL Server 2008 R2)

Query to find SQL Server memory utilisation

SELECT total_physical_memory_kb/1024 TotMemMB, available_physical_memory_kb/1024 as AvailPhyMemMB,
total_page_file_kb/1024 as TotpagefileMB, available_page_file_kb/1024 as AvailPagefileMB,
system_memory_state_desc, (select memory_utilization_percentage from sys.dm_os_process_memory)
as memory_utilization_percentage FROM sys.dm_os_sys_memory;

Query to check OS Performance counters. (I specifically look for PLE about which I will write in another post)

SELECT object_name,counter_name,cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = N'Page life expectancy' and OBJECT_NAME= N'SQLServer:Buffer Manager'
or counter_name like('Free pages%') and OBJECT_NAME= N'SQLServer:Buffer Manager'
or counter_name like ('Free list stalls/sec%') or counter_name like ('Page reads%') or counter_name like 'Index%';

Find the Database files Read / Write

SELECT top 10 db.name, files.filename, num_of_reads,(num_of_bytes_read/1024/1024) as bytesreadinMB, num_of_writes, ( num_of_bytes_written/1024/1024) as byteswritteninMB
,(size_on_disk_bytes/1024/1024/1024) as SizeondiskGB, log_reuse_wait_desc
FROM sys.dm_io_virtual_file_stats (NULL,NULL) dmv
join sys.databases as db on db.database_id = dmv.database_id
join sys.sysaltfiles as files on dmv.database_id = files.dbid
and dmv.[file_id]=files.fileid
ORDER BY dmv.num_of_bytes_read DESC;
--=============================================

There are N number of things apart from this. Few of those will be coming up sooner here.

MvM