Jun201010

Alex

0

SQL Server Memory Checking

–==================================
–== MEMORY CHECKING ==
–==================================
select * from sys.dm_os_performance_counters where counter_name like ‘%Target Server Memory%’
GO
select * from sys.dm_os_performance_counters where counter_name like ‘%Total Server Memory (KB)%’
GO
select *, cntr_value/1024.00/1024.00 from master..SYSPERFINFO where counter_name like ‘%Target Server Memory%’
GO
DBCC MEMORYSTATUS
GO
select sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb] from sys.dm_os_memory_clerks

Feb201001

Alex

0

Cache and Performance - AdHoc Queries and Proc Cache

—————————————
–    STORED PROCEDURE CACHE ALLOCATION
—————————————
SELECT CAST(name AS VARCHAR(28)) AS ‘Object’,
single_pages_kb AS ‘Cache in Kb’,
entries_count AS ‘Num of entries’
FROM sys.dm_os_memory_cache_counters
WHERE type in (’CACHESTORE_SQLCP’, ‘CACHESTORE_OBJCP’, ‘CACHESTORE_PHDR’, ‘CACHESTORE_XPROC’)
—————————————
–    DATABASE BUFFER CACHE ALLOCATION
—————————————
SELECT count(*) AS cached_pages_count
,CASE database_id
WHEN 32767 THEN ‘ResourceDb’
ELSE db_name(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC;
—————————————
–    CACHED PAGES FOR CURRENT DATABASE
—————————————
SELECT count(*)AS cached_pages_count
,name ,index_id
FROM sys.dm_os_buffer_descriptors [...]

Jan201029

Alex

0

Number of cached pages used by each database in SQL Server 2005

SELECT count(*) AS cached_pages_count
,CASE database_id
WHEN 32767 THEN ‘ResourceDb’
ELSE db_name(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC;
See:
http://msdn.microsoft.com/en-us/library/ms173442.aspx

Jan201029

Alex

0

sys.dm_os_memory_cache_clock_hands - Caches and memory pressure

Caches and memory pressure
An alternative way to look at external and internal memory pressure is to look at the behavior of memory caches.
One of the differences of internal implementation of SQL Server  2005  compared to SQL Server 2000 is uniform caching framework. In order to remove the least recently used entries from caches, the framework implements a [...]

Sep200916

Alex

0

SQL Server, Memory Manager Object

SQL Server, Memory Manager ObjectSQL Server, Memory Manager Object
The Memory Manager object in Microsoft SQL Server provides counters to monitor overall server memory usage. Monitoring overall server memory usage to gauge user activity and resource usage can help you to identify performance bottlenecks. Monitoring the memory used by an instance of SQL Server can help [...]

Sep200916

Alex

1

Memory Performance

select * from master.dbo.sysperfinfo where counter_name like ‘%server memory%’
exec sp_configure ‘max server memory’, 3072
reconfigure
go
exec sp_configure ‘min server memory’, 0
reconfigure
go