In the part 1 we saw how SQL server memory was allocated at SQL server start up, in this article we will see how to find out what is the memory assigned to the different parts of memory discussed in earlier article.
Most of the scripts used in this article are taken from MSDN white paper "Troubleshooting performance problems in sql server 2008" written by Sunil Agarwal, Boris Baryshnikov, Keith Elmore, Juergen Thomas, Kun Cheng and Burzin Patel (http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx). I have merely used these scripts to explain on SQL Server Memory.
1. To find total size of the physical memory on the Server:-
select total_physical_memory_kb/1024/1024 AS [total_physical_memory_GB] from sys.dm_os_sys_memory
total_physical_memory_GB
------------------------
63
(1 row(s) affected)
2. To Find Total Memory allocated to SQL Server :-
select "Max_Memory" =
CASE
WHEN a.value = 2147483647 THEN (select total_physical_memory_kb/1024/1024 AS [total_physical_memory_GB] fromsys.dm_os_sys_memory)
ELSE a.value
END
from sys.configurations a where name = 'max server memory (MB)'
Max_Memory
------------------
47344
(1 row(s) affected)
3. To Find the total memory allocated to Buffer Pool:-
a. 64- bit SQL:-
select SUM(awe_allocated_kb)/1024/1024 from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLBUFFERPOOL'
--------------------
46
(1 row(s) affected)
b. 32 Bit Awe Enable:- To find if Awe is enable run select value from sys.configurations where name = 'awe enabled', The Value 1 indicates AWE is enabled.
select SUM(awe_allocated_kb)/1024/1024 from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLBUFFERPOOL'
--------------------
21
(1 row(s) affected)
c. 32 bit AWE not allocated:-
I could not find exact query but the below query will give you the almost the the correct value,
select SUM(virtual_memory_reserved_kb)/1024 from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLBUFFERPOOL'
4. To Find Total Memory assigned to the VAS(User Kernel mode):-
The below Query is taken from MSDN white paper "Troubleshooting performance problems in sql server 2008" written by Sunil Agarwal, Boris Baryshnikov, Keith Elmore, Juergen Thomas, Kun Cheng and Burzin Patel (http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx) and Christian Bolton's Blog http://sqlblogcasts.com/blogs/christian/archive/2008/01/07/sql-server-memtoleave-vas-and-64-bit.aspx
WITH VASummary(Size,Reserved,Free) AS
(SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size, region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size)
SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024/1024 AS [Total avail mem, MB] ,CAST(MAX(Size) AS BIGINT)/1024/1024 AS [Max free size, MB]
FROM VASummary
WHERE Free <> 0
Result:-
Total avail mem, MB Max free size, MB
-------------------- --------------------
288 84
(1 row(s) affected)
We have seen how to find the total memory allocated to the in each section, now we can discuss how to find the amount of memory used in each parts of SQL memory.
Before SQL 2005 we used DBCC MEMORYSTATUS output to find the memory components used space, post SQL 2005 we can get all the info from OS DMV sys.dm_os_memory_clerks.
Before SQL 2005 we used DBCC MEMORYSTATUS output to find the memory components used space, post SQL 2005 we can get all the info from OS DMV sys.dm_os_memory_clerks.
As we discussed in PART 1 MultiPage allocation happens in the VAS Area and Single page allocation comes from Buffer Pool, we can calculate the space used in Bpool and outside Bpool with the below query,
The below Query is taken from MSDN white paper "Troubleshooting performance problems in sql server 2008" written by Sunil Agarwal, Boris Baryshnikov, Keith Elmore, Juergen Thomas, Kun Cheng and Burzin Patel (http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx)
-- amount of memory consumed by components outside the bBuffer pool
-- note that we exclude single_pages_kb as they come from BPool
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb) as [Overall used w/o BPool, Kb]
from
sys.dm_os_memory_clerks
where
type <> 'MEMORYCLERK_SQLBUFFERPOOL'
-- amount of memory consumed by BPool
-- note that currenlty only BPool uses AWE
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb
+ awe_allocated_kb) as [Used by BPool with AWE, Kb]
from
sys.dm_os_memory_clerks
where
type = 'MEMORYCLERK_SQLBUFFERPOOL'
We can also use Ring buffers to find memory used, we can discuss this in a separate article as i am planning write a blog on RING BUFFERS and extended events.
No comments:
Post a Comment