Server side tracing for SQL Server Analysis Services

Question

We are looking to continuously run server side trace on Analysis Services and record its output in a table in database in the Database Services.

There are tons of articles showing server side trace for Database Services but I have yet to find one for Analysis Services.

Any suggestions on where to start recording and automating server-side traces for Analysis Services to run always?

Answer 1

maybe you can setup the query log in the ssas instance properties.

enter image description here

• soccer7: Thanks, I will look into this. As I keep reading about tracing, would Extended Events help to run server side tracing on analysis services?

Answer 2

It depends on what kind of data you want to Capture.

I've just set up tracing as @MBuschi has suggested, but IMO it's pretty limited. To enable it I reccomend you create a small database for it to write to, and change the flag show above for "CreateQueryLogTable" to true. You also need to provide the connection string to your logging location, and the login you provide needs enough credentials to create the table. Then just restart SSAS. It should create the table and start logging for you. Here's the offical doco

I've also been playing with Extended events, and learned how to start and stop a session via script. Extended events give you a lot of configuration flexiblity to target specific technical details.

The most useful thing i've found so far are the flight recorder files stored in your SSAS Data or log directory. (Maybe I just prefer the layout of them). However these files overwrite themselves. So although they are useful for diagnosis, they're not ideal for long term logging.