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


Find the memory consumetion by each database


Find the memory consumetion by each database

Author
Message
Subhash Chander
Subhash Chander
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 224
Comments posted to this topic are about the item Find the memory consumetion by each database
francesco.mantovani
francesco.mantovani
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 162
It was so good I had to script it in PowerShell:

#Provide SQLServerName
$SQLServer ="localhost"
#Provide Database Name
$DatabaseName ="master"
#Prompt for user credentials
$credential = Get-Credential

$Query = "
-- Memory used by each database
SELECT
DB_NAME(database_id) AS DB_Name,
COUNT (1) * 8 / 1024 AS MB_Used
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY COUNT (*) * 8 / 1024 DESC
GO
"

# Start the process here
do {

invoke-sqlcmd -ServerInstance $SQLServer -Database $DatabaseName -Credential $credential -Query $Query | Format-Table

Sleep 1; Clear

}

while ($true)

Subhash Chander
Subhash Chander
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 224
Its brilliant idea SSC Veteran.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)

Group: General Forum Members
Points: 897341 Visits: 48321
I respect anyone that will take the time to publish knowledge and, no... this isn't a slam. Just some helpful hints for your next post.

I think your script would have gotten better marks if you remembered the special but simple handling for the ResourceDB (which is what MS called it in one of their sample scripts) and if you had done your ORDER BY based on the column alias instead of repeating the formula. Also, you can easily convert page counts to megabytes simply by dividing by 128. Last but not least, you might want to do a spell check before you submit your post.

For example:

SELECT [DB_Name] = CASE WHEN database_ID = 32767 THEN 'ResourceDB' ELSE DB_NAME(database_id) END
,MB_Used = COUNT(*)/128.0
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY MB_Used DESC
;


--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)

Group: General Forum Members
Points: 897341 Visits: 48321
p.s. I'm not sure why someone would write a PoSh script for this when it's so easy to do in T-SQL and requires no special handling.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
francesco.mantovani
francesco.mantovani
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 162
Hello @Jeff, I was thinking this was a good idea for monitoring SQL Server on real time but maybe I'm wrong.
Do you think my PowerShell script might be to heavy because it keeps running every second in an infinite loop?
Thank you
Subhash Chander
Subhash Chander
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 224
Thanks for your inputs...
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)

Group: General Forum Members
Points: 897341 Visits: 48321
francesco.mantovani - Sunday, July 22, 2018 9:49 PM
Hello @Jeff, I was thinking this was a good idea for monitoring SQL Server on real time but maybe I'm wrong.
Do you think my PowerShell script might be to heavy because it keeps running every second in an infinite loop?
Thank you

First and again, just to be sure because it can be a sensitive subject.... in no way am I slamming you for the code.

To answer your question, since it does run once a second, it does seem a bit heavy handed. Of course, that can be easily changed. Also, running once per second may be totally necessary when trying to troubleshoot a problem or when trying to establish some sort of pattern.

I'm just not sure why so many people resort to PoSh for such things when it's actually simpler to do such a thing from T-SQL. Also and in my case, having such an output go to the screen would be a bit overwhelming to review simply because of the number of databases present on my server. It would be much better if it were logged to a semi-permanent scratch table with a sample date/time and contain the code in a DBA utility stored procedure where you could tell it not only what the sample rate should be but also how long it should sample with a decent set of defaults. And, if necessary, the stored procedure could easily be scheduled as a one -time or regularly scheduled job to assist with late night troubleshooting.

Yes, most of that can certainly be done in PoSh but why?

To make a play on words similar to what I'm frequently told, "Just because you can do something in T-SQL, doesn't mean you SHOULDN'T". BigGrin


--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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