Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How many GB of memory does 1 database take


How many GB of memory does 1 database take

Author
Message
NJDave
NJDave
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 598
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
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36280 Visits: 18752
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 585
Glen Barry wrote a good set of scripts that you might find use in.

Here's the link

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.

.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search