Running a profile trace in the background

We want to record accurate usage statistics per user of our OLAP database by logging the queries into a database table - the profile trace give us exactly what we want (user ID, domain, duration, CPU time) except it means actively running the app on the server.

Is there not a way to run it in the background as a scheduled job or service (or something) and writing it into a database table for later analysis?

Ultimately we want to use number of queries or CPU time to change the system out to those who are actually using the system. So monitoring accurately is important.

Hi Johannes,

This is an area I'm also looking into.

AS2005's 'Flight Recorder' functionality automatically runs a trace in the background and dumps the results to .trc files on the server (see C:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\Log) - they may not contain all of the data you're after, but they might well contain most of it. It should then be possible to load this data into a relational table and do some cool stuff with it...

If you (or for that matter anyone else who's reading this) is interested in discussing what the requirements for an app that monitored AS2005 would be, then please drop me an email. My contact details are available at http://www.crossjoin.co.uk/contact.html .

Chris

|||

Try also using the QueryLog server properties: you can setup a connection string to a relational database where queries will be logged. With SQL Management Studio, right click on the AS2005 server item -> Properties -> look for "Log \ QueryLog" entries.

More info here: http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/config_ssas_querylog.mspx

(please note that there is a current issue with the QueryLog: once it has a connection string setup, future runs of the Server Properties dialog will toggle starting/stopping the log; this problem is being fixed in the next SP, meanwhile the work-around is to not run directly the server properties dialog, but to script it and then remove the QueryLog properties - that is for future runs, not for the initial run when you setup the query log for the first time)

Adrian Dumitrascu