public/Get-SSASTabularMemoryUsage.ps1

Function Get-SSASTabularMemoryUsage {

<#
    .SYNOPSIS
    Gets memory usage from a tabular analysis services instance per database.
     
    .DESCRIPTION
    Gets memory usage from a tabular analysis services instance per database. Requires administrative permissions on the Analysis Services instance.
 
    Returns a PSObject per database with database name, size and memory usage. The size is the size of the database itself eg. data, dictionaries, hierarchies etc.
    Memory usage includes all memory consumed by the database ie. the database size + memory consumed by any processing and querying activies.
 
    This tool is mainly intended for DBAs hosting multiple SSAS databases on a single server. For detailed analysis on a database level, it is recommended to look into the VertiPaq Analyzer: https://www.sqlbi.com/tools/vertipaq-analyzer/
 
    .EXAMPLE
    Get-SSASTabularMemoryUsage -ASServerInstance '.\SSAS2016TAB'
 
    Get-SSASTabularMemoryUsage -ASServerInstance '.\SSAS2016TAB' | Sort-Object -Property MemoryUsage -Descending | Format-Table Database, Size, @{ Name = 'SizeMB'; Expression = {“{0:N1}” -f ($_.Size / 1MB)}; align='right'}, @{ Name = 'SizeGB'; Expression = {“{0:N1}” -f ($_.Size / 1GB)}; align='right'} , MemoryUsage, @{ Name = 'MemoryUsageMB'; Expression = {“{0:N1}” -f ($_.MemoryUsage / 1MB)}; align='right'}, @{ Name = 'MemoryUsageGB'; Expression = {“{0:N1}” -f ($_.MemoryUsage / 1GB)}; align='right'}
     
    .PARAMETER ASServerInstance
    The Analysis Services instance to get memory usage from.
 
    .PARAMETER ASDatabase
    Restrict data collection to one or more Analysis Services databases. This is faster, than adding a filter to the result set of this function.
 
    .INPUTS
    TO DO
     
    .OUTPUTS
    a PSObject for each AS tabular database in the server.
     
    .LINK
    https://github.com/DennisWagner/SQLServerDevOpsTools
     
    .NOTES
    Written by (c) Dennis Wagner Kristensen, 2022 https://github.com/DennisWagner/SQLServerDevOpsTools
    This PowerShell script is released under the MIT license http://www.opensource.org/licenses/MIT
#>

    [CmdletBinding()]
    Param (
                [Parameter(Mandatory=$true)]$ASServerInstance,
                [Parameter(Mandatory=$false)]
                [string[]]
                $ASDatabase
    )
    BEGIN {
        $QUERY_MEMORY_USAGE = @'
SELECT OBJECT_ID AS DatabaseName, OBJECT_MEMORY_NONSHRINKABLE + OBJECT_MEMORY_CHILD_NONSHRINKABLE AS MemoryUsage
FROM $SYSTEM.DISCOVER_OBJECT_MEMORY_USAGE
WHERE RIGHT(OBJECT_PARENT_PATH, 10) = '.Databases'
'@


        $QUERY_DICTIONARY_SIZE = @'
SELECT DICTIONARY_SIZE AS SIZE
FROM $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMNS
WHERE COLUMN_TYPE = 'BASIC_DATA'
'@


        $QUERY_DATA_HIERARCHY_SIZE = @'
SELECT USED_SIZE AS SIZE
FROM $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS
WHERE RIGHT(LEFT(TABLE_ID, 2), 1) <> '$' -- Data
   OR LEFT(TABLE_ID, 2) = 'H$' -- Column hierarchies
   OR LEFT(TABLE_ID, 2) = 'U$' -- User hierarchies
'@

    }

    PROCESS {       
        $memory_usage = Invoke-ASQuery -Query $QUERY_MEMORY_USAGE -ASServerInstance $ASServerInstance -ASDatabase '' # do not specify a database, to get a result containing all databases
        $memory_usage_grouped = $memory_usage | Group-Object -Property DatabaseName 

        If ($ASDatabase) {
            $memory_usage_grouped = $memory_usage_grouped | Where-Object { $ASDatabase -contains $_.Name}
        }

        ForEach ($item in $memory_usage_grouped  ) {

            $DictionarySize = Invoke-ASQuery -Query $QUERY_DICTIONARY_SIZE -ASServerInstance $ASServerInstance -ASDatabase $item.Name
            $DataHierarchySize = Invoke-ASQuery -Query $QUERY_DATA_HIERARCHY_SIZE -ASServerInstance $ASServerInstance -ASDatabase $item.Name
            $Size = ($DictionarySize + $DataHierarchySize | Measure-Object -Property SIZE -Sum).Sum
            
            New-Object PSObject -Property @{
                Database       = $item.Name
                MemoryUsage    = ($item.Group | Measure-Object -Property MemoryUsage -Sum).Sum 
                Size = $Size        
            }
        }
    }
    END {

    }
}