--查看内存占用

SET TRAN ISOLATION LEVEL READ UNCOMMITTED
SELECT
ISNULL(DB_NAME(database_id), 'ResourceDb') AS DatabaseName
, CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2))
AS [Size (MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY DatabaseName

--查看IO使用情况

SELECT 
DB.database_id AS DatabaseId, 
DB.name AS DatabaseName, 
MF.name AS LogicFileName, 
MF.type_desc AS TypeDesc, 
CAST(VF.num_of_bytes_read/1024.0/1024.0 AS INT) AS num_of_MB_read, 
CAST(VF.num_of_bytes_written /1024.0/1024.0 AS INT) AS num_of_MB_written, 
VF.num_of_reads, 
VF.num_of_writes, 
VF.io_stall_read_ms, 
VF.io_stall_write_ms, 
VF.io_stall, 
CAST(VF.size_on_disk_bytes/1024.0/1024.0 AS INT) AS size_on_disk_MB 
FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS VF 
inner JOIN sys.master_files MF 
ON VF.database_id=MF.database_id and VF.file_id=MF.file_id 
inner JOIN sys.databases DB 
ON VF.database_id=DB.database_id