Here is a query that gives the consumption in the buffer cache per database. I hope it is correct, but I welcome if Gail and others can review it.
select db_name(database_id), convert(int, count_big(*)*8192 / 1E6)
group by db_name(database_id)
order by db_name(database_id)
The first question is more difficult to answer, because it is not very well defined. If there is a single query that scans and aggregates a 20 GB fact table, then that query takes up 20 GB of memory. But if there are umpteen such queries, is still correct to say that this query takes up 20 GB of memory? Maybe we should only count memory consumed by the local execution for hash tables and sorting? But what if there are 17 parallel executions of the same query? Does the number 17-fold?
Erland Sommarskog, SQL Server MVP, www.sommarskog.se