Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-06-28 : 10:43:58
|
| Stu writes "I am running SQL Server 2000 with Crystal Reports XI. The majority of the time, reports that are ran through Crystal load relatively quickly, within a few seconds. However, there are many times during the day where the reports just hang, and get stuck on the "Accessing Database" status for up to 3 or 4 minutes. Very often I would run a report for May, for instance, and it would load instantly. About 30 seconds later I would run the same report for June, and it would take up to 3 or 4 minutes for the same report to load. I have tried copying the SQL Query from Crystal and pasting into the SQL Query Analyzer, and this, too, will take 3 or 4 minutes to process. However, when the reports in Crystal load quickly, the Query Analyzer loads it quickly as well. I ran a trace using the Profiler, and when Crystal hangs, the only processes that appear to be running are "SELECT N'Testing Connection...'" and "EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters" from the SQL Agent - Alert Engine Application. These two processes keep repeating in the Profiler 5 or 6 times before the report finally loads, but I don't seem to get any other messages in the Profiler.The SQL Server is a Single 3.0 GHZ Pentium IV Processor with 4 GB of RAM and 100 GB of free disk space, running Windows 2003 Server. The SQL Database itself is about 8 GB in size. The server is set to auto-shrink the database, and is scheduled Any ideas as to what's going on here? Any suggestions at all would be greatly appreciated!" |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-06-28 : 11:16:00
|
| I have not used Crystal since version 7, but there could be few things going on. 1. Did you monitor T-SQL and Stored Procedures on the SQL Server with Profiler?2. Is there anything else running on the server like IIS or exchange? Have you looked at the Task Manager on the server during these slow downs?3. Have you checked the network traffic during these slow downs?4. Have you tried converting the query to a stored procedure and calling that from Crystal?Sean RoussyPlease backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.I am available for consulting work. Just email me though the forum. |
 |
|
|
John_David
Starting Member
1 Post |
Posted - 2006-03-22 : 01:23:35
|
quote:
Originally posted by AskSQLTeam
Stu writes "I am running SQL Server 2000 with Crystal Reports XI. The majority of the time, reports that are ran through Crystal load relatively quickly, within a few seconds. However, there are many times during the day where the reports just hang, and get stuck on the "Accessing Database" status for up to 3 or 4 minutes. Very often I would run a report for May, for instance, and it would load instantly. About 30 seconds later I would run the same report for June, and it would take up to 3 or 4 minutes for the same report to load. I have tried copying the SQL Query from Crystal and pasting into the SQL Query Analyzer, and this, too, will take 3 or 4 minutes to process. However, when the reports in Crystal load quickly, the Query Analyzer loads it quickly as well. I ran a trace using the Profiler, and when Crystal hangs, the only processes that appear to be running are "SELECT N'Testing Connection...'" and "EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters" from the SQL Agent - Alert Engine Application. These two processes keep repeating in the Profiler 5 or 6 times before the report finally loads, but I don't seem to get any other messages in the Profiler.The SQL Server is a Single 3.0 GHZ Pentium IV Processor with 4 GB of RAM and 100 GB of free disk space, running Windows 2003 Server. The SQL Database itself is about 8 GB in size. The server is set to auto-shrink the database, and is scheduled Any ideas as to what's going on here? Any suggestions at all would be greatly appreciated!"
|
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-22 : 01:51:16
|
| "The server is set to auto-shrink the database, and is scheduled"If this runs at the time your report does it will cripple the performance. Its also a bad idea other in exceptional circumstances.Pls seehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Database%20Shrinking,Shrink,Shrinkingfor details of why shrinking is a bad idea!"The SQL Database itself is about 8 GB in size"If the database is set to Auto Extend by a percentage (the default is 10% I think) then that will be 800MB on each extension. That will take a significant amount of time, and will slow up all access whilst it runs. If you DO have a percentage I suggest you change it to a fixed size instead - perhaps 50MB. Until we changed this the 10% thingie killed our servers for about 30 minutes when it kicked in!Kristen |
|
|
|
|
|
|
|
|
Occasional Poor Query Performance