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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » 4 database instances in a single server (SQL...

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

Rate Topic

Display Mode

Topic Options

Author

Message

chewychewy

Posted Monday, March 11, 2013 9:42 PM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 9:47 PM
Points: 72, Visits: 287

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

SQLRNNR

Posted Monday, March 11, 2013 10:50 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

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 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1429576

chewychewy

Posted Tuesday, March 12, 2013 1:26 AM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 9:47 PM
Points: 72, Visits: 287

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

opc.three

Posted Tuesday, March 12, 2013 5:15 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:55 PM
Points: 6,720, Visits: 11,759

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1429685

SQLRNNR

Posted Tuesday, March 12, 2013 10:12 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

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.

Post #1429928

Lowell

Posted Tuesday, March 12, 2013 10:47 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:23 AM
Points: 11,638, Visits: 27,715

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

SQLRNNR

Posted Tuesday, March 12, 2013 11:00 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

Lowell (3/12/2013)

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.

Post #1429979

chewychewy

Posted Monday, March 18, 2013 11:58 PM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 9:47 PM
Points: 72, Visits: 287

thanks all

Post #1432477

« Prev Topic | Next Topic »

Permissions