question

amfineandyou avatar image
amfineandyou asked

Table scan with cognos report

Cognos application is slow generating reports from SQL Server and I can see APP is selecting all the columns from the table, the table is huge with 20 gigs and ,Someone created Non-unique,non clustered Index for four columns on the table prevoiulsy.So the query is doing a Table scan with 100% cost The query is waiting with ASYNC_NETWORK_IO and in suspended state. Any suggestion on this.
sql-server-2008sqltablesindex-performancecognos
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
ASYNC_NETWORK_IO isn't generally a SQL server problem, but an application problem - the application has generated a resultset, but hasn't consumed it all. See Paul Randal's SQLSkills Waits Library entry for this wait at https://www.sqlskills.com/help/waits/async_network_io/ As to the rest of it, difficult to know what to suggest without seeing the query (there may be some indexing optimisations), or how your Cognos environment is set up, or how your report is built/defined. And would definitely need someone with more Cognos expertise than me...
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

kevaburg avatar image
kevaburg answered
I wouldn't advise doing anything until you speak with the vendor otherwise you could invalidate any Support Agreements you have running. If you are doing a full table scan then you have a missing index and that is the theme that you need to speak to them about. Try restoring the Cognos system on a test machine and add the index that you Need before testing the Performance again. With this information you can approach the vendor with authority and explain to them not only what you need but also how to achieve it.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.