一个数据库服务器上有多个数据库，不同数据库连接到不同的应用程序，请问怎样知道这些数据库各自占用的内存、CPU等
一个数据库服务器上有多个数据库，不同数据库连接到不同的应用程序，请问怎样知道这些数据库运行时各自占用的内存、CPU等？谢谢！
[解决办法]
貌似可以用性能监视器来获取大概值，某些计数器有细化到库级别的。
[解决办法]
#1.数据库级别的应该无法统计。比较跨库多表连接，内存如何计算。
#2.内存，CPU的占用可以统计到实例级别，SQL级别；IO可以统计到数据库级别。
[解决办法]
-- Various SQL Server IO Statistics
;WITH?
?IOT AS??? -- Total sums of all properties.
???(SELECT SUM(IOS.num_of_reads) AS Reads
??????????,SUM(IOS.num_of_bytes_read) BytesRead
??????????,SUM(IOS.io_stall_read_ms) AS IoStallReadMs
??????????,SUM(IOS.num_of_writes) AS Writes
??????????,SUM(IOS.num_of_bytes_written) AS BytesWritten
??????????,SUM(IOS.io_stall_write_ms) AS IoStallWritesMs
??????????,SUM(IOS.io_stall) AS IoStall
??????????,SUM(IOS.size_on_disk_bytes) SizeOnDisk
????FROM sys.dm_io_virtual_file_stats(default, default) AS IOS)
,IOF AS???
???(SELECT DBS.name AS DatabaseName
??????????,MF.name AS [FileName]
??????????,MF.type_desc AS FileType
??????????,SUBSTRING(MF.physical_name, 1, 3) AS Drive
??????????,CASE WHEN DBS.name IN ('master', 'model', 'msdb', 'tempdb')
????????????????THEN 1 ELSE 0 END AS IsSystemDB
??????????,IOS.*
????FROM sys.dm_io_virtual_file_stats(default, default) AS IOS
?????????INNER JOIN sys.databases AS DBS
?????????????ON IOS.database_id = DBS.database_id
?????????INNER JOIN sys.master_files AS MF
?????????????ON IOS.database_id = MF.database_id
????????????????AND IOS.file_id = MF.file_id)
/*
-- Detailed for each file
SELECT IOF.DatabaseName
??????,IOF.FileName
??????,IOF.FileType
??????,CONVERT(numeric(5,2), 100.0 * IOF.num_of_reads / IOT.Reads) AS [Reads%]
??????,CONVERT(numeric(5,2), 100.0 * IOF.num_of_bytes_read / IOT.BytesRead) AS [BytesRead%]
??????,CONVERT(numeric(5,2), 100.0 * IOF.io_stall_read_ms / IOT.IoStallReadMs) AS [IoStallReadMs%]
??????,CONVERT(numeric(5,2), 100.0 * IOF.num_of_writes / IOT.Writes) AS [Writes%]
??????,CONVERT(numeric(5,2), 100.0 * IOF.num_of_bytes_written / IOT.BytesWritten) AS [BytesWritten%]
??????,CONVERT(numeric(5,2), 100.0 * IOF.io_stall_write_ms / IOT.IoStallWritesMs) AS [IoStallWritesMs%]
??????,CONVERT(numeric(5,2), 100.0 * IOF.io_stall / IOT.IoStall) AS [IoStall%]
??????,CONVERT(numeric(5,2), 100.0 * IOF.size_on_disk_bytes / IOT.SizeOnDisk) AS [SizeOnDisk%]
FROM IOF CROSS APPLY IOT
ORDER BY IOF.DatabaseName
????????,IOF.FileType;
*/
??
/*
-- Overview by file type
SELECT IOF.FileType
??????,CONVERT(numeric(5,2), SUM(100.0 * IOF.num_of_reads / IOT.Reads)) AS [Reads%]
??????,CONVERT(numeric(5,2), SUM(100.0 * IOF.num_of_bytes_read / IOT.BytesRead)) AS [BytesRead%]
??????,CONVERT(numeric(5,2), SUM(100.0 * IOF.io_stall_read_ms / IOT.IoStallReadMs)) AS [IoStallReadMs%]
??????,CONVERT(numeric(5,2), SUM(100.0 * IOF.num_of_writes / IOT.Writes)) AS [Writes%]
??????,CONVERT(numeric(5,2), SUM(100.0 * IOF.num_of_bytes_written / IOT.BytesWritten)) AS [BytesWritten%]
??????,CONVERT(numeric(5,2), SUM(100.0 * IOF.io_stall_write_ms / IOT.IoStallWritesMs)) AS [IoStallWritesMs%]
??????,CONVERT(numeric(5,2), SUM(100.0 * IOF.io_stall / IOT.IoStall)) AS [IoStall%]
??????,CONVERT(numeric(5,2), SUM(100.0 * IOF.size_on_disk_bytes / IOT.SizeOnDisk)) AS [SizeOnDisk%]
FROM IOF CROSS APPLY IOT
GROUP BY IOF.FileType
ORDER BY IOF.FileType;
*/
??
-- Overview per drive
SELECT IOF.Drive
??????,CONVERT(numeric(5,2), SUM(100.0 * IOF.num_of_reads / IOT.Reads)) AS [Reads%]
??????,CONVERT(numeric(5,2), SUM(100.0 * IOF.num_of_bytes_read / IOT.BytesRead)) AS [BytesRead%]
??????,CONVERT(numeric(5,2), SUM(100.0 * IOF.io_stall_read_ms / IOT.IoStallReadMs)) AS [IoStallReadMs%]
??????,CONVERT(numeric(5,2), SUM(100.0 * IOF.num_of_writes / IOT.Writes)) AS [Writes%]
??????,CONVERT(numeric(5,2), SUM(100.0 * IOF.num_of_bytes_written / IOT.BytesWritten)) AS [BytesWritten%]
??????,CONVERT(numeric(5,2), SUM(100.0 * IOF.io_stall_write_ms / IOT.IoStallWritesMs)) AS [IoStallWritesMs%]????? ,CONVERT(numeric(5,2), SUM(100.0 * IOF.io_stall / IOT.IoStall)) AS [IoStall%]
??????,CONVERT(numeric(5,2), SUM(100.0 * IOF.size_on_disk_bytes / IOT.SizeOnDisk)) AS [SizeOnDisk%]
FROM IOF CROSS APPLY IOT
GROUP BY IOF.Drive
ORDER BY IOF.Drive;
SELECT dbname=DB_NAME(database_id), [file_id],num_of_bytes_read, num_of_bytes_written
FROM sys.dm_io_virtual_file_stats(NULL,NULL)
