Cognos, Win NT, SQL, Defrag 2

[donafran]

Hello!
Here's our story: Cognos Version 6.x of everything on a WIN NT machine, using SQL server database.

About a month ago we started noticing things like Decision Stream taking twice as long as it had the week before, Cube build times, Impromptu reports, etc running more slowly.

In 3 years, we ahd never Defrag's the server. We loaded acquired and loaded Diskeeper and not only changed our hard drive fragmentation from 43% to less than 1%, we run the boot time defrag tools for the pagefile, directories and the MFT.

We ran these multiple times for increased results.

However, after all that, we have found no noticable difference in performance.

Our next step is to llok at dropping and re-creating the indexes on the SQL table.

Anyone out there have any other ideas? Or is there something we missed in the defrag process.

Thanks

 

[techsmith]

No rogue processes clogging up the CPU on the server? A bit obvious, but I missed one a few months ago and had similar problems.

 

[donafran]

Thanks for your suggestion - but no rogue processes that we can find -- it was one of the first things we looked for. We've even gone so far as to turn off services, reboot the server, double check that those services are NOT started, and submitted the job at this point. No difference -- we still have a very long run time. Seems to be getting worse as time goes on..much more than the increase in data would indicate.

 

[griffindm]

Donna,

Is DiskKeeper's real-time defrag option turned on? If so, I would turn it off and only run defrag periodically. It may be part of your slowdown. Don't know for sure, but it's worth considering as it was the principal variable between your prior performance and current slowdown.

Give it a shot.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[donafran]

Well, yes and no ===> we encountered the slow down in mid-April for no apparent reason. All the research we did seemed to point towards a defrag. We just installed and ran Diskeeper over July 4th weekend. We have seen no performance improvement since then. Although, this Sat our Rebuild job ran longer than it ever had before..and based on your suggestion, I found that Diskeeper is set to continuous. So that explains the unusual increase from last week to this week, but it still doesn't help with the problem that led us to Diskeeper in the first place.

 

[BruceReed]

Is the orginal SQL app slower? Did you defrag the source database or just the Cognos end of things?

Your own suggestion to re-index the source database sounds like a good idea especially if you have not done in a long time and/or lots of daily data gets added.

Where I used to work the source database got to be over 50G; when IT re-indexed, and archieved the OLD junk both Cognos reports & the ERP system response improved.

Good luck
Bruce

 

[donafran]

We defrag'ed EVERYTHING !! It is the original SQL application -- and one specific table to be exact that is causing the biggest slow down, but all other SQL jobs are noticably, if negligible, slower also.
Thank you for your input -- I think we will try a manual drop and re-create of the index -- if that has visible results, we will try to automate the process so it runs periodically.

I can't think of anything else that would cause this type of problem.

 

[griffindm]

Dona,

Are you updating statistics on the table nightly? This could help your performance as well, though dropping and recreating the indices would be just as effective.

Dave G.


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[donafran]

Dave,
This is a question we have been confused on before -- would your recommendation be to UPDATE statistics every night or to NOT update statistics every night ?

 

[BruceReed]

One more thought...

Have you checked out to see if it could be a HARDWARE problem?

Not sure what else to tell you, I'm not a SQL expert.

Bruce

 

[nagrajm]

I would look at the following when I notice slowdown;

1. It is advisable to update STATISTICS atleast once a week usually on a weekend when the system is available for such maintenance. Depending upon the size of the database, STATISTICS update could take a while.

2. Relook at key indexes. Do you have indexes on all KEY columns including those on columns that are used for JOIN conditions.

3. Make sure you have enough TEMP space set aside for the database and bounce the database, if possible, every night.

4. Look at the possiblity of archiving the data, if large database size is an issue.

5. Look at the possiblity of splitting the cubes into "Historical" and "Current". Historical cubes will contain data from start till the end of previous year. The current cubes will contain data for previous and the current year. The historical cube can be built once a year at the end of every year whereas the current cube would be rebuilt every day. This will reduce considerably the cube build times going forward.

6. If the query building the cube has multiple select statements in the form "SELECT....FROM SELECT....FROM SELECT...", this will degrade performance going forward everyday. Impromptu produces an IQD in this form when the query contains complex summary functions in the summary filter. The performance of the report will degrade in geometric progression as the result set of data from the detail filter condition increases naturally from day-to-day.

Nagraj