Processing is very slow and rendering report in PDF is very slow. Need suggestions and a s

Hi,
I am trying to re-engineer a report that was originally developed in
Crystal 7 into SQL Server 2000 Reporting Services. The reason for this
is that we are moving to a new infrastructure that is running Windows
2003 Server. Crystal 7 is unsupported and it won't even install.
The report has one parameter: a string variable that can be up to 15
characters long.
The original report in Crystal 7 had two subreports each with a
grouping level linked to a master report with three grouping levels.
The first cut of this report in SQL Server 2000 Reporting Services is
producing the correct amounts. But processing and rendering this
report in PDF (this would be the preferred method) is taking forever.
I have no sub reports in this first design. I embedded sub-tables (two
with one group level each) with another table (with three grouping
levels). There are embedded lists inside these tables. I merged all
the rows together and embedded a list inside to position elements in
the report. Even rendering a row of data is taking too long.
Could I send this report (RDL file) to someone at Microsoft to look at
the design and give me suggestions on what to try next?
I am thinking on removing the groups. I just don't know how I would
link the master dataset with another dataset that is grouping the data
based on the parameter sent.
I have a real short time frame to get this to work and I am not finding
a lot of documentation on performance improvements. Once I correspond
with a Microsoft expert, I could discuss the design, data amounts, and
execution/rendering time frames.Do you have SP1 installed. There were significant improvements in PDF
performance with SP1.
To check version go to http://yourserver/Reportserver
It should be version 8.00.878.00.
If not, that is the first thing I would do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mossman" <tmosson1@.sbcglobal.net> wrote in message
news:1107912663.898526.219740@.g14g2000cwa.googlegroups.com...
> Hi,
> I am trying to re-engineer a report that was originally developed in
> Crystal 7 into SQL Server 2000 Reporting Services. The reason for this
> is that we are moving to a new infrastructure that is running Windows
> 2003 Server. Crystal 7 is unsupported and it won't even install.
> The report has one parameter: a string variable that can be up to 15
> characters long.
> The original report in Crystal 7 had two subreports each with a
> grouping level linked to a master report with three grouping levels.
> The first cut of this report in SQL Server 2000 Reporting Services is
> producing the correct amounts. But processing and rendering this
> report in PDF (this would be the preferred method) is taking forever.
> I have no sub reports in this first design. I embedded sub-tables (two
> with one group level each) with another table (with three grouping
> levels). There are embedded lists inside these tables. I merged all
> the rows together and embedded a list inside to position elements in
> the report. Even rendering a row of data is taking too long.
> Could I send this report (RDL file) to someone at Microsoft to look at
> the design and give me suggestions on what to try next?
> I am thinking on removing the groups. I just don't know how I would
> link the master dataset with another dataset that is grouping the data
> based on the parameter sent.
> I have a real short time frame to get this to work and I am not finding
> a lot of documentation on performance improvements. Once I correspond
> with a Microsoft expert, I could discuss the design, data amounts, and
> execution/rendering time frames.
>|||Yes, we have SP1 installed. Maximum amount of data brought back by a
parameter value is 20698. I run the query in query analyzer based on
the parameter value and it takes up to 24 secs. It seems I am having
problems when the record count is around 1000 or greater. Need to keep
the structure of the report the same as it was in Crystal but I need to
speed up the processing. I could look into re-engineering the query as
well. We have the RSExecutionLog process setup. Looking at the data
it seems a lot time is spent processing and rendering. By the way, the
TimeDataRetrieval, TimeProcessing, and TimeRendering attributes, is the
numeric data in milliseconds?|||Yes, we have SP1 installed. Maximum amount of data brought back by a
parameter value is 20698. I run the query in query analyzer based on
the parameter value and it takes up to 24 secs. It seems I am having
problems when the record count is around 1000 or greater. Need to keep
the structure of the report the same as it was in Crystal but I need to
speed up the processing. I could look into re-engineering the query as
well. We have the RSExecutionLog process setup. Looking at the data
it seems a lot time is spent processing and rendering. By the way, the
TimeDataRetrieval, TimeProcessing, and TimeRendering attributes in the
ExecutionLogs table in the RSExecutionLog DB, is the numeric data in
milliseconds?|||This is a 500 plus page report if I understand correctly. RS does all this
in memory and when you have grouping it definitely takes longer. As an
experiment I would try removing all groups (don't worry about what it looks
like, just see if this speeds it up substantially). If there is any work you
could put into a stored procedure I would do that as well. But, the real
issue could just be the shear amount of processing you are requiring to
render this. My philosophy is that reports are for humans to read. No human
is going to sit down and read 500 pages. RS is great if you want to create
reports that someone is going to use and look at. It is not good for large
number of pages (I've seen someone with a 1,000 page report as a
requirement). Where it shines is having on-line reports. I provide summary
reports and then the user does drill throughs to get to more detail about
the information they need. If you can I would look at the requirements and
see if it could be designed a different way.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mossman" <tmosson1@.sbcglobal.net> wrote in message
news:1107977412.708510.39290@.g14g2000cwa.googlegroups.com...
> Yes, we have SP1 installed. Maximum amount of data brought back by a
> parameter value is 20698. I run the query in query analyzer based on
> the parameter value and it takes up to 24 secs. It seems I am having
> problems when the record count is around 1000 or greater. Need to keep
> the structure of the report the same as it was in Crystal but I need to
> speed up the processing. I could look into re-engineering the query as
> well. We have the RSExecutionLog process setup. Looking at the data
> it seems a lot time is spent processing and rendering. By the way, the
> TimeDataRetrieval, TimeProcessing, and TimeRendering attributes in the
> ExecutionLogs table in the RSExecutionLog DB, is the numeric data in
> milliseconds?
>

Process Info (SQL Server Enterprise - Management - Current Activity)

Dear All
I have problem with my database server which running SQL server 2000.
The server running very slow. The worst case, to save a record required
more than 20-30 seconds.
Since this problem, I usually monitoring Process Info from Enterprise
Manager (Management - Current Activity), and I found a misterious
process as follow :
1. User: System
AccessTo: Master
Status: Background
Common: Task Manager
Waiting: >438 Million
2. User: System
AccessTo: Master
Status: Background
Common: Task Manager
Physical IO: > 51000
3. User: Administrator (Join domain)
Database: MSDB
Status: Sleeping
Common: Awaiting Command
App: SQL Agent Alert Engine
CPU Usage: > 16 Million
Anybody know about these condition? Does it normal?
Thanks
Michael
Mike
What is amount of memory?
Run SQL Server Profiler to see what is going on. (Blocking ,locks)
EXEC sp_who2 , see blkby column (If I remember well).
"Michael" <yapmichael2000@.gmail.com> wrote in message
news:1115718206.985119.318980@.o13g2000cwo.googlegr oups.com...
> Dear All
> I have problem with my database server which running SQL server 2000.
> The server running very slow. The worst case, to save a record required
> more than 20-30 seconds.
> Since this problem, I usually monitoring Process Info from Enterprise
> Manager (Management - Current Activity), and I found a misterious
> process as follow :
> 1. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Waiting : >438 Million
> 2. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Physical IO : > 51000
> 3. User : Administrator (Join domain)
> Database : MSDB
> Status : Sleeping
> Common : Awaiting Command
> App : SQL Agent Alert Engine
> CPU Usage : > 16 Million
> Anybody know about these condition? Does it normal?
> Thanks
> Michael
>
|||Thanks for your answer,
Amount of memory that be used by SQL Server is 1 GB.
I've check using sp_who, and on nothing on column blkby.
I think that not caused by Locks.
Note : After I restart the server, everything run well for a while. In
the worst case, the problem will be occured in 1 day, but usually
system running well within 3,4 days.

Process Info (SQL Server Enterprise - Management - Current Activity)

Dear All

I have problem with my database server which running SQL server 2000.
The server running very slow. The worst case, to save a record required
more than 20-30 seconds.
Since this problem, I usually monitoring Process Info from Enterprise
Manager (Management - Current Activity), and I found a misterious
process as follow :

1. User: System
AccessTo: Master
Status: Background
Common: Task Manager
Waiting: >438 Million

2. User: System
AccessTo: Master
Status: Background
Common: Task Manager
Physical IO: > 51000

3. User: Administrator (Join domain)
Database: MSDB
Status: Sleeping
Common: Awaiting Command
App: SQL Agent Alert Engine
CPU Usage: > 16 Million

Anybody know about these condition? Does it normal?

Thanks

MichaelMichael (yapmichael2000@.gmail.com) writes:
> I have problem with my database server which running SQL server 2000.
> The server running very slow. The worst case, to save a record required
> more than 20-30 seconds.
> Since this problem, I usually monitoring Process Info from Enterprise
> Manager (Management - Current Activity), and I found a misterious
> process as follow :
> 1. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Waiting : >438 Million
> 2. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Physical IO : > 51000
> 3. User : Administrator (Join domain)
> Database : MSDB
> Status : Sleeping
> Common : Awaiting Command
> App : SQL Agent Alert Engine
> CPU Usage : > 16 Million
> Anybody know about these condition? Does it normal?

These are system processes, and they be normal, particularly if SQL Server
has been up for a long time. I checked a production box, and while it
had lower numbers than yours, they were still big.

The most likely reason when a server appears to be slow is poor indexing,
poorly written code and fragmentation. For instance, when saving a row and
there is a poorly written trigger, this could make the INSERT statement
to take a long time. Blocking could also be an issue, and blocking can
also easily occur, if there are slow queries.

You don't say whether this is an application, you have control over
or a third-party app. But in any, case you need to analyse exactly
which queries that are slow. One way to do this is use the SQL Profiler,
and filter for operations with a long duration. Note though that from
duration alone, you cannot tell whether it was due to blocking or bad
performance. The CPU, Reads and Writes columns can give some hints about
this. (If they are low and duration is high, there was blocking.) You
can also use sp_who to see if you have any blocking, by looking for
non-zero values in the Blk column.

Once you have found the queries that are long-running, you can look
into improving indexes, and if possible also rewrite them.

You can also try running DBCC DBREINDEX on tables where you experience
problem. If you have fragmentation, you can get improvements.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your answer.

I've problem, as I wrote down, sometime to save a record required much
time.
When this happen, usually I restart the server, and then the problem
solved for a while. The problem will happen again within 10 day.
When the problem occured, on the Task Manager, Process Tab, SQLServ.exe
using more than 1 Gigabyte memory. And after restart the server,
SQLServ.exe only use about 450 to 500 Megabyte.

While the problem occured, there were difficulty to make connection to
the server (using Enterprise manager, Query Analyser, Application).
Usually an error message : Timeout expired.

About the application, I use visual basic to develop application.
And there were a few trigger, some of there were use cursor.
At this momenth, I have disable many trigger that used cursor, but
there a bit trigger which used cursor still active.

Would you like to give any suggestion?

Thanks very much

Michael|||Michael (yapmichael2000@.gmail.com) writes:
> I've problem, as I wrote down, sometime to save a record required much
> time.
> When this happen, usually I restart the server, and then the problem
> solved for a while. The problem will happen again within 10 day.
> When the problem occured, on the Task Manager, Process Tab, SQLServ.exe
> using more than 1 Gigabyte memory. And after restart the server,
> SQLServ.exe only use about 450 to 500 Megabyte.

That's perfectly normal. SQL Server grabs as much memory it needs and
can get. This memory is used for cache. So if SQL Server are kept running,
and there is no other activity on the machine, SQL Server should by
time have grown to use about all memory on the machine that the OS
does not need. (If there are other apps asking for memory, SQL Server
will yield memory.) Thus, a high memory consumption is no sign of
problem.

> While the problem occured, there were difficulty to make connection to
> the server (using Enterprise manager, Query Analyser, Application).
> Usually an error message : Timeout expired.

This one on the other hand obviously is a token of that something is wacko.
Do you get these problems also when you try to connect from the machine on
which SQL Server is running? If this works fine, one could suspect network
problems.

If not, it sounds like something is bogging down SQL Server very heavily.
This could be a poorly written query, but it also be an anomaly in the
server. Check what is in the SQL Server log at these occassions; there
might be some interesting messages. Particularly, I have one about UMS
Scheduler in mind. (A message that was added in SP3, but you are running
SP3 aren't you? By the way, SP4 is out.)

It could also be an idea to keep a Profiler trace running so you can see
what commands that are submitted and then try to correlate these commands
with the conditions where there server is not very reposnive.

Another check to make, just to rule out the more silly stuff, is that
you don't have any compressed database files.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Process Info (SQL Server Enterprise - Management - Current Activity)

Dear All
I have problem with my database server which running SQL server 2000.
The server running very slow. The worst case, to save a record required
more than 20-30 seconds.
Since this problem, I usually monitoring Process Info from Enterprise
Manager (Management - Current Activity), and I found a misterious
process as follow :
1. User : System
AccessTo : Master
Status : Background
Common : Task Manager
Waiting : >438 Million
2. User : System
AccessTo : Master
Status : Background
Common : Task Manager
Physical IO : > 51000
3. User : Administrator (Join domain)
Database : MSDB
Status : Sleeping
Common : Awaiting Command
App : SQL Agent Alert Engine
CPU Usage : > 16 Million
Anybody know about these condition? Does it normal?
Thanks
MichaelMike
What is amount of memory?
Run SQL Server Profiler to see what is going on. (Blocking ,locks)
EXEC sp_who2 , see blkby column (If I remember well).
"Michael" <yapmichael2000@.gmail.com> wrote in message
news:1115718206.985119.318980@.o13g2000cwo.googlegroups.com...
> Dear All
> I have problem with my database server which running SQL server 2000.
> The server running very slow. The worst case, to save a record required
> more than 20-30 seconds.
> Since this problem, I usually monitoring Process Info from Enterprise
> Manager (Management - Current Activity), and I found a misterious
> process as follow :
> 1. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Waiting : >438 Million
> 2. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Physical IO : > 51000
> 3. User : Administrator (Join domain)
> Database : MSDB
> Status : Sleeping
> Common : Awaiting Command
> App : SQL Agent Alert Engine
> CPU Usage : > 16 Million
> Anybody know about these condition? Does it normal?
> Thanks
> Michael
>|||Hi Michael,
I agree with Uri, something is a bit iffy with your server and you should
run the profiler.
If you don't have any jobs running I would sugest you stop then restart the
service that way when it re-starts it will start with a 'clean plate' then
your can put on your monitoring stuff. NB if you to have jobs running then
you run the risk of losing data.
However when it does re-start it should be a lot faster.
Peter
"Michael" wrote:
> Dear All
> I have problem with my database server which running SQL server 2000.
> The server running very slow. The worst case, to save a record required
> more than 20-30 seconds.
> Since this problem, I usually monitoring Process Info from Enterprise
> Manager (Management - Current Activity), and I found a misterious
> process as follow :
> 1. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Waiting : >438 Million
> 2. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Physical IO : > 51000
> 3. User : Administrator (Join domain)
> Database : MSDB
> Status : Sleeping
> Common : Awaiting Command
> App : SQL Agent Alert Engine
> CPU Usage : > 16 Million
> Anybody know about these condition? Does it normal?
> Thanks
> Michael
>|||Thanks for your answer,
Amount of memory that be used by SQL Server is 1 GB.
I've check using sp_who, and on nothing on column blkby.
I think that not caused by Locks.
Note : After I restart the server, everything run well for a while. In
the worst case, the problem will be occured in 1 day, but usually
system running well within 3,4 days.

Process Info (SQL Server Enterprise - Management - Current Activity)

Dear All
I have problem with my database server which running SQL server 2000.
The server running very slow. The worst case, to save a record required
more than 20-30 seconds.
Since this problem, I usually monitoring Process Info from Enterprise
Manager (Management - Current Activity), and I found a misterious
process as follow :
1. User : System
AccessTo : Master
Status : Background
Common : Task Manager
Waiting : >438 Million
2. User : System
AccessTo : Master
Status : Background
Common : Task Manager
Physical IO : > 51000
3. User : Administrator (Join domain)
Database : MSDB
Status : Sleeping
Common : Awaiting Command
App : SQL Agent Alert Engine
CPU Usage : > 16 Million
Anybody know about these condition? Does it normal?
Thanks
MichaelMike
What is amount of memory?
Run SQL Server Profiler to see what is going on. (Blocking ,locks)
EXEC sp_who2 , see blkby column (If I remember well).
"Michael" <yapmichael2000@.gmail.com> wrote in message
news:1115718206.985119.318980@.o13g2000cwo.googlegroups.com...
> Dear All
> I have problem with my database server which running SQL server 2000.
> The server running very slow. The worst case, to save a record required
> more than 20-30 seconds.
> Since this problem, I usually monitoring Process Info from Enterprise
> Manager (Management - Current Activity), and I found a misterious
> process as follow :
> 1. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Waiting : >438 Million
> 2. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Physical IO : > 51000
> 3. User : Administrator (Join domain)
> Database : MSDB
> Status : Sleeping
> Common : Awaiting Command
> App : SQL Agent Alert Engine
> CPU Usage : > 16 Million
> Anybody know about these condition? Does it normal?
> Thanks
> Michael
>|||Thanks for your answer,
Amount of memory that be used by SQL Server is 1 GB.
I've check using sp_who, and on nothing on column blkby.
I think that not caused by Locks.
Note : After I restart the server, everything run well for a while. In
the worst case, the problem will be occured in 1 day, but usually
system running well within 3,4 days.