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 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

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.
>

Performance of stored procedure is improved after database is backed up and restored

I have a stored procedure that performs very poorly on SQL Server 2000 standard edition. It takes approximately 40 seconds to execute. In the course of troubleshooting the issue I backed up the database to disk and restored it to another SQLServer and the performance issue was resolved. On a hunch I went to the original server, backed up the database to disk, and then restored the database from the backup I just created and voila the problem was resolved. Why would this resolve the issue? I have another database with the same stored procedure with the same problem. I have tried the following on the stored procedure and all related tables with no success:

DBCC DBREINDEX

UPDATE STATISTICS

sp_recompile my_storedprocedure.

What is the restore doing to resolve the issue? What command can I run to resolve this. I have a feeling it is the query plan but wouldn't update statistics correct this?

Is it possible that the physical database files are getting fragmented?

The restore would then lay the files down contiguously, which would improve performance.

Other than that, the content of the database including statistics would be exactly the same after the restore as it was at the time of backup. SQL backup is a physical copy of every allocated byte in the database files.