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

4 database instances in a single server (SQL Server 2005) Expand / Collapse
Author
Message
Posted Monday, March 11, 2013 9:42 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 21, 2014 1:40 AM
Points: 94, Visits: 410
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!
Post #1429568
Posted Monday, March 11, 2013 10:50 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:21 AM
Points: 17,977, Visits: 15,981
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
Post #1429576
Posted Tuesday, March 12, 2013 1:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 21, 2014 1:40 AM
Points: 94, Visits: 410
Hi,

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

Which should take? Total or Target?

thanks!
Post #1429605
Posted Tuesday, March 12, 2013 5:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 7,135, Visits: 12,749
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 for more info.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1429685
Posted Tuesday, March 12, 2013 10:12 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:21 AM
Points: 17,977, Visits: 15,981
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
Post #1429928
Posted Tuesday, March 12, 2013 10:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:20 AM
Points: 12,927, Visits: 32,332
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1429969
Posted Tuesday, March 12, 2013 11:00 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:21 AM
Points: 17,977, Visits: 15,981
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
Post #1429979
Posted Monday, March 18, 2013 11:58 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 21, 2014 1:40 AM
Points: 94, Visits: 410
thanks all
Post #1432477
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse