iTel Reporting for contact Centre - Slow Historical Reporting - SQL Issues

[Crimsondelo]

Hi all/Anyone;

I have an issue, when running reports in itel - RCC the reports can take upto 15 mins to run. Here is what i have discovered

* The CCRDB hammers the Hard Drive during this process
* The Database indexes were heavily fragmented
* I have been unable to move CCRDB to another Hard drive due to the RCC Install Verification tool detecting that the Database has moved and re-creating it.

Here's what i have done to address this:
* Created a Database stored procedure which is called daily to 'defragment' the indexes.
* Moved the tempDB to another hard drive
* altered the ReportServer$RCCSQLEXRP & ReportServer$RCCSQLEXPRTempDB to not autoclose
* Turned off pagefile on c: and turned on on the other Hard drive (don't just turn this off as you will break your machine)

The Issue:
* Reporting is still slow
* Disk is still getting hammered

Before I buy a machine which supports RAID and I can address the issue with a hardware solution does anyone know if it is possible to move the CCRDB database to another hard drive and not have the RCC IVT create the database?

Regards

James

 

[SupportDude]

Crimsondelo,
What version is the BCM?
What version is the RCC?
What is the current size of the CCRDB?
What is the processor speed on the PC hosting RCC?
How much RAM is installed on the PC hosting RCC?

-SD-

http://www.mysupportguy.net

 

[Crimsondelo]

Q. What version is the BCM?
A. BCM450 R6 (Build ID: bcmsusemfw60ad06)

Q. What version is the RCC?
A. V5.0.451.4

Q. What is the current size of the CCRDB?
A. 3.5GB

Q. What is the processor speed on the PC hosting RCC?
A. Intel Core i5-2400 @ 3.10GHZ

Q. How much RAM is installed on the PC hosting RCC?
A. 8GB

 

[jaspertel]

I would save the old database, replace with a fresh database and test. Sound like the
database may have some corruption. Also the old dbfixutil should still run if you want to
give that a try. Always save the database files before attempting.

 

[Crimsondelo]

When I attempted to move the Database to another Drive it actually detected an error and rebuild the Database.

Surely also, if I rebuild the DB then I would lose all the data stored in it? Or does it just pull it back down from the BCM?

 

[SupportDude]

@Crimsondelo ...
The BCM only stores 16,000 calls, so you won't get all your call records back.

The DB is rather large, but SQL should be able to handle it.

Avaya released a patch earlier this week, SU Desktop 004, that will update the RCC to 2.6.420.1.61.53.13.
I would suggest installing the patch on the BCM, pulling the updated version of RCC from the BCM to the webhost PC, then installing it and see if it improves.
If not, you might want to take a look at purging some of your database.

-SD-

http://www.mysupportguy.net

 

[Quirkafleeg]

Hello,

Reverting from v5.0 to v2.6 will not probably help. If v5.0 has trouble handling your data, then multiply the time by approximately 3 and that is how long 2.6 will likely take.

Other questions would be:
1) Which reports are you generating - is the slowness on all or just certain ones?
2) How many skillsets are you generating reports for?
3) How many agents are in each skillset (rough average)?
4) What is the report period (hours, days, weeks)?

An important point to mention - SQL Server Express only allows databases of sizes up to 4GB. Your database is close to the point where it would be splitting. In fact, it might be at the point where it IS trying to split. The hard disk churning could well be caused by RCC trying to split the database, but failing. This processing could be clogging up all other database activity.
This appears to be a problem in 451.

 

[Crimsondelo]

Q. Which reports are you generating - is the slowness on all or just certain ones?
A. All Reports are slow, Agent Activity by Agent though is by far the slowest

Q. How many skillsets are you generating reports for?
A. I would only run the report for one skillset at a time

Q. How many agents are in each skillset (rough average)?
A. Approx 15 members per skillset

Q. What is the report period (hours, days, weeks)?
A. I would generally run the report for the previous day anything more without running the script against the Database would result in the report timing out.

------------------
In regards to the size of the database, the disk churn only happens when reports are run. outside of this the disk is quiet. What would the possibility be of upgrading to 2008 express? Would this break the reporting? Has anyone done this successfully?

 

[Quirkafleeg]

Hello,

It is expected that the Agent Activity By Agent would be slower - but I would expect the Agent Audit to be the slowest.
In terms of the reports, you are generating against a minimalist set of data. I asked those questions, because it is normally the number of skillsets and agents that causes slowdown, not number of calls.

The benefit SQL Server 2008 Express gives, compared to 2005 is expanding the maximum database size to 10GB. It is still limited to 1 core of the CPU. RCC will split the database at around 3.7GB anyway.
If you wish to upgrade, you do have to keep 2005 Reporting Services. Microsoft have designed the SQL Server so that there is no compatibility when it comes to populating/rendering RDL files (report definition layout) - which is what RCC uses.

The best short term solution would be to have the database split, so you "start again". You will still have your historical data in a 2nd database, but it would still take a long time to generate reports on that data. New data should be back at a more acceptable speed. Further examination of the database via your support channel would be required to see if a resolution could be found.