SSAS Usage Totals

Use Relevant Metrics to Troubleshoot SSAS Problems

What is SSAS Usage Totals?

The SSAS Usage Totals tab in SentryOne SQL Sentry provides information that can be used to determine what responses to take based on activity levels. This information is divided into three different groups:

  • Attributes—used to see which attribute combinations are hit the most to determine if creating an aggregation would improve performance
  • Aggregations—shows the aggregations that are being used
  • Partitions—shows which partitions are being read the most

You'll find this feature in:

SQL Sentry

Do you need deeper data usage insights into your SSAS instance?

SSAS works efficiently with default settings right out of the box.

However, when you're dealing with factors such as large databases, many concurrent users, insufficient resources on your server, or a SSAS database design that doesn't follow best practices, you can run into performance bottlenecks and problems.

As a DBA, do you have enough detailed information on the data that is in use in your SSAS Multidimensional or Tabular instance? Can you find data, such as aggregations, that your SSAS Multidimensional instance isn't using, as these may negatively impact SSAS processing and storage?

If you're struggling to troubleshoot SSAS performance problems with insufficient information, take a look at SQL Sentry. SQL Sentry gives you the visibility you need to create and maintain a high-performance SSAS environment.

 

SQL Sentry SSAS Usage Totals to the rescue

 

For SSAS Multidimensional instances, SQL Sentry provides three tabs to help you analyze data usage.

Attributes

The first tab, Attributes, consists of attribute combinations—such as date, postal code, country, and so onin SSAS commands, along with information such as duration and the number of queries used in a particular attribute group.

You can expand each attribute group to see various queries along with pertinent information for each query, including its duration, CPU usage, Formula Engine time, Storage Engine time, and so on.

This information helps you find queries that might be good candidates for aggregations.

Aggregations and Partitions Tabs

The Aggregations and Partitions tabs in SQL Sentry provide, similar to the Attributes tab, information about their usage. You can use these two tabs to see if some aggregations and partitions are never being used.

Because aggregations can be expensive resource-wise, SQL Sentry helps you decide if a particular one is necessary.

This short tour of SQL Sentry SSAS Usage Totals dives into the performance metrics you can uncover about SSAS to troubleshoot performance.

Object Memory Usage

In SSAS Tabular instances, the SSAS Usage Totals shows you one tab, Object Memory Usage. You can view the objects that are using the most memory.  As you drill down, you can see how much of that memory is dedicated to the data and the dictionary as well as see a hierarchy or the relationships to other columns.

Cardinality

Another piece of information you need to successfully troubleshoot SSAS performance is the cardinality of your data. Cardinality refers to the number of unique values. The SSAS VertiPaq columnar storage can realize up to 10x data compression.

However, this rate depends on your data’s cardinality. Data with low cardinality (such as gender) will see the best compression. Data with high cardinality (such as invoice number) will see the worst compression.

What You Can Do with SSAS Usage Totals

  • SSAS Usage Totals supports Multidimensional and Tabular modes
  • Provides mode-specific detailed information on the data that is in use
  • In SSAS Multidimensional mode, helps you identify potential “pre-aggregating” sets of data to improve query performance and calculation times
  • In Tabular mode, surfaces essential memory usage details and tracks memory usage over time
  • And more

Ready to end your database performance problems?

 

Download a SQL Sentry trial

See for yourself how SQL Sentry can help you achieve optimal database performance.

Download Free Trial

Schedule a SQL Sentry demo

Learn more about how SQL Sentry can help you troubleshoot and prevent database performance problems. Register to attend a public demo or schedule a one-on-one session.

Schedule Demo

Learn more about SQL Sentry

Discover how you can quickly find and fix SQL Server performance problems with SQL Sentry, which delivers proven scalability, easy-access dashboards, and visibility across your entire database environment.

Learn More