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.
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...
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.
19 People are following this question.
