Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How many GB of memory does 1 database take Expand / Collapse
Author
Message
Posted Wednesday, November 6, 2013 8:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 9:54 AM
Points: 82, Visits: 498
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


Post #1511910
Posted Wednesday, November 6, 2013 9:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 9:02 PM
Points: 33,153, Visits: 15,284
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
Post #1511928
Posted Monday, December 2, 2013 1:00 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 2:54 PM
Points: 611, Visits: 507
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.


.
Post #1519008
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse