Find busiest Databases

Question

Find busiest Databases

Grace09

SSCommitted

Points: 1532
More actions
June 23, 2014 at 7:38 am

#292668

My sql server 2008 R2 is experiencing memory pressure, use the following query to find out the busiest databases.
SELECT SUM(deqs.total_logical_reads) TotalPageReads,
SUM(deqs.total_logical_writes) TotalPageWrites,
CASE
WHEN DB_NAME(dest.dbid) IS NULL THEN 'AdhocSQL'
ELSE DB_NAME(dest.dbid) END Databasename
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
GROUP BY DB_NAME(dest.dbid)
TotalPageReadsTotalPageWritesDatabasename
145503AdhocSQL
48 0db4
16 0msdb
173 0db3
3785 3db2
106330db1
0 0db5
It looks like there are lots of Adhoc queries. How can I tell which adhoc queries use most memory? Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The majority of the memory usage on an instance is not going to be by the queries, it's going to be the buffer pool, the data cache should be the largest portion probably followed by the plan cache.

Queries only take memory grants for things like hash tables or sorts, those should not be large in the overall scheme of things.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grace09 SSCommitted Points: 1532 More actions · Answer 2

When I ran DBCC memorystatus, MEMORYCLERK_SQLOPTIMIZER consumes about 35.5 GB of memory out of 64 GB total memory. Does it mean I have too many adhoc queries?

rmunwin SSC Journeyman Points: 76 More actions · Answer 3

Crazy idea, does anyone know what version the memory leak on stats was in? If I recall there is some sort of memory leak with auto update stats async on. I can't recall the version or where the memory shows in dbcc memorystatus.

Robert Klimes SSCoach Points: 17865 More actions · Answer 4

This query will let you know which memory objects are taking all the space. It may help in trouble shooting the issue

SELECT a.type,(a.pages_allocated_count)*8 as mem_used_KB

FROM sys.dm_os_memory_objects a

inner join sys.dm_os_memory_clerks b

on a.page_allocator_address = b.page_allocator_address

where b.type = 'MEMORYCLERK_SQLOPTIMIZER'

order by mem_used_KB desc

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

Grace09 SSCommitted Points: 1532 More actions · Answer 5

It is MEMOBJ_INDEXSTATSMGR that consumes the most memory. I don't find much information about it. Can you help direct me to find the related resource? Thanks

liying.1208 SSC Rookie Points: 37 More actions · Answer 6

I had a similar problem recently. The memobj_indexstatsmgr of memoryclerk_sqloptimizer consumed most of the memory and caused sql server to emit message "There is insufficient system memory in resource pool 'internal' to run this query" in the error log. We could see it gradually creeping up using perfmon. It was 2008 R2 64 bit that we used.

But after we applied the service pack 2, it no longer crept. Refer to this bug list http://support.microsoft.com/kb/2528583 , fix of id 657405 seems relative, but I'm not sure.