Question about using ASTrace service against a tabular instance/model...

Mar 25, 2013 at 8:34 AM
Hi,

I think I've got this installed and running correctly. When I browse my a tabular model in ssms, every field I drag onto the query pane results in a new row added to the ASTraceTable table.

But the columns in ASTraceTable aren't human-readable, by contrast with what I see when I run SQL Profiler.

The events and associated data mentioned in the ASTrace documentation, like QueryEnd, with its query text and so on, I see those in SQL Profiler trace. But in ASTraceTable, all I see are columns like EventClass, EventSubclass, IntegerData, and so on.

So my question is: how do I transform the data in the ASTraceTable into the more human readable version that SQL Profiler provides? What am I missing?

Thanks much,
sff
Developer
Mar 25, 2013 at 2:09 PM
I'm not sure if you can open what's saved to ASTraceTable in Profiler. The goal is just logging it to a SQL table without Profiler being the tool that's logging to the SQL table, since there's a cost for that GUI being open.

Generally people use ASTrace to log important trace events to a table. For example, you can log the Query Begin event and capture the MDX queries that are being executed.

To interpret it, I would either run a Profiler trace simultaneously and then compare ASTrace and Profiler to interpret what the numbers in ASTraceTable mean. Or you could also look at the following file (slightly different path depending on your version and instance name) to figure out what EventClass and EventSubclass mean:
C:\Program Files\Microsoft SQL Server\MSAS11.TABULAR\OLAP\bin\Resources\1033\tracedefinition110.xml
Mar 26, 2013 at 5:02 PM
Thanks for the reply!

So if I wanted what is essentially the trace profiler info (what query ran when and by whom), logged to a database for usage statistics collection purposes, what would you recommend?

thanks again,
sff
Developer
Mar 27, 2013 at 2:59 AM
I would use ASTrace and make sure you use a .tdf which just requests the minimum events and columns you need. I think you're going down the right path.