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 { } } |