How do I find the total memroy consumed by a SQL2K5 instance
My Box:
I have an SQL2k5 Dev 64 bit running on a Win2K3 64 bit box with 12 GB of RAM with two dual cores intel xeon processors.
My concern:
Task manager never shows sqlserver.exe consuming more than 500 MB of memory even when running very big jobs.( for e.g. joins on mutliple tables of 10 Million plus rows with lots of calculations while also using some CLR UDFs)
My question: Is the task manager best way to find the total memory consumption by a SQL2K5 instance? is there any other way to find the total memory consumption by SQL2K5?
Apprciate any answers or leads.
Thanks
Saptagiri Tangudu
PNT Marketing Services Inc.
Yes there are other methods/mechanisms and in fact task mgr is horrible overall for wanting detailed system metrics.
I would leverage Windows Performance Monitor with the following objects:
SQL Server Buffer Manager
SQL Server Buffer Node
SQL Server Memory Manager
If your instance is running under an account with the lock pages in memory priveledge then all memory used for the buffer pool will be allocated through mechanisms that do not show in task manager or via the process\private bytes counter in perfmon.
To determine how much memory is used for the buffer pool you will need to have a look at the SQLServer:Memory Manager\Total Server memory counter.
Jerome,
Thanks for the answer. Is there a DM view or any internal SQL objects that I can query to get this info. I would like to be able to include these stats in my script, so I can measure performance. Also, a little lazy to go start the perfmon
Saptagiri Tangudu
PNT Marketing Services, Inc.
