SQL BI

My Experience - Troubleshooting SQL BI Stack

SSAS Monitoring Tool– ASTrace

SSAS Monitoring Tool– ASTrace

  • Comments 3

Guys, We have noticed customers asking a tool for  Monitoring SSAS Activities;  So we have added new features to an existing ASTrace of SQL 2005 and introduced a new version for SQL 2008/R2 & SQL 2012(MOLAP and Tabular).

With this tool you can monitor minimum to maximum SSAS Information.  Architecture is pretty simple, ASTrace runs as a service on any server which got prerequisite installed (as stated in documentation) -> collect trace information based on trace template for an SSAS Instance stated in configuration file (you can customize and use your own trace template) and finally pushes trace data into a SQL Table enabling you to query this SQL Table and pull required information.

You can customize Trace Template, SQL Table and Database Name. Documentation will provide you an idea of enhancements which we had done in  new version of ASTrace.

 

AS Trace2008 (SQL 2008 / R2)
AS Trace2012 (MOLAP / Tabular)

Caveat- By design

Restarting ASTrace Service –

By design, ASTrace will stop auditing whenever you restart the Analysis Service instance. Therefore, any time that you restart Analysis Services you will also need to restart the ASTrace service.

When the ASTrace service restarts, the service automatically writes the content of the existing table, ASTrace, to a new table with a timestamp. For example:

Old traces

ASTraceTable_Archive_Tuesday, January 31, 201212:56:35 AM

New and current traces

ASTrace

This workaround is required because trace files are always truncated by the function, InitializeAsWriter. Unless you take action to save the existing trace information, when the service restarts, all previous trace information would be lost. To preserve the trace information prior to the service restart, the ASTrace utility programmatically creates a table, adds a timestamp, and writes any existing trace to the time-stamped table. This lets you maintain a history of events prior to the service restart.

This trigger enables you in collating the result of all ASTrace table and you can run a SQL query for pulling information against single table.

Feel free to ping me if you need any assistance.

Feel free to ping me if you need any assistance.

Disclaimer

These is a sample tool and will not be supported by CSS.

Comments
  • Hello.

    I downloaded AS Trace 2012, checked the documentation and found small mistake in file "Capture AS Queries.tdf".  This template was created for MS SQL Server 2008 Analysis services, but it stored in archive for AS Trace 2012. I Created my own template, but you can replace file in archive with correct.

    And very thanx for this tool!

  • Does this put a huge strain on the Server? running a trace load in the background might put a heavy load on?

  • hi

    do you have information about how consume the information save in the table, i mean the info is saving on the table but how i can to identify some problem in a easy way.

    thanks a lot

Page 1 of 1 (3 items)
Leave a Comment
  • Please add 1 and 7 and type the answer here:
  • Post