Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to get how many memory is used for each T-SQL ? Expand / Collapse
Author
Message
Posted Saturday, August 31, 2013 9:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, September 14, 2013 8:33 AM
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?
Post #1490397
Posted Sunday, September 1, 2013 4:58 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
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
Post #1490449
Posted Sunday, September 1, 2013 7:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, September 14, 2013 8:33 AM
Points: 4, Visits: 9

Dear Erland Sommarskog,

what is the measure unit for the each database ?thank you!
Post #1490455
Posted Sunday, September 1, 2013 9:24 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
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
Post #1490463
Posted Monday, September 2, 2013 2:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, September 14, 2013 8:33 AM
Points: 4, Visits: 9
Dear Erland Sommarskog

thank you for your kind help!
thanks so much!
Post #1490548
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse