you need to determine if sql is the bottle neck. it is not enough to say that reports are taking a long time it must be sql's fault. I have monitored reports previously and found that the bottle neck was in cognos. sql returned the large result sets quickly but cognos took forever to union the result sets and then sort the result set..
questions
1) has the execution time increased or has it always taken too long?
2) if execution time has increased - has anything changed recently: new version of sql, new sql server, new version of cognos, new cognos server, merge with another company, etc.
3) is your sql and cognos server the same? are they in the same building. what is the ping time between them.
4) does the report take a long time if the output is html versus report.
5) do even simple reports take a long time. or is it only complex reports or reports with a lot of data returned (or a lot of data that must be summarized or totaled)
6) is there any updating of your data during the day? could something be blocking the data that the report is trying to read? sp_whoisactive has a blocking column.
I would pick one query/report that is taking too long. try to make it not too complicated and with only a single query if possible. from within cognos generate the sql and then run that sql in sql server ssms query window. how long did it take to complete? (realize that if there is a large result set the most time may be spent in actually displaying the results in the ssms results pane. you can go up to tools and change the query window to run the query, but not return results. this is a more true way to test the execution of the query.) .
So if the query took a long time in to execute in sql then run the free download sp_whoisactive in another ssms query window while the query is running and then click on the link in the plan column. use the plan to find out what is the bottle neck of the query.
if the sql query is quick or quickish to execute then your problem is probably not sql. again use sp_whoisactive to monitor the Cognos report total elapsed time and how much of it is actually sql. Using sp_whoisactive (repeatedly hitting F5 for execute), run the cognos query and see when the query starts to run in sql and when it finished in sql. Then how much longer does it take to produce the html version of the cognos report. you can monitor the cognos temp folder and see if there is a temporary file that is growing and growing in size. this temporary folder (google it) is the cognos work space. if there is a lot of cognos processing that has to be done on a large sql result set you should see a temporary file in this folder. I don't know much else about how to monitor cognos processing of data. But anything in the cognos report that you can move from cognos to sql would improve performance. cognos is not a database. Try removing any after query processing and see how that affects total elapsed time. Change a copy of the cognos report to only have the query, no sorts, group by, summary or total lines, etc. How did this affect your elapsed cognos report time?
Of course the issue could also be the connection between your sql and cognos server, especially for large result sets. Not having adequate disc space. Heavy load on your sql or cognos server. Not enough memory. Poor disc I/o latency. and on and on.
exec sp_whoisactive @get_plans = 1, @get_locks = 1, @get_task_info = 2, @get_additional_info = 1
------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------
Original Message:
Sent: Tue July 14, 2020 12:35 PM
From: Elihu El
Subject: SQL Server Configuration
Hello,
I hope that you day is going well. We have SQL Server version 17. Our Cognos queries are taking too long to execute. What configuration changes should we make to SQL Server Management Studio to best improve the performance of Cognos queries?
If you have any questions, do not hesitate to call me at 410-312-8822.
Warm Regards,
Eli
#CognosAnalyticswithWatson