Hi,

As this discussion open for the discuss about how we can actively monitor SQL Server memory utilization and you want to know by email.

Before we jump in to the any memory we have to first know how SQL will grab memory in to Server memory since we are talking about the SQL Server 32-bit edition on Windows Server 2k3 32-bit on without /3GB or USRENVA environment.

As we all know SQL or any application will get the 2GB of virtual Address now internally SQL Server allocated memory in two mail part. which is known as

1. Buffer pool (used for SQL Optimizer,Plan Generation Etc..

2. Non Buffer Pool (Which is AKA "Mem to leave Area")(Used for the 3rd party DLL COM component for extended stored proc and CLR related operation and it will be 256 MB default and

Now these two sections has their own separate function in the SQL Server. so if we are talking to the which region is consuming more memory is decided by the queries to SQL Sever DMV which is very use full.

-- I will give you some list good DMVs in SQL Server 2005 which will describe how you will find memory consumption by querying them

-- Now you can check the performance Counter with the SQL Server

-- SQL Server memory related DMV

-- DBCC MEMORYSTATUS output

-- In the SQL Server 2008 there are new DMV added for the dwell in to the problem of the SQL Server

sys.dm_os_memory_brokers

sys.dm_os_memory_nodes

sys.dm_exec_procedure_stats

-- Like we have there existing DMVs in the SQL Server 2005

sys.dm_os_sys_info

sys.dm_exec_requests

sys.dm_exec_requests

Please refer to the BOOKS Online for further description of the these DMV's these all DMVs will talk about the buffer pool

-- Now let's talk about how will you check for Non Buffer pool memory (Mem to leave) we can use the following DMV

sys.dm_os_virtual_address_dump

Please let me know if you need any more information about it.

Regards,

Nirav Joshi