sql2018 ssas_SQL Server Analysis Services（SSAS）监视简介

sql2018 ssas

When it comes to monitoring of SQL Server Analysis Services (SSAS) performance, as it relates to the database engine, there are several categories that should be mentioned and compared.

当涉及到与数据库引擎有关SQL Server Analysis Services（SSAS）性能的监视时，应提及和比较几个类别。

	Database Engine	Analysis Services
Applied data model	Relational data model (slower performance)	Multi-purpose semantic data model (faster performance)
High scalability and availability	Potential issues and bottlenecks can arise if internal cache or system memory is overwhelmed	Both Multidimensional and Tabular models have high availability and scalability.
Performance of queries	If multiple queries run in a sequence, it will significantly slow down their performance	Faster, because of multi-threaded architecture of SSAS engine
Query optimization	SQL, as a language, has simpler syntax than MDX and DAX, but it lacks optimization for multiple query processing (slower performance)	MDX and DAX have more complicated syntax, but the query code can be efficiently optimized with specific built-in functions, which shorten the code and provides more efficiency (faster performance)
Caching	Mostly internal, with direct utilization of physical disk IO if the internal cache is overwhelmed	Combining internal cache with Windows file system cache, in order to optimize processing

	数据库引擎	分析服务
应用数据模型	关系数据模型（性能较慢）	多功能语义数据模型（更快的性能）
高扩展性和可用性	如果内部缓存或系统内存不堪重负，可能会出现潜在的问题和瓶颈	多维模型和表格模型均具有高可用性和可伸缩性。
查询性能	如果按顺序运行多个查询，将大大降低其性能	由于SSAS引擎的多线程体系结构，速度更快
查询优化	SQL作为一种语言，其语法比MDX和DAX更为简单，但缺乏针对多个查询处理的优化（性能较慢）	MDX和DAX具有更复杂的语法，但是可以使用特定的内置函数有效地优化查询代码，从而缩短了代码并提供了更高的效率（更快的性能）
快取	主要是内部的，如果内部缓存不堪重负，则直接利用物理磁盘IO	将内部缓存与Windows文件系统缓存相结合，以优化处理

Analysis Services is part of SQL Server, but it operates independently through its own service (msmdsrv.exe).

Analysis Services是SQL Server的一部分，但是它通过自己的服务（ msmdsrv.exe ）独立运行。

In terms of the SSAS itself, there are two basic semantic SSAS models that can be deployed and used as an analytical engine which corresponds with SQL Server:

就SSAS本身而言，可以部署两种基本的语义SSAS模型，并将其用作与SQL Server对应的分析引擎：

• Multidimensional 多维 model – it uses 模型 –它使用放置在多维多维数据集中的OLAP modeling constructs like dimensions, measure groups and other, placed within the Multidimensional cube. This model is widely used in SQL-Server-based OLAP建模结构，如尺寸，度量值组等。 此模型广泛用于基于SQL Server的large-scaled production servers. 大规模生产服务器 。
• Tabular 表格 model – this model uses relational modeling constructs such as tables and relationships for modeling data in the Tabular cube and using the in-memory analytics engine for storing and calculating data. 模型 –该模型使用诸如表和关系之类的关系建模结构来对表格立方体中的数据进行建模，并使用内存中分析引擎来存储和计算数据。

For a detailed comparison between the two mentioned models, see Comparing Tabular and Multidimensional Solutions (SSAS).

有关上述两个模型的详细比较，请参阅比较表格和多维解决方案（SSAS） 。

Working components and corresponding activities of the mentioned SSAS models are:

提到的SSAS模型的工作组件和相应的活动是：

• Formula Engine – it provides MDX (or DAX) querying mechanism (parsing and processing), data retrieval from Storage Engine (as a part of query processing), and performs necessary calculations to prepare raw cube data for further aggregation or additional calculation Formula Engine –它提供MDX（或DAX）查询机制（解析和处理），从Storage Engine检索数据（作为查询处理的一部分），并执行必要的计算以准备原始多维数据集数据以进一步聚合或进行其他计算
• Storage Engine – it manages cube data reader/writer activities, in order to reach specific data 存储引擎 –它管理多维数据集数据读取器/写入器的活动，以达到特定的数据granularity – in other words, to fulfill the specific purpose of analysis process. 粒度 –换句话说，以满足分析过程的特定目的。

The basic areas of SSAS performance monitoring are main engine construction, regarding actions (conducted by Formula Engine and Storage Engine), and performance counters measurements, which provide insights on how SSAS is performing.

SSAS性能监视的基本领域是主机构造 ，有关操作（由Formula Engine和Storage Engine进行）以及性能计数器测量 （可提供有关SSAS性能的见解）。

查询和多维数据集处理工作流程 (Querying and cube processing workflow)

The diagram below represents a visual interpretation of the SSAS service workflow (both Multidimensional and Tabular models), with the corresponding engine and elements of that engine, described further:

下图是对SSAS服务工作流程（多维模型和表格模型）的直观解释，并带有相应的引擎和该引擎的元素，进一步说明：

Executing particular MDX or DAX input (it depends on SSAS instance model) is a good starting point for SSAS performance monitoring. If the query is valid (there is no syntax errors, e.g.), it is accepted and iterates through the query parser and the actual query processor, which combined are usually called the Formula Engine (as previously mentioned). Functioning as an XMLA– based auditor, the query parser fetches requests and parameters from a query, retrieves raw data from the Storage Engine and includes it in process (to make response to query), and further, generate result assets. After that, the query parser passes that data to the query processor, which automatically creates the execution plan, caches the previous requests for further reuse.

执行特定的MDX或DAX输入（取决于SSAS实例模型）是SSAS性能监视的良好起点。 如果查询有效（例如，没有语法错误），则该查询将被接受并遍历查询解析器和实际查询处理器，它们通常被称为公式引擎 （如前所述）。 查询解析器充当基于XMLA的审核器，从查询中获取请求和参数，从存储引擎检索原始数据，并将其包括在处理中（以响应查询），并进一步生成结果资产。 之后，查询解析器将该数据传递给查询处理器，后者自动创建执行计划，并缓存先前的请求以供进一步重用。

At the moment the query is fully processed, the cube data already filtered by the query, or the subcube data request is sent to the Storage Engine, in order to retrieve and finish the processing (reading) and writing of actual cube data. The Storage Engine has multiple working threads, which include dimension and measure group data (including other related objects like dimensions attribute store, aggregations e.g.), because it reads/writes from the internal (SSAS-based) cache, and also partially uses Windows file system cache, in order to fully optimize performance of the cubes.

在查询被完全处理时，已经被查询过滤的多维数据集数据或子多维数据集数据请求被发送到存储引擎 ，以检索并完成实际多维数据集数据的处理（读取）和写入。 存储引擎具有多个工作线程，其中包括维度和度量值组数据 （包括其他相关对象，例如维度属性存储 ， 聚合等 ），因为它从内部（基于SSAS）缓存中进行读/写操作，并且部分使用Windows文件系统缓存，以充分优化多维数据集的性能。

As stated before, the mentioned workflow applies on both SSAS models, but there are some minor differences between them, due to their construction.

如前所述，上述工作流程适用于两种SSAS模型，但由于其构造，它们之间存在一些细微差异。

表格和多维模型的工作流程之间的差异 (Differences between Tabular and Multidimensional models’ workflow)

There are several differences between SSAS Tabular and Multidimensional models, which should be mentioned when it comes to their workflow.

SSAS表格模型和多维模型之间存在若干差异，在涉及其工作流程时应提及。

First, regarding the basic flow of querying process (Formula Engine), there are syntax differences between MDX and DAX query languages. An interesting fact is that the Tabular model can be queried with DAX and MDX, but the Multidimensional model can be queried only with MDX, which makes Tabular model more progressive.

首先，关于查询过程（公式引擎）的基本流程，MDX和DAX查询语言之间存在语法差异。 一个有趣的事实是，可以使用DAX和MDX来查询表格模型，但是只能使用MDX来查询多维模型，这使得表格模型更加先进。

Due to different approaches to the processing and caching, overall, managing the data, the SSAS Tabular model utilizes in-memory processing (regarding caching and keeping the integrity of processed data using xVelocity engine), which is faster, compared to plain cube processing and pre-defined aggregations in the Multidimensional model. The diagram below represents the workflow of SSAS Tabular model:

由于处理和缓存的总体方法不同，总体而言，SSAS表格模型利用内存中处理（关于缓存和使用xVelocity引擎保持已处理数据的完整性）， 与普通立方体处理和多维模型中的预定义聚合。 下图显示了SSAS表格模型的工作流程：

While the Tabular models keeps the cache in-memory, the Multidimensional model manages cache differently.

表格模型将高速缓存保留在内存中 ，而多维模型则以不同的方式管理高速缓存。

多维SSAS缓存 (Multidimensional SSAS Caching)

The caching system of Multidimensional SSAS is divided between the Formula and Storage Engine cache subsystems (as in diagram above).

多维SSAS的缓存系统在公式和存储引擎缓存子系统之间进行了划分（如上图所示）。

The Formula Engine cache subsystem stores flat data (unprocessed, actually), and data which is created after the calculation or multiple calculations. This data is getting parsed faster than other data involved in the process. Also, by default, it allocates nearly 10% of the TotalMemoryLimit property within the SSAS main engine.

Formula Engine缓存子系统存储平面数据（实际上是未处理的）以及在计算或多次计算之后创建的数据。 与处理中涉及的其他数据相比，此数据的解析速度更快。 同样，默认情况下，它在SSAS主引擎内分配TotalMemoryLimit属性的近10％。

The Dimension cache and a Measure Group cache are parts of the Storage Engine cache subsystem. The Performance of this cache subsystem and its maintenance is important in SSAS monitoring, because this engine cache has a more sophisticated and complex workload than the Formula Engine, and, as mentioned before, it also corresponds with the Windows file system cache in cube processing.

维度缓存和度量值组缓存是Storage Engine缓存子系统的一部分 。 此缓存子系统的性能及其维护在SSAS监视中很重要，因为此引擎缓存比Formula Engine具有更复杂的工作负载，并且如前所述，它还与多维数据集处理中的Windows文件系统缓存相对应。

These cache subsystems are important because cached data plays a more important role in preserving data integrity during query (Formula Engine) and cube (Storage Engine) processing. Both of these engines retrieve data from dedicated cache subsystems, and if it is necessary, they additionally retrieve the present Windows file system cache data.

这些高速缓存子系统非常重要，因为在查询（Formula Engine）和多维数据集（Storage Engine）处理期间，高速缓存的数据在保持数据完整性方面扮演着更重要的角色。 这两个引擎都从专用缓存子系统检索数据，并且如果有必要，它们还可以检索当前的Windows文件系统缓存数据。

When processing the same Multidimensional cube with a different set of parameters is needed, the whole procedure can be easily repeated as updated, because of previously cached data in Formula and Storage Engine.

当需要使用一组不同的参数处理同一多维多维数据集时，由于先前在“公式”和“存储引擎”中缓存了数据，因此整个过程可以轻松地在更新时重复进行。

重要的SSAS性能计数器 (Important SSAS performance counters)

Analysis Services performance counters mentioned here are most important to track and analyze issues. In almost all critical areas, there is a combination of system and SSAS counters, which leveraged and compared, can help isolate potential issues

此处提到的Analysis Services 性能计数器对于跟踪和分析问题最重要。 在几乎所有关键领域中，系统计数器和SSAS计数器结合在一起，可以进行比较和利用，从而有助于隔离潜在问题

Critical area	Full performance counter name
CPU	[ Windows] Processor: % Processor Time
	[ Windows] System: Processor Queue Length
	[ SSAS] Threads: Query pool job queue length
	[ SSAS] Threads: Processing pool job queue length
MDX	[ SSAS] MDX: Total cells calculated
	[ SSAS] MDX: Number of calculation covers
	[ SSAS] MDX: Total Sonar subcubes
	[ SSAS] MDX: Total recomputes
	[ SSAS] MDX: Total NON EMPTY unoptimized
	[ SSAS] MDX: Total NON EMPTY for calculated members
Memory	[ SSAS] Memory: Memory Usage KB
	[ SSAS] Memory: Cleaner Memory shrinkable KB
	[ SSAS] Memory: Cleaner Memory nonshrinkable KB
	[ SSAS] Memory: Memory Limit Low KB
	[ SSAS] Memory: Memory Limit High KB
	[ SSAS] Memory: Cleaner: Memory Shrunk KB/sec
	[ SSAS] Cache: Inserts/sec
	[ SSAS] Cache: Evictions/sec
	[ SSAS] Cache: KB added/sec
	[ SSAS] Other: Calculation cache lookups/sec
	[ SSAS] Other: Calculation cache hits/sec
	[ SSAS] Other: Flat cache lookups/sec
	[ SSAS] Other: Flat cache hits/sec
	[ SSAS] Other: Dimension cache lookups/sec
	[ SSAS] Other: Dimension cache hits/sec
	[ SSAS] Other: Measure group cache lookups/sec
	[ SSAS] Other: Measure group cache hits/sec
Processing	[ SSAS] Processing: Rows written/sec
	[ SSAS] Processing: Aggregations: Rows created/sec
	[ SSAS] Processing: Indexes: Rows/sec
Disk I/O	[ Windows] LogicalDisk: Disk Read Bytes/sec
	[ Windows] LogicalDisk: Avg. Disk sec/Read
	[ Windows] LogicalDisk: Avg. Disk sec/Write
	[ SSAS] Storage Engine Query: Queries from files/sec
	[ SSAS] Storage Engine Query: Data bytes/sec
	[ SSAS] Cache: Copy Reads/sec
Network	[ Windows] Network Interface: Bytes Received/sec
	[ Windows] Network Interface: Bytes Sent/sec
	[ SSAS] Processing: Rows read/sec
	[ SSAS] Storage Engine Query: Rows sent/sec

关键区域	完整的性能计数器名称
中央处理器	[Windows] 处理器 ：处理器时间百分比
	[Windows] 系统 ：处理器队列长度
	[SSAS] 线程 ：查询池作业队列长度
	[SSAS] 线程 ：处理池作业队列长度
MDX	[SSAS] MDX ：已计算的总单元格
	[SSAS] MDX ：计算范围
	[SSAS] MDX ：总声纳小队
	[SSAS] MDX ：总计重新计算
	[SSAS] MDX ：未优化的总NON EMPTY
	[SSAS] MDX ：计算所得成员的总非空
记忆	[SSAS] 内存 ：内存使用率KB
	[SSAS] 内存 ：清洁的内存可收缩KB
	[SSAS] 内存 ：清洁内存不可收缩KB
	[SSAS] 内存 ：内存限制低KB
	[SSAS] 内存 ：内存限制上限KB
	[SSAS] 内存 ：清除程序：内存缩减KB /秒
	[SSAS] 缓存 ：插入次数/秒
	[SSAS] 缓存 ：逐出量/秒
	[SSAS] 缓存 ：每秒增加KB
	[SSAS] 其他 ：计算缓存查找/秒
	[SSAS] 其他 ：计算缓存命中率/秒
	[SSAS] 其他 ：平面缓存查找/秒
	[SSAS] 其他 ：固定缓存命中率/秒
	[SSAS] 其他 ：维缓存查找/秒
	[SSAS] 其他 ：维度缓存命中率/秒
	[SSAS] 其他 ：衡量组高速缓存查找/秒
	[SSAS] 其他 ：衡量组缓存命中/秒
处理中	[SSAS] 处理 ：每秒写入的行数
	[SSAS] 处理：聚合：创建的行/秒
	[SSAS] 处理：索引：行/秒
磁盘I / O	[Windows] LogicalDisk ：磁盘读取字节/秒
	[Windows] LogicalDisk ：平均。 磁盘秒/读
	[Windows] LogicalDisk ：平均。 磁盘秒/写
	[SSAS]存储引擎查询：来自文件的查询/秒
	[SSAS]存储引擎查询：数据字节/秒
	[SSAS]缓存：每秒复制读取
网络	[Windows] 网络接口：接收的字节数/秒
	[Windows] 网络接口：发送字节数/秒
	[SSAS]处理：行读取/秒
	[SSAS]存储引擎查询：每秒发送的行

翻译自: https://www.sqlshack.com/introduction-to-sql-server-analysis-services-ssas-monitoring/

sql2018 ssas