4 database instances in a single server (SQL Server 2005)

Question

4 database instances in a single server (SQL Server 2005)

chewychewy

Hall of Fame

Points: 3126
More actions
March 11, 2013 at 9:42 pm

#290619

Hi Guys,
I have 4 database instances (2005) running in a single server.
How am i able to know how much memory each database instance is using?
thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Perfmon has counters by the instance. You can load up memory counters by instance in perfmon to track mem usage.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

chewychewy Hall of Fame Points: 3126 More actions · Answer 2

Hi,

thanks. Is it something like total server memory and target server memory in buffer manager?

Which should take? Total or Target?

thanks!

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

Look for SQL Server:Memory Manager (as MSSQL$InstanceName:Memory Manager) counters:

- Target Server Memory (KB) is how much memory the instance would want to use if the demand was there from active processes and the memory were available.

- Total Server Memory (KB) is how much memory the instance actually is using at the moment.

Once the buffer pool is warmed up those numbers will generally be the same, but immediately after a restart, or if demand for data never reaches the capabilities of the server memory, Target will be lower than Total.

See Page 153 of Troubleshooting SQL Server: A Guide for the Accidental DBA[/url] for more info.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Here is a quick query to see the internal "point in time" memory clerk use for the sql instance

SELECT @@servername AS InstanceName,SUM(single_pages_kb)/1024 AS [SPA Mem, Mb]

FROM sys.dm_os_memory_clerks

ORDER BY [SPA Mem, Mb] DESC

You would need to run this against each instance and run it multiple times to see how things are changing or not changing.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lowell SSC Guru Points: 323504 More actions · Answer 5

ok dumb question time guys:

i also have 4 instances on my developer machine, for versions 2005 thru 2012.

when selecting the memory counters in Perfmon, i see instances for 2008, 2008R2 and 2012 , which are Standard(default instance) , Developer(named instance) and Developer(named instance) respectively.

i don't see my 2005(named instance) , which i know is an Express version.

Do Installations of Express Editions not have performance counters available to them?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Lowell (3/12/2013)
ok dumb question time guys:
i also have 4 instances on my developer machine, for versions 2005 thru 2012.
when selecting the memory counters in Perfmon, i see instances for 2008, 2008R2 and 2012 , which are Standard(default instance) , Developer(named instance) and Developer(named instance) respectively.
i don't see my 2005(named instance) , which i know is an Express version.
Do Installations of Express Editions not have performance counters available to them?

I just checked my express edition counters. I have the counters - but that is SQL 2008 Express. I think there was something changed between 2005 and 2008 though in regards to Perfmon counters - but not sure off the top of my head.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

chewychewy Hall of Fame Points: 3126 More actions · Answer 7

chewychewy

Hall of Fame

Points: 3126

March 18, 2013 at 11:58 pm

#1598293

thanks all