November 6, 2013 at 8:39 am
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
November 6, 2013 at 9:45 am
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.
December 2, 2013 at 1:00 pm
Glen Barry wrote a good set of scripts that you might find use in.
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