Analysis Services

Overview

Most SQL Server shops that run Integration Services and Reporting Services also run SQL Server Analysis Services (SSAS). Processing failures, memory limit breaches and long running DAX or MDX queries do not show up in anything SQL Server reports. The Analysis Services report connects to the SSAS instance and shows:

  • the mode (Tabular or Multidimensional), version and edition,
  • memory used against the Low, Total and Hard limits, with a status: fine below the Low limit, a warning above it, a problem above the Total limit,
  • the sessions and the commands running now, longest first, with the XMLA to cancel one,
  • memory by object as a treemap,
  • processing freshness: when each table (Tabular) or cube (Multidimensional) was last processed, with stale and failed ones flagged,
  • the databases: model type, compatibility level, memory, tables, partitions, roles, and when each was last processed, changed and queried,
  • the server properties that differ from their defaults, and the ones waiting for a restart.

Every call the report makes is a read. It never processes, cancels or changes anything on the SSAS instance.

Where to find it

Route How
Server tree Right-click the server → Instance Level Reports → Analysis Services
Instance reports navigator Configuration group
Related Links bar From Host and Hardware
Report arrows Next is Availability Groups

Which SSAS instance is read

By default the report looks for SSAS on the same host with the same instance name as the SQL Server, which is what setup gives them when they are installed together: a SQL Server named HOST\SALES is paired with SSAS at HOST\SALES, and a default instance with the default SSAS instance on HOST.

When SSAS runs somewhere else, click Set SSAS Server on the toolbar and enter its name (SERVER, SERVER\INSTANCE or SERVER:port). The name is saved for this SQL Server. Enter AUTO to go back to looking beside the SQL Server.

If no SSAS instance answers, the page says which name it tried and why it failed. A SQL Server with no Analysis Services beside it needs nothing here, and nothing else in Database Health Monitor changes.

The toolbar

Control What it does
Refresh Reads everything again
Sessions and Commands / Memory by Object / Processing Freshness / Databases / Server Configuration Which view is shown. Switching does not read again
Stale after 1 day / 7 days / 30 days On Processing Freshness and Databases: how old a last processed time can be before it is flagged
Non-default only / All properties On Server Configuration
Set SSAS Server Names the SSAS instance for this SQL Server
Cancel Stops a read in progress

Reading the page

The overview above every view except Memory by Object names the SSAS instance, its mode, version and edition, and counts the databases, their memory, the other sessions and the running commands. The memory bar is filled to the memory SSAS is using, green below the Low limit, amber above it and red above the Total limit, with the Low, Total and Hard limits marked.

The memory used and the limits come from the SSAS performance counters on its host (for example MSOLAP$INSTANCE:Memory). When those cannot be read (another host without access to its counters), the limits come from the server properties instead: a value of 100 or less is a percentage of the host’s physical memory, which is taken from the SQL Server when SSAS is on the same host.

Sessions and Commands lists every session, running commands first and longest first. A command running for more than a minute is marked in red. Right-click a row to copy the command text or the XMLA Cancel script for that session, to run in SQL Server Management Studio if you decide to. The session this report opened is marked This report.

Memory by Object shows each database, each engine (Global) object and each server object as a tile sized by the memory it holds, from DISCOVER_OBJECT_MEMORY_USAGE. Click a tile to find it in the grid, which lists every object with its own memory and its children’s.

Processing Freshness lists every Tabular partition (or every Multidimensional cube) with its last processed time on the SQL Server’s clock, its age and its state. Failed (a semantic, evaluation or dependency error, or an error message), Never (never processed) and Stale (older than the threshold) sort to the top. DirectQuery partitions hold no data and are never stale.

Databases has one row per database with the counts of stale and failed partitions.

Server Configuration lists the server properties that differ from their defaults, with a note on the memory and timeout properties people most often change. A property changed but waiting for a restart is marked in its Pending column.

Every grid exports to CSV and Excel like every other grid.

Permissions and setup

The report connects to SSAS with ADOMD.NET as your Windows user. Most of the DMVs it reads need Analysis Services server administrator; a read that is refused is listed on the page and the rest of the report still shows. The performance counters on a remote host need your account in that host’s Performance Monitor Users group.

From the SQL Server it reads SERVERPROPERTY('MachineName'), SERVERPROPERTY('InstanceName') and the physical memory from sys.dm_os_sys_info (which needs VIEW SERVER STATE; without it only the percentage limits cannot be resolved).

Azure SQL and SQL Server on Linux have no Analysis Services beside them; use Set SSAS Server to name one to read.