Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
 Posted - 2005-01-09 : 21:20:37
|
| is there a way to determine memory usage of sqlserver through QA?tia--------------------keeping it simple... |
|
|
RoyalSher
Yak Posting Veteran
95 Posts |
Posted - 2005-01-10 : 00:18:28
|
| There are two system tables in master db - sysprocesses & sysperfinfo which you may want to query for your desired results. Though, you may not have the right data you are looking for. But the above tables would give you worthy data. Try those!RoyalSher.*********The world is the great gymnasium where we come to make ourselves strong. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-10 : 01:29:23
|
Jen, do you mean these two chaps?SELECT object_name, counter_name, instance_name, cntr_valueFROM master..sysperfinfo PageSplitsSec (nolock)WHERE (object_name = 'SQLServer:Memory Manager' AND counter_name = 'Total Server Memory (KB)' AND instance_name = '') OR (object_name = 'SQLServer:Memory Manager' AND counter_name = 'Target Server Memory(KB)' AND instance_name = '') if so I posted some fuller code at the bottom of this threadhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44103&SearchTerms=TargetServerMemoryKristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-01-10 : 02:50:18
|
quote:
Originally posted by Kristen
Jen, do you mean these two chaps?SELECT object_name, counter_name, instance_name, cntr_valueFROM master..sysperfinfo PageSplitsSec (nolock)WHERE (object_name = 'SQLServer:Memory Manager' AND counter_name = 'Total Server Memory (KB)' AND instance_name = '') OR (object_name = 'SQLServer:Memory Manager' AND counter_name = 'Target Server Memory(KB)' AND instance_name = '') if so I posted some fuller code at the bottom of this threadhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44103&SearchTerms=TargetServerMemoryKristen
tnx kristen, saw this query from your other posts, i think this is the data then. I was trying to figure out if the memory being used for sql server 2000 standard edition is really 2GB only or is that for both OS and SQL Server?We have one server showing total ~2.1GB of memory being used and ~1.6GB used for SQL server. Can you shed some light on this?--------------------keeping it simple... |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-10 : 02:58:55
|
| Total Server Memory is the memory being reserved strictly for SQL Server. Target Server Memory is what SQL Server would like to have, which will be equal or greater than Total Server Memory.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-10 : 11:22:39
|
| SQL will (in my limit experience) allocate the memory it needs - so that fact that it is only currently using 1.6GB doesn't mean that it does not have access to 2GB worth, should it need it in the future.Start doing some less-cached operations, on some bigger databases, or copy databases, to see if you can make it use some more!But as Derrick says - if the Total >= Target then SQL has access to all the memory it currently needs.Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-01-11 : 00:31:54
|
| tnx for the explanation. the reason i wanted to know the amount of memory being used is because:my boss tells me that upgrading the server memory to 4GB is useless because SQL standard can only use 2GB max (without the switch ofcourse)so i queried my way into disproving his interpretation of the link he found. The total memory being used by the server is 2.1GB, sql uses 1.6GB of that memory. so if we had 2GB only, that explains why the server is slow.am i correct in this assumption? once we've upgraded to 4GB, everything was running faster compared to before (although the solution is to optimize the queries, increasing the memory improved the situation a bit)--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-11 : 01:38:28
|
| If you have more than 2GB then SQL can use 2GB, and the operating system 2GB (but it won't use anything like that much, I suppose).Our servers seem to have 4GB in them, rather than 2-and-a-bit-GB. Maybe that's 'coz 4GB is a multiple of RAM chip sizes and 2-and-a-bit-GB isn't?Kristen |
|
|
|
|
|
|
|
|
used memory