Performance Problem on SQL Server 2000 SP 4 on Win2003

Hello,
we migrate an application from old to new hardware. In this step we migrate
also from SQL Server 2000 SP 2 on a Windows 2000 Server to SQL Server 2000
SP 4 on a Windows 2003 Server. Because of the new hardware almost every job
on the database ist much faster (up to 3 and 4 times) than before except one
job.
This job is recalculating values in the database. The job starts a stored
procedure which opens a transaction. In this transaction there is a loop
over a large amount of entries. For each entry another stored procedure is
called to recalculate the values. This stored procedure is also covered in a
transaction. The inner stored procedures does some insertions and updates.
On the old system the recalculations are done on a constant rate. On the new
system the recalculation rate degrades on a factor of 10 % every minute. So
the complete job lasts almost four times as long on the new system, although
the beginning rate of the calculations is much higher on the new system. The
problem is that this job now takes about 8 hours to complete so we cannot
reconfigure our system during the night any more.
We did a lot of testing and changes about this issue like database
maintenance (recalculation of indices, reorganisation of indices etc.) and
changes of the configuration (enlarge the transaction log files of the
database and the temp.db).
The issue occure on different systems with different configurations and is
not hardware dependent. It seems to be a problem in the combination of SQL
Server 2000 and Windows 2003.
Are there any suggestions about this issue?
Best Regards
Harald Gehringhttp://support.microsoft.com/kb/899...2852&sid=global
Please check the stored procedure / TSQL to check if you are running into
the above issue.

Performance Problem on SQL Server 2000 SP 4 on Win2003

Hello,
we migrate an application from old to new hardware. In this step we migrate
also from SQL Server 2000 SP 2 on a Windows 2000 Server to SQL Server 2000
SP 4 on a Windows 2003 Server. Because of the new hardware almost every job
on the database ist much faster (up to 3 and 4 times) than before except one
job.
This job is recalculating values in the database. The job starts a stored
procedure which opens a transaction. In this transaction there is a loop
over a large amount of entries. For each entry another stored procedure is
called to recalculate the values. This stored procedure is also covered in a
transaction. The inner stored procedures does some insertions and updates.
On the old system the recalculations are done on a constant rate. On the new
system the recalculation rate degrades on a factor of 10 % every minute. So
the complete job lasts almost four times as long on the new system, although
the beginning rate of the calculations is much higher on the new system. The
problem is that this job now takes about 8 hours to complete so we cannot
reconfigure our system during the night any more.
We did a lot of testing and changes about this issue like database
maintenance (recalculation of indices, reorganisation of indices etc.) and
changes of the configuration (enlarge the transaction log files of the
database and the temp.db).
The issue occure on different systems with different configurations and is
not hardware dependent. It seems to be a problem in the combination of SQL
Server 2000 and Windows 2003.
Are there any suggestions about this issue?
Best Regards
Harald Gehringhttp://support.microsoft.com/kb/899976?spid=2852&sid=global
Please check the stored procedure / TSQL to check if you are running into
the above issue.

Performance on RS

Hi All,
Everytime when I run the report (by URL access), it need a very long time to
wait. After such init step, then it return normal speed when I run another
report!
Anyone can help ! Thanks!
TonyI noticed this when I first started with RS and what I did to get around it
is to have a report open on my desktop that auto executes every 5 minutes,
this keeps this going. I have been told that the below will work. If you
want to do my way then get a simple report, in report->properties set it to
refresh every 5 minutes (or 10 or 15, whatever).
Here is what Chris suggests:
>>>>>>>>>>
If you are running Windows 2003 server for your IIS reportserver, then this
is a simple issue - I'll explain what happens:
The report service engine, once it is idle for more than the default 20
minutes, the worker process is shutdown.
This is controlled by IIS.
Open up the Internet Information Services (IIS) Manager
Expand the server node then the application pools.
On my IIS machine, I created an application pool dedicated to the
reportserver & reportmanager virtual webs.
But anyways, for the application pool that the reportserver is pointing to
if you left everything to their defaults will be the DefaultAppPool.
Right click the default app pool and select properties.
There are two things that are checked by default - On the recycling tab
there is a checkbox for recycling worker processes - it is currently set to
1740 minutes (29 hours). Leave it.
The other one is on the performance tab - which is the one you are
interested in changing...
See the "Idle Timeout" section and increase the number of minutes to be 8
hours a typical working day - 8*60 = 480 minutes.
Next, to be sure the "morning person" that runs the first report doesn't get
the delay, set up a schedule for either a dummy or adhoc report to fire off
like at 6am so that the report component worker processes get loaded.
I hope this helps you.
There is no need to have a report fire off every minute to keep things
alive - it is just that the report service was "unloaded" and needed to load
back up.
=-Chris
>>>>>>>>>>>>>>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:35CB04BD-BB0B-4D38-A4FF-03A52557E4A3@.microsoft.com...
> Hi All,
> Everytime when I run the report (by URL access), it need a very long time
> to
> wait. After such init step, then it return normal speed when I run
> another
> report!
> Anyone can help ! Thanks!
> Tony
>|||Thank you Bruce! I try it!
"Bruce L-C [MVP]" wrote:
> I noticed this when I first started with RS and what I did to get around it
> is to have a report open on my desktop that auto executes every 5 minutes,
> this keeps this going. I have been told that the below will work. If you
> want to do my way then get a simple report, in report->properties set it to
> refresh every 5 minutes (or 10 or 15, whatever).
> Here is what Chris suggests:
> >>>>>>>>>>
> If you are running Windows 2003 server for your IIS reportserver, then this
> is a simple issue - I'll explain what happens:
> The report service engine, once it is idle for more than the default 20
> minutes, the worker process is shutdown.
> This is controlled by IIS.
> Open up the Internet Information Services (IIS) Manager
> Expand the server node then the application pools.
> On my IIS machine, I created an application pool dedicated to the
> reportserver & reportmanager virtual webs.
> But anyways, for the application pool that the reportserver is pointing to
> if you left everything to their defaults will be the DefaultAppPool.
> Right click the default app pool and select properties.
> There are two things that are checked by default - On the recycling tab
> there is a checkbox for recycling worker processes - it is currently set to
> 1740 minutes (29 hours). Leave it.
> The other one is on the performance tab - which is the one you are
> interested in changing...
> See the "Idle Timeout" section and increase the number of minutes to be 8
> hours a typical working day - 8*60 = 480 minutes.
> Next, to be sure the "morning person" that runs the first report doesn't get
> the delay, set up a schedule for either a dummy or adhoc report to fire off
> like at 6am so that the report component worker processes get loaded.
> I hope this helps you.
> There is no need to have a report fire off every minute to keep things
> alive - it is just that the report service was "unloaded" and needed to load
> back up.
> =-Chris
> >>>>>>>>>>>>>>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:35CB04BD-BB0B-4D38-A4FF-03A52557E4A3@.microsoft.com...
> > Hi All,
> >
> > Everytime when I run the report (by URL access), it need a very long time
> > to
> > wait. After such init step, then it return normal speed when I run
> > another
> > report!
> >
> > Anyone can help ! Thanks!
> >
> > Tony
> >
>
>

performance on Reconcile/Compuware

The performance bottlenet is at the following query sent by the
Reconcile/Compuware third party application to SQL server
The most expensive step is on the sort step on the requ_tag column.
However if I put attv_value (text data type) to the last returning column,
the performance is OK.
Do anyone know why ?
How can I do from SQL server to improve performance without changing the
TSQL ?
(indexing ?)
SELECT RC_Requirements.requ_id,
RC_Requirement_type.reqt_id,
attv_value, -- text data type
requ_tag
FROM [RC_Requirements],
[RC_Requirement_type],
[RC_Attribute_value],
[RC_Attribute_definition]
WHERE RC_Requirement_type.reqt_id = RC_Requirements.reqt_id
AND RC_Attribute_value.requ_id = RC_Requirements.requ_id
AND RC_Attribute_value.attd_id = RC_Attribute_definition.attd_id
AND RC_Requirements.proj_id=43144
ORDER BY RC_Requirements.requ_tag"pc" <pc@.discussions.microsoft.com> wrote in message
news:25CB6A22-0DF5-4B18-A783-67F4846941D1@.microsoft.com...
> The performance bottlenet is at the following query sent by the
> Reconcile/Compuware third party application to SQL server
> The most expensive step is on the sort step on the requ_tag column.
> However if I put attv_value (text data type) to the last returning
> column,
> the performance is OK.
> Do anyone know why ?
> How can I do from SQL server to improve performance without changing the
> TSQL ?
> (indexing ?)
>
You can always try indexing requ_tag. This may avoid the expensive sort, as
the index will already maintain a sorted list of requ_tag.
David

performance on Reconcile/Compuware

The performance bottlenet is at the following query sent by the
Reconcile/Compuware third party application to SQL server
The most expensive step is on the sort step on the requ_tag column.
However if I put attv_value (text data type) to the last returning column,
the performance is OK.
Do anyone know why ?
How can I do from SQL server to improve performance without changing the
TSQL ?
(indexing ?)
SELECT RC_Requirements.requ_id,
RC_Requirement_type.reqt_id,
attv_value, -- text data type
requ_tag
FROM [RC_Requirements],
[RC_Requirement_type],
[RC_Attribute_value],
[RC_Attribute_definition]
WHERE RC_Requirement_type.reqt_id = RC_Requirements.reqt_id
AND RC_Attribute_value.requ_id = RC_Requirements.requ_id
AND RC_Attribute_value.attd_id = RC_Attribute_definition.attd_id
AND RC_Requirements.proj_id=43144
ORDER BY RC_Requirements.requ_tag
"pc" <pc@.discussions.microsoft.com> wrote in message
news:25CB6A22-0DF5-4B18-A783-67F4846941D1@.microsoft.com...
> The performance bottlenet is at the following query sent by the
> Reconcile/Compuware third party application to SQL server
> The most expensive step is on the sort step on the requ_tag column.
> However if I put attv_value (text data type) to the last returning
> column,
> the performance is OK.
> Do anyone know why ?
> How can I do from SQL server to improve performance without changing the
> TSQL ?
> (indexing ?)
>
You can always try indexing requ_tag. This may avoid the expensive sort, as
the index will already maintain a sorted list of requ_tag.
David