SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to get how many memory is used for each T-SQL ?


How to get how many memory is used for each T-SQL ?

Author
Message
892717952
892717952
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 9
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?
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5424 Visits: 875
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?

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
892717952
892717952
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 9
Dear Erland Sommarskog,

what is the measure unit for the each database ?thank you!
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5424 Visits: 875
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.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
892717952
892717952
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 9
Dear Erland Sommarskog

thank you for your kind help!
thanks so much!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search