How many GB of memory does 1 database take

  • Hello -

    I need to take each database off of a physical instance and give memory, cpu specs of what each database needs to run on the VM.

    If I have 10 databases on one instance (SQL Server 2005 Standard) and need the memory used by one database, if I run the following query...

    DECLARE @total_buffer INT;

    SELECT @total_buffer = cntr_value

    FROM sys.dm_os_performance_counters

    WHERE RTRIM([object_name]) LIKE '%Buffer Manager'

    AND counter_name = 'Total Pages';

    ;WITH src AS

    (

    SELECT

    database_id, db_buffer_pages = COUNT_BIG(*)

    FROM sys.dm_os_buffer_descriptors

    --WHERE database_id BETWEEN 5 AND 32766

    GROUP BY database_id

    )

    SELECT

    [db_name] = CASE [database_id] WHEN 32767

    THEN 'Resource DB'

    ELSE DB_NAME([database_id]) END,

    db_buffer_pages,

    db_buffer_MB = db_buffer_pages / 128,

    db_buffer_percent = CONVERT(DECIMAL(6,3),

    db_buffer_pages * 100.0 / @total_buffer)

    FROM src

    ORDER BY db_buffer_MB DESC;

    (found here http://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/)

    Is db_buffer_percent the percentage of the max server memory set for the instance?

    (db_buffer_percent of DB) * (Max server memory) = Amount of memory needed for DB

    Is this accurate or at least accurate enough?

    Thanks

    Dave

  • I think that's a good estimate of what's being used. It's not what's needed, but it's what's in used based on the workload. You can't assume the same buffer pool numbers will apply on a different instance.

    However, I'd think this is a good enough number for you.

  • Glen Barry wrote a good set of scripts that you might find use in.

    Here's the link[/url]

    the below script tells you well... what his comments say. Buffer Usage by database. If you're running under powered already, the virtuals will still want more ram I'm sure. This number is a current window in time, not a history. Take it at face value.

    -- Get total buffer usage by database for current instance (Query 22) (Total Buffer Usage by Database)

    -- This make take some time to run on a busy instance

    SELECT DB_NAME(database_id) AS [Database Name],

    CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2)) AS [Cached Size (MB)]

    FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)

    WHERE database_id > 4 -- system databases

    AND database_id <> 32767 -- ResourceDB

    GROUP BY DB_NAME(database_id)

    ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

    To note, I wouldn't use this as an exact value. This is just more of a helpful hint as an idea of how much each database is using in cache.

    .

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

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