Chapter 14. Monitoring and tuning a Tabular service
Now that you have seen how to build a complete tabular solution, this chapter provides information on how to monitor its behavior and guarantee that your solution is running at its best. In Chapter 12, “Inside VertiPaq,” you saw how the tabular engine uses memory to process and query databases. This chapter shows you how to monitor the resources used by the system. It also shows you how to change some of the parameters to optimize SQL Server Analysis Services (SSAS) and memory use.
What’s new in SSAS 2016
There are no important differences with previous versions of Analysis Services for monitoring and tuning an instance in Tabular mode. However, this chapter has been updated to include references to extended events and related tools.
Finding the Analysis Services process
Analysis Services is a process running as a service under the Microsoft Windows operating system (OS). It starts as soon as the OS starts and normally waits to receive commands, answer queries, process databases, and perform its work. The process’s name is MSMDSRV.EXE (Microsoft Multidimensional Server), and you can verify its existence by opening Windows Task Manager’s Services tab, as shown in Figure 14-1.
You can see in Figure 14-1 that there are several instances of SSAS: two running Tabular (TAB14 and TAB16) and two running Multidimensional (K14 and K16). If you want to see the process that is running Tabular, right-click MSOLAP$TAB16 (or MSOLAP$TABULAR; the name after the dollar sign is the name of the Tabular instance you installed) and choose Go to Details. Task Manager opens the Details tab, highlighting msmdsrv.exe, the Tabular instance of MSMDSRV, as shown in Figure 14-2. (Note that if the process is impersonating a different user, you must run Task Manager as the administrator to see the user name.)
Note
The name of an Analysis Services instance is chosen during the installation operation. In this book, we use the Tabular and Multidimensional instance names to identify the corresponding roles of the different SSAS instances. However, you can choose different instance names during the installation.
SSAS, like any other Windows process, consumes resources by asking for them from the Windows OS. It is important to monitor whether it has enough resources to run optimally to ensure that the system is always responsive. The easiest tool for monitoring Tabular is Task Manager. It already provides much of the information about memory and CPU use and is available to any user on any Windows installation, without requiring special knowledge or administrative rights. Nevertheless, to fine-tune a solution, you will need more advanced tools and a deeper knowledge of the SSAS internals.
Warning
When you use Task Manager to monitor SSAS, the server should not be running other time-consuming processes. Otherwise, your observations will be contaminated by the other tasks that are consuming the server resources.
Because it is an in-memory columnar database, SSAS Tabular is easy to monitor because it primarily uses two kinds of resources: memory and CPU. The disk use of SSAS Tabular is not very important to monitor because disk activity only happens during processing. So, the activity is when the database is written to disk and when the database is being loaded, as soon as the first user accesses the database after the service starts. Both activities are sequential reads and writes, which are very fast operations on normal disk systems. Random I/O might occur during the processing, but only if there is not enough memory to complete the operation and if some data must be paged on disk.
Resources consumed by Analysis Services
Analysis Services consumes resources provided by the Windows OS. The first level of monitoring is looking at the behavior of SSAS Tabular as you would with any other process running on the server. Every process consumes CPU, memory, and I/O bandwidth. SSAS Tabular is particular because it is hungry for CPU processing and memory, but it does not demand much I/O bandwidth throughout.
CPU
Analysis Services consumes CPU processing during two operations: processing and querying. Not all the operations performed by SSAS Tabular can scale over multiple cores. The process of a single partition reads data sequentially, but the compression of the columns in each segment can be parallelized.
The process of a single partition reads data sequentially, but the compression of the columns in each segment can be parallelized. Usually the process of a single partition creates spikes in CPU use at the end of each segment (by default it is 8,000,000 rows). If you process multiple partitions in parallel (from the same tables or from different tables), then the CPU consumption can increase. In general, you should not increase the parallelism if, during a process operation, you already saturate the CPU available. However, you should consider increasing the parallelism if your system has a low use of CPU during the entire process operation.
During querying, SSAS consumes CPU to scan compressed data in the memory and to perform the calculations that are requested by a query. Every query has a part of the execution that can scale up on multiple cores (the storage engine, which uses internal calls to VertiPaq), and another part that is sequential (the formula engine, which manages uncompressed data that is returned by VertiPaq or by external SQL queries in DirectQuery mode). Queries that have a bottleneck in the formula engine will use no more than the equivalent of one core. As shown in Figure 14-3, on an eight-core server, you will see a constant consumption of one-eighth of our available CPU, which is 12 to 13 percent. (You might see a higher percentage because of the time spent by other processes.)
In such cases, you must optimize the DAX expression so that the execution requires less resources than the formula engine. In general, SSAS can consume a lot of CPU resources during processing and, depending on the conditions, while running the queries. You need to bear this in mind when specifying servers for SSAS to run on or deciding if SSAS should be installed on the same server as other CPU-intensive applications.
Memory
Analysis Services uses memory for a lot of different purposes—even if for a tabular model, most of the memory is probably used to store the columnar database that is managed by VertiPaq. It is not the goal of this book to explain all the details of Analysis Services’ memory settings or how to tune them. For most scenarios, the default settings are good enough. However, it is important to understand what happens when SSAS requests the memory from the OS because that memory is not always physical RAM. This could have important consequences like increased paging of memory to disk.
Analysis Services, like any other process in Windows, requires memory from the OS, which in turn provides blocks of virtual memory. Each process has a separate address space, called a virtual address space. Each allocation made by a Windows process inside the virtual address space gets a part of the OS virtual memory, which might correspond to either the physical RAM or the disk paging file. It is up to the OS to determine whether a page of memory (which corresponds to 4 KB) is in physical RAM or is moved to the paging disk file. This concept is very important, especially when you have several other services running on the same server, like Reporting Services, Integration Services, and the relational engine of SQL Server itself.
The memory allocated by SSAS might be paged to disk due to other process activities, and this is partially controlled by some memory settings. An explanation of these settings is available in the section “Understanding memory configuration” later in this chapter, and in the MSDN Library at https://msdn.microsoft.com/en-us/library/ms174514.aspx.
To understand how much virtual and physical memory a process is using, it is important to know how to read the numbers provided by Task Manager. The total amount of virtual memory requested by a process is displayed in a Commit Size column. The total amount of physical RAM consumed exclusively by a process is displayed in a Memory (Private Working Set) column.
The virtual memory manager in Windows is a complex system that aims to optimize the use of physical memory by sharing the data between processes whenever possible. In general, however, it isolates each virtual address space from all the others in a secure manner. Therefore, it could be difficult to interpret the counters we just mentioned. It could also be useful to recap how virtual memory allocation works in Windows, focusing mainly on memory that is allocated privately by a process, such as SSAS allocating RAM for VertiPaq and other internal structures.
When a process allocates private memory, as SSAS does when it requires space for its data, it is requested from virtual memory. When that memory is written, the OS ensures that the page is in physical RAM. When there is not enough RAM to hold all the virtual memory pages that are used to run the processes, the OS moves older pages from RAM to disk. These pages will be recalled from disk as soon as a process needs to read or write data there. This activity is called memory paging, and you want it to happen as little as possible. One way to stop it from happening is to remove the paging file from the OS. You do this by using the no-paging file setting, but we do not recommend using this option on a server running SQL Server or Analysis Services. Another option is to use VertiPaqPagingPolicy in mode 0, as explained later in this chapter.
Thus, you have a paging file and you need to optimize its use. Ideally SSAS should not use it at all. If SSAS were the only process running on the system, it would be sufficient to set its memory limits to a value that does not exceed the amount of physical RAM on the system. In fact, the default settings of SSAS are below this limit, but they do not consider that other memory-hungry processes may run concurrently on the same machine. For example, it is quite common to have both SQL Server and Analysis Services running on the same machine. Think about what would happen when you processed a cube, which of course would mean that SSAS would need to query the fact table in SQL Server: Both services require memory, and paging to disk could be unavoidable. There is a difference between SQL Server and Analysis Services in terms of memory management. SQL Server can adapt the amount of virtual memory it requests from the OS to the amount of physical RAM available to it. SSAS is not as sophisticated as SQL Server and does not dynamically reduce or increase the size of its requests for memory to the OS based on current available physical memory.
The memory requested by a process is always requested as virtual memory. In situations where the virtual memory allocated by SSAS is much larger than the available physical RAM, some SSAS data will be paged to disk. If you use VertiPaqPagingPolicy in mode 1, this could happen during processing or for queries that are creating materialization that is too large, even if an out-of-memory error is more likely in the latter case. You should avoid these situations by configuring Analysis Services’ memory settings (which we discuss in the “Understanding memory configuration” section later in this chapter) so that they limit the amount of memory that can be allocated by it. However, when no other processes are asking for memory, you might find that limiting Analysis Services’ memory use prevents it from using extra memory when it needs it—even when that memory is not used by anything else. We will explore the available memory options for Analysis Services and see how to monitor its memory use in the “Understanding memory configuration” and “Using memory-related performance counters” sections later in this chapter, respectively.
I/O operations
Analysis Services generates I/O operations in two ways:
Directly A direct I/O request from SSAS is made when it needs to read data from or write data to disk and when it sends query results back to the client. This involves an inter-process communication (typically made through the network’s I/O operations).
Indirectly The indirect I/O requests generated by SSAS come from paging-disk operations. It is very important to be aware that this can happen. You cannot see these operations using the performance counters you might typically monitor for SSAS. Paging operations are not visible to the SSAS process and can be seen only by using the appropriate OS performance counters, like Memory: Pages/Sec.
In its regular condition, SSAS performs direct I/O requests only when it reads the database at services startup or during a restore or when it writes data during processing and the backup operation. The only other relevant I/O activities performed by SSAS should be indirect and caused by paging.
Another I/O operation generated by Analysis Services is the transfer of query results to the client. Usually this is not a slow operation, but if a query returns a very large number of rows, the query response time might be affected by the time needed to transfer the result from the server to the client. Take a look at the network traffic to understand if this is a possible issue.
Note
In general, it is not very important to monitor I/O operations that are performed by an SSAS Tabular service.
Understanding memory configuration
Because memory is so important to Tabular, being able to monitor how much memory is used and learning how to configure memory use is a very important topic. In this section, you learn about the main tools available to configure and monitor memory that is used by a Tabular instance.
First, it is important to understand that SSAS uses memory during the two following phases:
Processing During processing, SSAS needs memory to load data and create dictionaries and related data structures before it flushes them to disk. In addition, if the database being processed already contains some data, it must hold the previous version of the database until the transaction commits and the new database is ready to query. Refer to Chapter 12 for more information about the internal structure and processing.
Querying During a query, SSAS sometimes needs memory to hold temporary data structures that are needed to resolve the query. Depending on the database size and query shape, these data structures might be very big. Sometimes they are much bigger than the database itself. Later in this chapter, in the “Using memory-related performance counters” section, you will see an example of a complex query that uses a lot of RAM.
Memory settings in Tabular are configured in the msmdsrv.ini file, which contains the whole SSAS configuration. You can edit the file manually to change the memory settings, but the easiest way to read or modify the content of the configuration is to right-click the server in the Object Explorer pane of SQL Server Management Studio (SSMS) and choose Properties. The Analysis Services Properties dialog box opens (see Figure 14-4). From there, you can make most of the SSAS configurations.
Figure 14-4 The Analysis Server Properties dialog box, which contains all the configurations of SSAS.
In the highlighted box, you see the following memory settings for SSAS. (Note that to display all these settings, you must select the Show Advanced (All) Properties check box.)
HeapTypeForObjects Choose the heap system to allocate objects of a fixed size, such as instances of classes in C++ (which is the language used by Microsoft to write Analysis Services). The possible values are as follows:
• 0 Use the Windows Low-Fragmentation Heap (LFH), which is the default in SSAS Tabular 2016.
• 1 Use the custom heap implementation of Analysis Services.
MemoryHeapType Choose the heap system to allocate objects of a dynamic size, such as strings, vectors, bytes, and so on. The possible values are as follows:
• –1 This choice is made automatically by SSAS Tabular (the default in SSAS Tabular 2016).
• 1 Use the custom heap implementation of Analysis Services.
• 2 Use the Windows LFH.
• 5 This is a hybrid allocator (new in SSAS Tabular 2016).
VertiPaqPagingPolicy This is the first setting you need to learn. It can have a value of 0 or 1. We refer to its value as mode 0 or mode 1. In mode 0, all the VertiPaq data is locked into memory, whereas in mode 1, the data is not locked. This allows the VertiPaq in-memory engine to page data on disk if the system is running out of memory. More specifically, in mode 1, only hash dictionaries are locked. Data pages can be flushed to disk. This enables VertiPaq to use more memory than is available. Keep in mind that if paging occurs, performances will suffer a severe degradation. The default value is mode 1.
VertiPaqMemoryLimit If you choose mode 0, VertiPaqMemoryLimit defines the total amount of memory VertiPaq can lock in the working set (the total that can be used for in-memory databases). Remember that the Analysis Services service might use more memory for other reasons. In mode 1, it defines a limit for the physical memory that is used by VertiPaq, which allows paging for the remaining memory (virtual committed memory) above this limit.
The VertiPaqPagingPolicy setting provides a way to prevent VertiPaq data from interacting badly with the memory-cleaning subsystem. In mode 1, it causes the cleaner subsystem to ignore the memory allocated for VertiPaq data beyond VertiPaqMemoryLimit when calculating the price of memory. In this mode, the server’s total memory use can exceed the physical memory. It is constrained primarily by the total virtual memory, and it pages data out to the system page file.
If you want to reduce the memory for an instance of Analysis Services, it makes sense to set VertiPaqMemoryLimit to a number that is lower than LowMemoryLimit (see the upcoming bullet).
HardMemoryLimit This is the maximum memory that SSAS can allocate. If SSAS exceeds the hard memory limit, the system aggressively kills the active sessions to reduce memory use. Sessions killed for this reason receive an error that explains the cancellation due to memory pressure. With VertiPaqPagingPolicy in mode 0, it is also the limit for the maximum working set of the process. If HardMemoryLimit is set to 0, it will use a default value midway between the high memory limit and the total physical memory (or the total virtual address space, if you are on a 32-bit machine on which the physical memory exceeds the virtual memory).
LowMemoryLimit This is the point at which the system starts to clear caches out of memory. As memory use increases above the low memory limit, SSAS becomes more aggressive about evicting the cached data until it hits the high/total memory limit. At this point, it evicts everything that is not pinned.
TotalMemoryLimit If memory use exceeds the total memory limit, the memory manager evicts all the cached data that is not currently in use. TotalMemoryLimit must always be less than HardMemoryLimit.
The HeapTypeForObjects and MemoryHeapType settings are important for memory-management performance and stability. The new defaults in SSAS Tabular 2016 are usually the best choice for most of the server, whereas upgrades from previous versions might keep settings that could create memory fragmentation after extensive use. More details on these problems are available at https://www.sqlbi.com/articles/heap-memory-settings-for-analysis-services-tabular-2012-2014/.
Important
If you upgraded previous versions of Analysis Services to SSAS Tabular 2016, you should change the MemoryHeapType setting to the new default value of –1. The previous default value of 2 creates memory fragmentation that slows down the process and query performance. If you experienced an improved performance after a service restart in previous versions of Analysis Services, you were likely affected by this problem. If you do not modify the MemoryHeapType setting, you might experience the same performance degradation in SSAS Tabular 2016.
How aggressively SSAS clears caches depends on how much memory is currently being allocated. No cleaning happens below the LowMemoryLimit value, and the level of aggression increases as soon as the memory use approaches the TotalMemoryLimit value. Above the TotalMemoryLimit value, SSAS is committed to clearing memory, even if the panic mode only starts after the HardMemoryLimit value.
All the limit values are expressed as numbers. If their value is less than 100, it is interpreted as a percentage of the total server memory. (On 32-bit systems, the maximum available memory can be up to 2 GB, regardless of the memory installed on the system.) If it has a value greater than 100, it is interpreted as the number of bytes to allocate.
The value of these parameters, if greater than 100, is in bytes. If you use 8,192, you are not allocating 8 GB. You are allocating 8 KB, which is not so useful. If you provide the wrong values, SSAS will not raise a warning. Instead, it will try to work with the memory you made available to it.
When SSAS is working, it requests memory from the OS to perform its tasks. It continues to use memory until it reaches the TotalMemoryLimit value. Nevertheless, as soon as the LowMemoryLimit value has been reached, SSAS starts to reduce memory use by freeing memory that is not strictly necessary. The process of reducing memory (which means cache eviction) is more aggressive as the system moves toward the TotalMemoryLimit value. If SSAS overcomes the TotalMemoryLimit value, it becomes very aggressive. When it reaches the HardMemoryLimit value, it starts to drop connections to force memory to be freed.
Because cache-eviction decisions and hard-limit enforcement are normally done based on the process’s total memory use, it has been necessary to change that calculation when allowing databases to exceed physical memory in Tabular. (Remember that previous versions of Analysis Services supported only multidimensional models.) Therefore, when VertiPaqPagingPolicy is in mode 1, which indicates that memory can grow beyond the total physical memory, the system tracks the total memory used by VertiPaq as a separate quantity. (This is reported in the MemoryVertiPaq* counters that you can analyze in Performance Monitor.) If the total memory used by VertiPaq exceeds the VertiPaqMemoryLimit value, the memory used by VertiPaq in excess of the limit will be ignored for the purpose of determining what to evict.
The following example demonstrates these concepts. Suppose VertiPaqMemoryLimit is set to 100 GB, LowMemoryLimit is 110 GB, and TotalMemoryLimit is 120 GB. Now assume that VertiPaq data structures are using 210 GB of memory and the process’s total memory use is 215 GB. This number is well above the TotalMemoryLimit value (and probably above the HardMemoryLimit value), so ignoring VertiPaqMemoryLimit, the cleaning would be very aggressive and would kill sessions. However, when PagingPolicy is set to 1, the memory used by VertiPaq in excess of the limit is ignored for the purpose of computing memory pressure. This means that the number that is used is computed according to the following formula:
+ <Total Memory> + 215GB
- <Total VertiPaq Memory> - 210GB
+ <VertiPaqMemoryLimit> + 100GB = 105GB
Because this value (105 GB) is below the LowMemoryLimit value (110 GB), the cache is not cleaned at all.
As you have probably noticed, this chapter covers how the SSAS engine behaves with memory and how to configure it. This chapter does not cover how to reduce memory use by using a correct database design. If you need some hints on this, see Chapter 15, “Optimizing tabular models.”
Using memory-related performance counters
Now that you have learned how memory parameters can be set, you will learn how to monitor them by using performance counters. To obtain a very useful graphical representation of the memory use in SSAS, you can use Performance Monitor. Performance Monitor is a Windows utility that can show you many counters that programs make available to monitor their behavior. SSAS offers many interesting and useful counters.
If you open Performance Monitor, you must add the SSAS memory-related counters to the graph. If the instance is called Tabular, as in our example, you can find the counters under the MSOLAP$TAB16:Memory tree of the counter hierarchy. (If your instance is the default, the name will be MSAS11:Memory.) As shown in Figure 14-5, you see the Add Counters dialog box with the desired counters already selected.
Figure 14-5 The Add Counters dialog box, which enables you to choose the counters to add to the graph.
Because the values are in kilobytes, you must adjust the counter scale to make it fit into the chart and to ensure that all the counters use the same scaling. For these counters, a scale of 0.00001 is a good choice. The chart, immediately after a service restart, is shown in Figure 14-6.
The chart was drawn using a computer with 8 GB of RAM. We changed the default values for the parameters by setting HardMemoryLimit to 70, TotalMemoryLimit to 55, and LowMemoryLimit to 45. When values are smaller than 100, these numbers are used as percentages of the available memory (8 GB). The server has been configured in mode 0, so no paging will happen. Figure 14-7 shows what happens if you execute a query requiring a lot of memory.
The query failed to execute. The following analysis is of the five points highlighted in the chart:
1. The query starts Then, the LowMemoryLimit value having been reached, the cleaner starts to clean some memory by using a graceful tactic (because there is still plenty of memory available).
2. The TotalMemoryLimit value has been surpassed The cleaner works aggressively to free memory because it is using too much memory. Cache is being cleared, and the query starts to suffer in performance.
3. The HardMemoryLimit value has been surpassed This can happen because of the speed at which the memory is requested during query execution. It takes some time for the cleaner to start. Nevertheless, now the cleaner is very aggressive. In fact, after point 3, a large amount of memory is cleared.
4. Even if the cleaner has tried its best to reduce the memory use, the query is still asking for memory The connection is closed. There is no way to give it the memory it needs because it is over the HardMemoryLimit.
5. The connection has been closed All the memory requested by the query is cleared, and SSAS can work normally.
Now, check the same query, on the same server, running in mode 1. In this mode, SSAS can page out memory to use more memory than the physical RAM available. The first part of the chart is shown in Figure 14-8.
In the chart, the highlighted line is the VertiPaq Paged KB counter, which shows how many kilobytes of pageable memory are used by the engine. The other interesting line is Memory Usage KB. You can see that SSAS is not going over the HardMemoryLimit value, so the connection will not be dropped. Nevertheless, to avoid using RAM, VertiPaq is using pageable memory. The system is paging huge amounts of memory to disk, which leads to poor performance. Moreover, during paging, the system is nonresponsive, and the whole server is suffering from performance problems.
This example is deliberately flawed. The query needed 15 GB of RAM for execution, and trying to make it work on an 8-GB server was not a very good idea. Nevertheless, it is useful to understand the difference between mode 0 and mode 1 and to learn how to use the counters to check what is happening to the server under the covers.
Using mode 1 has advantages and disadvantages. It lets the server answer complex queries even when it is running out of memory. However, it can also cause severe performance problems—not only for the complex query, but also for all the users who are running the much lighter queries. Using mode 0, the server is always very responsive, but as soon as it reaches the HardMemoryLimit value, it will close connections due to memory pressure.
Correctly setting the mode in a production server is a very complex task that requires a deep understanding of how the server will be used. Keep in mind that Tabular is very memory-hungry. You need to carefully check the memory use of your queries before correctly sizing the memory for the production server.
Using dynamic management views
SSAS makes available a set of dynamic management views (DMVs), which are useful for gathering precise information about the status of a database or an instance of SSAS. The information provided by DMVs is reset at service startup, so it can show very interesting statistics about the use of SSAS since its last restart.
There are many DMVs, and you can find a detailed description of each one at https://msdn.microsoft.com/en-us/library/ms126221.aspx. A selection of the most interesting DMVs is shown later in this section. If you want to list all available DMVs, you can execute the following query from inside an MDX query panel in SSMS:
SELECT * FROM $SYSTEM.DISCOVER_SCHEMA_ROWSETS
A simpler way to see the list of the available DMVs is to switch to the DMV pane in DAX Studio. As shown in Figure 14-9, the DMV pane contains the same names returned by the DMV above. You can double-click one of these names to get the corresponding statement to query the DMV ready to be executed.
Although we will not provide a complete description of all the available DMVs, we will briefly discuss some of the queries to give you a better idea of the kind of information you can obtain by using DMVs.
As a first example, the following query retrieves the activity executed on different objects in the database since the service startup. It is useful to see the objects in your instance on which the engine has spent more time:
SELECT TOP 10
OBJECT_ID,
OBJECT_CPU_TIME_MS
FROM $system.DISCOVER_OBJECT_ACTIVITY
ORDER BY
OBJECT_CPU_TIME_MS DESC
The result is the set of the 10 objects on which the SSAS instance has spent the most time (expressed in CPU milliseconds).
Note
You cannot use the full SQL syntax when querying the DMV. You have only a subset of SQL available, and features such as JOIN, LIKE, and GROUP BY are not available. DMVs are not intended to be used in complex queries. If you need complex processing, you should issue simple queries and then process the results further.
All the DMVs return many columns, most of which are useful for Multidimensional. (There are several columns that show numbers related to I/O, which, in Tabular, are of no use.) This is a clear indication of the big difference between Tabular and Multidimensional. In Tabular, because all the data should be in memory, there should be no I/O at all, and the system maintenance and optimization are greatly reduced. All you need to do is optimize the DAX queries and make sure that enough memory is available in the system.
Because memory is so important to Tabular, a very useful function of DMVs is gathering memory occupation by object. The DMV that returns this information is DISCOVER_OBJECT_MEMORY_USAGE. In the information you get with this DMV, there are both SHRINKABLE and NONSHRINKABLE memory usages. In the following query, there is an ORDER BY on the NONSHRINKABLE memory size. Note that in Multidimensional, the SHRINKABLE column is always empty; you must use the NONSHRINKABLE column to get meaningful values. For example, you might run the following query:
SELECT * FROM $system.DISCOVER_OBJECT_MEMORY_USAGE ORDER BY OBJECT_MEMORY_NONSHRINKABLE DESC
As a result, you will receive the list of all the objects currently loaded, along with the amount of memory they are using, as shown in Figure 14-10.
A big difference between using this view and the other views used by VertiPaq Analyzer is that you have a single complete view of the memory used by the service, regardless of the single database. You can analyze this view by using a Power Pivot for Excel data model called BISM Server Memory Report, created by Kasper De Jonge and available at http://www.powerpivotblog.nl/what-is-using-all-that-memory-on-my-analysis-server-instance/. The technique of extracting data from DMVs used in this workbook was the inspiration behind the creation of VertiPaq Analyzer, which seeks to provide a more detailed analysis of a single database.
Interesting DMVs to monitor a Tabular service
The most interesting DMVs you can use to monitor the state of an instance of Analysis Services Tabular that runs on a server are a subset of those with the DISCOVER prefix. In the following list, you will find a selection of these DMVs. The list does not include other DMVs with the same prefix that are useful to analyze the size of objects in the databases (which is already covered by VertiPaq Analyzer) and to get other metadata from the system (a list of keywords, profiler events, and others).
You should use the following DMVs for troubleshooting:
DISCOVER_CONNECTIONS This lists the connections that are currently opened on the server. You can see the name of the user connected, the timestamp of the last command sent, and other counters. This could be useful to make sure there are no clients connected before restarting the service.
DISCOVER_COMMANDS This shows the last command sent for each connection. It could be a command currently executing or the last command executed if the connection is idle.
DISCOVER_DB_CONNECTIONS This provides resource use and activity information about the currently opened connections from SSAS Tabular to an external database. (Usually, during a database process operation, SSAS retrieves data from the other databases.)
DISCOVER_LOCKS This returns information about current locks on the server. There are different types of locks in SSAS Tabular, but like SSAS Multidimensional, these locks manage the conflict between process operations and queries. If a long process operation blocks many of the queries, or a long-running query is blocking a process, you use this DMV to identify the operations responsible for the lock.
DISCOVER_MEMORY_USAGE This provides the memory-usage statistics for the objects allocated by Analysis Services.
DISCOVER_OBJECT_MEMORY_USAGE This returns information about memory that is allocated for specific objects.
DISCOVER_SESSIONS This provides resource use and activity information about the currently opened sessions on the server. It provides more details than the information returned by DISCOVER_CONNECTIONS because SSAS might have more sessions within the same connection.
DISCOVER_TRANSACTIONS This lists the pending transactions on SSAS Tabular.
An open source tool, SSAS Activity Monitor, uses these views and helps you to monitor the current activity on Analysis Services. It works for both Multidimensional and Tabular instances, and is available at https://www.sqlbi.com/tools/ssas-activity-monitor/.
Automating monitoring info and logs acquisition
The tools we have seen so far are useful to monitor what is happening and what will happen on Analysis Services Tabular. However, a common requirement is trying to understand what happened in the past, whether it was two minutes ago, two days ago, or two weeks ago. If you did not collect the data, you will have very little information to analyze. For this reason, it is better to acquire the logs that will help you in future investigations.
Performance counters
The performance counters that are available from the OS are visible in Performance Counter, which is a snap-in for Microsoft Management Console (MMC). In reality, these performance counters are available through a set of APIs, and there are third-party tools that can access them, too. However, in this book, we use Performance Monitor to show them. The concepts related to each counter described are valid, regardless of the tool used to display them.
Note
There are differences in the Performance Monitor user interface, depending on which version of Windows you have, but they are not significant for the purposes of this chapter.
Performance Monitor can display performance-counter data captured in real-time. It can also be used to display a trace session of the performance-counter data that is recorded by using the Data Collector Sets feature. This trace data is very useful for monitoring a production server to detect bottlenecks and to measure the average workload. We suggest reading the documentation at https://technet.microsoft.com/en-us/library/cc749337.aspx to understand how to make good use of Data Collector Sets.
It is a good idea to keep a data collector active on a server that is running SSAS Tabular, as shown in Figure 14-11. You should include the Memory and Processor counters from the OS (selecting those we mentioned in this chapter), certain Process counters (at least those related to memory and CPU), and specific counters from Analysis Services instances that you want to monitor (they have the prefix MSOLAP$ followed by the name of the instance). In the latter group of counters, the more interesting for SSAS Tabular are Connection, Locks, Memory, Processing, and Storage Engine Query. Because all these counters produce a certain amount of data, you should consider the sample interval for a production server in minutes (because it will be always running). The same sample should be seconds when you need to analyze a specific problem in detail (for example, a database process operation), which enables and disables the Data Collector for the minimum amount of time necessary.
Figure 14-11 The Data Collector configuration, which is available in the Performance Monitor snap-in.
SQL Server Profiler
Microsoft SQL Server Profiler is a tool for capturing database engine trace information, but it is still supported for Analysis Services workloads. (See more info at https://msdn.microsoft.com/en-us/library/ms181091.aspx.) It is a good idea to create one or more templates for SSAS Tabular including only the events you are interested in for query and process analysis. For example, Figure 14-12 shows a simple set of events to monitor for capturing long-running queries and information about process operations.
Note
Certain feature sets of SQL Server Profiler, including Database Engine Trace Capture, Trace Replay, and the associated namespace, will be deprecated in the version after SQL Server 2016. However, SQL Server Profiler for the Analysis Services workloads is not being deprecated, and it will continue to be supported.
The events chosen in a profiling session are, in fact, classes of events. For each class, there are many actual events that can be generated. These events are shown in the EventSubClass column in SQL Server Profiler, which is shown in Figure 14-13.
Looking at these events in SQL Server Profiler is not particularly easy. Saving the trace data to a SQL Server table is a good idea because it enables you to query and report on it much more easily. To save a captured trace session, open the File menu, choose Save As, and select Trace Table. You could also choose to save a trace session in advance by selecting the Save to Table option in the Trace Properties dialog box that is shown when you define a new trace session.
The trace events that you might be interested in are listed below. Event classes and subclasses are identified by an integer value when saved in the SQL Server log tables. These definitions are available in the following DMVs in Analysis Services: DISCOVER_TRACE_EVENT_CATEGORIES and DISCOVER_TRACE_COLUMNS.
The events that are relevant for processing operations are as follows (the corresponding integer value appears in parentheses):
Command Begin (15) and Command End (16) These contain only one interesting subclass event, as follows:
• Batch (12) This contains the XMLA command sent to Analysis Services to process one or more objects.
Progress Report Begin (5) and Progress Report End (6) These contain several subclass events that apply mainly to processing operations for a tabular model. Following are the subclass events that are relevant to processing:
• Process (1) and Tabular Object Processing (59) These notify the process of single objects (database, table, partition, and segment). One process can invoke other process operations. For example, processing a table will execute the process of every partition of the table. Tabular Object Processing is new to SSAS Tabular 2016 for model compatibility 1200.
• ExecuteSQL (25) This contains the syntax sent to the data source to query data (which is actually a SQL syntax for a relational database).
• ReadData (17) This shows in the IntegerData column the milliseconds required to read the data from the data source. Usually this event has the longest duration, even if the CPU consumption is a fraction. This is because SSAS Tabular is waiting for data from the data source most of the time.
• AnalyzeEncode Data (43) This reports the activity of compression for a segment, which includes VertiPaq and Compress Segment events.
• VertiPaq (53) This reports the activity of the compression made by VertiPaq.
• Compress Segment (44) This notifies the compression of each single column in each segment.
• Hierarchy Processing (54), Relationship Build Prepare (46), and Build Relationship Segment (47) These are events related to the calculation of hierarchies and relationships in the data model.
• Tabular Transaction Commit (57) This indicates the final commit operation, which could be long when there are long-running queries that must complete before the process commit takes place.
The events that are relevant for analyzing the query workload are as follows:
Query Begin (9) and Query End (10) These usually include only one subclass event, which corresponds to the type of the query received: MDXQuery (0) or DAXQuery (3). The Query End event contains the total duration of the query. It could be the only event you want to collect in long-running logs so you can identify the slow-running queries and users affected. The other events are interesting for analyzing single queries in more detail.
DAX Query Plan (112) This contains two subclasses that are raised for every query. Be careful about intercepting this event; the query plan is represented with a text string that can be extremely long, and its construction can slow down the activity on a server. Activating this event in a profiling session can slow down all the queries sent by any user to that server. Activate it only if necessary and for a limited time on a production server. The subclasses are as follows:
• DAX VertiPaq Logical Plan (1) This contains the logical query plan for the MDX or DAX query to Tabular.
• DAX VertiPaq Physical Plan (2) This contains the physical query plan for the MDX or DAX query to Tabular.
VertiPaq SE Query Begin (16) and VertiPaq SE Query End (16) These contain the following two interesting subclass events:
• VertiPaq Scan (0) This contains an xmSQL query, which is sent by the formula engine to the VertiPaq storage engine.
• Internal VertiPaq Scan (10) This contains an xmSQL query, which is generated to solve part or all of the VertiPaq Scan request made by the formula engine. Every VertiPaq Scan event generates one or more Internal VertiPaq Scan events.
VertiPaq SE Query Cache Match (16) and VertiPaq SE Query Cache Miss (16) These have no related subclass events and notify cache match and miss conditions.
Serialize Results Begin (75) and Serialize Results End (77) These have no related subclass events. They mark the start and end of the query results that are being sent back to the client. A large result from a query could take a long time to be serialized and sent to the client.
A sort of nesting of events can be seen in the trace data. For example, the Process event for a database initiates several other Process events for related objects such as the tables, partitions, and segments in that database. The outermost events have an execution time (the Duration column, in milliseconds) that includes the time taken for all the operations executed within those events. Therefore, the values in the Duration and CPU columns for different events cannot easily be summed because you must be careful not to sum events that might include each other.
ASTrace
Using SQL Server Profiler to capture trace data is a good option if you want to create a trace manually, but it is not the best way to automate the trace-data capture on a production server. A useful tool is ASTrace, which is part of the Microsoft SQL Server Community Samples for Analysis Services, available from http://sqlsrvanalysissrvcs.codeplex.com. ASTrace captures an Analysis Services trace and logs it into a SQL Server table.
This utility runs as a Windows service that connects to Analysis Services. It creates a trace and logs trace events into a SQL Server table by using the SQL Server Profiler format. To customize the trace (for example, to filter on certain events), you can use a standard trace template authored with SQL Server Profiler. Running as a service, this tool does not require a logged-in user, unlike SQL Server Profiler.
Flight Recorder
Flight Recorder is a feature of Analysis Services that maintains a log of all events that have occurred in the recent past. This might be useful when investigating crashes or performance problems. It works by running a trace. By default, it does not capture all the events and keeps data for only a limited time so as not to fill the disk with the trace data. However, you can customize it by changing both the length of time it keeps the data and the events it records. You must remember, though, that Flight Recorder can affect performance. The more events it records, the more I/O operations are required to update the trace files it generates. Moreover, certain events could slow down all the queries sent by the users. For example, the DAX Query Plan really slows down queries with a complex query plan.
You can open Flight Recorder trace files with SQL Server Profiler. They are stored in the OLAPLog folder (usually found at C:Program FilesMicrosoft SQL ServerMSAS13.TABULAROLAPLog, where TABULAR is the name of the instance of SSAS Tabular). You can customize the trace definition used by Flight Recorder by defining a SQL Profiler template in the same way you can for ASTrace.
Extended Events
Analysis Services, like SQL Server, has an alternative API to capture trace events other than the SQL Server Profiler: Extended Events. This includes all the events provided by the profiler. It also includes a set of additional events that are useful for debugging for programmers who write the internal code of Analysis Services, but are not so useful for BI developers.
If Extended Events traces the same events that are of interest to us as SQL Server Profiler, why should you should consider changing? The reason is that the standard trace events (managed by SQL Server Profiler) are more expensive to manage. They create an additional overhead on the server. In contrast, events in Extended Events are lighter and have fewer side effects with respect to server performance. Moreover, capturing these events does not require an additional process of listening to Analysis Services as it does for standard trace events. (You saw earlier that ASTrace is an additional service.) However, Extended Events is not commonly used in Analysis Services because it lacks a quick and intuitive user interface to start interactive sessions and to analyze the data collected in recorded sessions. SQL Server 2016 added some features in SSMS to manage Extended Events through a graphical user interface, but they are still not mature enough to replace SQL Server Profiler completely.
The lack of a good user interface does not greatly affect the requirements for collecting the events of a production server. In this case, a low impact in performance is as important as the availability of tools that automate the monitor and export the raw data to an analytical platform. Because Extended Events is more of an API than user interface, this is probably the best choice for implementing an infrastructure that will constantly monitor a production server.
SSAS Events Analyzer is an open source set of batches and analytical tools that collect and analyze Extended Events for Analysis Services. It is available at http://www.sqlbi.com/tools/ssas-events-analyzer/. If you need a step-by-step tutorial for Extended Events for Analysis Services 2016, read the article at https://blogs.msdn.microsoft.com/analysisservices/2015/09/22/using-extended-events-with-sql-server-analysis-services-2016-ctp-2-3/ and the MSDN documentation at https://msdn.microsoft.com/en-us/library/gg492139.aspx.
Using Extended Events, you will collect and manage the same events described in the SQL Profiler section, but using a collection technology that is more efficient and has a lower overhead on the server.
Other commercial tools
Few vendors provide commercial tools for monitoring Analysis Services Tabular. As of this writing (March 2017), only two tools provide these services: SQL BI Manager from Idera (https://www.idera.com/productssolutions/sqlserver/sql-server-business-intelligence/) and SQL Sentry Performance Advisor (http://www.sqlsentry.com/products/performanceadvisor/ssas). We do not have specific experience in these tools, and this list might not be complete by the time you read this book. We have listed these resources in case you want to consider a commercial product to manage monitoring operations for Analysis Services.
Monitoring data refresh (process)
When you deploy a tabular model by using the default in-memory storage (VertiPaq), you have a copy of the data that has to be refreshed periodically using a process operation. In Chapter 11, “Processing and partitioning tabular models,” you saw the different processing strategies and operations you can use. After you deploy a model and execute a process operation, you might want to monitor it to optimize the process performance or use it for troubleshooting.
Processing data in a tabular model is a stressful operation for the server. CPU and memory are used intensively. In addition, to accelerate the completion time, you also must make sure the throughput of data transfer between data sources and the Tabular instance is optimal because this is often the bottleneck of the process.
You can use different tools to collect the information you need to control the behavior of SSAS Tabular while processing the data. In general, you should increase the CPU consumption to parallelize the process. That way you can control it so the memory required by SSAS Tabular does not exceed the physical RAM available.
Performance counters for monitoring a data refresh
You can use Task Manager for a first look at the performance of a server. You can easily see in the Performance tab whether you are saturating the cores available (the CPU use). You can identify the msmdsrv.exe process by looking at the memory that is required for the OS (Commit Size). Analyzing paging with Task Manager might not be simple. You have more counters in Performance Monitor, which we introduced in the previous section of this chapter.
You must monitor the use of memory and CPU, and the throughput transfer speed between the data sources and SSAS Tabular. You saw a few memory counters in the Performance Monitor in the section “Using memory-related performance counters” earlier in this chapter. This section focuses on the counters that are relevant for a process operation, without further examples of user interface.
The first element to control is the memory. You should avoid memory paging (which occurs when there is not enough physical memory to complete an operation). For this reason, you should monitor the following counters in the Memory category. These provide information about the overall consumption of memory, regardless of the consumer (a process, a driver, or the OS itself):
Pages/Sec This is the number of pages read from and written to disk, to resolve hard page faults. Access to a page of virtual memory that has been paged to disk generates this type of event. The cause is insufficient physical RAM to satisfy all the memory requests. If this happens, you should apply a technique that will lower the memory pressure, which is described in Chapter 15, “Optimizing tabular models.”
Committed Bytes This is the amount of virtual memory requested by all the running processes. If this value is higher than the amount of available physical RAM, then data has been paged to disk. However, if the Pages/Sec rate is low or null, the data paged to disk is not used often. As long as that data does not need to be accessed, this situation will not result in performance problems. If the data paged is from databases that are not queried during a nightly batch, nobody will recognize the issue. However, the first user who queries the database will hit a very slow response time because data must be restored in physical RAM from the paging file to execute a query.
The Process category contains other counters related to memory. These include memory counters and others that are useful for analyzing the state of an Analysis Services instance:
Virtual Bytes This is generally not so useful because it represents the amount of virtual memory used for both private allocations and file mapping (including the executable files).
Page File Bytes and Private Bytes These are usually very similar for Analysis Services. They correspond to the Commit Size counter you have seen in Task Manager.
Page File Bytes Peak This is very important because it reports the maximum value reached by Page File Bytes from the start of the process execution, regardless of when you started monitoring. If Page File Bytes Peak is higher than Page File Bytes, then there has been a reduction in the amount of memory requested by Analysis Services. But, because this peak has been reached before, this usually implies that it can be reached again in the future.
Working Set This corresponds to the Working Set (Memory) value in Task Manager.
Working Set–Private This corresponds to the Memory (Private Working Set) value in Task Manager.
Working Set Peak This is the maximum value reached by Working Set since the start of process execution, regardless of when you started monitoring. This value can be higher than the actual Working Set counter because, even if a process has not released memory that was previously allocated, part of that memory may have been paged due to memory activity by other processes. If this happens with Analysis Services, you could investigate what other processes are requesting memory that is concurrently on the same server.
Profiler events for monitoring a data refresh
You can capture trace profiler events from Analysis Services with several tools: SQL Profiler, ASTrace, Flight Recorder, and Extended Events. Regardless of how you collect this data, to monitor a process operation, you must focus on a certain subclass of events. You cannot sum the duration and CPU time between different subclasses because certain subclasses include others. Unfortunately, this hierarchical nature of the events is not represented in the data model of the profiler events you receive.
When you process an object in SSAS Tabular, the following events are raised (the corresponding integer value appears in parentheses):
Command End (16)/Batch (12) This is the high-level batch that starts a process operation. The duration reported is the amount of time spent completing the process, whereas the CPU time is not a valid indication. This event includes the time of all the other Progress Report End events executed internally.
Progress Report End (6)/Process (1) This reflects most of the time required for the process, minus the time to save the file and to wait for the lock to switch the processed objects so that they become available for querying. This event includes both the duration and CPU time of the other events in the Progress Report End class and different subclasses (ReadData, Analyze Encode Data, Commit, and Tabular Object Processing).
Progress Report End (6)/ReadData (17) This is the time spent reading data from the data source.
Progress Report End (6)/AnalyzeEncode Data (43) This is the time spent finding the best encoding and compression for the data.
Progress Report End (6)/VertiPaq (17) This is the time spent compressing data in VertiPaq for each object. This time is included in the AnalyzeEncode Data subclass event.
Progress Report End (6)/Compress Segment (17) This is the time spent compressing each segment by VertiPaq. This time is included in the VertiPaq subclass event.
Progress Report End (6)/Tabular Object Processing (59) This is the time spent computing hierarchies and relationships.
Progress Report End (6)/Commit (6) This is the time spent compressing each segment in VertiPaq. This time is included in the VertiPaq subclass event.
Progress Report End (6)/Tabular transaction commit (57) This is part of the time spent committing the transaction. It is already computed in the Commit subclass event.
Progress Report End (6)/Sequence point (58) This is an event related to the completion of a sequence of internal events.
Usually the main parts of a process operation are the ReadData and the AnalyzeEncode Data subclass events. Drilling down in the single events of each subclass, you can identify the objects (tables or partitions) that get most of the time of a process operation.
If the bottleneck is the ReadData operation, you must improve the speed of the data source. However, if the CPU time of ReadData is very close to the duration, then the bottleneck is the connection and/or the driver on the client side (Analysis Services). You can improve the speed by increasing the parallelism of the process (by processing more partitions in parallel) or by increasing the clock speed of the Analysis Services server.
When AnalyzeEncode Data is the bottleneck and your process runs using all the cores available (you observe a 100 percent CPU time during the process for Analysis Services), then you can try to increase the number of CPUs available to Analysis Services or increase the clock speed. When this is not possible, you can improve the speed by lowering the number of columns imported. (This reduces the memory pressor and the work required to compress each segment.)
In an ideal condition, the commit time is close to zero seconds, but in the case of long-running queries in execution, the process operation might have to wait before completing the commit phase. If you notice a large amount of time spent during commit, you might consider moving the process operation to a different time slot to avoid conflicts with other queries that are running on the same data model.
These are just basic suggestions for starting the optimization of the process operation. By investigating specific bottlenecks, you might find other techniques to apply to specific data models. For example, you might consider changing the segment size or the parallelism of the process to better balance the memory and the CPU available on a server. The goal of this chapter is to provide you with the tools to understand what is going on and to locate the bottlenecks, which are the longer and harder activities, especially when you need to optimize a process operation.
Monitoring queries
When users query a tabular model, you might want to analyze the overall level of use by answering the following questions (and others):
How many users access the service?
Who are the users running the slowest queries?
At which time does the server have the peak workload?
You saw earlier in this chapter that SSAS Activity Monitor can help you analyze existing connections to an SSAS instance. This section is focused on the analysis of the data collected on a server, analyzing the past workload and identifying possible bottlenecks in queries, before the users of the system call support because of bad performance.
The most important information to collect is probably a trace of the queries sent by the users to the server. However, a minimal set of performance counters could be helpful in identifying critical conditions that are caused by a high number of concurrent queries or by particularly expensive queries that require temporary memory because of large materialization.
Performance counters for monitoring queries
You can use the same counters described in the “Monitoring data refresh (process)” section to monitor the resources of the OS that are consumed at query time.
In ideal conditions, you should not see a particular increase in memory consumption when the user queries a data model. However, the following two main effects could decrease perceived performance:
A DAX expression that creates a large materialization will use a large amount of memory for the duration of the query execution. Different parts of the DAX engine (the formula engine and the storage engine) communicate by exchanging uncompressed data in memory. In particular, the storage engine materializes uncompressed data for the formula engine, which executes the final part of the expression evaluation. In certain conditions (such as complex queries or badly written DAX code), this materialization could require too much memory, generating a slower response time, or, in the worst case, an out-of-memory error. In this case, you should modify the DAX code for measures and queries to minimize the required materialization.
Other services running on the same server might consume physical RAM, paging part of the data that is managed by Analysis Services. When paging occurs, subsequent queries on the same data will be much slower. The VertiPaq engine is optimized for the data stored in physical memory, but if paging happens, the algorithm used by the DAX engine might have a severe decrease in performance.
In both cases, you will have high pressure in memory and possibly paging activities, which are easy to detect by collecting performance counters from the OS. Thus, you can use the same Data Collector Sets configuration that you defined to monitor a data refresh. You can also use them to monitor the behavior of the server under a query workload that is generated by the users.
Profiler events for monitoring queries
You always use the same tools to capture trace profiler events from Analysis Services: SQL Profiler, ASTrace, Flight Recorder, and Extended Events. However, you need to be very careful to keep these recording systems active on a production server because they might affect the performance perceived by the users. The number of events generated by a process operation is smaller and usually does not have a big impact on the overall duration of monitored operations. This is because a single-process operation usually requires minutes, if not hours. A single query is possibly a quick operation, hopefully running in less than 2 seconds. You will find most of the queries running in only a few milliseconds. Adding an overhead to these operations could be very dangerous for the performance. For these reasons, we suggest gathering the minimum number of events, such as one event per query, to retrieve the user, time, duration, CPU, and text of the query.
In practice, you should capture only the following trace profiler event during queries:
Query End (10) The only subclasses you should consider are MDXQuery (0) and DAXQuery (3). You might observe a SQLQuery (2) subclass event; it shows DMV queries that are usually not useful for monitoring the server’s activity.
By collecting these events, you can analyze how many queries have been generated by which users and accurately identify slow-running queries. Having the DAX or MDX statement available in the log enables you to replicate the query behavior in an environment where you can use more diagnostics, and you can then optimize the query. For example, DAX Studio provides an environment that automatically collects detailed trace profiler events that are helpful in the optimization process. It would be too expensive to collect these events on a production server for every single query. You might see a trace volume increase of two orders of magnitude doing that. For this reason, intercepting only Query End is a suggested practice if you want to monitor queries running on an SSAS Tabular server.
Summary
In this chapter, you learned how to monitor an instance of SSAS Tabular by collecting the performance counter and trace profiler events to locate bottlenecks in queries and processing operations. You saw how to use tools such as Performance Monitor, Data Collector Sets, SQL Profiler, and Extended Events. Now you know which counters, events, and DMVs you should consider, depending on the analysis you must perform. For a production server, you should consider a continuous data-collection strategy to find bottlenecks in data-refresh tasks and to locate slow-running queries in the user’s workload.