June 30, 2005 at 9:19 am
Hello All...
We are running SQL 2000 w/service pack 3A, Crystal Reports 8.5 , and CrystalDesk Scheduler. Here is the problem,...CPU usage. My CPU usage continually spikes from 50 to 100 when running reports. Looking at the task manager, the process that is using it all is SQLserver. I have run consistency checks with DBCC commands and am at a loss. THere are no error logs within SQL or the machine itself. THis is obviously effecting our report production.
We run 22 reports daily, very basic reports. They each now take anywhere from 5 to 10 minutes per report. Unacceptable. If I restart the server and then run reports I can get 3-5 reports out at approx. 1 minute each then they go up to 5 - 10 minutes.
I am looking for any questions to alleviate these problems. Thank you.
June 30, 2005 at 9:40 am
Andreij,
Crystal will simply pass on your SQL request to SQL Server. It has no in-built optimization process. There could be any number of reasons.
Perhaps you should be looking at;
1. Are the tables your reports are accessing in SQL Server properly indexed
2. Are the join conditions in you Crystal reports properly defined. (Auto-join can be a hazard as well! I prefer manual joins)
3. Are the number of tables used within reports too high (eg more that 6 and you should be looking at views so that SQL optimization is done at SQL Server box)
4. Consider using stored procedures as your data source for reports. They will be optimized by SQL server, and, allow you to do complex querying at the server level. They may also be parameterized.
5. The number of records being retrieved is too high (look to aggregate high level reports)
6. Too many sub-reports within a report?
7. Filters within a report are not defined. (Gets all records from the db)
Hope that helps
June 30, 2005 at 10:52 am
Are your reports using stored procedures as their source? If so this sounds like a classic situation where you need to recompile your sp's, maybe even alter them to include the WITH RECOMPILE option. Although I wouldn't do this until you are sure of the problem.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
July 1, 2005 at 10:10 am
SQL 101 --> A SQL Server is a dedicated machine. Dedicated to SQL Server and no other software.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply