how to find Ram memory occupied by sql server

Question

Post reply

how to find Ram memory occupied by sql server

vaithi.saran846

SSC Eights!

Points: 992
More actions
September 23, 2011 at 4:39 am

#98140

Hi,
I want to know how much RAM memory is using for sql server while running any job or inserting anything or etc. But i should know how much RAM memory is taking for sql server not all other processes. That mean if any other process or related to sql server process that can include
Regards,
Vaithilingam.K

Viewing 15 posts - 1 through 15 (of 16 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I guess that looking at the task manager is not exactly what you want.

Do you want to do this programatically? From T-SQL or another language?

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Task manager is a terrible way to check SQL's memory. It's incorrect in many cases (yes, there is a reason, it's not a bug)

Performance monitor with the total server memory counter is one of the easier ways to check accurately. DBCC MemoryStatus, but it's hard to interpret, then there are lots of memory-related DMVs under sys.dm_os_*. See Books Online for details.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

GilaMonster (9/23/2011)
Task manager is a terrible way to check SQL's memory. It's incorrect in many cases (yes, there is a reason, it's not a bug)

Thank you for the info, Gail.

How would you check it, then?

-- Gianluca Sartori

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Gianluca Sartori (9/23/2011)
GilaMonster (9/23/2011)
Task manager is a terrible way to check SQL's memory. It's incorrect in many cases (yes, there is a reason, it's not a bug)
Thank you for the info, Gail.
How would you check it, then?

Check out her edit.

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

GilaMonster (9/23/2011)
Task manager is a terrible way to check SQL's memory. It's incorrect in many cases (yes, there is a reason, it's not a bug)
Performance monitor with the total server memory counter is one of the easier ways to check accurately. DBCC MemoryStatus, but it's hard to interpret, then there are lots of memory-related DMVs under sys.dm_os_*. See Books Online for details.

I know about memory-related DMVs, but how would you check total allocated memory?

This MSDN page suggests reading the "Process: Working Set" counter, but I don't think it would be any different from what displayed in the task manager, under the "Working Set" column.

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Performance monitor with the total server memory counter.

Working set is wrong for the same reasons that Task Manager is wrong. Easy to see it's wrong...

Task manager (and Process(sqlservr.exe\Working Set) say 200MB. Total server memory says 2GB. Since 55% of my machine's memory is in use (total of 12GB) and SQL has max server memory currently at 2GB (and I'd just done checkDB on the 4 largest DBs I have), the 200MB is an impossible figure

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

Thanks Gail.

So, why does TaskManager display wrong info for sqlservr.exe? Is there a particular reason?

BTW, is there a reason why are you running SQLServer with SYSTEM user?

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Gianluca Sartori (9/23/2011)
So, why does TaskManager display wrong info for sqlservr.exe? Is there a particular reason?

Yes, it's not a bug (not really)

I could plug Jonathan Kehayias's upcoming book that I know covers it (in chapter 4). but it won't be out for a couple weeks, or I could let the CSS engineers explain it: http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

BTW, is there a reason why are you running SQLServer with SYSTEM user?

Why not? Local desktop. I could set up a domain user for it, but it's overkill and unnecessary in a single-user environment

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spaghettidba SSC Guru Points: 105732 More actions · Answer 9

GilaMonster (9/23/2011)
Gianluca Sartori (9/23/2011)
So, why does TaskManager display wrong info for sqlservr.exe? Is there a particular reason?
Yes, it's not a bug (not really)
I could plug Jonathan Kehayias's upcoming book that I know covers it (in chapter 4). but it won't be out for a couple weeks, or I could let the CSS engineers explain it: http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

Thanks, much clearer now.

BTW, is there a reason why are you running SQLServer with SYSTEM user?
Why not? Local desktop. I could set up a domain user for it, but it's overkill and unnecessary in a single-user environment

I always feel safer when I set up a SQL_SERVICE user on my laptop with no special permissions. Maybe I'm paranoid, but that's just how I feel. -)

-- Gianluca Sartori

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

GilaMonster (9/23/2011)
I could plug Jonathan Kehayias's upcoming book that I know covers it (in chapter 4). but it won't be out for a couple weeks...

Title? That is a book that I'm gonna have to buy.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

SQLRNNR (9/23/2011)
GilaMonster (9/23/2011)
I could plug Jonathan Kehayias's upcoming book that I know covers it (in chapter 4). but it won't be out for a couple weeks...
Title? That is a book that I'm gonna have to buy.

Not 100% sure if it's completely decided on. It's published with Simple Talk, so will be promoted here. It's mostly for the involuntary/accidental DBA. Common problems, explanations and resolutions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

GilaMonster (9/23/2011)
SQLRNNR (9/23/2011)
GilaMonster (9/23/2011)
I could plug Jonathan Kehayias's upcoming book that I know covers it (in chapter 4). but it won't be out for a couple weeks...
Title? That is a book that I'm gonna have to buy.
Not 100% sure if it's completely decided on. It's published with Simple Talk, so will be promoted here. It's mostly for the involuntary/accidental DBA. Common problems, explanations and resolutions.

OIC

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Gianluca Sartori (9/23/2011)
I always feel safer when I set up a SQL_SERVICE user on my laptop with no special permissions. Maybe I'm paranoid, but that's just how I feel. -)

I could, but seriously what's the point?

This is machine on a single-user network (it's not a laptop that could connect to other networks). If someone compromises my network to the point they can get access to the SQL Server, I'm in a world of hurt already and the SQL Server will be the least of my concerns.

When I change it to a domain account it'll be for cross-machine permissions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vaithi.saran846 SSC Eights! Points: 992 More actions · Answer 14

Hi,

Either through front end or back end query anything is enough.. Please send to me

Regards,

Vaithilingam.K