Post reply

CPU,I/O,Memory and #connections for a database

  • August 19, 2010 at 2:08 pm

    #239179

    Hi,

    how to find the CPU,I/O,Memory and # connections for a database and compare it with the same entities at server level in order to find out which database is most resource intensive.

    Thanks in advance,

    hash

  • August 23, 2010 at 10:46 pm

    #1210781

    Percentage I/O by each database can be found using DMVs.

    WITH Agg_IO_Stats

    AS

    (

    SELECT

    DB_NAME(database_id) AS database_name,

    CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576.

    AS DECIMAL(12, 2)) AS io_in_mb

    FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS DM_IO_Stats

    GROUP BY database_id

    )

    SELECT

    ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS row_num,

    database_name,

    io_in_mb,

    CAST(io_in_mb / SUM(io_in_mb) OVER() * 100

    AS DECIMAL(5, 2)) AS pct

    FROM Agg_IO_Stats

    ORDER BY row_num;

    Memory within buffer pool data cache can be figured using below DMV.

    SELECT count(*)*8/1024 AS 'Cached Size (MB)',CASE database_id

    WHEN 32767 THEN 'ResourceDb' ELSE db_name(database_id) END AS 'Database' FROM sys.dm_os_buffer_descriptors GROUP BY db_name(database_id) ,database_id ORDER BY 'Cached Size (MB)' DESC

    I think you may find DMVs for other things like CPU, number of connections.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply