Determining if slowness is caused by Cognos or the database

[Tim86]

We're having performance issues with our Cognos reports, and trying to isolate if it is a Cognos issue or a database issue.  Many reports across the board started running slower a few weeks ago, and after enough complaints we looked at the Audit reports and confirmed a distinct point in time where many reports started running much longer. Anyone have some good tips to isolate if the slowness is coming from Cognos versus the SQL Server 2005 database?

The server is Windows Server 2008 R2 Standard 64 bit.  Cognos version is 10.1.

[Rahul Ganguli]

May be you migt have scheduled lot of reports in that timeframe.

[simon.hodgkiss]

Hi Tim,

nagoole had a similar query in this thread:

http://www.cognoise.com/index.php/topic,21088.msg64012.html#msg64012

If the queries are going to SQL DBs then you could try SQL Server Query Actual Execution Plan (if you can afford to run the query in MSSQL Server Management Studio), or Display the Estimated Execution Plan if you would rather not.

All the best,

Simon

[Tim86]

Thanks.  I forgot to followup with our outcome on this issue.  We determined that the slower reports all shared a particular table in the database.  Reports that didn't use that particular table were not experiencing the slowness.  So it seemed to be a database issue, not a Cognos issue.  That table had outdated statistics, so we rebuilt the indexes, and everything went back to normal performance again!  FYI...this was a very tiny table (less than 200 records).  I had made the mistake of ignoring that table as a possible culprit, since it was so small.  But lesson learned...even a tiny table can have significant impact on SQL Server's query optimization if the statistics are sufficiently outdated.