Welcome to the second part in my series on Azure Analysis Services monitoring. In part one, I covered what makes up the Analysis Services engines (Tabular and multi-dimensional) and how each engine processes queries, as well as monitoring tool options.
In this session, I’ll review how to use the SQL Server Profiler Trace to capture data to evaluate events which gives you the ability to monitor server and Analysis Services database activity. SQL Server Profiler is installed with SQL Server Management Studio (SMS).
Use SQL Server Profiler Trace to:
- Monitor the performance of an Analysis Services instance
- Debug queries
- Identify poorly performing queries and test query statements in the development phase
- Troubleshoot problems
- Audit activity
- Display data on captured events
Some things to remember when using SQL Profiler:
- Only database events are available for a Power BI Premium workspace, server events are not available
- Multiple traces can be running at the same time
- Multiple connections can receive events from the same trace
- Passwords are not revealed in trace events, will be replaced with ****
- For optimal performance, use Profiler for those events you are most interested in, as monitoring too many events can cause the trace file or table to grow very large
- Use filtering to limit the amount of data collected to prevent overly large traces
My video below goes much more in depth and covers things such as: an overview of trace events and a demo that walks you through using SQL Server Analysis Services (SSAS) Tabular and Azure Analysis Services Profiler Trace.
Part 3 of my mini-series on this topic will come out next week. If you have questions or want help with any Azure product or service, our team of experts and MVPs are here to help. Our consulting and managed services offerings will help you use your data to grow your business and take it from good to great.