How to get the memory consumption of a SQL Server database

Introduction

This PowerShell script is used to get SQL Server buffer pool usage of a specified database. The script will provide a stored procedure with an available server name and a database name. If both are correctly provided, the script will output the related consumption of the database.

Scenarios

As some people want to know the memory consumed by database, the script will provide some help.

Script

You can use this script in this way:
1. Run Microsoft PowerShell as Administrator
2. Run the script in the form: Path or &”Path”
3. Press “Enter” and enter the correct server name and database name.
4. Press “Enter”, after the script finishes running, we’ll get the following figure:

 

Here are some code snippets for your reference.

 

PowerShell
Edit|Remove
Param 
( 
    [Parameter(Mandatory = $true, Position = 0)][String] $Servername, 
    [Parameter(Mandatory = $true, Position = 1)][String] $DBName 
 
) 
 
 
$conn = New-Object System.Data.SqlClient.SqlConnection "Server=$Servername;Database=master;Integrated Security=SSPI;";  
 
## Attach the InfoMessage Event Handler to the connection to print the messages  
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender$event) Write-Host $event.Message };  
$conn.add_InfoMessage($handler);  
$conn.FireInfoMessageEventOnUserErrors = $true; 
 
$conn.Open(); 
 
$SQLCommand = $conn.CreateCommand() 
$SQLCommand.CommandText = " 
    IF @dbname NOT IN ( 
        SELECT 
        (CASE WHEN ([database_id] = 32767) THEN 'mssqlsystemresource' ELSE DB_NAME (database_id) END) AS DBName 
        FROM sys.dm_os_buffer_descriptors 
        ) 
        BEGIN 
            PRINT 'The database you entered is not available' 
        END 
 
    ELSE 
 
       BEGIN 
     
        ;WITH CTE_DBConsumption 
        AS 
        ( 
        SELECT 
        (CASE WHEN ([database_id] = 32767) THEN 'mssqlsystemresource' ELSE DB_NAME (database_id) END) AS DBName, 
         CAST(COUNT(row_count)*8/1024.0 AS decimal(10,2)) [size] 
        FROM sys.dm_os_buffer_descriptors 
        GROUP BY database_id 
        ) 
        SELECT DBName AS [Database Name],size AS [Size(MB)] 
        FROM CTE_DBConsumption 
        WHERE DBName = ISNULL(@dbname,DBName) 
        ORDER BY DBName 
      END 
     
 
" 
$SQLCommand.Parameters.AddWithValue("@dbname",  $DBName| Out-Null 
 

Prerequisites

SQL Server 2005 or higher version

Prerequisites

SQL Server 2005 or higher version