Poor performance in Audit Reporting

[kmuller]

I have a 8v2 Instance of Cognos BI running in a Applciation cluster with 2 Gateways, 3 Applciation servers, and a dedicated sql box for our content store.  This instance has been running full steam for 14 months with some days having 6000 report runs.

My audit report for current day runs is getting really slow.  This is a report that filters the run data down to the current day and displays it.  The report that took 10 seconds 6 months ago now runs for over a minute.  This report is a part of a Admin Audit dashboard.

The audit d/b is only 10GB, has been indexed via cognos support recomendations and was migrated to the dedicated SQL 2005 last month.

Has anyone seen this type of slowness in their audit reporting?  If so, have you found a fix?

[jue]

Yes, we had exactly that problem when we went Live 4 years ago. I can't remember our daily load at the time but we have grown since then and are now churning out around 3 millions reports a month. I distinctly remember the Audit database becoming virtually unusable in a very short space of time - queries taking a very long time to execute and the audit database started running a day or 2 behind real time because events were being queued (couldn't get added to the DB quickly enough) and it affected system performance.

The fix? We have 8 UNIX servers in our Live environment running 18 Cognos components (mainly report servers). We send all logging information to a dedicated Cognos service (separate Content Store) whose only purpose is to write logs to file system (uses the remote logging function via TCP/IP in Cognos Configuration). We have have a custom java process (very simple in reality) that loads those Cognos logs into a separate database every time a new file is generated. We then report from that database using a simple Frm Mgr model and reports. It's fast, reliable, customisable and we can run logging across all components without affecting overall service performance.

I was about to reinvestigate the latest Audit offering to see if things have improved. I might not waste my time now !!!!

[goose]

The audit db is not great out the box I would say do something similair to what jue recommends. If logging itself is not slowly your system down then simply create an SSIS job to pull the data you want into another schema on another instance. Make sure its properly indexed and you do all the report name etc substring crap when you import and you should be hundreds.

[rajwarrier]

This is Raj. you can email me at tech_aid@yahoo.com

Did you try keeping logging as bare minimum in your settings.

Done in the past and it improves. Did you try running the sql from the report seperately in SQL Navigator or any sql tool to see if it works well there.

On a seperate note:
What was the installation sequence you followed for your installation of
2 Gateways, 3 Applciation servers, and a dedicated sql box

thanks
Raj

[kmuller]

What I was create a view on the db to display only the current day info in the view.  We then tuned the db a little and were able to get great performance.

I would recoment anyone looking to improve tuning do this or archive off old autit data.

sk