August 31, 2013 at 9:40 am
Dear All,
Please kindly help below, Many thanks!
1. how do you know that how many memory is used for each T-SQL ?
2. how do you know the memory is used by each database for the SQL Server instance?
September 1, 2013 at 4:58 am
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)
from sys.dm_os_buffer_descriptors
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?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 1, 2013 at 7:08 am
Dear Erland Sommarskog,
what is the measure unit for the each database ?thank you!
September 1, 2013 at 9:24 am
The query returns the data in true megabytes. That is, 1MB = one million bytes.
More to the point, the query counts pages in the buffer cache, and the size of a page is 8192 bytes.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 2, 2013 at 2:48 am
Dear Erland Sommarskog
thank you for your kind help!
thanks so much!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply