August 14, 2014 at 6:31 pm
I have a SQL Server that is using 1.7 GB of memory which is pretty high rather than usual . How do I know the activity that is running on it ?
Any response is highly appreciated
Cheers
August 14, 2014 at 9:45 pm
SQL will buffer as much as it can according to the Max Memory setting.
To check which database is using most of the buffer pool, run the following query from http://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/
-- Note: querying sys.dm_os_buffer_descriptors
-- requires the VIEW_SERVER_STATE permission.
DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Total Pages';
;WITH src AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3),
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;
Then which object from that database:
;WITH src AS
(
SELECT
[Object] = o.name,
[Type] = o.type_desc,
[Index] = COALESCE(i.name, ''),
[Index_Type] = i.type_desc,
p.[object_id],
p.index_id,
au.allocation_unit_id
FROM
sys.partitions AS p
INNER JOIN
sys.allocation_units AS au
ON p.hobt_id = au.container_id
INNER JOIN
sys.objects AS o
ON p.[object_id] = o.[object_id]
INNER JOIN
sys.indexes AS i
ON o.[object_id] = i.[object_id]
AND p.index_id = i.index_id
WHERE
au.[type] IN (1,2,3)
AND o.is_ms_shipped = 0
)
SELECT
src.[Object],
src.[Type],
src.[Index],
src.Index_Type,
buffer_pages = COUNT_BIG(b.page_id),
buffer_mb = COUNT_BIG(b.page_id) / 128
FROM
src
INNER JOIN
sys.dm_os_buffer_descriptors AS b
ON src.allocation_unit_id = b.allocation_unit_id
WHERE
b.database_id = DB_ID()
GROUP BY
src.[Object],
src.[Type],
src.[Index],
src.Index_Type
ORDER BY
buffer_pages DESC;
August 14, 2014 at 10:50 pm
Andrew G (8/14/2014)
SQL will buffer as much as it can according to the Max Memory setting.To check which database is using most of the buffer pool, run the following query from http://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/
-- Note: querying sys.dm_os_buffer_descriptors
-- requires the VIEW_SERVER_STATE permission.
DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Total Pages';
;WITH src AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3),
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;
Then which object from that database:
;WITH src AS
(
SELECT
[Object] = o.name,
[Type] = o.type_desc,
[Index] = COALESCE(i.name, ''),
[Index_Type] = i.type_desc,
p.[object_id],
p.index_id,
au.allocation_unit_id
FROM
sys.partitions AS p
INNER JOIN
sys.allocation_units AS au
ON p.hobt_id = au.container_id
INNER JOIN
sys.objects AS o
ON p.[object_id] = o.[object_id]
INNER JOIN
sys.indexes AS i
ON o.[object_id] = i.[object_id]
AND p.index_id = i.index_id
WHERE
au.[type] IN (1,2,3)
AND o.is_ms_shipped = 0
)
SELECT
src.[Object],
src.[Type],
src.[Index],
src.Index_Type,
buffer_pages = COUNT_BIG(b.page_id),
buffer_mb = COUNT_BIG(b.page_id) / 128
FROM
src
INNER JOIN
sys.dm_os_buffer_descriptors AS b
ON src.allocation_unit_id = b.allocation_unit_id
WHERE
b.database_id = DB_ID()
GROUP BY
src.[Object],
src.[Type],
src.[Index],
src.Index_Type
ORDER BY
buffer_pages DESC;
Thanks for your response!!
What about if I want to know the query that consume the biggest memory ?
cheers
August 15, 2014 at 1:20 am
But memory usage is tied mostly to the buffer pool for your database. You can check the amount of memory assigned to a session which is currently connected though:
SELECT
sys.dm_exec_sessions.session_id AS [SESSION ID]
,DB_NAME(database_id) AS [DATABASE Name]
,HOST_NAME AS [System Name]
,program_name AS [Program Name]
,login_name AS
,status
,cpu_time AS [CPU TIME (in milisec)]
,total_scheduled_time AS [Total Scheduled TIME (in milisec)]
,total_elapsed_time AS [Elapsed TIME (in milisec)]
,(memory_usage * 8) AS [Memory USAGE (in KB)]
,(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)]
,(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)]
,(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)]
,(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)]
,CASE is_user_process
WHEN 1 THEN 'user session'
WHEN 0 THEN 'system session'
END AS [SESSION Type], row_count AS [ROW COUNT]
FROM
sys.dm_db_session_space_usage
INNER join
sys.dm_exec_sessions
ON sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id
ORDER BY sys.dm_exec_sessions.memory_usage desc;
Otherwise, check which queries are using the most (logical) IO which is what will be causing more pages to be cached in the buffer pool.
Glen Berry's SQL performance scripts have queries to show top 10 queries by IO http://sqlserverperformance.wordpress.com/
August 15, 2014 at 2:18 am
WhiteLotus (8/14/2014)
What about if I want to know the query that consume the biggest memory ?
Your largest memory consumer won't be a query. Most of the memory will be going to the buffer pool, the data cache and plan cache forming the major parts of that.
1.7GB of memory is very low for SQL Server. Sounds like a 32 bit instance without AWE on. Unless it's a small DB with minimal usage.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 17, 2014 at 6:22 pm
GilaMonster (8/15/2014)
WhiteLotus (8/14/2014)
What about if I want to know the query that consume the biggest memory ?Your largest memory consumer won't be a query. Most of the memory will be going to the buffer pool, the data cache and plan cache forming the major parts of that.
1.7GB of memory is very low for SQL Server. Sounds like a 32 bit instance without AWE on. Unless it's a small DB with minimal usage.
what is the measurement on small or big Database ? any number as the guidance ?
then which GB of memory that we should be worried ?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply