SQL Server Analysis Services Flight Recorder

By: Siddharth Mehta

Overview

We are at the last chapter of this tutorial, and we are continuing our discussion on SQL Server Analysis Services (SSAS) performance monitoring where we will discuss the Flight Recorder and its use in monitoring SSAS performance.

Explanation

In earlier chapters when we discussed logging, we saw the Flight Recorder related properties at the instance level. The below screenshot may help you recall the properties.

The Flight Recorder continuously captures Analysis Services activity while the SSAS service is on. It is enabled by default and can be configured using the properties seen in the above screenshot from SSMS. Flight Recorder creates a trace file for each run of the service, which can later be used by Administrators to perform simulation of the entire recorded activity trace. This ability to perform recorded simulations can help admins to recreate the scenario that one needs to analyze, which can be an extremely helpful tool to recreate the set of events in the actual order. So even if one missed collecting the required parameters for performance analysis, using Flight Recorder the same can be regenerated and the performance of the server at any point in time can be analyzed using the available performance monitoring tools.

To use the trace file created by the Flight Recorder to replay server activity, follow these steps:

• Open SQL Server Profiler, and open the trace file created by the Flight Recorder, located by default at C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Log and named FlightRecorderCurrent.trc.
• On the toolbar select Replay -> Start
• On the Connect to Server dialog, enter the server name and authentication information.
• On the Replay Configuration dialog, set up the playback features of interest within a given time frame.

Summary

We looked at different stages of SSAS, where an Administrator would start his journey by installing SSAS all the way to the phase where the SSAS admin would stabilize the server performance and establish a standard monitoring process. SSAS administration is a vast area and can only be improved by hands-on practice and by working with various kind of issues that are generally faced during different activities on the server.

SSAS requires you to learn new concepts and unlearn some old ones. It also requires an Administrator to think a bit more like a developer than you may be accustomed to. Learning more about the use of SSAS as an overall solution and how the development team makes use of SSAS during the development phase, an Admin can bring the right balance between business critical server performance as well as facilitation of project critical development requirements. I hope this tutorial provides a launching pad to developers and Admins who want to start their journey on SSAS Administration.