InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 03:19:39 GMT20http://www.sqlservercentral.com/Forums/Topic1215340-391-1.aspxExcellent scripts... It was really helpful :-)Thu, 18 Oct 2012 03:29:26 GMTCOOL_ICEhttp://www.sqlservercentral.com/Forums/Topic1215340-391-1.aspxthese queries tell you which databases are consuming most memory in the buffer cache and then you can drill down to which objects in that database are using the most memory, that might help point you in the direction of the queries that use those objects[code="sql"]--find out how big buffer pool is and determine percentage used by each databaseDECLARE @total_buffer INT;SELECT @total_buffer = cntr_value FROM sys.dm_os_performance_countersWHERE 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 srcORDER BY db_buffer_MB DESC;--then drill down into memory used by objects in database of your choiceUSE db_with_most_memory;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) / 128FROM srcINNER JOIN sys.dm_os_buffer_descriptors AS b ON src.allocation_unit_id = b.allocation_unit_idWHERE b.database_id = DB_ID()GROUP BY src.[Object], src.[Type], src.[Index], src.Index_TypeORDER BY buffer_pages DESC;[/code]Fri, 02 Dec 2011 09:52:22 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1215340-391-1.aspxThanks Gila, I'll check it out to see if i can find something.Fri, 02 Dec 2011 08:52:11 GMTdsbolanoshttp://www.sqlservercentral.com/Forums/Topic1215340-391-1.aspxThe majority of the memory usage is usually the data cache and the plan cache together. The memory used by individual queries is usually tiny in comparison.Use the sys.dm_os_memory_clerks DMV, not all that understandable, but shows the various caches and their memory usage.[code="sql"]SELECT * FROM sys.dm_os_memory_clerks ORDER BY (single_pages_kb + multi_pages_kb + awe_allocated_kb) desc[/code]On my test machine, that shows the largest memory consumer to be MEMORYCLERK_SQLBUFFERPOOL, the data cache, with CACHESTORE_SQLCP (ad-hoc plans) second.Fri, 02 Dec 2011 07:31:07 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1215340-391-1.aspxHi GuysI'm trying to find which processes are the most memory consuming on my SQL Server, when I monitor the page usage via perfmon and see the available pages in memory (say for example 1 Million) and the free pages the server has (for example 5k pages) I wonder which processes/transactions are eating up all these pages.If I check the sysprocesses view the "memusage" column gives me a number that MSDN says is the number of pages the process has (not sure if this is the actual value it is using at the moment I see it or if it is the total of pages it has been using since it started) but the SUM of all of these pages is very very smal (for example 2k out of the million) so I wonder who or what else has these pages?I tried to see the data DBCC MEMORYSTATUS but I don't seem to find nothing useful, maybe I'm looking wrong, but the memory usage is always on the commited or database(clean) section, the procedure cache has just a small chunk of it, but I don't seem to find actually which processes are eating the memory here, maybe I'm just looking wrong.Can you guys help me?Thanks in advanceFri, 02 Dec 2011 07:17:51 GMTdsbolanos