Tuning for job performance

[cfsindorf]

I just started a new gig and they have their tuning all set at default. 90% of what they do are jobs that run from 5am to 11am. It has been so long since i have had to do any tuning so I really could use some help on where to start.

Thanks CFS

[bus_pass_man]

I am not entirely convinced that a certain four letter acronym is not apropos as you will need to do a certain amount of reading.

My advice is don't muck around with the existing stuff which you have inherited.

[cfsindorf]

The issue with leaving it as it is, is that its been this way for years as they only altered the Audit settings. I know there are way to help jobs run more efficiently just don't remember what they are.

[cognostechie]

The issue with leaving it as it is, is that its been this way for years as they only altered the Audit settings. I know there are way to help jobs run more efficiently just don't remember what they are.

Report performance is an interesting and challenging thing and it depends on what is actually causing it though there are only a few factors which cause it.

1> Absence of a Data Warehouse/Data Mart constructed with a star schema design - This is tough to do and since most companies have unqualified BI Managers, who know nothing about BI, so they cannot do it so they take the approach of making reports from the source system database which is always in a snowflake design. Making a Framework model over snowflake not only causes performance issues but also causes wrong data in many reports

2> Absence of Cubes - This is also tough for an unqualified BI manager and hence he/she decides to take the short-cut/easy approach of making reports from the snowflake schema of their source system

3> Bad report designing by a bad developer - This is the least possible scenario and is only found when the two scenarios mentioned above apply

Now to make reports with situation No 1 mentioned above - This will require multiple queries in the report joined to each other which is exactly what causes the performance issue. I have dealt with this in multiple projects and have been very successful in improving the performance (hours to seconds and minutes to seconds). Remove all joins from the report queries, hand write your own SQL and make sure to include all queries into that single hand-written SQL and all joins to be inside that SQL, create only one query in the report and use the entire hand-written SQL in that one query. Cognos generates the same report in seconds which was taking hours earlier !! Yes, believe it or not, that's the case. Cognos generates inefficient queries when it finds that there are joins in the report as well as in the package which is being used for the report. That's what causes the problem. I used this technique for re-making hundreds of reports for multiple clients and in all cases this strategy worked perfectly! The most horrible Framework Model I have seen in my career is the one made by Deltek for their Costpoint application. One report was taking 5 days to run and the same report now runs in 30-40 seconds. That being said, most BI developers will not be able to do this because they are not good SQL coders.     

For situation No 2 - Won't be possible for most companies because there are very few developers who know how to make cubes and more than that, very few who know MDX to use in the report.

You have to open each report and see what the problem is. In most cases, you will find scenarios No 1. In addition to this, if your reports are scheduled to export data in Excel then that's another reason because Cognos takes quite a bit of time to plot the data in Excel and that will also depend on how much data has to be plotted. Amount of data is directly proportional to the time taken by Cognos.
   

[cfsindorf]

But with all the settings that can be  used during peek and non peek such as job service connections and report service, low affinity connections and high affinity connections. There is bound to be a few that people have used to help the system along above the default settings.

[cognostechie]

Those settings are only to spawn connections and provide parallel processes etc. They will not improve report performance. This is the short cut way which is used by people who are not interested in making efforts to solve the problem. 

[dougp]

Maybe backup a bit...?

Are you noticing any performance problems?  Can you describe them?  Are they consistent?

[cfsindorf]

cognostechie said something that woke me up. I had a bad choice of words in the subject. The issue is not 100% the report but with all the jobs we run getting them to finish quicker. How can i get more jobs to run so more reports can run and the emails can go out sooner or their outputs be saved to the system?

[cognostechie]

Well, if you just noticed when the 1st job starts and when the last job finishes, that doesn't say a whole lot of what's going on. If there are few reports which take an hour to run then that will cause more time to be taken because other jobs will have to wait till those report finish. That definitely is a report performance issue and will result in jobs finishing sooner if those reports are re-written to improve efficiency. As I mentioned earlier, I have done lot of that. If that's the problem then send me a private message using the icon under my profile and I will see if I can help you out.

If reports are not a problem then you can install Cognos on multiple servers and schedule some reports on one and some on other servers. Content Store database will have to be synchronized so that all servers have updated metadata. One dispatcher uses one processor so you cannot have multiple dispatchers on one install if your server has one processor.

I am still suspecting reports are the problem otherwise it won't take 6 hours for the jobs to finish. How many reports do those jobs run in total?