Here are two more PowerShell scripts I’ve been using for a while now to collect SSAS-related performance data. Each script is structured as a PowerShell function w/ a single call at the bottom of the script so that they can be executed ad-hoc. One cool thing about these scripts is that they’re SSAS-mode agnostic. Basically the script connects to the instance, determines the mode (i.e. Tabular or Multidimensional) and then collects counters/events specific to the SSAS-mode. For example, there’s not a lot of value (in most cases to collect disk/IO counters for SSAS-Tabular instances).
********** Standard “random script from the internet” warning obviously applies **********
Performance Monitor (perfmon)
This was an adaptation from code originally posted here by Aaron Bertrand (b | t) way back in 2011.
Ultimately you’ll want to wrap this snippet in some more robust code that allows you to dynamically control various aspects of a perfmon collection. For example, you may want to run this in the background from a collection server that starts traces on multiple target servers with varying collection frequencies (i.e. collect counter data every 15s vs 5s depending on the instance). You’ll also still need to write some code to ingest the output trace files into a database for analysis.
param( [string]$ssasServer, [string]$ssasInstanceName, [int]$sample_dur_minutes, [int]$sample_freq, [string]$trace_collection_path, [bool]$ssasNamedInstance_flag ) Function Invoke-AS-PerfmonTrace { param( [Parameter(Position=0,mandatory=$true)][string] $ssasServer, [Parameter(Position=1,mandatory=$true)][string] $ssasInstanceName, [Parameter(Position=2,mandatory=$true)][int] $sample_dur_minutes, [Parameter(Position=3,mandatory=$true)][int] $sample_freq, [Parameter(Position=4,mandatory=$true)][string] $trace_collection_path, [Parameter(Position=5,mandatory=$true)][bool] $ssasNamedInstance_flag ) ########################################################### ## calculate number of samples ($sample_count) based on the ## frequency ($sample_freq) for the specified collection ## duration ($sample_dur_minutes) ########################################################### $sample_count = ($sample_dur_minutes*60)/$sample_freq ########################################################### ## Determine server mode (multidimensional vs tabular) ########################################################### $loadAssembly = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") $svr = New-Object Microsoft.AnalysisServices.Server $svr.Connect($ssasInstanceName) $ssasServerMode = $svr.ServerMode $ssasVersion = $svr.Version ########################################################### ## Calculate the perfmon counter base which depends on ## whether the instance is named or not ########################################################### if ($ssasNamedInstance_Flag) { $ssas_PrfCtr_base = "\MSOLAP`$$($ssasInstanceName.Split("\")[1])" } else { $ssas_PrfCtr_base = "MSAS$($ssasVersion.substring(0,2))" } ########################################################### ## ServerMode: Multidimensional ########################################################### if ($ssasServerMode -eq "Multidimensional") { $counters = @(` #Disk "\PhysicalDisk(*)\Avg. Disk sec/Transfer" ,"\LogicalDisk(*)\Avg. Disk sec/Transfer" #Memory ,"\Memory\Pages/sec" ,"\Memory\Page Faults/sec" ,"\Memory\Available MBytes" #Network Interface #,"\Network Interface\Bytes Total/sec" #,"\Network Interface\Current Bandwidth" #Processor ,"\Processor(*)\% Processor Time" #Process ,"\Process(msmdsrv)\% Processor Time" ,"\Process(msmdsrv)\Working Set" ,"\Process(msmdsrv)\Virtual Bytes" ,"\Process(msmdsrv)\Private Bytes" #System ,"\System\Processor Queue Length" #Cache ,"$ssas_PrfCtr_base`:Cache\Current entries" ,"$ssas_PrfCtr_base`:Cache\Current KB" ,"$ssas_PrfCtr_base`:Cache\Lookups/sec" ,"$ssas_PrfCtr_base`:Cache\Misses/sec" ,"$ssas_PrfCtr_base`:Cache\Direct hits/sec" ,"$ssas_PrfCtr_base`:Cache\Evictions/sec" #Connections ,"$ssas_PrfCtr_base`:Connection\Current connections" ,"$ssas_PrfCtr_base`:Connection\Current user sessions" ,"$ssas_PrfCtr_base`:Connection\Requests/sec" #Locks ,"$ssas_PrfCtr_base`:Locks\Lock waits/sec" ,"$ssas_PrfCtr_base`:Locks\Total deadlocks detected" #MDX ,"$ssas_PrfCtr_base`:MDX\Number of cell-by-cell evaluation nodes" ,"$ssas_PrfCtr_base`:MDX\Number of cell-by-cell hits in the cache of evaluation nodes" ,"$ssas_PrfCtr_base`:MDX\Number of bulk-mode evaluation nodes" ,"$ssas_PrfCtr_base`:MDX\Number of cached bulk-mode evaluation nodes" ,"$ssas_PrfCtr_base`:MDX\Total cells calculated" #Memory ,"$ssas_PrfCtr_base`:Memory\Memory Limit Hard KB" ,"$ssas_PrfCtr_base`:Memory\Memory Limit High KB" ,"$ssas_PrfCtr_base`:Memory\Memory Limit Low KB" ,"$ssas_PrfCtr_base`:Memory\Memory Usage KB" #Proc Aggs ,"$ssas_PrfCtr_base`:Proc Aggregations\Current partitions" ,"$ssas_PrfCtr_base`:Proc Aggregations\Total partitions" ,"$ssas_PrfCtr_base`:Proc Aggregations\Memory size bytes" ,"$ssas_PrfCtr_base`:Proc Aggregations\Temp file bytes written/sec" #Proc Indexes ,"$ssas_PrfCtr_base`:Proc Indexes\Current partitions" ,"$ssas_PrfCtr_base`:Proc Indexes\Total partitions" ,"$ssas_PrfCtr_base`:Proc Indexes\Rows/sec" ,"$ssas_PrfCtr_base`:Proc Indexes\Total rows" #Processing ,"$ssas_PrfCtr_base`:Processing\Total rows read" ,"$ssas_PrfCtr_base`:Processing\Total rows written" ,"$ssas_PrfCtr_base`:Processing\Total rows converted" ,"$ssas_PrfCtr_base`:Processing\Rows read/sec" ,"$ssas_PrfCtr_base`:Processing\Rows written/sec" ,"$ssas_PrfCtr_base`:Processing\Rows converted/sec" #Threads ,"$ssas_PrfCtr_base`:Threads\Processing pool busy non-I/O threads" ,"$ssas_PrfCtr_base`:Threads\Processing pool idle non-I/O threads" ,"$ssas_PrfCtr_base`:Threads\Processing pool job queue length" ,"$ssas_PrfCtr_base`:Threads\Processing pool job rate" ,"$ssas_PrfCtr_base`:Threads\Processing pool busy I/O job threads" ,"$ssas_PrfCtr_base`:Threads\Processing pool idle I/O job threads" ,"$ssas_PrfCtr_base`:Threads\Processing pool I/O job queue length" ,"$ssas_PrfCtr_base`:Threads\Processing pool I/O job completion rate" ,"$ssas_PrfCtr_base`:Threads\Query pool busy threads" ,"$ssas_PrfCtr_base`:Threads\Query pool idle threads" ,"$ssas_PrfCtr_base`:Threads\Query pool job queue length" ,"$ssas_PrfCtr_base`:Threads\Query pool job rate" ) } ########################################################### ## ServerMode: Tabular ########################################################### if ($ssasServerMode -eq "Tabular") { $counters = @(` #Memory ,"\Memory\Pages/sec" ,"\Memory\Page Faults/sec" ,"\Memory\Available MBytes" #Network Interface #,"\Network Interface\Bytes Total/sec" #,"\Network Interface\Current Bandwidth" #Processor ,"\Processor(*)\% Processor Time" #Process ,"\Process(msmdsrv)\% Processor Time" ,"\Process(msmdsrv)\Working Set" ,"\Process(msmdsrv)\Virtual Bytes" ,"\Process(msmdsrv)\Private Bytes" #System ,"\System\Processor Queue Length" #Cache ,"$ssas_PrfCtr_base`:Cache\Current entries" ,"$ssas_PrfCtr_base`:Cache\Current KB" ,"$ssas_PrfCtr_base`:Cache\Lookups/sec" ,"$ssas_PrfCtr_base`:Cache\Misses/sec" ,"$ssas_PrfCtr_base`:Cache\Direct hits/sec" ,"$ssas_PrfCtr_base`:Cache\Evictions/sec" #Connections ,"$ssas_PrfCtr_base`:Connection\Current connections" ,"$ssas_PrfCtr_base`:Connection\Current user sessions" ,"$ssas_PrfCtr_base`:Connection\Requests/sec" #Locks ,"$ssas_PrfCtr_base`:Locks\Lock waits/sec" ,"$ssas_PrfCtr_base`:Locks\Total deadlocks detected" #MDX ,"$ssas_PrfCtr_base`:MDX\Number of cell-by-cell evaluation nodes" ,"$ssas_PrfCtr_base`:MDX\Number of cell-by-cell hits in the cache of evaluation nodes" ,"$ssas_PrfCtr_base`:MDX\Number of bulk-mode evaluation nodes" ,"$ssas_PrfCtr_base`:MDX\Number of cached bulk-mode evaluation nodes" ,"$ssas_PrfCtr_base`:MDX\Total cells calculated" #Memory ,"$ssas_PrfCtr_base`:Memory\Memory Limit Hard KB" ,"$ssas_PrfCtr_base`:Memory\Memory Limit High KB" ,"$ssas_PrfCtr_base`:Memory\Memory Limit Low KB" ,"$ssas_PrfCtr_base`:Memory\Memory Limit VertiPaq KB" ,"$ssas_PrfCtr_base`:Memory\Memory Usage KB" ,"$ssas_PrfCtr_base`:Memory\VertiPaq Paged KB" ,"$ssas_PrfCtr_base`:Memory\VertiPaq Nonpaged KB" #Processing ,"$ssas_PrfCtr_base`:Processing\Total rows read" ,"$ssas_PrfCtr_base`:Processing\Rows read/sec" #Threads ,"$ssas_PrfCtr_base`:Threads\Processing pool busy non-I/O threads" ,"$ssas_PrfCtr_base`:Threads\Processing pool idle non-I/O threads" ,"$ssas_PrfCtr_base`:Threads\Processing pool job queue length" ,"$ssas_PrfCtr_base`:Threads\Processing pool job rate" ,"$ssas_PrfCtr_base`:Threads\Processing pool busy I/O job threads" ,"$ssas_PrfCtr_base`:Threads\Processing pool idle I/O job threads" ,"$ssas_PrfCtr_base`:Threads\Processing pool I/O job queue length" ,"$ssas_PrfCtr_base`:Threads\Processing pool I/O job completion rate" ,"$ssas_PrfCtr_base`:Threads\Query pool busy threads" ,"$ssas_PrfCtr_base`:Threads\Query pool idle threads" ,"$ssas_PrfCtr_base`:Threads\Query pool job queue length" ,"$ssas_PrfCtr_base`:Threads\Query pool job rate" ) } ########################################################### ## the following statements can be wrapped in a loop and ## driven by a configuration table ########################################################### # if ($ssasServer.Split(".")[0] -eq $env:COMPUTERNAME -or $ssasServer -eq "localhost" ) { $metrics = Get-Counter -Counter $counters -SampleInterval $sample_freq -MaxSamples $sample_count } else { $metrics = Get-Counter -ComputerName $ssasServer -Counter $counters -SampleInterval $sample_freq -MaxSamples $sample_count } $datetime_start = (get-date) $date_str = $datetime_start.ToString("yyyyMMdd") $time_str = $datetime_start.ToString("HHmmss") $trace_id = "InstID_Perfmon_$date_str$time_str" $sequence = 1; foreach($metric in $metrics) { $obj = $metric.CounterSamples | Select-Object -Property Path, CookedValue, Timestamp; # add these columns as data $obj | Add-Member -MemberType NoteProperty -Name ServerName -Value $ssasServer.Split(".")[0] -Force; # export with unique file name $export_filepath = "$trace_collection_path$trace_id.Seq$($sequence.ToString("000000")).csv" $obj | Export-Csv -Path $export_filepath -NoTypeInformation; $sequence += 1; } ########################################################### } $ssasServer = "localhost" $ssasInstanceName = "localhost\SSAS_MD" $sample_dur_minutes = 1 $sample_freq = 15 $trace_collection_path = "C:\temp\" $ssasNamedInstance_flag = 1 Invoke-AS-PerfmonTrace ` -ssasServer $ssasServer ` -ssasInstanceName $ssasInstanceName ` -sample_dur_minutes $sample_dur_minutes ` -sample_freq $sample_freq ` -trace_collection_path $trace_collection_path ` -ssasNamedInstance_Flag $ssasNamedInstance_flag
Extended Events (xEvents)
As is the case w/ the previous script, you’ll most likely want to wrap this stubbed out version into a more complete solution that allows you to have more granular control over the collection variables. But it is good for the 1-off traces until you have time to roll a more complete solution.
param( [string]$ssasServer, [string]$ssasInstanceName, [int]$sample_dur_minutes, [string]$trace_collection_path ) Function Invoke-AS-xEventTrace { param( [Parameter(Position=0,mandatory=$true)][string] $ssasServer, [Parameter(Position=1,mandatory=$true)][string] $ssasInstanceName, [Parameter(Position=2,mandatory=$true)][int] $sample_dur_minutes, [Parameter(Position=3,mandatory=$true)][string] $trace_collection_path ) ########################################################### ## Determine server mode (multidimensional vs tabular) ########################################################### $loadAssembly = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") $svr = New-Object Microsoft.AnalysisServices.Server $svr.Connect($ssasInstanceName) $ssasServerMode = $svr.ServerMode $ssasLogPath = $svr.ServerProperties.Item("LogDir").Value ########################################################### ## Initialize Loop Commands ########################################################### $datetime_start = (get-date) $date_str = $datetime_start.ToString("yyyyMMdd") $time_str = $datetime_start.ToString("HHmmss") $trace_id = "InstID_xEvent_$date_str$time_str" $trace_name = "xEvent_$date_str$time_str" ########################################################### ## ServerMode: Multidimensional ########################################################### if ($ssasServerMode -eq "Multidimensional") { $xEventTraceStart = ' <Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300"> <ObjectDefinition> <Trace> <ID>'+$trace_id+'</ID> <Name>'+$trace_name+'</Name> <ddl300_300:XEvent> <event_session name="xeas" dispatchLatency="1" maxEventSize="4" maxMemory="4" memoryPartitionMode="none" eventRetentionMode="allowSingleEventLoss" trackCausality="true"> <!-- ### ERRORS AND WARNING ### --> <event package="AS" name="Error" /> <!-- ### PROGRESS REPORTS ### --> <event package="AS" name="ProgressReportEnd" /> <event package="AS" name="ProgressReportError" /> <!-- ### QUERY EVENTS ### --> <event package="AS" name="QueryEnd" /> <!-- ### QUERY PROCESSING ### --> <event package="AS" name="QuerySubcube" /> <!-- ### TARGET ### --> <target package="Package0" name="event_file"> <parameter name="filename" value="'+$trace_id+'.xel" /> <parameter name="max_file_size" value="32" /> </target> </event_session> </ddl300_300:XEvent> </Trace> </ObjectDefinition> </Create> ' } ########################################################### ## ServerMode: Tabular ########################################################### if ($ssasServerMode -eq "Tabular") { $xEventTraceStart = ' <Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300"> <ObjectDefinition> <Trace> <ID>'+$trace_id+'</ID> <Name>'+$trace_name+'</Name> <ddl300_300:XEvent> <event_session name="xeas" dispatchLatency="1" maxEventSize="4" maxMemory="4" memoryPartitionMode="none" eventRetentionMode="allowSingleEventLoss" trackCausality="true"> <!-- ### ERRORS AND WARNING ### --> <event package="AS" name="Error" /> <!-- ### PROGRESS REPORTS ### --> <event package="AS" name="ProgressReportEnd" /> <event package="AS" name="ProgressReportError" /> <!-- ### QUERY EVENTS ### --> <event package="AS" name="QueryEnd" /> <!-- ### QUERY PROCESSING ### --> <event package="AS" name="VertiPaqSEQueryCacheMatch" /> <event package="AS" name="VertiPaqSEQueryEnd" /> <!--<event package="AS" name="DaxQueryPlan" /> --> <!-- ### TARGET ### --> <target package="Package0" name="event_file"> <parameter name="filename" value="'+$trace_id+'.xel" /> <parameter name="max_file_size" value="32" /> </target> </event_session> </ddl300_300:XEvent> </Trace> </ObjectDefinition> </Create> ' } ########################################################### ## Stop xEvent Trace - XMLA Command ########################################################### $xEventTraceStop = ' <Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <TraceID>'+$trace_id+'</TraceID> </Object> </Delete> ' ########################################################### ## the following statements can be wrapped in a loop and ## driven by a configuration table ########################################################### # Start xEvent Trace Invoke-ASCmd -Query $xEventTraceStart -Server $ssasInstanceName # Sleep duration Start-Sleep -s ($sample_dur_minutes * 60) # Stop xEvent Trace Invoke-ASCmd -Query $xEventTraceStop -Server $ssasInstanceName # Move File to Trace Collection Path $log_fpath_unc = "\\$ssasServer\$($ssasLogPath.Replace(":","$"))\$trace_id" Move-Item -Path $log_fpath_unc* -Destination $trace_collection_path ########################################################### } $ssasServer = "localhost" $ssasInstanceName = "localhost\SSAS_MD" $sample_dur_minutes = 1 $trace_collection_path = "C:\temp\" Invoke-AS-xEventTrace ` -ssasServer $ssasServer ` -ssasInstanceName $ssasInstanceName ` -sample_dur_minutes $sample_dur_minutes ` -trace_collection_path $trace_collection_path
Want to Learn More?
You can catch me at any one of the following events where I’ll be presenting…
![image[27]](https://byobi.wordpress.com/wp-content/uploads/2021/05/e24e7-image27.png "image[27]")
- SQL Saturday 445 – Raleigh
October 10 - SQL Saturday 452 – Charlotte
October 17 - PASS Summit 2015 – Seattle
October 27-30
Not Enough? Interested in a more hands-on approach?
I got you covered. I’m now taking on short-term consulting gigs helping clients get setup with a performance monitoring solution. Whether off-the-shelf or roll-your-own, I’ll help you find the right fit for your needs, minimizing your costs while maximizing the value by showing you some pretty cool things you can do with all this new awesome historical performance data you’ll be collecting.
byoBI.com 
5 replies on “PowerShell Scripts for Collecting SSAS-related Perfmon and xEvent Trace Data”
[…] 20151008: here’s an example where this snippet is being used in a more realistic […]
LikeLike
[…] of the on-going performance monitoring of an analysis services solution, you might want to script it out using PowerShell or […]
LikeLike
[…] Here are 2 links to help you get started: one, two […]
LikeLike
[…] if you’re interested in automating this process (i.e. collecting perfmon data) here’s a link to get you […]
LikeLike
[…] written about extended events for Analysis Services quite a bit (here, here, here, here, here) because I think they’re super helpful and very important in managing/monitoring an […]
LikeLike