I have a new Windows 2003 64-bit server on which I'm running a 32-bit SQL
Server 2000. I backed up the database on the old (Windows 2000 SQL Server
7) server, copied the backup file across the network, and restored it on the
new server. I'm finding in some cases that I'm getting very, very poor
performance; taking some of the queries and doing an Estimated Execution
Plan in Query Analyzer shows that I'm getting almost no index usage--the
optimizer seems to be choosing to do table scans instead.
I didn't see this problem when we were copying these backup files onto a
32-bit Windows 2000 server running SQL Server 2000. Is there something
wrong with running 32-bit SQL Server 2000 on 64-bit Windows Server 2003?
What can I do to make it perform like it used to?
Thanks very much.Run UPDATE STATISTICS (see also sp_updatestats) and see if there is
any improvement.
Roy Harvey
Beacon Falls, CT
On Thu, 29 Jun 2006 16:37:09 -0400, "Alexander J. Oss"
<alex@.alexoss.net> wrote:
>I have a new Windows 2003 64-bit server on which I'm running a 32-bit SQL
>Server 2000. I backed up the database on the old (Windows 2000 SQL Server
>7) server, copied the backup file across the network, and restored it on the
>new server. I'm finding in some cases that I'm getting very, very poor
>performance; taking some of the queries and doing an Estimated Execution
>Plan in Query Analyzer shows that I'm getting almost no index usage--the
>optimizer seems to be choosing to do table scans instead.
>I didn't see this problem when we were copying these backup files onto a
>32-bit Windows 2000 server running SQL Server 2000. Is there something
>wrong with running 32-bit SQL Server 2000 on 64-bit Windows Server 2003?
>What can I do to make it perform like it used to?
>Thanks very much.
>|||Alex
SQL Server 7 is not supported on Windows 2003. Although you are restoring
your SQL 7 backup into SQL 2000 it may be that the database is not upgrading
correctly due to this. Can you upgrade the SQL 7 backup to SQL 2000 on a
server running Windows 2000 before trying to restore it? This may help.
Regards
John
"Roy Harvey" wrote:
> Run UPDATE STATISTICS (see also sp_updatestats) and see if there is
> any improvement.
> Roy Harvey
> Beacon Falls, CT
> On Thu, 29 Jun 2006 16:37:09 -0400, "Alexander J. Oss"
> <alex@.alexoss.net> wrote:
> >I have a new Windows 2003 64-bit server on which I'm running a 32-bit SQL
> >Server 2000. I backed up the database on the old (Windows 2000 SQL Server
> >7) server, copied the backup file across the network, and restored it on the
> >new server. I'm finding in some cases that I'm getting very, very poor
> >performance; taking some of the queries and doing an Estimated Execution
> >Plan in Query Analyzer shows that I'm getting almost no index usage--the
> >optimizer seems to be choosing to do table scans instead.
> >
> >I didn't see this problem when we were copying these backup files onto a
> >32-bit Windows 2000 server running SQL Server 2000. Is there something
> >wrong with running 32-bit SQL Server 2000 on 64-bit Windows Server 2003?
> >What can I do to make it perform like it used to?
> >
> >Thanks very much.
> >
>|||It is true that Microsoft does not give any support if you try to run
SQL Server 7 on Windows 2003, probably because of the policy to support
only the current (2005) and previous (2000) version. But I have seen
many posts claiming that it works just fine.
Besides, I don't see why it would affect the performance after the
database has been converted to SQL Server 2000.
Gert-Jan
John Bandettini wrote:
> Alex
> SQL Server 7 is not supported on Windows 2003. Although you are restoring
> your SQL 7 backup into SQL 2000 it may be that the database is not upgrading
> correctly due to this. Can you upgrade the SQL 7 backup to SQL 2000 on a
> server running Windows 2000 before trying to restore it? This may help.
> Regards
> John
> "Roy Harvey" wrote:
> > Run UPDATE STATISTICS (see also sp_updatestats) and see if there is
> > any improvement.
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> > On Thu, 29 Jun 2006 16:37:09 -0400, "Alexander J. Oss"
> > <alex@.alexoss.net> wrote:
> >
> > >I have a new Windows 2003 64-bit server on which I'm running a 32-bit SQL
> > >Server 2000. I backed up the database on the old (Windows 2000 SQL Server
> > >7) server, copied the backup file across the network, and restored it on the
> > >new server. I'm finding in some cases that I'm getting very, very poor
> > >performance; taking some of the queries and doing an Estimated Execution
> > >Plan in Query Analyzer shows that I'm getting almost no index usage--the
> > >optimizer seems to be choosing to do table scans instead.
> > >
> > >I didn't see this problem when we were copying these backup files onto a
> > >32-bit Windows 2000 server running SQL Server 2000. Is there something
> > >wrong with running 32-bit SQL Server 2000 on 64-bit Windows Server 2003?
> > >What can I do to make it perform like it used to?
> > >
> > >Thanks very much.
> > >
> >sql
Showing posts with label old. Show all posts
Showing posts with label old. Show all posts
Wednesday, March 21, 2012
Performance Problems Restoring SQL 7 Win2000 DB To SQL 2000 Win2003 x64
I have a new Windows 2003 64-bit server on which I'm running a 32-bit SQL
Server 2000. I backed up the database on the old (Windows 2000 SQL Server
7) server, copied the backup file across the network, and restored it on the
new server. I'm finding in some cases that I'm getting very, very poor
performance; taking some of the queries and doing an Estimated Execution
Plan in Query Analyzer shows that I'm getting almost no index usage--the
optimizer seems to be choosing to do table scans instead.
I didn't see this problem when we were copying these backup files onto a
32-bit Windows 2000 server running SQL Server 2000. Is there something
wrong with running 32-bit SQL Server 2000 on 64-bit Windows Server 2003?
What can I do to make it perform like it used to?
Thanks very much.Run UPDATE STATISTICS (see also sp_updatestats) and see if there is
any improvement.
Roy Harvey
Beacon Falls, CT
On Thu, 29 Jun 2006 16:37:09 -0400, "Alexander J. Oss"
<alex@.alexoss.net> wrote:
>I have a new Windows 2003 64-bit server on which I'm running a 32-bit SQL
>Server 2000. I backed up the database on the old (Windows 2000 SQL Server
>7) server, copied the backup file across the network, and restored it on th
e
>new server. I'm finding in some cases that I'm getting very, very poor
>performance; taking some of the queries and doing an Estimated Execution
>Plan in Query Analyzer shows that I'm getting almost no index usage--the
>optimizer seems to be choosing to do table scans instead.
>I didn't see this problem when we were copying these backup files onto a
>32-bit Windows 2000 server running SQL Server 2000. Is there something
>wrong with running 32-bit SQL Server 2000 on 64-bit Windows Server 2003?
>What can I do to make it perform like it used to?
>Thanks very much.
>
Server 2000. I backed up the database on the old (Windows 2000 SQL Server
7) server, copied the backup file across the network, and restored it on the
new server. I'm finding in some cases that I'm getting very, very poor
performance; taking some of the queries and doing an Estimated Execution
Plan in Query Analyzer shows that I'm getting almost no index usage--the
optimizer seems to be choosing to do table scans instead.
I didn't see this problem when we were copying these backup files onto a
32-bit Windows 2000 server running SQL Server 2000. Is there something
wrong with running 32-bit SQL Server 2000 on 64-bit Windows Server 2003?
What can I do to make it perform like it used to?
Thanks very much.Run UPDATE STATISTICS (see also sp_updatestats) and see if there is
any improvement.
Roy Harvey
Beacon Falls, CT
On Thu, 29 Jun 2006 16:37:09 -0400, "Alexander J. Oss"
<alex@.alexoss.net> wrote:
>I have a new Windows 2003 64-bit server on which I'm running a 32-bit SQL
>Server 2000. I backed up the database on the old (Windows 2000 SQL Server
>7) server, copied the backup file across the network, and restored it on th
e
>new server. I'm finding in some cases that I'm getting very, very poor
>performance; taking some of the queries and doing an Estimated Execution
>Plan in Query Analyzer shows that I'm getting almost no index usage--the
>optimizer seems to be choosing to do table scans instead.
>I didn't see this problem when we were copying these backup files onto a
>32-bit Windows 2000 server running SQL Server 2000. Is there something
>wrong with running 32-bit SQL Server 2000 on 64-bit Windows Server 2003?
>What can I do to make it perform like it used to?
>Thanks very much.
>
Tuesday, March 20, 2012
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.
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.
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.
Subscribe to:
Comments (Atom)